How to Integrate Power BI with Dynamics 365 for Finance and Operations

 

Microsoft Power BI is the primary Business Intelligence tool for Microsoft Dynamics 365 for Finance and Operations. It provides rich interactive dashboards, reports, drill-downs, and offers various filter options.

Power BI is a suite of business analytics tools that lets users analyze data and share insights. This tool is also robust and enterprise-grade, ready for extensive modeling and real-time analytics, as well as custom development. Power BI provides users and business analysts a great platform to create their own analytical reports, modify, or tweak the existing ones as per their requirement. The integration between Dynamics 365 for Finance and Operations and PowerBI give users the capability to personalize workspaces with tiles from PowerBI.com and they can also add links to the reports hosted in PowerBI.com. This helps users to access the PowerBI reports directly from Dynamics 365 for Finance and Operations.

Prerequisites

1. The Developer or the Technical Consultant must have Administrator rights on Dynamics 365 for Finance and Operations and Visual Studio.

2. The Developer must have a login created in PowerBI.com.

3. The Developer must have the Power BI desktop installed on their desktop. Also, be sure to use the same login that is used for PowerBI.com service.

Step Details

To integrate Microsoft PowerBI with Dynamics 365 for Finance and Operations, we must complete these two stages:

1. Connect PowerBI.com and Dynamics 365 for Finance and Operations.

2. Connect PowerBI desktop and Dynamics 365 for Finance and Operations database.

Now we will go through the step-by-step process for each of the above-mentioned stages.

1. Connect PowerBI.com and Dynamics 365 for Finance and Operations

In PowerBI.com service we need to register our system:

• Login to https://dev.powerbi.com/apps with your credentials.
• Register your application in PowerBI.

• After the App registration is done, it will create client id and key

• Once the Application is registered in PowerBI.com, the next step is to link PowerBI.com to Dynamics 365 for Finance and Operations. Log in to your Dynamics 365 for Finance and Operations instance.
• Go to System Administrator > Setup > Power BI.

• The data filled in the above form needs to be same as the data filled in PowerBI.com service while registering the App. This way, the workspaces of PowerBI.com service relates to Dynamics 365 for Finance and Operations.

2. Connect the Power BI desktop and the Dynamics 365 for Finance and Operations database.

The Power BI desktop is a tool that is required to develop new Power BI reports. In this section, details are provided to connect the PowerBI desktop with Dynamics 365 Finance and Operations database:

• Open the Power BI desktop and login with your PowerBI.com login credentials.
• Click on the Get data and select SQL server.

• The next screen will ask the Developer to enter the details of the SQL server and database. The Data Connectivity mode needs to always be on Direct Query. This technology enables reports to be created over large volumes of data. Reports that are created by using Direct Query technology will not cache data in the PowerBI.com service. Instead, data is always stored in Dynamics 365 for Finance and Operations.

• Once the Developer connects to the Dynamics 365 database, all the Aggregate measures available in Dynamics 365 for Finance and Operations will be presented. The developer can choose the required measure for the report.

• The Developer can select the Aggregate measure(s) to design and develop new PowerBI report as per requirement.
• Once the report is developed, the Developer can publish it to a specific workspace created in PowerBI.com.

• Once the report is published from the Power BI desktop, it will be available in PowerBI.com and we can pin it in PowerBI.com, then it will be available to choose in the Dynamics 365 Power BI tiles.

Conclusion

Dynamics 365 for Finance and Operations and Power BI are a great combination for users and business analysts. One great benefit is that Power BI reports are easily shared. There are a few Power BI content/reports already available for use in LCS (Microsoft Lifecycle Services). We can link them directly to Dynamics 365 for Finance and Operations for instant use. To connect to Power BI contents, the detailed steps are described here.

Apart from the PowerBI.com service, Microsoft also provides Power BI embedded. Power BI content (PBIX files) that partners and independent software vendors (ISVs) develop, can be embedded directly into the application. PBIX files that are associated with a model file are automatically published in Power BI Embedded as part of the application deployment process. With Power BI as an option for analytical reporting in Dynamics 365 for Finance and Operations, users experience a great visual presentation of data in different designs like graphs, pie charts, etc. Users also have the ability to tweak the reports as needed and can easily analyze the trends.