Microsoft Dynamics Business Central (NAV) Adapter

This adapter simplifies the extraction of data from Microsoft Dynamics Business Central (NAV).

If you connect to a Dynamics Business Central (NAV) database as a regular data source, you will have to apply and maintain selection rules on all tables because different companies are stored in separate tables. With the Dynamics Business Central (NAV) adapter, you can select company accounts at a global level and apply only one set of selection rules. It is, however, also possible to overrule this behavior on a table-by-table basis.

Adding a Dynamics Business Central (NAV) Adapter

1. Open a business unit, right-click Data Sources, click Adapter Data Sources and then click Add Dynamics Business Central (NAV) Adapter.
2. Click Wizard Setup and, in the Choose Dynamics Business Central (NAV) Provider window, select the provider you want to use:
   • Microsoft SQL Provider: Data is stored in SQL Server
   • Navision Native: Data is stored in the legacy native Navision format.
   • BC 365
3. In the Name box, type a name for the adapter.
4. [Optional] Select Read Aggregation Tables - SIFT if you need to include Sum Index Flow Technology (SIFT) tables,
5. Click OK.
6. The flow continues with setting up the provider you selected. Please refer to the sections below:
   • Setting Up a Microsoft SQL Server Provider
   • Setting Up a Navision Native Provider
7. A window appears where you can input the table that contains the company accounts if it is different from the defaults:
   • Schema: Default: "dbo"
   • Table: Default: "Company"
   • Field: Default: "Name"
8. Click OK.
9. The Adapter Settings window opens.
   • Select Enable Enhancements to enable enhancements and enable the following settings.
     • Display language
     • Option field text length
     • NAV 2013 or Later: Select this if the version of Dynamics Business Central (NAV) is 2013 or later.
   • In the Invalid Identifiers box, enter any invalid identifiers.
   • Select Consistent Read to only transfer records added before an execution is started, i.e. records added or updated during an execution will not be transferred. This setting only has an effect if you use the SQL Server provider.
     Warning: Take care when using this option when records are likely to be updated or deleted. If a record is updated during execution, the timestamp will change and subsequently, the updated record will not be copied to the staging database during execution.

Setting Up 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 varchar.
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.

Setting Up a Navision Native Provider

When you want to retrieve data from Navision databases hosted in a Native Navision server environment, you will have to use ODBC. The Navision ODBC driver must be installed and configured prior to adding the Native NAV data source.

1. Expand the business unit, and then right-click Data Sources.
2. Point to Adapter Data Sources, and select Add Dynamics Business Central (NAV) Adapter.
3. Select Wizard Setup.
4. Select Navision Native.
5. In the Name field, type the name of the data source.
6. In the DSN Name, select the ODBC connection that you have configured for the data source. In the Escape Character list, select the escape character specific to your ODBC driver. The Text Type Behavior fields are used to control how the ODBC driver handles text. These fields are optional. You have the following options:

   Option	Definition
   Set Length	Specifies an exact text string length
   Set Variable Length	True, if you want a variable text string length
   Set Unicode	True, if you want to use Unicode

7. (Optional) In Set Number of Decimals, specify a fixed number of decimals.
   Note: "Convert Out of Range Dates to MS SQL min/max" is not available for Navision native databases.
8. Select Use low compatibility mode if you have trouble retrieving data from the database.
9. In the Command Timeout field, specify the number of seconds to wait before terminating the attempt to connect to the database.
10. In the Connection Timeout field, specify the number of seconds to wait before terminating the attempt to connect to the server.
11. 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 Up a BC 365 Provider

1. In the Wizard Setup, select BC 365.
2. In the Name field, type the name of the data source.
3. In the API URL enter https://api.businesscentral.dynamics.com/v2.0/{tenantId}. 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.
4. Install "Jet Analytics" from the Extensions Marketplace in BC and enable the setting "Allow HttpClient Request", as shown in the following article. https://support.jetglobal.com/hc/en-us/articles/360051062673-Connecting-to-Business-Central-Version-16-or-Later
5. If using OAuth 2.0 authentication, select this option in the Authentication dropdown 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.
6. Ensure that the user that will be authorizing the connection has D365 AUTOMATION or SUPER access in BC, this can be achieved by navigating to the relevant environment in https://businesscentral.dynamics.com/, and then clicking on the search button in the top right, and searching 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.
7. Click on authorize OAuth, and enter the user credentials with D365 AUTOMATION or SUPER access in BC.
8. If authenticating using “Service to Service Authentication” then select this option in the Authentication dropdown.
9. Follow Task 1 in the guide below to register an Azure AD application for authentication to BC, and under “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
10. Follow Task 2 in the same guide above, by searching for Azure Active Directory Applications in BC and selecting new, and for the client ID enter the Application (client) ID for your app registration, which can be found on the Overview page in Azure, and enter a description. Give the Application the “D365 AUTOMATION” User Permission Set.
11. 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

Dynamics Business Central (NAV) Companies

Changing the Dynamics Business Central (NAV) Company Table

By default, when you add a Dynamics Business Central (NAV) adapter, the company account table is set to dbo.Company.

However, it is possible to change the account table. This is generally not recommended.

1. Right-click the Dynamics Business Central (NAV) adapter, and then click Edit Account Table.
2. In the Table list, select the account to the table that you want to use.
3. In the Name Field list, select the field that contains the account name, and then click OK.

Setting Up Dynamics Business Central (NAV) Companies

When you have added a Dynamics Business Central (NAV) adapter and specified a provider, you need to set up accounts representing the companies requiring the extracted data.

To set up accounts:

1. Right-click the Dynamics Business Central (NAV) adapter, and then click Set Up Accounts. A window is displayed that shows all companies in the database.
2. In the Template list, select the company account you want to use as a template for the table and column structure. If you are only selecting one company, then the template company must match the company that is selected.
3. Select Use to specify whether to retrieve data from the company.
4. In the Default Table Usage list, specify the order in which tables are 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

Modifying Table Usage on Dynamics Business Central (NAV) 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. Expand Data Sources, right-click the NAV adapter that contains the tables 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 field that contains the setting for the table and the account for which you want to change the priority of 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 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 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 Business Central (NAV) Table

You can change the order in which data is retrieved from individual tables.

1. Right-click the Dynamics Business Central (NAV) 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 containing the settings 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	Specify the order of priority in the range from 1-9
   Enter priority	If the order of priority exceeds the numbers 1-9 you can specify additional numbers here

4. Click OK.

Changing Dynamics Business Central (NAV) Schemas

You can change the schema for the entire Dynamics Business Central (NAV) adapter or for individual tables that belong to the adapter.

1. Right-click the Dynamics Business Central (NAV) adapter and then click Change Schema.
   - OR -
   Expand the Dynamics Business Central (NAV) adapter, and then select the table whose schema you want to change.
2. In the Select Schema to change list, select the schema you want to change, and then select Change Schema.
3. In the New Schema Name field, enter a name for the schema, and then click OK.