Jet Analytics Business Central 365 Data Source

This article explains how to set up the Jet Analytics Business Central 365 data source and the Jet Analytics Business Central 365 Option Values data source. For brevity, these will be referred to as BC throughout this article.

What is the Jet Analytics Business Central 365 provider?

This provider is used to access the businesscentral.dynamics.com cloud service. It is not compatible with a local on-premises BC server. For a local BC server, use an SQL provider to connect instead.

Prerequisites

Before setting up the BC data source, ensure the following are in place:

• An active Microsoft Dynamics 365 Business Central online environment.
• Your Azure AD Tenant ID — this can be found by navigating to your BC environment, selecting the ? icon, and selecting Help & Support.
• For Service-to-Service authentication: a registered Azure AD application with Client ID and Client Secret.
• For OAuth 2.0 authentication: a valid local file path for storing the OAuth token (e.g. C:\oauth\oauth.txt). The file does not need to exist beforehand — Jet Analytics will create it after authorization.

Set up the BC data source in the Jet Analytics Portal

1. In Data Sources, click Add new Data Source.
2. Search for Business Central and choose the Jet Analytics Business Central 365 Data Source option.

   

3. In the Name field, enter a name for the data source.
4. In the API URL field, update the default URL by replacing {tenantId} with your actual Azure AD Tenant ID:

   https://api.businesscentral.dynamics.com/v2.0/{tenantId}

   The Tenant ID and environment name can be found in your BC environment by selecting the ? icon and then Help & Support.

   

   

5. Choose your authentication method:
   • OAuth 2.0 authentication: Select this option and enter a valid local file path for the OAuth token (e.g. C:\oauth\oauth.txt). Jet Analytics will create this file automatically when you authorize OAuth.

     

   • Service to Service Authentication: Select this option and enter the Client ID and Client Secret from your registered Azure AD application.
   • Username and Password: This option is no longer supported by Business Central and should not be used.

     

6. Once the data source is configured, map it to the Ingest instance you want to use it with.

   

Set up the BC data source in the Ingest instance

1. The new data source is now visible in your Ingest instance.

   

2. Right-click on the new data source and choose Authorize OAuth.

   

3. If your authentication method is OAuth 2.0, a sign-in prompt will appear. Sign in with the Microsoft user account you use to access Business Central. On success, a confirmation message will be displayed.

   

4. To select which Business Central accounts to use, right-click on the data source and choose Select Accounts….

   

5. Change the setting to Use specific accounts to restrict the data source to specific BC accounts.

   

6. To specify which tables to include, right-click on the data source and choose Select Tables.
7. Click the Include all tables rule and click Remove to delete it, then click Switch to Simple Selection. This avoids generating individual rules for every table in the source.

   

8. Search for the tables you want to add. Click Add to add a single table, or select multiple tables to add them all to the Selected Tables area at once.

   

9. Click Select Columns to deselect any columns that are not needed. Always keep SystemModifiedAt, as it is optimal for use with incremental load.

   

10. Once table selection is finalized, the tables are ready to use in a Data Area.

    

11. Execute the Synchronize task for the BC data source.

12. Right-click on the data source and choose Set up Primary Keys. Identify any tables with no primary keys and use the override feature to add one. Click OK to confirm.

    

13. Right-click on the data source and choose Set up Incremental Load. Click Add to add a new rule. Set the Column field to Contains (ignore case) and check both Handle primary key updates and Handle primary key deletes. A message will be displayed if any tables affected by the rule have no primary keys.

    

14. Run the Synchronize task again. Once complete, open the Data Source Explorer to review the tables. Multiple versions of each table will be visible, as tables are replicated for each BC account. Use Select Accounts to restrict to specific accounts if needed.

    

15. Run the Synchronize task once more, then run the Transfer task. The BC data source is now ready for use in your Prepare instances.

What is the Jet Analytics Business Central 365 Option Values data source?

The Jet Analytics Business Central 365 Option Values provider is used alongside the standard BC provider to populate readable names for type field values. Many tables contain a type field that stores integer values — for example, the Value Entry table has a Document Type field with integer values from 0 to 9. The Option Values provider supplies the description for each of these values across all tables and fields.

Set up the BC Option Values data source in the Jet Analytics Portal

The setup process is the same as for the standard BC data source. Follow the steps in the Set up the BC data source in the Jet Analytics Portal section above.

Create the BC Option Values data source in the Ingest instance

1. Search for Business Central and choose the Jet Analytics Business Central 365 Option Values data source.

   

2. Once the provider is selected, the remainder of the setup follows the same steps as the standard BC data source guide above.
3. Once the data source is created, it will appear as a new data source in the Ingest instance.

   

4. Right-click on the newly created data source and choose Authorize OAuth.

   

5. If your authentication method is OAuth 2.0, sign in with the Microsoft user account you use to access Business Central. A confirmation message will be displayed on success.



6. Run the Synchronize task, then run the Transfer task.

How to use the BC Option Values data source in a Prepare instance

For more information on using the BC Option Values data source in a Prepare instance, see the Use the Dynamics 365 Business Central Values provider section in the Lookup Transformation Template guide.