Check my previous post here about Power BI configuration, prerequisites and components needed for report.
In this post, We will see how to create a Power BI report, Upload report to LCS and deploy report. We will use Microsoft cloud hosted environment to build report(tier 1 environment) with continuous updates. We have to use tier 2 environment to view the report in Dynamics 365 F&O workspace.
Note : There are some limitations to view report in tier 1 environment. Always use Tier 2 environment to deploy report and see results. Mostly it can be done by LCS admin. You can develop, test and verify using tier 1 environment before moving to tier 2 environment.
1. Develop a report
- Create a new data entity. Here I took an example of HcmBICurrentPositionEntity.
You can use table/View/Entity. It is based on your requirement
- Create new dimension.
- Create new measurement
- After completing above steps, We have to compile and sync model to see the changes.
- If there are no issues with step 4 then we can see DemoPositionMeasurement is created in Entity Store( System Admin> Setup > Entity Store)
Here we can make periodic refresh for Power BI report.
- Deploy the measurement to see definition and data in AxDW database.
- Lets open Power BI desktop and login with your credentials
Click on Home > Get data > SQL Server
Select the SQL server name, AxDW database and select Direct Query mode
- Select the measurement
- Drag and Drop fields to Visuals and create some filters based on your requirement
- Save the Report
- Add this PBIX file to Your model folder
For example : K:\AosService\PackagesLocalDirectory\YourModelName\ YourModelName \AxResource\ResourceContent\PowerBIReport
Note : Change to your directory path.
- Select the workspace where you want to display Power BI report
Make PoweBIReportGroup – Auto declaration to Yes.
- Write below piece of code to display report when form is initialized.
Here we have to provide Power BI report name(.PBIX) and Menu item name(Form menu item).
2. Upload PBIX file to LCS Environment
3. Deploy report
Now go to System Admin > Setup > Deploy Power BI files
Note : Make Sure, you are logged in with Admin account otherwise It will give an error below.
After you deploy the report successfully then go to Workspace and see the report is embedded within Dynamics 365 F&O workspace.
Note : Sometimes, It ask you to connect LCS again.
In this way, We can embed Power BI report in Dynamics 365 F&O. If you have any questions related to above steps then please contact me.