Exposing Dynamics 365 Business Central data to the outside world: my two cents

I had a discussion in the last days on socials with some of my followers (yes, I have them :D) about what’s the best way to expose Dynamics 365 Business Central data to external systems for integrations and for data analysis.

Exposing data is an hot topic on every ERP implementation. With Dynamics 365 Business Central on-premise you have the possibility to access the ERP data by directly querying the SQL Server database (bad choice in general in my opinion), but on SaaS things are different. Database access is not an option and your data must be exposed in a “service oriented” way.

On a Dynamics 365 Business Central SaaS environment, for exposing data to the outside world you have essentially the following choices:

1. Publish a page object as web service and use OData or SOAP
2. Publish a query object as web service and use OData
3. Publish a dedicated API page and use REST APIs

The worst choice is in my opinion choice 1. I know that this is the simplest way, but by directly exposing a page you are using an object that can contain also logic for handling the user interface (interactive mode) and this can be extremely inefficient for OData. A page object will not permit you to have a fixed contract (interface) with your clients too.

Choice 2 is much better: you can create a query object and in this object you can use JOIN to link tables and aggregate data. Queries are more performant than pages because here you can do aggregations, sums and so on while on pages you’re doing only “select” operations.

Choice 3 is what I think you should start doing with Dynamics 365 Business Central. By creating an API page with AL you can create an object that exposes data by using OData protocol, that can be consumed by using REST APIs (simple HTTP methods), that can be distributed with your extension (natively configured, no need to perform administration tasks to expose the endpoints), that has a fixed contract, that supports namespaces and  versioning and that is natively “webhook enabled”.

Saying that, are APIs the solution to all your problems related to exposing data to the outside? NO. It depends a lot on your business scenarios I think

I think that API pages are the best way if you need to interact with external system via HTTP: create your custom entities APIs pages (or use the standard APIs for standard entities if they satisfy your needs) and an external system will have HTTP endpoints to interact with your ERP by simply sending HTTP requests (GET, POST, PATCH and so on).

If you need to interact with PowerApps or Flows (other question received), unfortunately the standard connectors for Dynamics 365 Business Central actually can see only standard entities exposed by using native pages and query web services (OData v4). If you want to use your custom entities (exposed via API) you need to create a custom connector (not so easy task).

If you need to expose data to an external system for data analysis (BI) I’m absolutely not a fan of a “direct integration” between the ERP and the BI system. Imagine to use for example PowerBI with Dynamics 365 Business Central (but it could be another BI tool able to access data via services). You can directly query the ERP data by using the native Power BI connector or by using OData endpoints (for example a query object exposed as an OData endpoint) but in this way you’re “directly connected” to the ERP and huge reports or huge data calculations can affect your ERP system.

In these cases, I think that the best choice is not directly using the OData endpoints for performing your data analysis. A more performant and scalable solution could be to extract the data from the ERP (for example by using a custom code that periodically calls your APIs, reads data and saves those data to an Azure SQL database or to Azure Datawarehouse). Then, your BI system (in this example is Power BI) can directly access this datawarehouse and perform all the data operations it needs for reporting purposes, without affecting the ERP system or locking users.

And if you need more power? Systems are decoupled, so you can scale the Azure SQL or Azure Datawarehouse instance as needed in order to guarantee the level of performances you need.

So, what’s the conclusion of this post?

• Use API pages as much as possible instead of exposing standard pages
• Think on your business case and architect a solution that guarantees performances and scalabilities.
• I know that publishing Dynamics 365 Business Central data by exposing a page object is the simplest solution for everyone (it’s just a click), but simplicity is not equal to efficiency. In many cases you will need more work to do, but the final solution will have lots of benefits.