Create a PowerBI (PowerPivot) Financial report using ODATA entities in D365 F&O (Dynamics 365 Finance and Supply Chain) Part 1

Hello World,

Hope all of you are safe out there. I am going to break this post into several parts so that I am able to cover the scenario in its entirety. I have searched the internet several times to find a solution to this issue and couldn’t find anything so I decided to write a blog post about it.

The common question is how do I build a report using General ledger data (Accounting data) in PowerBI or Power Pivot for Excel. Which data entities do I use, how do I build the data model, how should I build the DAX measures?. How do I do this using the OOB data entities in dynamics 365 F&O (to me it will always be Finance and Operations, even though MS keeps changing the name)

For my example below I am going to use Power Query and Power Pivot inside Excel, just because my accountants love Excel. To be honest I think this solution is best fitted for Excel as well. But the steps can be used in PowerBI desktop to achieve the same result.

Step 1

Connect to the data entities in Excel. To connect to the data entities simply add “/data” to the end of  your D365 URL. Example:

Let me say a few things before we start:

1.  The URL is case sensitive

2.The list of data entities that you will see when you connect are not the actual AXDB data tables. In other words you are not connecting to the D365 F&O SQL data tables. I won’t go all nerdy and try to explain what the difference is, there are plenty of blogs out there and documentation from MS that goes well into this detail.

3.Connecting via ODATA to D365 F&O is slow and to be honest in large data sets this probably will take a long time to load the data. It may not even load for large data sets. Throwing this caveat out there, MS will probably recommend to use the BYOD or a Data Lake storage for a larger enterprise solution. 

Now that we got out of the way, it is time to start having some fun.

Open up Excel, then head over to the Data tab>Get data>From other sources>From Odata Feed

then paste your D365 URL as explained before

If this is the first time you are connecting then you will need to authenticate. Most people will do so using an organizational account. Once you click sign in, login using your D365 F&O credentials.

Once connected you will see a list of data entities to choose from. Search for one Called GeneralLedgerActivities (This data entity contains summarized totals for all general ledger totals for all accounts and financial dimension breakdowns in your D365 F&O environment) Caveat: It’s summarized data, not individual voucher transnational data. 

Click on transform data to open up the Power Query editor.

I am going to walk you to most of the clean steps performed, but for the more advanced users the goals is to be left only with the columns we need to do our analysis.

First thing is to remove the columns we don’t need, for that I used the UI to remove all the columns and be left with only “LedgerDimension”, “MainAccountRecId”, “Ledger”, “PostingLayer”, “LedgerGregorianDateId”, “AccountingCurrencyAmount” columns

Next is to make sure we format the columns with the correct data types. Basically all should be Text, except for the AccountingCurrencyAmount which is a decimal number data type

I am going to stop here today, but I will give you a preview of what the completed data model should look like.  It will be a star schema data model with one fact table that will contain actual and budget amounts in order to create Actual Vs Budget comparisons

I apologize for not putting all the content all at once but this will take various posts to build. In the meantime I will share the rest of the data entities so that you can build the model above if you wish to work ahead.

BudgetActivities – This one contains all of the summarized budget entry data (Pro-tip append this data to the GeneralLedgerActivities to have one fact table with both Actual and budget data)

Ledgers – This data entity contains the name of the legal entities in your environment (Pro-tip Use the Ledger.LedgerRecId field to join with the GeneralLedgerActivities.Ledger field)

MainAccounts – This one contains all of your COA data.(Pro-tip Use the MainAccounts.MainAccountRecID field to join with the GeneralLedgerActivities.MainAccountRecID field)

DimensionCombinations – This one is the magic one, will contain the financial dimension breakdown of the transactions. (Pro-tip Use the DimensionCombinations.RecordID field to join with the GeneralLedgerActivities.LedgerDimension field)

FiscalCalendars – This one contains the calendar data. This data entity you will use to build your Date table. You will need some M magic to extract the calendar dates. (Pro-tip Use the FiscalCalendars.LedgerGregorianDateId field to join with the GeneralLedgerActivities.LedgerGregorianDateId field) the real trick will be to have a proper calendar table to take advantage of the time intelligence functions within DAX.

Until next time,

Juan Sebastian Grijalba, CPA