Connecting to ODBC

Contents[Hide]

1. Overview

The ODBC generic data provider option lets you connect to data sources that have an ODBC driver. Since there are third-party drivers available for a wide range of databases and other data sources, this means that Dundas BI has the ability to connect to more data sources out-of-the-box than we can list. You can often use an existing ODBC driver rather than rely on a custom data provider.

Important
Only the 64-bit versions of the ODBC drivers are supported.

Here are some examples of data sources that have an ODBC driver:

  • Adaptive Server (Sybase)
  • Databricks
  • Informix (IBM)
  • Ingres
  • MongoDB
  • Pervasive PSQL (Pervasive)
  • Proficy Historian (GE)
  • QuickBooks
  • SAS
  • Spark SQL
  • SPSS
  • SQL Anywhere (Sybase)

There are some limitations to be aware of when connecting via ODBC. See Dundas BI - Product Notes for more details.

Note
Dundas does not guarantee that any specific third-party ODBC driver will work properly with Dundas BI. If you encounter any connection issues with a specific driver, contact Dundas Support and we'll do our best to help you investigate the problem.

When you or your organization have your own Dundas BI installation, run the ODBC driver installer or follow the instructions provided for the driver to install it on the computer(s) where Dundas BI is installed.

2. Connect using ODBC

With an ODBC driver installed, you can create a new data connector from the main menu to use it.

Enter a Name for the data connector, and then set the Data Provider dropdown to ODBC Generic.

Connect using an existing system DSN
Connect using an existing system DSN

2.1. Using a system DSN

If a system DSN has already been configured that you want to use, select the Use System DSN option.

Enter the System DSN or choose it from the dropdown that appears. Enter a Password if necessary.

Connect using an existing system DSN
Connect using an existing system DSN

Optionally click Test connection above. Click the Submit button at the bottom of the dialog when finished.

2.2. Without DSN

To enter most of the connection details yourself, you can leave the Use System DSN option unchecked.

Click into the ODBC Driver textbox and choose from the dropdown listing the available drivers that are installed.

Select the ODBC driver
Select the ODBC driver

Enter the ODBC Connection String, usually consisting of one or more name-value pairs separated by semi-colons. The required information here is different depending on the specific driver/database and should be documented by the provider of the driver.

For example, you can use a connection string instead of a system DSN with the MongoDB ODBC driver for a BI Connector in MongoDB Atlas (the system DSN steps list other available parameters in the Linux tab):

UID=username;PORT=27015;DATABASE={databasename};SERVER={biconnector.etc.mongodb.net};

Enter the ODBC connection string
Enter the ODBC connection string

Optionally click Test connection above. Click the Submit button at the bottom of the dialog when finished.

3. Advanced options

3.1. Specify driver capabilities

Some ODBC drivers do not fully support SQL functionality such as joins. In other cases, an ODBC driver may report that it supports certain functionality when it really doesn't. To deal with these cases, you can specify yourself what SQL functionality from the ODBC driver to use. Doing so will help to avoid errors when you try to use the data connector to build metric sets and dashboards.

As an example, suppose you have created an ODBC data connector successfully. When dragging one of the discovered tables to the canvas, you see an error such as:

ERROR [HY000] [iAnywhere Solutions][Advantage SQL][ASA] Error 7200: AQE Error: State = S0000; NativeError = 2145;
[SAP][Advantage SQL Engine]Unable to ORDER BY this column: Notes

Error occurs when dragging table to canvas
Error occurs when dragging table to canvas

The error message in this example indicates a problem with the use of ORDER BY.

To avoid this, edit the ODBC data connector. Expand Advanced, select Specify Driver Capabilities and uncheck ORDER BY.

Uncheck ORDER BY
Uncheck ORDER BY

If you enable the Specify Driver Capabilities option but do not enable any of the capabilities, Dundas BI will perform all of the corresponding operations (other than selecting all data) in memory.

Specifying no ODBC driver capabilities
Specifying no ODBC driver capabilities

4. Data source name (DSN)

An ODBC Data Source Name (DSN) stores the information necessary to make a connection to a data source via ODBC, which can include the database name, driver, and credentials. Once a System DSN is created for a data source on the computer running Dundas BI, the computer's applications can use it, and you can select it from a dropdown when creating a Dundas BI data connector as shown above.

When Dundas BI is installed on Windows, you can use the ODBC Data Source Administrator (64-bit) to create and manage system DSNs on the computer where Dundas BI is installed. Type to search for ODBC Data Sources (64-bit) in the start menu, or find it in Administrative Tools.

Managing System DSN entries in Windows
Managing System DSN entries in Windows

4.1. DSN-less connections

Dundas BI also lets you connect to ODBC data sources without using a system DSN. In this case, you must specify the connection information in the Dundas BI data connector according to the instructions of the driver provider.

An advantage of DSN-less connections is that they are not machine-specific and will still work if you transfer the data connector or your Dundas BI installation to another server provided that the driver is installed. On the other hand, setting up a DSN-less connection requires more knowledge about the ODBC driver.

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri