Follow

Microsoft Dynamics NAV Adapter

Microsoft Dynamics NAV Adapter

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

If you connect to a Dynamics 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 Dynamics 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.

Setup

Adding a Dynamics NAV Adapter

  1. Open a business unit, right click Data Sources, click Adapter Data Sources and then click Add Dynamics NAV Adapter.
  2. Click Wizard Setup and, in the Choose Dynamics 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.
  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:
  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 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 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 an 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 .

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.

Note: If you are connecting to NAV through an ODBC connection, you must be using the 32-bit version of Discovery Hub as the NAV ODBC driver only supports 32-bit connections.

  1. Expand the business unit, and then right-click Data Sources .
  2. Point to Adapter Data Sources , and select Add Dynamics 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:
    OptionDefinition
    Set LengthSpecifies an exact text string length
    Set Variable LengthTrue, if you want a variable text string length
    Set UnicodeTrue, 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 .

Dynamics NAV Companies

Changing the Dynamics NAV Company Table

By default, when you add a Dynamics 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 NAV adapter, and then click Edit Account Table .
  2. In the Table list, select the account to 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 NAV Companies

When you have added a Dynamics 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 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 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:
    OptionDefinition
    PrimaryData from this company account is read and retrieved first
    SecondaryData from this company account is read and retrieved after the primary account if they have not already been retrieved from the primary account
    NoneTables from this company are not retrieved, unless you specify at table level that you want to retrieve data from a specific table

Modifying Table Usage on Dynamics 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 priority of data retrieval. You have the following options:
    PriorityDefinition
    DefaultData from this table is read and retrieved first
    PrimaryData from this table is read and retrieved first
    SecondaryData from this table is read and retrieved after the primary table if they have not already been retrieved from the primary table
    NoneData from this table is not retrieved
    1-9Specify the order of priority in the range from 1-9.
    Enter priorityIf the order of priority exceeds the numbers 1-9, you can specify additional numbers.
  3. Click OK .

Modifying the Usage of a Single Dynamics NAV Table

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

  1. Right-click the Dynamics 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:
    PriorityDefinition
    DefaultData is retrieved based on the settings specified when you set up the company accounts.
    PrimaryData 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
    NoneSpecify the order of priority in the range from 1-9
    Enter priorityIf the order of priority exceeds the numbers 1-9 you can specify additional numbers here
  4. Click OK .

Changing Dynamics NAV Schemas

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

  1. Right-click the Dynamics NAV adapter and then click Change Schema .
    - OR -
    Expand the Dynamics 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.
Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.