Denodo Dynamics 365 Business Central Templates - Quick Use Guide

Last modified on: 04 Aug 2021

You can translate the document:

Overview

Microsoft Dynamics 365 is a cloud-based business applications platform that combines components of customer relationship management (CRM) and enterprise resource planning (ERP). It was launched in 2016, when Microsoft combined Dynamics AX (its ERP application) with its Dynamics CRM application.

Dynamics 365 removes the divide between traditional ERP and CRM functionality into a unified, multi-functional application with specific business processes designed for Sales, Marketing, Finance, Customer Service, Field Service, Operations, Project management and more for all the roles and business units across your business.

As this results in a very large amount of data, menus, views… that will be a hard work to port all of them to Denodo. To make this process more friendly, we distribute this utility. Spending only a few minutes configuring one properties file and your Denodo installation with the needed tools, you’ll be able to access to your Dynamics 365 Customer Business Central.

Dynamics 365 Business Central templates

Importing the Dynamics 365 Business Central VQL templates will grant you the access to the following functionalities:

Finance

General journals
Chart accounts
G/L Account Categories
Fixed Assets
Employees

Cash Management

Bank Accounts
Bank Acc. Statements

Sales

Customers
Items
Sales Quotes
Sales Orders
Sales Invoices
Sales Credit Memos
Finance Charge Memos
Posted Sales Invoices
Posted Sales Credit Memos

Purchasing

Vendors
Purchase Quotes
Purchase Orders
Purchase Invoices
Purchase Credit Memos
Posted Purchase Invoices
Posted Purchase Credit Memos

For each one of the mentioned views, there will be a simplified one (only shows the fields already shown in the Dynamics 365 web interface) and a full one (shows all the fields of the entity). In some cases, there will be a lines view to see the line detail of invoices, orders, quotes...

In the Configuration section you’ll find how to generate your own Dynamics 365 VQL templates.

Configuration

Get OAuth2 credentials

In order to get the parameters needed to authenticate to Dynamics 365, you need to follow these steps:

Log into https://portal.azure.com/ with your username and password.

Grant permissions to the app in Settings → Required Permissions () and check Delegated Permissions.

Setup client secret key just in Settings → Keys. Write it down since it won’t be accessible again. It will be your Client secret value.

Token endpoint URL needs you to add your username into it: https://login.windows.net/<username>/oauth2/token. Username may be something like mycompany.onmicrosoft.com.

Auth. server URL would be like https://login.windows.net/<username>/oauth2/authorize?resource=https://api.businesscentral.dynamics.com. You also have to include your username like you did in the previous step.
You also need to configure the callback URL in Settings → Callback URL and set http://localhost:9090/oauth/2.0/redirectURL.jsp.

Now, you have all the data needed for the OAuth 2.0 Credentials wizard in Denodo:

Once you have filled all the input fields of the step one of this wizard, just click Generate the authorization URL and a URL will be displayed just below the link. Open it in a browser and you will see something like this:

Finally, you have to copy that code and paste it in the step 3 of the wizard and click in Obtain the OAuth 2.0 credentials. You can copy the credentials to the clipboard as you will need to use them later on the configuration process.

Configure dynamics_365_BC.properties file

Before importing the VQL templates in Denodo, you have to set the following parameters defined in the dynamics_365_bc.properties file:

company: you’ll find this value in the Companies section of Business Central. The value will be in the Name column of the companies list. Note that you may escape specials characters like blank spaces (CRONUS%20US, for example)

api_version: the version you want to use of the Dynamics 365 API for Business Central. By default, it’s set to v1.0.

azure_directory_id: Navigate in Azure through Azure active directory → Properties and you’ll see the value in Directory id.

access_token: generated in the last step of the “Get OAuth2 credentials” section.

refresh_token: generated also in the last step of the “Get OAuth2 credentials” section.

client_id: you’ve got it in the step 2 of the “Get OAuth2 credentials” section.

client_secret: you’ve got it in the step 4 of the “Get OAuth2 credentials” section.

dynamics_365_BC_vql.properties:

Note: Make sure you leave an empty line at the end of the properties file.

Import OData4 Custom Wrapper

There is one last thing to do before importing the VQL templates into Denodo. The templates will create an OData data source so, if you don’t have this custom wrapper imported in your Denodo installation, now you’ll have to do it.

