How to: Integration with Dynamics 365 OData APIs

Center of Excellence · July 2023

1. Abstract

This document illustrates two different methods of connecting Board to Dynamics 365 OData APIs and all aspects related to these configurations, such as authentication, testing and pagination.

2. Context

Integrating Board with Microsoft SaaS products is becoming an increasingly frequent ask. One of the most popular such products is Dynamics 365, which offers solutions in the ERP and CRM spaces. As a SaaS, REST services can be leveraged by applications like Board for data ingestion. In this guide we will go through all the required steps, both on the server side (D365) and on the client side (Board), to set up this integration.

3. Content

3.1 Authentication

Dynamics 365 exposes OData APIs which Board can consume via REST API calls. It requires OAuth2 authentication provided that the application is registered on Azure AD and the service principal is created for machine-to-machine integrations. Here is a guide by Microsoft on how to do so. The Azure AD owner needs to provide these parameters.

Tenant ID
App ID
Client Secret (Value)
Service URL (the same one they use to access the Dynamics web interface)

With this information, the authentication on the API client application should be configured as follows.

Type = “OAuth 2.0”
Grant Type = “Client Credentials”
Access Token URL = “https://login.microsoftonline.com/<Tenant ID>/oauth2/v2.0/token”
Client ID = App ID
Client Secret = Client Secret (Value)
Scope = “https://<Service URL>/.default”
Client Authentication = “Send as basic auth header”

This is essentially a POST request to the Token URL with Client ID and Secret as headers and Grant Type and Scope in the body. The response gives the access token, which is used in the Authorization header of the GET request to the API full path. This GET request should be configured as follows.

URL = “https://<Service URL>/data/<D365 table or entity>”

Headers: Authorization = “Bearer <access token>”

In Postman this header can be composed automatically by setting the options “Add auth data to = Request Headers” and “Header Prefix = Bearer”

As a general best practice, we recommend validating connection credentials and URLs with any REST client application such as CURL or Postman before implementing the connection in Board.

Here is a detailed guide on how to test D365 services with Postman. It includes an example of how to retrieve Finance & Operations data from the entity Customers.

3.2 Connecting with the Dynamics 365 Connector

As part of the Board cloud solution, there is a set of D365 connectors available on the Datapipeline portal.

This connector gives Board read and write access to D365 data entities and tables. Here is an online resource explaining the difference between tables and entities and what these APIs expose.

Here on Progress’ website, you can consult the full documentation on these connectors.

The configuration required is shown below.

In this example, we leverage the Client Credentials authentication flow by setting the prefix “POST” in the Token URI. Here are some additional info on this topic.

Alternatively, one can use a different D365 authentication flow by setting the token URI without the “POST” prefix. In this case, we need to press the “Fetch OAuth Token” button and the D365 login window will appear. Once credentials are provided and validated, the refresh and access tokens are fetched.

At this point, the connection can be saved and tested with the Test button.

If the test is successful, the D365 schema can be explored and queried from the SQL Editor.

This connection can then be used in Board as a standard SQL data source after creating the connection alias on the Cloud Administration portal. Details on this step can be consulted here. The main benefits of using this connector are the following.

It allows to explore and query the entire D365 schema in one single connection
You can use the Data pipeline SQL Editor interface to interact with the data in a user-friendly fashion
To Board, this is a SQL source, and as such it can leverage all the ROLAP cube read and write features

3.3 Connecting from a REST API Call

Board can connect to D365 APIs directly from a REST API Call within a procedure, bypassing entirely the Datapipeline server architecture. For more information on the REST API call, available from Board version 12.5 (Spring 2023), you can consult our online manual on this page.

The authentication is configured in the same way explained above.

If the connection test is successful, the REST API call in a procedure can be configured as follows.

Within the API path, you can set any D365 data entity. If this configuration is correct, when pressing the “Edit Configuration” button, the status returned is 200 and the response body will look as follows.

This also gives you a preview of the structure of the output file. We recommend selecting within the Advanced options only the data we need in the output file, to make the output file writing process lighter.

In this way, we neglect the field @odata.context that the D365 returns in every response, making the pagination configuration easier and more dynamic. In fact, in this way, we can keep the Stop After parameter blank.

3.3.1 API Call Pagination

For more information on the pagination configuration, which is available from Board version 12.6 (Summer 2023), please visit this link.

D365 APIs support two types of pagination, server-driven and client-driven. In this example, we are leveraging the latter, which is documented here and is a row-offset method. The Increment value set for $skip must match with the value set for $top. This means that Board HTTP requests will look like

https:// <Service base URL>/data/<data entity>?$skip=0&$top=500
https:// <Service base URL>/data/<data entity>?$skip=500&$top=500
https:// <Service base URL>/data/<data entity>?$skip=1000&$top=500
…

and so on until the entire dataset is fetched and the last call receives an empty response. The output file with these records can eventually be ingested with a text data reader.