POWER BI: EXTRACT DATA OUT OF DYNAMICS 365 FOR REPORTING
You may have a requirement for purpose-built reporting that requires data extraction from a Dynamics 365 product, like Business Central, FinOps (Finance And Operations), or Dynamics 365 CRM/CE. While Power BI has excellent connectors to Dynamics 365 products, it fails in that it’s very slow to run report queries over large entities containing your data. Therefore, a data warehouse is required to really optimise your data for reporting.
You have a few choices in when it comes to extracting data:
1) Use the Web API (See https://api.businesscentral.dynamics.com/v2.0 /<your tenant name>/api/v2.0/companies(<your tenant id>)/ )
1) Use the Microsoft Graph API (currently in BETA, see https://graph.microsoft.com/beta/financials)
2) Use the out of the box OData feed API (See https://api.businesscentral.dynamics.com/v2.0/<your tenant id>/BC_Sandbox/ODataV4/)
3) Write your own custom API (see https://docs.microsoft.com/en-us/dynamics365/get-started/developers ). Business Central uses AL code.
4) If you’re using FinOps, or Dynamics CRM/CE you can do a live data export to a SQL Server in Azure which replicates all your data. This option is not available in Business Central at this stage, however, Business Central does support OData API.
In this article, I will be using the OData extraction method as it’s been around for many years, and is the Microsoft standard in accessing system data, including the Microsoft 365 cloud.
Business Central >> OData >> DataFlow Staging >> DataFlow Transformation >> Power BI
Business Central has an array of out-of-the-box OData APIs ready to use, with no coding needed. All pages in Business Central can be converted into an OData query. In other words, if you need a list of all the items you stock, you can go to the items page, add the fields you need, then add it as an OData API. Microsoft have advised version 4.0 of OData is way faster than 3.0, so please ensure you use 4.0.
DataFlows – Staging: According to Microsoft best practices, this is used to stage all your data and load it into CDM format, which include creating CSV files, along with meta data. Each entity in business central must have an audit column if you need to configure incremental refresh, where only data that has changed will be loaded.
DataFlows – Transformation: Since all your business data is pre-loaded into CDM format, this becomes the DataFlow cache. And reading from cache is always going to be faster than reading a query from the source system.
Also, this is the place where what I call the “Corporate Data Model” will live. It must be modeled as a STAR schema and optimised for high performance by using methods like aggregations, DirectQuery and the Enhanced Compute Engine.
This foundation DataFlow layer is important as your customers will be using self-service BI to eventually create and manage their own reports via Microsoft TEAMS.
Business Central Data Extraction
From an OData perspective, and for incremental refresh to work, you will need to have the data source with a System Date Time stamp column. That is, whenever a record is updated, then this column (maintained by source system), is automatically updated.
Here is a list Out Of The Box hidden pages in Business Central that actually have a System Data Time column, called SystemModifiedAt:
| Object | Page ID | Page Name |
| page | 30014 | APIV2 - Accounts |
| page | 30011 | APIV2 - Company Information |
| page | 30027 | APIV2 - Countries/Regions |
| page | 30019 | APIV2 - Currencies |
| page | 30055 | APIV2 - Customer Payments |
| page | 30009 | APIV2 - Customers |
| page | 30013 | APIV2 - Cust. Paym. Journals |
| page | 30021 | APIV2 - Dimensions |
| page | 30040 | APIV2 Dimension Values Entity |
| page | 30017 | APIV2 - Employees |
| page | 30018 | APIV2 - G/L Entries |
| page | 30025 | APIV2 - Item Categories |
| page | 30008 | APIV2 - Items |
| page | 30049 | APIV2 - JournalLines |
| page | 30016 | APIV2 - Journals |
| page | 30020 | APIV2 - Payment Methods |
| page | 30023 | APIV2 - Payment Terms |
| page | 30042 | APIV2 - Purchase Invoices |
| page | 30066 | APIV2 - Purchase Orders |
| page | 30064 | APIV2 - Purchase Receipts |
| page | 30038 | APIV2 - Sales Credit Memos |
| page | 30012 | APIV2 - Sales Invoices |
| page | 30028 | APIV2 - Sales Orders |
| page | 30037 | APIV2 - Sales Quotes |
| page | 30062 | APIV2 - Sales Shipments |
| page | 30024 | APIV2 - Shipment Methods |
| page | 30036 | APIV2 - Tax Areas |
| page | 30015 | APIV2 - Tax Groups |
| page | 30041 | APIV2 - Time Registr. Entries |
| page | 30030 | APIV2 - Units of Measure |
| page | 30060 | APIV2 - Vendor Payments |
| page | 30061 | APIV2 - Vendor Paym. Journals |
| page | 30010 | APIV2 - Vendors |
If you are after a page that just does not have a System Date Time stamp, then you will need to fire up Visual Studio Code, and publish some AL code to Business Central. For example, lets add SystemModifiedAt to the “Chart Of Accounts” page.
| pageextension 50102 GlAccountExt extends "Chart of Accounts" { layout { addafter("Gen. Bus. Posting Group") { field(SystemModifiedAt; rec.SystemModifiedAt) { } } } } |
After uploading the extension in Business Central instance, you will need to reload the OData web service:
Then browse the chart of account OData web service url and you can see the SystemModifiedAt field is there:
If you'd like to learn more about Power BI capabilities and what it could do for your business, get in touch with us today to discuss your requirements and how to get started.
Email Fiona - f[email protected] or call on 0415 870 135
