POWER BI: EXTRACT DATA OUT OF DYNAMICS 365 FOR REPORTING

|
Tony Consentino
|
8 minute read

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 - fiona.stewart@digital-first.com.au or call on 0415 870 135
 
 

Tony Consentino