Power BI with Microsoft Dynamics 365 for Finance and Operations

Introduction

Data from Microsoft Dynamics 365 cannot be directly exposed with other systems or applications since there is no direct connection of the tables. It goes for Power BI reports also. Power BI desktop applications supports multiple data source connections by which the data can be used and displayed in Power BI reports such as using Data warehousing, using OData feed connection, using Azure SQL database.

This blog contains the steps to create a Power BI report using OData feed connection. OData connection will take the Data entities of Dynamics 365 as data source to create reports.

Data Entity creation

Create a new project and give the appropriate name of the project.

For demo purpose, we will create a report showing customer and customer transactions data. Create a new data entity and give the appropriate name.

Clicking on Add will open up a data entity wizard which will ask for properties value for Public entity name and Public collection name. Set these values as shown below:

When creating a new entity, a staging table and security privileges get created automatically. If we want this entity only for Power BI report purpose, we can unselect the Data management capabilities checkbox since we do not want this entity to be used for data import/export purpose.

Click on Next and wizard will ask for the fields that needs to be added to Data entity.

As of now we will not need all the fields so for the time being, we will uncheck the Select all checkbox.

Click on Finish to close the wizard. All the required elements will be added to project.

Double click on data entity to add the required table and fields.
Before we add fields, we need to set some properties of the data entity. Open the properties window of data entity. The default properties will look as follows.

From these, we need to change Primary company context property. This property will only show the records in Power BI report based on the default company selected on the user who had logged in to view the report.

Remove the dataareaid from the property and save the changes.

Since we want to display the customer transactions also in the report, add CustTrans table as a child to CustTable already added in Entity.

Once CustTrans has been added, we need to create the relations between these tables.

Expand the CustTrans datasource node and select the relations node. Right click on Relations and click on New relation.

Set the relations property between CustTable and CustTrans in the properties window as shown below:

The relation will be created in Entity as shown below:

Save the changes.

Now we will add the required fields to be shown in the report. To add the fields, just drag and drop the fields from the respective data sources to the Fields node.

Following is the list of fields added to the entity:

 

Save all the changes.

Next is to create the Entity key. Since we are showing customer transactions, we will add the following fields to Entity key, which will be unique always.

Once all the above changes are implemented, build and synchronize the project.

Report Designing

Next step is to create the designer file for the report in Power BI desktop.

Open Power BI desktop and login with your credentials. Make sure you have Power BI Pro license in order to create and publish the report.

To create a report, in Power BI desktop, navigate to Get data menu option and select OData feed.

It will prompt for the environment URL from which the data to be considered. This URL should be of following manner:

<Environment URL>/data.

It means we need to append /data at the end to the URL whenever we use OData feed. Here in our case the environment URL will be https://usnconeboxax1aos.cloud.onebox.dynamics.com/data

Clicking on OK will prompt for another dialog. In the dialog, select Organizational account and click Sign in button.

Sign in with your organizational account and then click Connect.

Once the connection has been successfully established, it will list all the available data entities deployed from D365.

Now search for the entity we created previously with the Public Entity Name property value. The Public Entity Name property value is set to CustomerTransactions in the search bar above. It will show the records present in the entity on selecting the entity.

Click on Load button to load the data on to the report. It will take some time to complete data load operation.

Once the data has been loaded, we will be able to see the list of fields available, which can be used in the report. These fields are the same what we have added in Data entity.

Since we want only to display the table data, we would take the Table to show the data from the Visualization section. Click on the highlighted Table icon.

Now to add the columns to the report, select the checkbox against the fields such that it gets added to Values section. Once the fields is selected, the relevant column gets automatically added to the report.

Similarly add all the fields in same manner.

By default, all the real values are summarized automatically. Since we want to show all the transactions individually, we need to change the aggregation property. For that, click on AmountMST field in Values section and select Don’t summarize option.

Perform the same operation for SettleAmountMST column. Save your changes. Give the appropriate file location and file name.

The column headers are the field names which is visible by default. To rename the column headers, select the AcountNum column in Visualizations section and select Rename option.

Rename it to Customer account and press Enter to change the column header name. Below is the output:

Several other properties we can set on the columns for better visualization. Let us change the Style of the table. To perform that, select the Format option present in Visualization section.

In style tab, the default style is set to None. Change it to Alternating rows. The change implemented would look as follows:

Save your changes. Next we can add a filter option to filter the records. Let us add a filter of Customer account by which user can select one or multiple customers from the present data. To implement that, drag and drop the AccountNum field to the Filters on all pages section as below:

Save the changes. Now the basic Power BI report is ready to be used for the users. In order for users to have the access to this report, we need to publish this report. To publish, click on the Publish button shown at the top menu.

If prompted for credentials, input your email address and proceed. It will ask for the workspace where this needs to be published. Select My Workspace for the report to be published.

It will publish the report.

To view the deployed report, open app.powerbi.com

From the home page, select My workspace and you will be able to see recently deployed report.

Clicking on the report will open it with all the changes implemented.

We can see the Format style is applied correctly on the table along with AccountNum filter added to the report.

Now users need this report to view from D365 UI. For that, users need to add this report into existing workspaces available in D365.

Add Report to D365

To add this report in D365, navigate to any of the workspaces present. For demo purpose, we will add this report to highlighted workspace inside Sales and marketing module.

On the workspaces form, click on the options button available and select Open report catalog option.

A form will open which will list all the reports to which users has access to. If you face any error, please check the PowerBI.com configuration option in System administration module.

You will see the recently created report in the list.

Select the report and click on OK. Refresh the page to see the new report added to the workspace page.

We can see the new section of Power BI reports has been added to the page.

Clicking on the report will open the Power BI report.

---

About the Author - Lalit Jivnani

A dynamic IT professional with progressive experience in Microsoft Technologies, with expertise in Microsoft Dynamics 365 Finance and Operations and Dynamics AX as a Senior Technical Consultant.