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.
On clicking the ODATA feed, enter the URL of the data entity.
Once the credentials are filled in, power bi reports can be created using Dynamics 365 FO exposed data entity
Entity Store
- Configure Power BI with Dynamics 365 for finance and operations: Register the power bi application to use Dynamics 365 FO, using the available step-by-step process mentioned in the MS link https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/configure-power-bi-integration
- The dataset for any power BI report which pulls Dynamics 365 FO data, will be from a Data entity, View or a Table. Hence, using these, aggregate measures can be created which can then be deployed as an entity store in Dynamics 365 FO.
Under Visual Studio Project, add a new aggregate measure under analytics, as shown below.
Next, for each measure, specify the data entity/table/view.
- For each measure created, add the dimensions, measures and attributes.
Next, right click on the project and deploy entity store. You can see the deployed entity store under system administration – > setup -> Entity Store
- 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
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.
creating entity store in Dynamics 365 FO and connecting to AXDW in Direct mode approach is the most recommended by Microsoft.
But in production environment we can’t have access of axdw db…then how to connect
Hi Rajeev, You are are right that we can not connect to AX DB or AxDW Db for production environment directly. We are only allowed to connect to sandbox DBs. The only way to connect a PowerBI dashboard to production AxDW is to use embedded PowerBI reports in D365 F&O workspaces.
It is possible to sync entities and aggregate entities to your own Azure DB. Below link provides some details on how to configure sync between AX entities and Azure DB. We are doing it for some of our customers for many of their reporting needs that can not be easily met by embedded reporting in D365 F&O.
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/export-entities-to-your-own-database