Hello all,
Decided to write a thanksgiving (In the US) blog to share my thoughts on this topic. If I had a dollar for every time I see this question asked on the Dynamics finance community forum, I will be a millionaire by now. I am going to give you my thoughts from a more functional side rather than a technical one, although it is hard not to get technical with this topic. The goal is to help new folks using Dynamics 365 F&O understand the differences between these.
- PowerBI using ODATA
This one is the most common question I see, how come when I connect to dynamics using ODATA, I don’t see all the Dynamics F&O underlying tables?
Before, I answer let me explain what ODATA connection looks like. Basically you can use the ODATA connector in PowerBI and then using your dynamics URL connect to the ODATA entities. Make sure you add “/data” to your url. Example: “https://YOURURLprod.operations.dynamics.com/data”
Another tip is that you can get a list of public data entities by just putting the above URL address into a web browser. I use this all the time to quickly search for the name of the entities. You can also use the web browser to preview the data stored in an entity. For example if I want to see the data stored in the Vendors entity, I will type this on the address bar.
This method I use to identify the data available in a data entity.
What is data entity and what’s the difference between a SQL table?
I will try to answer this from the business user side and not from the technical side. Basically a data entity is a flat table view of many tables within Dynamics D365 F&O. For example in the example above the actual vendor table doesn’t have the vendor name stored in it. The actual Dynamics data base is highly normalized in order to store large quantities of data efficiently. What the “Vendors” data entity does is flatten all the fields you would want to see for a vendor into one flat table of data.
Microsoft did this in order to facilitate data import into the system. The data entities that come OOB are meant mostly in my opinion to help with data migration efforts. To this point, I think they do a fairly good job.
This brings me to the next question:
What data entity do I use to view the detail general ledger transactions and export to PowerBI?
The cold answer is that there is no data entity that holds this info. Like I mentioned above the OOB entities are mostly for master data and not transactional data.
Could we custom build a data entity and expose the general ledger transactions and then use PowerBI?
The short answer is “yes”, but trust me you don’t want to use this method. Basically from a functional side, the ODATA connection is truly meant for data imports and not for large data exports. A medium size business would have thousands of general ledger transactions and that will take hours for ODATA to pull those from Dynamics and then import into PowerBI. (I wrote a blog post about how to actually do this, but with the big caveat above. https://empowerdynamics365axusers.com/2020/03/20/create-a-powerbi-powerpivot-financial-report-using-odata-entities-in-d365-fo-dynamics-365-finance-and-supply-chain-part-1/
So to summarize ODATA, it is great for data imports, data research, but not for reporting or BI.
2. What are my options for reporting using PowerBI then?
Glad you ask, I feel most new users or businesses that sign up for Dynamics 365 F&O have a hard time getting their heads around PowerBI embedded as a solution. I don’t blame them, trust me Microsoft has made this very hard to understand.
What is PowerBI embedded?
PowerBI embedded uses what MS calls the data entity store as the data source for reports and the analytical workspaces you see inside the application.
Sample Analytical workspace is the “Financial analysis” powerbi form
The entity store is basically the OOB data warehouse from Dynamics 365 F&O.
OK, this is great but how do I modify these reports?
That brings me to this confusing point for business users or even avid PowerBI report makers. In order to modify and connect to the data warehouse you need to do it from a developers environment. No, you can’t not do it from your desktop, connect modify and publish the reports to the system. This is the first hurdle you must overcome in order to attempt to makes sense of these reports. (I found a work around this, using the just in time connection to do it without a developers environment, although don’t know if this is supported by MS. Let me know if you want to find out and I ll write a blog post about it)
Let’s say you managed to connect, now how do I begin to understand the data model behind these reports?
My opinion and the starting point for me is to utilize the PowerBI desktop files available in the shared asset library within LCS.
These files will help you understand some of the relationships between the tables in the AXDW. I gotta say, these models are not easy to understand and from a PowerBI model development poorly made.
The second caveat is that since only Direct Query mode is supported for these models, you won’t be able to combine data from outside the AXDW, which in my opinion is the biggest drawback of this method. If the data you want to slice and dice from is not available on the AXDW then you must custom develop your own and publish to the AXDW.
3. What is BYOD and how is this relevant to reporting from PowerBI?
Most new Dynamics users don’t realize how important the concept is (was, a new kid was just released that might make this obsolete). Basically BYOD, stands for bring your own data base.
Going back to my general ledger example above, in order to achieve this efficiently you would want to leverage the BYOD concept in order to do this. Basically from a functional side you will want a developer to develop a custom data entity that contains the general ledger data and export that data to a separate Azure SQL database. Then use PowerBI to connect to that SQL database to build the report.
This sounds easy right, well that depends. The data entity must be customized to work with incremental refreshes and for exporting efficiency. Without going into many details, the asynchronous export process can be hard to set up and maintain.
Ok, let’s say you managed to get the export process running smoothly, How do I share my report now back to Dynamics 365 and other users?
This is where it gets confusing again, in the embedded PowerBI example above using AXDW, the PowerBI license is covered by your Dynamics subscription. Now when using BYOD as the data source then your users will require a PowerBI license or a premium Workspace (Inside PowerBI) in order to consume the data. Think about it this way, once the data is in your BYOD then it is really not connected to Dynamics 365 F&O anymore and therefore PowerBI licensing will come into place.
Although the licensing piece is a deterrent for many business, the flexibility that comes from building reports using this method (Being able to combine multiple data sources, using the import method in PowerBI) is worth it in my opinion.
3. The new kid, Azure Data Lake?
So what is this new kid in town. From a functional side, this makes the exporting and the synching of the data from Dynamics 365 F&O to your own data warehouse very smooth. No more having to build custom data entities, you can now sync SQL tables and data entities from Dynamics directly to your own data lake storage. MS will maintain and provide near real time data sync between the two. This is great to eliminate the BYOD maintenance burden. However the PowerBI licensing cost will still remain but with the ability to combine data from multiple sources, plus almost near real time from Dynamics then this is the way to go in the future. I have not gotten a chance to develop a report from Azure Data Lake Storage Gen2 but I am hoping soon I will have something to share.
Until then, happy thanksgiving. Visca Barsa.
Juan Sebastian Grijalba, CPA
Empower users to utilize Dynamics 365/AX to its fullest potential 