Connecting to DynamicsNav

The SisenseMicrosoft Dynamics Nav connector is a certified connector that allows you to import data from the Dynamics Nav API into Sisense via the Sisense generic JDBC connector. The Microsoft Dynamics Nav connector offers the most natural way to connect to Microsoft Dynamics Nav account data, including Items, Sales Orders, Purchase Orders, and more, and provides additional powerful features.

The Sisense Dynamics Nav is a certified connector. The support for the connector is provided by Sisense and will be assisted by the certification partner's support, if needed. For any support issues or additional functionality requests, please contact your Sisense representative or open a request through our Help Center. For advanced inquiries, you can also contact our certification partner’s support directly via [email protected].

After you have downloaded and installed the connector, you can connect through a connection string you provide Sisense. The connection string is used to authenticate users who connect to the Microsoft Dynamics Nav tables. To obtain a connection string, you will need to obtain your credentials from Dynamics Nav. Once you have connected to Dynamics Nav, you can import a variety of tables from Dynamics Nav.

This page describes how to download the Microsoft Dynamics Nav driver and deploy it, how to connect to Dynamics Nav with a connection string, provides information about the Dynamics Nav data model, and more.

Downloading the Dynamics Nav JDBC Driver

You can download the Dynamics Nav JDBC driver here.

Deploying the Dynamics Nav JDBC Driver

To run the setup, execute the following command: java -jar setup.jar (OR, if your system is set up to run Java applications, double-click on setup.jar).
During the installation, pay attention to the path of the installation (you will need it later on, to direct Sisense to the Jar file. The default path is C:\Program Files\CData\CData JDBC Driver for Microsoft Dynamics Nav\lib).
Note: The install file (setup.jar) is a Java application that requires Java 6 (J2SE) or above to run. If you do not have Java 6 installed, you may download it from here.

Connecting to Dynamics Nav

The connection string used to connect to the Microsoft Dynamics Nav connects to various instances you have in the Dynamics Nav. By providing your Dynamics Nav credentials as values in the connection string, you can add data from your Dynamic Nav tables to the ElastiCube.

The connection string can be set to a series of option-value strings separated by semicolons. If a connection string property value has special characters such as semicolons, single quotes, spaces, etc., then you must quote the value using either single or double quotes.

Connection options are case insensitive.

The connection string to connect to Dynamics Nav has the following structure:

jdbc:dynamicsnav:Property1=Value1;Property2=Value2;

The following is an example of a Dynamics Nav connection string:

jdbc:dynamicsnav:URL=http://myserver:7048;User=myserver\Administrator;Password=admin;ServerInstance=DYNAMICSNAV71;

The connection string to be passed to Dynamics Nav must contain the following parameters:

In addition, when the CacheLocation property is set, the driver will cache metadata to the specified file, ensuring that queries execute more quickly. CacheLocation specifies the path to the cache when caching to a file.

Note: Before the driver can connect with Dynamics Nav, OData services need to be enabled on the server. Once OData Services are enabled, the driver will be able to query any services that are published on the server.

If there is not a Service Default Company for the server, set the Company as well, where 'Company' is the company to submit queries against, for example, ‘CRONUS Canada, Inc.’. In a multitenant installation, specify the tenant Id in Tenant.

Adding Dynamics Nav Tables to your ElastiCube

  1. In the Data page, open an ElastiCubeor create a new ElastiCube.
  2. In the Model Editor, click . The Add Data dialog box is displayed.
  3. Click Generic JDBC to open the JDBC settings.

  4. In Connection String, enter the Dynamics Nav URL. See Creating a Connection String for more information.
  5. In JDBC JARs Folder, enter the name of the directory where the Dynamics Nav JAR file is located (see Deploying the Dynamics Nav JDBC Driver).
  6. In Driver's Class Name, enter the following class name: cdata.jdbc.dynamicsnav.DynamicsNAVDriver.
  7. In User Name and Password, enter your Dynamics Nav credentials. These fields are not required if the user name and password were provided in the connection string. 
  8. Click Next. A list of tables in the database are displayed. All tables and views associated with the database will appear in a new window.
  9. From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or click Preview to see a preview of the data inside it. 
  10. (Optional) Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.
  11. After you have selected all the relevant tables, click Done. The tables are added to your data model.

Dynamics Nav Connector: Additional Resources

For the full documentation set for the Dynamics Nav connector, click here.

For connection string options, click here.

For information on the Dynamics Nav data model, click here.