Power BI Custom Reports Via Entity Store (AXDW) on d365 Finance and Operations

We have standard Power BI reports available on Microsoft which has to be embedded onto UAT/ PROD environment to view them.

These reports use Entity Store OR Cubes as the data source. These cubes are basically termed as aggregate measurements.

What if we want to create a Custom BI Reports using Aggregate Measurements or Entity Store that points to the AXDW database?

Assuming that the Standard BI Reports embedding steps are followed in the UAT or PROD environment, we can follow the below mentioned steps for custom BI Reports.

Develop a Power BI Report on DEV

1. Develop Aggregate measurements in Visual Studio.
2. Refresh the Entity Store and cross check the SSMS – AxDW database for the newly created aggregate measurements.
3. Develop a Power BI Report in DEV Server by fetching data from SSMS (SQL Server).
4. Provide Server Name as Localhost OR . and Database name as AxDW and use Direct Query Mode.

Note: Direct Query Mode will not store the data in Power BI File and thus when we push it to Sandbox or PROD it will take the latest data from the respective environment after entity store refresh.
Whereas, using an import mode in Power BI file will store the DEV data and will never update the Power BI Report with the respective environment data.

5.  (Optional) If required write a query to fetch data from views from AxDW database under advanced options.

6. Create visuals and get the BI Report up and running in BI Desktop.

Deployment Process from DEV to SANDBOX

1. Let us consider a Power BI File say “Production_Variance_Report_V1”

2. Create a Form, Display Menu Item and Resource.

A. Form

B. Display Menu Item

C. Resource File

3. Attach the Power BI File to the Resource.
4. Check in all the objects associated with the Project from Visual Studio to Devops (Like Aggregate measurements, Aggregate dimensions, form, display menu item, menu and resource) and note down the change set number.
5. Deploy the package or the changeset to Sandbox.

Note: When we push or deploy the Power BI File as Resource to Sandbox the Server and DB name within BI File automatically changes and points to the respective Environment and display the latest data from respective environment.

6. Go to System Administration –> Setup –> Entity Store and make sure that the aggregate measures behind respective reports are refreshed.

7. Once the Entity store refreshes, refresh the Power BI Report in Sandbox environment, and view the latest data.

Re – Deployment Process from DEV to SANDBOX

1. If there are any modifications to the Power BI file then we need to update the Resource file, check in the object and re deploy the package to sandbox.
2. After the Power BI file is modified, replace the file at Ax Resource folder with the latest File that is used at the form level in D365 F&O.
3. Path of Ax Resource is mentioned below:
K:\AosService\PackagesLocalDirectory\ModelName\ModelName\AxResource\ResourceContent\PowerBIReport

4. Once replaced, the Resource object in Visual Studio will get updated with the latest file and “Red Tick” appears against the object mentioning that it’s checked out and ready for check in.

5. Check in the Resource file and note down the change set number.

6. Deploy the Change set number to Sandbox.

7. Go to System Administration –> Setup –> Entity Store and make sure that the aggregate measures behind respective reports are refreshed.

8. Once Entity store refresh, refresh the Power BI Report in Sandbox environment and view the latest data

Deploy Power BI Files from LCS to Powerbi.com

1. Once the package is available in Sandbox, place the Power BI file in LCS – > Assets Library -> Power BI Report Model.

2. Go to System Administration –> Setup –> Deploy Power BI Files

3. Select the file that you want to deploy and click on deploy BI Files.

4. After successful deployment, refresh powerbi.com and go to my workspaces and view the Power BI file/Report.

Note: Deployment to PROD environment follows same steps.