Navigate to the Denodo Support Site (https://support.denodo.com/) and go through Downloads → DenodoConnects and you will see a list of the available connects. Select the tab that matches your Denodo installation version, look for Denodo OData 4 Custom Wrapper and click in the Download link. Note that you need to install the 20180903 version or higher if you are using Denodo 6 or 7 versions.

Your browser will download a zip file. You must open it and extract denodo-odata4-wrapper-<denodo_version>-<version>-jar-with-dependencies.jar, located in the dist folder.

The last step is importing this jar file into Denodo:

Denodo 6: File → Jar management → Create

Denodo 7 and later: File → Extension management → Import

Import the VQL file into Denodo

Dynamics 365 Business Central does not have all of the endpoints needed active by default out of the box, so you will have to activate some of them manually. With the entities specified in “Appendix II” section you’ll have to follow the instructions in the first step (Expose the desired entity as a web service) of the “Appendix I: Accessing other entities” section.

Once you have completed all the previous steps of this configuration section, you finally can load the dynamics_365_BC.vql file into Denodo. You only have to import it, along with the .properties file, using the Import option of the VDP Administration Tool:

Refresh

After the importation process finishes, refresh the Server Explorer by selecting File → Refresh and you will be able to see the data source and base views for accessing to Dynamics 365 Business Central:

Dynamics 365 BC views

Query

Now, you can execute queries over the Dynamics 365 Business Central base views that have been created.

Limitations

Denodo versions

If you use Denodo 6 you need to have installed the update 20180206 or higher versions.

OData4 Custom Wrapper

Both Denodo 6 and 7 need to use 20180927 version or higher of the OData4 Custom Wrapper.

Dynamics 365 Business Central exposed web services

There are a lot of entities involving Business Central, but out of the box only a few of them have active endpoints to access them. Some have to be activated manually (described in “Appendix II” section), so you need to go to the “Appendix I: Accessing other entities” section of the manual in order to add it by yourself. Those steps are also valid for adding new entities not included in these templates.

Insert operations

Dynamics 365 Business Central reports some entities with not-nullable ID fields, so an insert operation won’t work without specifying an ID value. But, in the source, that field is auto generated and causes an error when specified a value in an insert operation.

Business Central API version

The current version of the Denodo Dynamics 365 Business Central templates only supports version 1.0 of the Microsoft Business Central API. Using these templates with newer versions might result in partial support of existing entities and fields.

Appendix I: Accessing other entities

There are a large number of entities involving Dynamics 365 Business Center and, out of the box, there are only exposed web services to consume the data for a few of them. So some of them need to be activated manually. And you’ll have to follow these steps also if you need to access an entity not included in this template set.

Expose the desired entity as a web service.
Go to your Business Center web application account and press the search button. Then type “web services” in the displayed dialog to get the link to the WS list and click on it.

Once you get there, just click the New button and type the entity name you want in the empty line displayed. Here you can see and example for the Employee entity:

After selecting the entity, you have to enter a Service Name and mark the Publishable checkbox. When saved, the application will show you the ODataV4 url for the web service you just have created.

Create the base view in Denodo VDP.
To continue, make sure you have imported the VQL templates, as explained in the “Import the VQL file into Denodo” section. Now that we have the web service, we can create a base view to access the data. So go to Denodo VDP, open the ds_dynamics_365_bc data source created during the importation of the VQL script and select Create base view.

You will see a new dialog that you may fill as follows:

Service endpoint: https://api.businesscentral.dynamics.com/v1.0/<azure_directory_id>/ODataV4/, where azure_directory_id will be the value you’ve got in the “Configure dynamics_365_BC_vql.properties file” section.

Entity collection: Company('<company_name>')/<entity>, where company_name will be the name of the company (‘CRONUS US’, for example), and the name of the entity you want to access. In this example, we set up employeeCard for the Employee entity.

Entity name: the name explained right before, it would be employeeCard in this example.

Service format: JSON

Access token: generated in the last step of the “Get OAuth2 credentials” section.

Refresh token: generated also in the last step of the “Get OAuth2 credentials” section.

Client id: you’ve got it in the step 2 of the “Get OAuth2 credentials” section.

Client secret: you’ve got it in the step 4 of the “Get OAuth2 credentials” section.

Token endpoint URL: https://login.microsoftonline.com/<azure_directory_id>/oauth2/token, same azure_directory_id as the used in Service endpoint.

Click OK and you will see your new base view. You can adjust final settings like view name, primary key or remove columns, for example, and finally click the save button.

Appendix II: List of Endpoints for the most relevant entities

As you’ve seen in the “Access to other entities” section, we had to search in the web services section of Business Central for the Employee endpoint, that matches the object “Employee” with the ID 5200.

Here is a list for the most relevant entities IDs with their endpoints disabled by default, so you’ll only have to follow the previous steps for each entity you want to add. Important, you have to select Page on the Object type combo before opening the object selector. Use the service names described here when you add them to guarantee the templates set to work:

Entity	Object ID	Service Name
Finance
G/L Account Categories	790	accountCategories
Employees	5200	employeeCard
Fixed Assets	5600	fixedAssetCard
Cash Management
Bank Accounts	371	bankAccountList
Bank Acc. Statements	383	bankAccountStatement
Sales
Finance Charge Memos	448	financeChargeMemoList
Posted Sales Invoices	143	postedSalesInvoices
Lines	526	postedSalesInvoiceLines
Posted Sales Credit Memos	144	postedSalesCreditMemos
Lines	527	postedSalesCreditMemoLines
Purchasing
Posted Purchase Invoices	146	postedPurchaseInvoices
Lines	529	postedPurchaseInvoiceLines
Posted Purchase Credit Memos	147	postedPurchaseCreditMemos
Lines	530	postedPurchaseCreditMemoLines

USER MANUALS