Microsoft Dynamics AX Adapter

This adapter simplifies the extraction of data from Microsoft Dynamics AX.

If you connect to a Dynamics AX database as a regular data source, you will have to apply and maintain selection rules on all tables. With TimeXtender Dynamics AX adapter, you can select company accounts at a global level. You can, however, override this behavior on a table by table basis.

 The adapter also extracts any virtual company accounts, including, table collections, and tables that are set up in the source database. The information can then be used in dimensions and cubes.

Furthermore, the adapter extracts all Base Enumerations and their associated labels and supports synchronization with the back-end application.

Importing XPO Files into Dynamics AX

The Dynamics AX adapter is only available if the .xpo file has been imported into Dynamics AX.

1. Import the .xpo file into Dynamics AX.
2. Compile the imported project within Dynamics AX.
3. Run all four classes in Dynamics AX to populate the tables.
4. Add a Dynamics AX adapter to your TimeXtender project. For more information, see Add Dynamics AX Adapters.

Adding a Dynamics AX Adapter

Use the Dynamics AX Adapter to load data from separate Dynamics AX company accounts tables in a single table.

1. Open a business unit, right click Data Sources, click Adapter Data Sources and then click Add Dynamics AX Adapter.
2. Enter a name for the adapter, and then click OK.

You can now choose the provider which contains the data source you want to connect to.

Adding a Microsoft SQL Server Provider

1. Right-click the adapter, click Source Providers and then click Microsoft SQL Provider.
2. In the Server name box, enter the location of the server.
3. In the Authentication list, click the mode of authentication you want to use. You have the following options:
   • Windows Authentication: Use the logged-in Windows user's credentials for authentication.
   • SQL Server Authentication: Use a login set up on the SQL Server. Enter the username and password in the corresponding fields.
   • Azure AD Password Authentication: Use Azure AD credentials from a domain that is not federated with Azure AD. Enter the username and password in the corresponding fields.
   • Azure AD Integrated Authentication: Use the logged-in Windows user's credentials for authentication, provided that he is logged in using Azure AD credentials from a domain that is federated with Azure AD.
     
4. In the Database field, enter the name of the database.
5. In the Connection timeout box, enter the number of seconds to wait before terminating the attempt to connect to the server, and then click OK.
6. In the Command timeout box, enter the number of seconds to wait before terminating the attempt to connect to the database.
7. (Optional) In the Encrypt connection list, you can enable encryption of the connection, which is recommended when you are not in a private network (e.g. when your server is on Azure). You have the following options:
   • No: The communication is not encrypted (default).
   • Yes: The communication is encrypted. The server's certificate is verified by a certificate authority.
   • Yes, trust server certificate: The communication is encrypted. but the server's certificate is not verified. This setting is not recommended for use on public networks.
8. In the Use SSIS for transfer list click on Yes to enable SQL Server Integration Services data transfer, No to disable it or leave it at As Parent to respect the project setting.
9. Select Force codepage conversion to convert all fields to the collation of the data warehouse.
10. Select Force Unicode conversion to declare all alphanumeric fields as nvarchar.
11. Select Allow dirty reads to allow reading from the source without locking the table.
12. If you want to add additional connection settings, click Additional Connection Properties . In the Connection String Properties window, enter the required connection strings, and then click OK.

Adding a Dynamics AX Odata Provider

The version of Dynamics AX released in 2016 - known as Dynamics AX 7 - introduced a new Azure-based data storage option. You can access this through the "AX7 Odata" provider.

The provider uses OAuth 2.0 authentication, which means you need to set up a client in Azure AD to connect - please see this article for more information:
https://ax.help.dynamics.com/en/wiki/dynamics-ax-7-services-technical-concepts-guide/

Note that this provider cannot use SSIS transfer.

To add a Dynamics AX OData provider, follow the steps below.

1. Right-click the adapter, click Source Providers and click Add AX7 Odata Provider. The AX7 Odata window appears.
   
   
2. In the Password row, enter the password you use to connect.
3. In the Username row, enter the username you use to connect.
4. Under client, enter the Client Id and Return URI you use to connect.
5. In the Authentication URL row, enter the URL used for authentication.
6. (Optional) In the Connection Timeout row, modify the number of seconds to wait before terminating an attempt to connect to the server.
7. (Optional) In the Command Timeout row, modify the number of seconds to wait before terminating an attempt to connect to the database.
8. (Optional) In the Max. concurrent threads per table row, modify the number of connections you will allow to a single table.
9. In the Odata URL row, enter the URL that you will get the data from.
10. (Optional) In the Max. character length row, set the max length in characters for fields that are strings.
11. (Optional) In the Max. key character length row, set the max length in characters for key fields that are strings.
12. If your need to connect through a proxy server, click Proxy settings in the Setup property list. In the WebProxyApproach list you have the following options:
    • NoProxy
    • ApplicationProxy: Use the proxy settings configured on the application level. To adjust the application settings, click Options on the Tools menu and then click Proxy Settings. Note that using a proxy server for Internet connections can also be turned on and off from here.
    • SpecificProxy: Enter the settings - server, port, username and password - to use for the adapter.

 Adding an Oracle Provider

1. Right-click the adapter, and select Source Providers. Then select Oracle Provider.
2. In the TNS alias field, type the alias that identifies the database.
3. In the Owner list, select the owner of the database.
4. Specify the authentication mode. When you select Oracle authentication, you are prompted for a user name and a password.
5. Select Convert Out of Range Dates to MS SQL min/max if you want to convert all dates older than January 01, 1753 to 01-01-1753.
6. In the Connection Timeout field, specify the number of seconds to wait before terminating the attempt to connect to the server.
7. In the Command Timeout field, specify the number of seconds to wait before terminating the attempt to connect to the database.
8. If you want to set the character encoding to either Unicode or Non-Unicode, select Force Character Setting. Then select the preferred character encoding in the list.
   Note: Forcing character encoding may affect performance.If you want to add additional connection strings, click the Additional Connection Properties button. In the Connection String Properties window, type the preferred connection strings, and then click OK.

