Extracting data out of Dynamics 365 Business Central

extracting data

written by Sumesh Dayaram

First things first, what is Dynamics 365 Business Central (previously known as Dynamics NAV/Navision)?

The best description I could find would define it as “a single, end-to-end solution for managing all your business processes, and part of Microsoft’s Dynamics 365 range of business applications. It’s also incredibly flexible and can be customised to suit any business need. Business Central is a trusted, proven ERP solution which has served 160,000 customers and is enhanced by Microsoft every year.” (from https://www.the365people.com/business-central)

Examples of industries who use it range from food and beverage, e-commerce, and financial service providers to name a few.

We were engaged by the latter, who wanted to do financial reporting based on the information contained within their Business Central instance, which acted as their core financial system. This involved creating a balance sheet, a profit and loss report and a productivity report.

This proved tricky as navigating it certainly isn’t intuitive and supporting information on the web is not readily available, but once you’re familiar with the navigation, you can easily extract data from it and ingest it for reporting purposes.

There will need to be a user available to access the Business Central environment.

Once in Business Central, navigate to the user tab, the easiest being via the search bar at the top of the page. Click on the relevant user to bring up the user card, to generate the Web Service Access Key which will be used to access the APIs to extract data.

 

Business Central user card

 

Thereafter, we find the relevant entity in the web services tab (also found using the search function) and click on its OData V4 URL to authenticate (using the username to login without the extension and the web access key) and retrieve the “odata.context” URL which can be used to extract data from Business Central, by an ingestion tool for example.

It is very important to use the odata.context URL when there are multiple environments containing entities with the same name, in one Business Central instance. Without this, it is hard to pinpoint exactly where, and from what entity you would want to pull data out of Business Central.

In this use case we extracted financial data out of Business Central using Azure Synapse via the odata.context URL’s. By doing this you can open more opportunities to shape and consume data that is stored in Business Central, with other tools such as Power BI, Azure Analysis Services and Excel.