Jet Analytics Dynamics 365 Finance Data Source

The Jet Analytics Dynamics 365 Finance — SQL Server data source provides features that simplify working with Dynamics 365 Finance (formerly Dynamics AX). Specify the accounts for which data is to be extracted, avoiding the need to filter out unwanted account data later in your data warehouse.

Add the data source connection

1. Sign in to the Jet Analytics Portal, navigate to Data Estate > Data sources, and click Add data source.

   The Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support.

2. On the Add data source page, select Jet Analytics Dynamics 365 Finance — SQL Server.

   

3. Enter the connection information:

   

   1. Enter the SQL Server name in the Server field.
   2. Enter the database name in the Database field.
   3. Set Trust server certificate to True.
4. In the Authentication area, select either SQL Server Authentication or Active Directory Password Authentication.

   

5. Enter a Username and Password for the selected authentication method.

   

6. Configure the Accounts field to include or exclude accounts based on the Include all accounts setting. Enter one account per line, using the account ID from the DataArea table.   

    

    

   • When Include all accounts is checked, all accounts are included except those listed in the Accounts field.

     

   • When Include all accounts is unchecked, only the accounts listed in the Accounts field are included.

     

   • If the Accounts field is empty and Include all accounts is checked, all accounts are included.

7. Select Compare accounts case-insensitive to convert account names to lowercase before comparing them. This ensures account names match even when letter casing differs.

   

8. Under Advanced Options, configure the following settings as needed:

   

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

9. Click Add to save the data source.

Add the data source in Jet Analytics Data Integration

1. Open Jet Analytics Data Integration and open the Ingest instance. Right-click Data sources and select Add Data Source…
2. Enter a Name for the data source and click Next.
3. Select the Dynamics 365 Finance data source connection configured in the Portal and click Next.
4. In the Table Selection menu, select Let me select the tables and click Next.

   

5. Use the search field to locate the tables to include. Start with a small set of tables and add more as needed.

   

6. Select the columns to include using Select Columns…

   

   

7. Include the fields DATAAREAID and MODIFIEDDATETIME where available. These fields contain the account identifier and a timestamp, which are ideal for incremental loading.
8. Configure Incremental Load. The recommended field for the incremental load lookup is ModifiedDateTime.