Impulz Technologies LLC

Menu
  • Home
  • About Us
  • Solutions
    • Microsoft Dynamics 365 Finance & Supply Chain Management
    • Microsoft Power Platform
      • Power BI
      • Power Apps
  • Products
    • Impulz Positive Pay
    • Impulz Construction 365
  • Blog
  • Services
    • Application Support And Maintenance
    • ISV Development
    • Project Resources
  • Clients
  • Contact Us

Impulz Technologies LLC

Deploying aggregate measurement for custom tables in Dynamics 365 F&O for Power BI Reports

  • Home
  • Blog
  • Dynamics 365
  • Deploying aggregate measurement for custom tables in Dynamics 365 F&O for Power BI Reports
  • October 27, 2022
  • Sawera Ansari

   

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).

Posted in Dynamics 365, Power BITagged D365, D365 FO, Power BI, Reporting

Recent Posts

  • Postman Best Practices for Microsoft Dynamics 365 Finance and Supply Chain Management
  • Microsoft Dataverse is not a database
  • Connect the Angular with Microsoft Dataverse – Part 3 of 3
  • Connect the Angular with a Microsoft Dataverse using Web API – Part 2 of 3
  • Connect the Angular App with a Microsoft Dataverse through Web API – Part I of 3

Recent Comments

No comments to show.

Archives

  • February 2023
  • January 2023
  • October 2022
  • September 2022
  • August 2022
  • July 2022

Categories

  • Business
  • Dynamics 365
  • Microsoft Azure
  • Microsoft Dataverse
  • Microsoft Power Platform
  • Power BI
  • Uncategorized

Latest Post

  • Postman Best Practices for Microsoft Dynamics 365 Finance and Supply Chain Management
  • Microsoft Dataverse is not a database
  • Connect the Angular with Microsoft Dataverse – Part 3 of 3
  • Connect the Angular with a Microsoft Dataverse using Web API – Part 2 of 3
  • Connect the Angular App with a Microsoft Dataverse through Web API – Part I of 3

Tags

Application integration with D365 Azure Azure AD Banking D365 D365 code management D365 FO D365 FO Azure DevOps D365 FO Customization D365 FO Extensions D365 FO VM Deployment D365 integration D365 MPOS Retail D365FO Excel Export to SharePoint Dataverse Dataverse integration Debugging Development Dynamics 365 Finance and Operations Dynamics 365 FO Security Dynamics 365 with git Lifecycle Services Microsoft .Net Microsoft Azure Microsoft Dataverse Microsoft Dynamics 365 Microsoft Dynamics 365 Commerce Microsoft Dynamics 365 Finance and Operations Microsoft Dynamics 365 Retail MPOS Microsoft Dynamics 365 Retail MPOS customization Microsoft Dynamics 365 version control Model Driven Apps OAuth 2.0 Positive pay Postman Power Apps Power BI Power Platform Reporting Safe pay SharePoint Virtual Machine Web APIs X++ XDS Framework
© Impulz Technologies - All right reserved.