Impulz Technologies LLC

Menu
  • Home
  • Products
    • IMPULZ Positive Pay
    • IMPULZ Product unlock
  • Solutions
    • Microsoft Dynamics 365 Finance & Supply Chain Management
    • Microsoft Azure
    • Microsoft Power Platform
      • Power BI
      • Power Apps
  • Services
    • Application Support And Maintenance
    • Azure Cloud Services
    • ISV Development
    • Project Resources
  • About Us
  • Blog
  • 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

  • Optimizing Azure Workloads: Automate VM Startup and Shutdown
  • Landed Cost – Auto Cost Setup in Microsoft D365 Finance and Supply Chain
  • Big Data Capabilities of Azure Synapse, Databricks, and Data Factory
  • Fix Database Synchronization Errors in Microsoft Dynamics AX 2012
  • Dynamics 365 Finance and Supply Chain: Implement budget check via custom logic

Recent Comments

No comments to show.

Archives

  • April 2024
  • December 2023
  • November 2023
  • October 2023
  • July 2023
  • April 2023
  • February 2023
  • January 2023
  • October 2022
  • September 2022
  • August 2022
  • July 2022

Categories

  • Business
  • Dynamics 365
  • Dynamics 365 Finance and Supply Chain
  • Dynamics AX 2012
  • Introductions
  • Microsoft Azure
  • Microsoft Dataverse
  • Microsoft Power Platform
  • New Technologies
  • Power BI
  • Uncategorized

Latest Post

  • Optimizing Azure Workloads: Automate VM Startup and Shutdown
  • Landed Cost – Auto Cost Setup in Microsoft D365 Finance and Supply Chain
  • Big Data Capabilities of Azure Synapse, Databricks, and Data Factory
  • Fix Database Synchronization Errors in Microsoft Dynamics AX 2012
  • Dynamics 365 Finance and Supply Chain: Implement budget check via custom logic

Tags

Auto Cost Automation Ax2012 Azure Azure AD Azure Databricks Azure Data Factory Azure Synapse Cost Accounting D365 D365 code management D365 FO Azure DevOps Dataverse Development DevOps Duty Dynamics 365 Finanace and Operations development VM Dynamics 365 Finance and Operations Dynamics 365 Finance and Supply Chain Dynamics 365 FO Security Dynamics 365 with git Dynamics Lifecycle Services Export Freight Full Sync errors Import Item Cost Allocation Landed Cost Lifecycle Services Microsoft Azure Microsoft Dataverse Microsoft dynamics Microsoft Dynamics 365 Microsoft Dynamics 365 Commerce Microsoft Dynamics 365 Finance and Operations Microsoft Dynamics 365 version control Model Driven Apps Power Apps Power Platform Shipment SQL Error Transportation Voyages Web APIs XDS Framework
© Impulz Technologies - All right reserved.