TimeXtender Business Central 365 Data Source

This article explains how to setup the TimeXtender Business Central 365 data source, as well as the TimeXtender Business Central 365 Option Values data source. For reference, it will be called just BC to keep it short. It is also possible to use a CData Dynamics 365 Business Central provider, but this is a separate provider.

What is the TimeXtender Business Central 365 provider?

It is a provider used to access the businesscentral.dynamics.com web source. It is not usable for a local BC server. In the case of a Local BC server, you would use an SQL provider to gain access.

 ​​​​​​​Prerequisites​​​​​​​

Set up the BC data source in the TimeXtender portal

1. In Data Sources click Add new Data Source.
2. Search for TimeXtender Business Central 365 and choose that option.

   

3. In the Name field, type the name of the data source. Also add a Description as it is a requirements.
4. In the API URL field change the https://api.businesscentral.dynamics.com/v2.0/{tenantId} value. Replace {tenantId} with the actual Azure AD Tenant ID which can be found by navigating to the BC environment in https://businesscentral.dynamics.com/, selecting the “?” icon and selecting “Help & Support”. Enter the environment which can also be found in the same location.

   

    

   

5. You have the following options to authenticate.  
   • If the choice is to use OAuth 2.0 authentication, select this option and enter a legitimate file path (e.g. C:\oauth\oauth.txt) for the locally stored OAuth token to authenticate the connection. The text file does not have to be created beforehand, as TimeXtender will create it once you authorize OAuth.

     

   • If the choice is to authenticate using “Service to Service Authentication” then select this option. To use this, apply the Client ID and the Client Secret from an Application as explained above.
   • The other options are not necessarily possible, specifically, the Username and Password option is expired by BC. It used to work with a Web Service Access Key as the Password.

     

      

6. Once the data source is set up, be sure to map it to the ODX Instance you want to use it with.

Create the BC data source in the ODX Instance

1. Create a new data source by right-clicking on Data Sources and clicking Add Data Source.
2. Give it a name
3. In the Connection menu choose the BC data source from the list.

   

4. Choose All tables in the data source.

   

5. Say yes to create a transfer task and name it Incremental or similar. Say yes to all default settings.
6. Right-click on the source just created and choose the Authorize OAuth option.

   

7. If your logon option is OAuth 2.0 authentication it will then request you to log on. Log on as the user you use for the portal. Once done and successful, this message will be shown.

   

8. Right-click on the data source and choose Select Tables. Click on Remove All to remove the existing rule. Click on Switch to Simple Selection and in that Search for the tables you want to add. Click Add to add one of them or select multiple tables to add more than one to the Selected Tables area. For example, I searched for customer and added the Customer table.

   

9. Click on Select Columns to unselect columns that are not needed. Be sure to always keep SystemModifiedAt as it will be optimal to use for Incremental load.

   

10. Once you have finalized table selection, you are ready to use the tables 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. Locate if any tables has no primary keys and use the override feature to add one. Click OK to acknowledge this.

    

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), check for Handle primary key updates and Handle primary key deletes. If you have any tables affected by the incremental load rule that have no primary keys, a message about this will be displayed.

    

14. Run the synchronize task again and once it is done open the Data Source Explorer.

    

    Here you can see that multiple versions of the tables exist, as a table is replicated for each BC account. If you want to only look at specific ones, you can do this by right-clicking on the data source and choosing Select Accounts.
     

    

    By default all accounts are selected with the Use all accounts option, but by changing it to Use specific accounts you can select only the ones you want to use 

    

15. Run the Synchronize task again, and then run the Transfer task and you are ready to use the BC data source in the Data Warehouse Instances you have.

What is TimeXtender Business Central 365 Option Values data source?

We also have a TimeXtender Business Central 365 Option Values provider. This is used together with the normal BC provider to set up names for Type field values. Many tables have a type field of some sort. For example, the table Value Entry has a field called Document Type. Its content is a int value of 0 - 9 and this contains the description of this value. It will contain all names for these values across tables and fields.

Set up the BC Values data source in the TimeXtender Portal

The setup is the same as for a normal BC.

Follow steps 1 - 6 from the above guide to set up the data source.

Create the BC data source in the ODX Instance

1. Create a new data source by right-clicking on Data Sources and clicking Add Data Source.
2. Give it a name
3. In the Connection menu choose the BC Values data source from the list.

   

4. Choose All tables in the data source.

   

5. Say yes to create a transfer task and name it Transfer or similar. Say yes to all default settings.
6. Right-click on the source just created and choose the Authorize OAuth option.

   

7. If your logon option is OAuth 2.0 authentication it will then request you to log on. Log on as the user you use for the portal. Once done and successful, this message will be shown.

   

8. Run the Synchronize task on the data source again and run the Transfer task.

How to use the BC values data source in a Data Warehouse instance

For more information on how to use the BC Values data source in a Data Warehouse instance, review the Use the Dynamics 365 Business Central Values provider section in the Lookup Transformation Template guide.