Setting the Account Table

Before you can continue with setting up accounts, you have to verify that the company account table is correct.

1. Right-click the adapter, and then choose Set Account Table. The table DATAAREA and the field ID are selected by default.
2. Click OK.

Setting Up Dynamics AX Companies

When you have added a Dynamics AX adapter and specified a provider, you need to set up the accounts. To set up accounts:

1. Right-click the adapter, and select Set Up Accounts. An information message is displayed, which lists the accounts that have been added. Click OK. The Accounts window appears.
   
   
2. Select the accounts from which you want to retrieve data. In the With selectedlist, click Include to use the selected accounts in the data warehouse or click Exclude to exclude the selected accounts and include all other accounts. The last option is useful if you often add new accounts in Dynamics AX and want to make sure that all accounts are included in the data warehouse as soon as they exist in the ERP system. Note that the exclude option isn't available when you use the OData provider.
3. In the Default Table Usage list, specify the order in which data from the tables is retrieved and read. You have the following options:

   Option	Definition
   Primary	Data from this company account is read and retrieved first
   Secondary	Data from this company account is read and retrieved after the primary account if they have not already been retrieved from the primary account
   None	Tables from this company are not retrieved unless you specify at the table level that you want to retrieve data from a specific table. For more information, see Modifying Table Usage on Dynamics AX Tables.

4. Click OK.

Loading and Selecting Data from Dynamics AX Data Sources

1. Right-click the Dynamics AX adapter you want to select data from, and then select Read objects. The Data Selection pane displays all tables and fields.
2. In the Data Selection pane, select the tables and fields you want to extract to your staging database.

The tables and fields are displayed in the data source tree and in the staging database tree.

Adding Dynamics AX Virtual Table References

1. Expand the AX adapter that contains the table to which you want to add a virtual table reference, right-click the table, and then choose Add Virtual Table Reference.
2.  In the Add Virtual Table Reference window, select the preferred virtual tables, and then click OK.

 Viewing Dynamics AX Table Information

TimeXtender can retrieve table information directly from your Dynamics AX database.

1. Expand the Dynamics AX adapter that contains the table you want to view information about, right- click the table, and then select View Table Information.

   The three tabs in the View Table Information dialog contain the following information:

   Fields Tab	Description
   Name	Specifies the name of the field as it appears in the database
   Label	Specifies the name of the field as it appears in the user interface
   Help Text	Contains the help text for the field
   EDT Name	Specifies the name of the extended Data Type if applicable
   Enum Name	Specifies the name of the enumeration if applicable
   System	Specifies whether the table is a system table or visible in the user interface

   Relations Tab	Description
   External Table	Specifies the name of the table the selected table is related to
   Directions	Specifies whether the selected table is the child or the parent in the relation
   Field	Specifies which field in the selected table that relates to a field in the related table
   External Field	Specifies the field on the related table
   Relation Type	Specifies the type of relation. Field specifies relation fields without conditions. ThisFixed specifies relation fields to restrict the records in the primary table. ExternFixed specifies relation fields that restrict the records in the related table

   Virtual Company References	Description
   Company	The name of the company account
   Virtual Company	The name of the Virtual Company that contains tables shared by several company accounts

Viewing Dynamics AX Enum Table Information

All Enum values in Dynamics AX are represented as integers in the tables. However, you can see the corresponding literal values by viewing the enumeration table information.

• Expand the Dynamics AX adapter that contains the table you want to view information about, right- click the table, and then select Preview Enum Table.

 Changing Dynamics AX Schemas

1. Right-click the AX adapter that contains the table whose priority you want to change, and then select Change Schema.
2. In the Select Schema To Change list, select the schema you want to change.
3. In the New Schema Name field, enter a name for the schema.

 Modifying Table Usage on Dynamics AX Tables

When you set up accounts, you specify the default order in which data is retrieved from the individual accounts. However, it is possible to specify a different order of priority for individual tables.

1. Right-click the AX adapter that contains the table whose priority you want to change, and then select Modify Table Usage. The company accounts and the usage of all tables will be displayed.
2. Right-click the table and account field with the setting you want to change the order of priority on for data retrieval. You have the following options:
   

   Priority	Definition
   Default	Data from this table is read and retrieved first
   Primary	Data from this table is read and retrieved first
   Secondary	Data from this table are read and retrieved after the primary table if they have not already been retrieved from the primary table
   None	Data from this table is not retrieved
   1-9	Specify the order priority in the range from 1-9
   Enter priority	If the order of priority exceeds the numbers 1-9, you can specify additional numbers

3. Click OK.

 Modifying the Usage of a Single Dynamics AX Table

If you want to change the order of priority in which data is retrieved on a single table, you can do so from the individual table.

1. Right-click the AX adapter that contains the table whose priority you want to change, and then select the preferred table.
2. Right-click the table, and select Modify Single Table Usage. The company accounts and the usage specified in Setup Company Accounts will be displayed.
3. Right-click the field that contains the setting for the table, and then specify the table usage. You have the following options:
   

   Priority	Definition
   Default	Data is retrieved based on the settings specified when you set up the company accounts
   Primary	Data from this table is read and retrieved first
   Secondary	Data from this table is read and retrieved after the primary table if they have not already been retrieved from the primary table
   None	Data from this table is not retrieved
   1-9	Specify the order of priority in the range form 1-9
   Enter priority	If the order of priority exceeds the numbers 1-9, you can specify additional numbers here.