In Microsoft Dynamics 365, we have multiple reporting options. It can either be Management Reporter, a solid GL-based financial reporting tool, or creating more complex, format-compliant reports using Electronic Reporting, which has all entities and data tables at your disposal. Both of these tools come in handy for functional consultants to meet clients’ reporting needs.

Apart from MR and ER, we can also leverage Power BI to integrate data from Dynamics 365 and create immersive analytical reports with stunning visualizations

In this Blog, I’ll try to explain steps to get data from Microsoft Dynamics 365 into Power BI

 

Lets create a very simple vendor balance report and by using same approach we can built more detailed and complex reporting.

Step 1: Connection Between D365 and Power BI

Power BI comes with built-in D365 connecter. For that first we will copy our Dynamics environment URL

We only need it till com/

Now open Power BI desktop app. Click on Get Data > More

Search Dynamics, select Dynamics 365 Online (legacy) then click connect

in Web API URL, Paste the URL and add data at the end

In next screen, select organizational account, Sign in and click connect

Step 2: Select Data

Once connection is made, Navigator pane will open which not only have access of raw tables but also the entities within our D365 environment

Lets’ Select

1. Vendors V3 Entity
2. Vendtrans Table

click on transform data

Step 3: Transform Data

Power BI contains a powerful transformation tool, Power query. We can use power query to clean and transform our raw data. Vendors V3 entity is basically a Master data entity having many columns which we many not require. I’m using choose column functionality to select only require columns and remove redundant data

Once data is transformed, as best practice, I’ll rewrite the name of query as Vendors V3 = Dim_Vendors

VendTrans = Fact_VendTrans

In home tab, click on “Close and Apply”

Step 4: Create Relation Between Dimension and Fact Table

Once the data gets loaded in Power BI, now we need to create link between both of our tables. Dim_Vendors contain the information related to vendor and Fact_VendTrans contains information regarding every transaction related to the vendors Click on Model view then Manage Relationship

Click on New Relationship

Select Dim_Vendor in From Table and Select VendorAccount column

Select Fact_VendTrans in To Table and select AccountNum column (AccountNum is basically vendor ID)

Our cardinality will be One to many, as there should be a unique vendor record in Dim_Vendors table.

Cross-Filter direction will be single as Dim-Vendor table will be filtering Fact_VendTrans table

We will mark relationship as active and click save

Now, our two tables will be related

Step 5: Create Vendor Balance Report

Go to Report view and select Vendor Account Number and Vendor Organization name from Dim_Vendor Table

Select amount Reporting Currency amount from Fact_VendTrans

After some Formatting a presentable Vendor Balance report can be created like this

This is one simple example I tried to explain around the use case. we can built financial reports using this method.


Thank you!!!