You may find yourself in a scenario where it is required to make Power BI reports using your Dynamics 365 Finance and Operations custom tables. In order to expose your D365 FO custom tables to Power BI, you are required to deploy an aggregate measurement for those custom tables in D365 FO. The steps to do so are as follows:
STEP 1: CREATE YOUR CUSTOM TABLE(S) IN D365 FO
This tutorial uses a custom table named LMSBookDetails.
STEP 2: CREATE AOT QUERY
Create the required AOT query keeping your custom table from the previous step as the datasource of the query to get the data you want. You can join multiple custom tables in this query according to the requirement. But for the sake of keeping this tutorial simple, we are querying a single table.
STEP 3: CREATE A VIEW USING THE AOT QUERY
Set the AOT query from step 2 as the Query property of the View and from View Metadata > YourQuery > DataSources > YourTable > Fields, drag the fields you need to the Fields node of the View.
STEP 4: CREATE AN AGGREGATE MEASUREMENT
Go to YourProject > Add > New Item > Aggregate Measurement, and create a new aggregate measurement.
Rename the aggregate measurement.
Set the View you created in the previous step as the Table property of MeasureGroup1.
Add your required Attributes.
Add your required measures.
Add your required dimensions.
Build your project and synchronize the database.
STEP 5: DEPLOY AGGREGATE MEASUREMENT
From the D365 FO environment, go to the Entity store (Modules > System administration > Set up > Entity store) and look up the aggregate measurement you just created and click ‘Refresh’.
STEP 6: CONNECT D365 FO TO POWER BI
Open SQL Server Management Studio and verify your view under Databases > AxDW > Views.
Open Power BI desktop. Go to Get data > SQL Server. Fill the required information as follows.
Select Windows > Use my current credentials and Connect.
Now, in the Navigator window, select your View and click Load.
This will load your custom tables to Power BI.
STEP 7: SCHEDULING AGGREGATE MEASUREMENT REFRESH
Schedule your custom aggregate measurement to be refreshed periodically using the Entity store form. Go to the Entity store and look up your custom aggregate measurement and click edit.
Under Refresh options, enable automatic refresh and set the period according to your requirement and save.
This will ensure that the data being entered into your custom table is also shown in your Power BI report(s). Now, you are ready to create your report(s).
Comments
Post a Comment