Embed stunning visualisations in Dynamics 365
Turn data into opportunity and drive better business decisions with Power BI
Microsoft Power BI has enabled everyone at every level of the organisation to access analytics. The flexibility and interactivity have resulted in establishing the new culture that turns data into opportunity.
It is not surprising that most Dynamics 365 Finance and Supply Chain Management customers request embedded Power BI visualisations straight in the ERP. This blog takes a closer look at the components required throughout implementation to embed stunning Power BI reports in D365 Finance and Supply Chain. As an introduction, a brief overview of Power BI’s make-up is needed: the ETL Tool, the Data Model and the Visualisations.
The ETL tool (called Power Query) is used for creating connections to data sources, filtering and transforming extracted data.
The Data model includes metadata:
(extracted from data sources and created inside the model),
between tables (are used for filtering related tables),
– values that are calculated using aggregation functions on tables data with respect of current filters context. Special powerful programming language DAX is used for measures set up.
The Data model has 2 options for storing and processing data from data sources:
- By default, data is stored inside the data model in server’s memory. The Data model is deployed to Azure Power BI service and all user’s requests are processed very quickly inside the cloud.
- Another option is DirectQuery mode. Here the Data model contains only metadata. Every user’s request is transferred and executed on data-source side; the Power BI service is used for creating a request and displaying the resulted data set only.
As you can see from statements above, DirectQuery mode is not as quick as an imported data option, but it does not require server’s memory volume for data model. Main restrictions of direct query mode are:
- Not all transformation in Power Query are supported
- Not possible to create tables in data model (they are in-memory)
- Not possible to use built-in date hierarchies (they are in-memory tables too)
- It not possible to use all DAX functions on measures creation
Detailed information about Power BI direct query mode can be found here.
Visualisation is a presentation layer and can be configured by developers (initial reports) or by business user (self-service BI). For visualization creators are available some built-in data presentation formats and option to download new visuals from Microsoft Power BI Visuals Store.
D365 Finance and Supply Chain Management: Power BI embedded
In D365 applications environments Microsoft has implemented a reduced Power BI service option: Power BI embedded. It is a service that supports Power BI data models based on direct query mode.
In D365 Finance and Supply Chain, an additional database AxDW (local Entity Store) was added for reporting purposes. Entities from Entity Store contain data required for reporting (measures and dimensions) and this data is updated via periodical batch jobs. For every business area, Data Model was developed and imported to AOT. Different visualisations based on these data models are available on the Analytics
tab in Workspaces. We’ll look at two approaches:
- Using and customising existing Data models
- Creating new Data models from existing entities in the Entity Store
Use and customise an existing Data Model
To set up a periodical Update procedure, open the Entity Store form (System administration > Set Up > Entity Store), choose Entity Store to update and click the Refresh button. When the window opens, setup batch job parameters and click on OK:
Report Visualisation modification:
Users who would like to modify Power BI embedded tiles should be assigned to the System report editor security group. Navigate to the Analytics tab of your workspace in the Options
menu and find the Power BI grouped items:
Select Edit Analytics. When the form opens you will see a screen that is very similar to the Power BI Desktop interface:
From here you can modify existing visualisations, create new ones and add pages or filters.
Note: The above steps are not personalised; changes will be visible to all users of the workspace.
Create a new Power BI embedded Data Model based on existing entities from the Entity Store
These steps can guide you through creating New reports based on data from several entities.
Step 1: Install Power BI desktop
You can create a New or extend and Existing Data model your development (1-box) environment. Download and install the Power BI Desktop application (requires Administrator permission) onto your 1-box environment.
Step 2: Create Power BI report
Launch Power BI desktop application as the Administrator. Connect to Microsoft SQL server:
In the Navigator form you can select, filter and transform views or tables you need for your data model:
After creating relationships between tables, calculated fields and measures you can setup visualisations and save your Power BI report to .pbix file.
Step 3: Upload .pbix file to LCS
Connect to LCS and choose your project and upload the Power BI file into Asset library > Power BI report model.
Step 4: Publish .pbix file from LCS to D365 environment
Your LCS project should be linked with your Dynamics 365 Finance and Supply Chain environment. You can check this on System administration > System parameters from the Help tab.
Power BI embedded service should be installed as a Web application in Azure.
To deploy Power BI files from LCS you need to navigate to System administration > Deploy Power BI files. Upon clicking to Deploy Power BI Files you’ll need to authorise Power BI, select the file and deploy it.
Step 5: Embed Power BI report into your workspace
Power BI report can be added to any workspace that contains a Links section. Simply open the Options section and choose Open report catalogue menu item, the list of all reports will be visible to you. You can choose reports you need for your dashboard and save it.
Power BI has enabled companies to create a data-driven culture with the foundation of “business intelligence for all”. Contact us here if you would like to uncover the opportunities hidden in your data.