Breaking news from around the world
Get the Bing + MSN extension
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | View virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
My company has recently implemented Dynamics 365 (CRM) and we would like to have some Power BI reporting against it. For various reasons, using a standard Power BI data connector to connect to D365 isn't the approach I shall take. I have, in theory, devised a solution architecture and would like to share it with you for you to provides your thoughts on what I propose.
1) I would like to use the Entity Store to de-normalise the D365 relational data to a more multi-dimensional model. (D365 >> Entity Store)2) Once the Entity Store has been created I will use the Data Export Services tool to export the Entity Store data over to a, pre-built based on the Entity Store schema, Azure SQL database. (Entity Store >> Azure SQL)3) I shall then use the SQL Server (or Azure SQL) connector from within Power BI to start the creation of reporting. (Azure SQL >> Power BI)
To me, this approach sounds plausible, however as this is just theory through research and that I haven't actually implemented it yet, I would like your thoughts on if this is achievable or if I have misunderstood what I have read. To elaborate on the above a little more...I do not wish to report directly against the D365 Production environment so to not slow down the application for end-users.At some point I would like to re-model the relational model to a multi-dimensional model so it is more optimised for reporting. Where do I do this re-modelling? I could do it in Power BI, or create the Data Warehouse in Azure SQL, but I am hoping to make use of the Entity Store as I have read this is what it does - it de-normalises the model. I cannot report against the Entity Store directly as I've read only embedded reports can use the Entity Store, and as I'm not using embedded reports I cannot do this - hence then using Data Export Services to export the data to a pre-built Azure SQL database. Can I use the Entity Store to do the de-normalising in this manner? Does the Entity Store exist for Dynamics 365 (CRM) as each article I read on the Entity Store always refers to Dynamics AX?
Can the Data Export Services export data from the Entity Store (if it can be used as I hope) to a pre-built Azure SQL database?
If you can confirm what I've stated above can be done, or correct me and point me in the right direction, then all it will be very much appreciated.
All contributions will be helpful.
Thanks in advance.
First, based on this article docs.microsoft.com/.../power-bi-integration-entity-store
Entity store is for Dynamics F/O and not for other parts of Dynamics. This might be your answer to not use Entity store if your Dynamics is anything but Finance&Operation.
I trust that you can't use the connector for whatever reasons you have, so my other suggestion will be why won't you build an operational database (which what Entity store is) and use a tool like Kingsway soft to extract the data you need from Dynamics into this new Database, model it the way you want and then use PowerBI to report on it?
Thanks for your reply. It is very much appreciated.
You confirming that the Entity Store is only available with the Dynamics F/O module is very useful. I now know not to consider it in my reporting solution.
You mention "why won't you build an operational database (which what Entity store is)"... My understanding is the Entity Store is a de-normalised model which is very different to an operational database (being a normalised model). Just want to clarify this?? Either way, I cannot use an Entity Store.
The standard Power BI connectors (Dynamics 365 (online), OData Feed, Common Data Services) to D365 all use an API which isn't optimal for reporting use. I am aware of connections like FetchXML/Power Query, or Power BI Accelerator, however I would prefer to migrate data from D365 to a separate SQL database and use Power BI on that SQL database. This way I would be able to use T-SQL to narrow down my dataset extracts rather than bring the whole dataset into Power BI as I would have to with the other connection methods. Plus I would be using Power BI against a non-Production environment.
At this stage in my experience with D365 data, and how it's constructed in the underlying SQL Server relational database, I feel it may be a step too far for me right now. I will need to be a bit more familiar with my organisation's data (I'm new at this company so am yet to fully understand the data domain) and how D365 models it before I attempt to create a big multi-dimensional model (a data warehouse).
I have two further questions if you (or anyone) can answer.
1) Through using the standard Power BI Dynamics 365 (online) I find the tables returned have columns with standard values (i.e. actual text or numbers), columns with 'Record', and columns with 'Table'. For the columns with 'Record' or 'Table' I have to expand them to retrieve other values. I've never experienced this before, but then I've never used D365 as a source before. My question is, when I attempt to export data from the D365 application's transactional database will I have to manually expand columns, or will all columns be expanded automatically when exporting from D365 to a separate SQL Server database?
2) Do you know of a tool that is intelligent enough to re-model the relational D365 model to a multi-dimensional model more suited for reporting? A tool that can automatically re-model the schema during the export process with much input from me?
3) With all that I have mentioned and explained, do you know of an industry 'best practice' approach (architecture) to using Power BI against D365?
Thank you again for your help.
Business Applications communities