TimeXtender Dynamics 365 Finance data source

This articles explains how to set up the TimeXtender Dynamics 365 Finance - SQL Server data source. The Dynamics 365 Finance (formerly known as "Dynamics AX") data source provides features that make working with Dynamics 365 Finance much easier. Specify the BC accounts for which data is to be extracted, and avoid having to later on filter out other unwanted account data in your data warehouse.

Adding a TimeXtender Dynamics 365 Finance data source

1. Sign in to the Portal, navigate to Data estate > Data sources, and click Add data source.
2. On the Add data source page, select the select TimeXtender Dynamics 365 Finance - SQL Server.

   

3. Enter the connection information

   

   1. Add a SQL server name in the Server field.

   2. Add the name of the database you connect to in the Database field.

   3. Check the Trust server certificate to True.

4. In the Authentication area, set the option to either SQL Server Authentication or Active Directory Password Authentication.

   

5. After choosing one of those options add a user and password.

   

6. The Accounts box can either be used to include or exclude accounts, depending on whether the “Include all accounts” box below is checked or not. When this box is checked, then all of the accounts will be included except for those listed out in the “Accounts” box. Therefore, enter the accounts (or companies) that you do not want to extract data from in the Accounts box, and enter one account per line. In the example below, all accounts will be included except for the DAT account listed in the “Accounts” box.

   

7. Conversely, clear the “Include all accounts” checkbox to explicitly define which accounts to be included. The accounts listed in the Accounts box will then be included rather than excluded, as in the following example. 

   

   If the Accounts field is empty and the “Include all accounts” checkbox is checked then all accounts will be added.

8. Select Compare accounts case-insensitive to convert account names to lowercase before comparing them. This ensures that account names will still match even when there are differences in the letter casing.

   

9. Under Advanced Options, the provider's behavior can be fine-tuned:  

   

   • Enum label length: The maximum length of an enum label. Increase this if the length is too short for the data in your system
   • Allow dirty reads: If enabled, tables will not be locked before data is read from them. This property only applies to the SQL Server provider.

10. Click Add to save the data source

Using the TimeXtender Dynamics 365 Finance data source

Use the following steps to set up and use this data source.

1. Open TimeXtender and then open the ODX instance. Right-click on data sources and select Add Data Source...
2. Provide a Name for the data source and click Next.
3. Select the Dynamics 365 Finance data source connection that was setup in the Portal and click Next.
4. In the Table Selection menu, choose the Let me select the tables option and click Next.

   

5. Use the search field to locate the tables to be used in the setup, starting with a few tables and adding more as appropriate.

   

6. Select the columns to include using Select Columns…

   

   

7. Be sure to add the fields DATAAREAID and MODIFIEDDATETIME, provided they exist, as they are the fields that contains the account name and a date, which are ideal to use for Incremental loading.
8. Set up Incremental Load. The best field to use for this is the ModifiedDateTime.