POWER BI: EXTRACT DATA OUT OF DYNAMICS 365 FOR REPORTING

17 May 2021

|

Tony Cosentino

|

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 //api/v2.0/companies()/   )

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//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:

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.

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

Tony Cosentino

Senior Consultant

Tony has worked as a Solutions Architect for over 11 years and has been integral to a number of key SharePoint, RecordPoint, Office 365 and Power BI projects. He has experience working with major clients in some of the largest SharePoint solutions that have ever been rolled out, inclusive of Fusion Retail Brands, the Bureau of Meteorology and Fujitsu Australia. This professional experience is further complemented by his impressive resume in the motivational speaking realm, inclusive of being a TEDx Public Speaker coach.