September 11, 2018

Power BI Reporting Options with Dynamics 365 for Finance and Operations

515 Views
Power BI Reporting Options with Dynamics 365 for Finance and Operations

Power BI is an analytical tool using which one can explore data and quickly create reports and dashboards. This is a rich platform and is used to create visuals using analytical data, helpful for business users.

Microsoft has now blended the Dynamics 365 Finance and Operations platform to support reporting using power BI, along with the traditional AX SSRS based reports that were prevalent in the previous AX versions.

Power BI dashboards, can be launched in Dynamics 365 for finance and operations by configuring Dynamics 365 FO to integrate with Power BI.

Why use Power BI

Both Power BI and Dynamics 365 FO are cloud-based services, and hence power BI dashboards can be launched from Dynamics 365 FO by authorizing Dynamics 365 FO workspace to connect to Power BI for accessing the visualizations through pbix files. This way, power BI visual representation can be leveraged in D365FO.

How to connect Power BI to Dynamics 365 FO Data

In one of our customers Dynamics 365 FO implementations, we came across many requirements especially from business users, where quick reports were needed to help users with analytical data like their inventory depletions, customer billing data etc.

To cater to those requirements, we tried to explore some of the reporting options in power BI with Dynamics 365 FO data.

OData feed

The common Data service in Dynamics 365 FO provides us with an option of creating data entities in Dynamics 365 FO, which can be exposed through OData (based on REST APIs). One can perceive data entity in Dynamics 365 FO similar in concept to Views. In fact, the entities created in Dynamics 365 FO as seen from Application Object Explorer in Visual Studio IDE, are underlying views in the SQL DB. This ODATA entity exposure provides the ease of use by enabling us to access these entities through browser via a simple URL.

In Power BI, this URL can be used to access the data entity, and get the data loaded, to be used as our power BI dataset.

Power BI Reporting options with Dynamics 365 for Finance and Operations

On clicking the ODATA feed, enter the URL of the data entity.

Power BI Reporting options with Dynamics 365 for Finance and Operations

Once the credentials are filled in, power bi reports can be created using Dynamics 365 FO exposed data entity

Entity Store

Under Visual Studio Project, add a new aggregate measure under analytics, as shown below.

Power BI Reporting options with Dynamics 365 for Finance and Operations

Next, for each measure, specify the data entity/table/view.

Power BI Reporting options with Dynamics 365 for Finance and Operations

  • For each measure created, add the dimensions, measures and attributes.

Power BI Reporting options with Dynamics 365 for Finance and Operations

Next, right click on the project and deploy entity store. You can see the deployed entity store under system administration - > setup -> Entity Store

Power BI Reporting options with Dynamics 365 for Finance and Operations

  • All entity stores are a part of AxDW database which is on azure. It uses in-memory, clustered column store index (CCI) functionality that is built into Microsoft SQL Server to optimize reporting and queries. AxDW database of D365 FO can be connected from power bi in direct query mode.
  • In Dynamics 365 for finance and operations, power Bi dashboards can be linked, through the report catalog as below. [workspace] -> options

Power BI Reporting options with Dynamics 365 for Finance and Operations

Clicking on the above, displays the dashboards in the power BI. Selecting a dashboard links the power BI reports from Power BI service, to Dynamics 365 for finance and operations.

However, Power BI reports created out of AXDW can also be launched from SharePoint, web-page and many other ways, for ease of use.

What is the best suited approach

Creating Power BI reports using ODATA feed can be used when the volume of data is relatively less. However, if reports need to be scalable and will need to handle huge volumes of data, creating entity store in Dynamics 365 FO and connecting to AXDW in Direct mode approach is the most recommended by Microsoft.

Leave a Reply

Your email address will not be published. Required fields are marked *