Follow

TimeXtender Dynamics 365 Business Central

This will explain how to create a Dynamics 365 Business Central data source in the portal and set it up in the ODX. 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 D365 Business Central provider?

It is a provider made together with Insight Software to make our program 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.

User requirements in BC

All users that will be authorizing the connection require the D365 AUTOMATION or SUPER access in BC, this can be achieved by navigating to the relevant environment in https://businesscentral.dynamics.com/, then clicking on the search button in the top right, then search for users, and click on users. Select the relevant user, and add either SUPER permission set (with blank company name) under User Permission Sets, or D365 Automation.Picture3.pngPicture4.pngPicture5.png

Setting up the BC extension

Install "Jet Analytics" from the Extensions Marketplace in BC and enable the setting Allow HttpClient Request, as shown in the following article. Connecting-to-Business-Central-Version-16-or-Later

    1. Within the Business Central portal, using the search icon, navigate to Extension Management
      mceclip5.png
    2. Click Manage → Extension Marketplace
      mceclip1.png
    3. Search for Jet Analytics and click Get it now.
      mceclip2.png
    4. Verify extension installation. Click Manage and then Deployment Status


      mceclip6.png
    5. Verify that the status column has a value of completed
      mceclip7.png
    6. Allow HTTP client requests. Note: if you are configuring a Production environment you can skip this step mceclip8.png
      Then you can set it to Allow.
      mceclip9.png

Set up an app to use for authentication

  1. To register an Azure AD application for authentication to BC, navigate to API permissions select Add a permission, and add the application permissions: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/automation-apis-using-s2s-authentication
    Picture6.pngPicture7.png
  2. Once the app is set up you can map it in BC. Search for Azure Active Directory Applications in BC and select new, and to find the client ID enter the Application (client) ID for your app registration, which can be found in the Overview page in Azure, and enter a description. Give the Application the “D365 AUTOMATION” User Permission Set.
    1. Picture8.png
    2. mceclip0.png
    3. Picture9.pngPicture10.png
      In the TimeXtender BC 365 Provider window, enter the Client id which is the Application (client) ID for your app registration in Azure used earlier. Also enter the Client secret which is the Value under “Certificates & secrets” in your app registration in Azure. Click OK.Picture11.png

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.
    mceclip1.png
  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.Picture1.pngPicture13.png
  5. You have the following options to authenticate.
    mceclip2.png
    1. 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.
    2. 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.
    3. 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.
      mceclip0.png
  6. Once the data source is set up, be sure to map it to the ODX Instance you want to use it with.
    mceclip2.png

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.
    mceclip1.png
  4. Choose All tables in the data source.
    mceclip5.png
  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.
    mceclip6.png
  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.
    mceclip7.png
  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.
    mceclip4.png
  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.
    mceclip8.png
  10. Once you have finalized table selection, you are ready to use the tables in a Data Area.
    mceclip9.png
  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.
    mceclip11.png
  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.
    mceclip10.png
    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.
    mceclip12.png
    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.
    mceclip13.png
    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.
    mceclip14.png
  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.
mceclip15.png

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.
    mceclip1.png
  4. Choose All tables in the data source.
    mceclip5.png
  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.
    mceclip6.png
  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.
    mceclip7.png
  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.

 

Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.