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 over 100 third-party ODBC drivers available for a wide range of database systems, this means that Dundas BI has the ability to connect to numerous types of data sources out-of-the-box. Using ODBC is a viable alternative to writing a custom data provider which requires programming expertise.

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

  • Adaptive Server (Sybase)
  • DB2 (IBM)
  • Informix (IBM)
  • Ingres
  • Pervasive PSQL (Pervasive)
  • Proficy Historian (GE)
  • Quickbooks
  • SAS
  • SPSS
  • SQL Anywhere (Sybase)
  • Vertica

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

Note
There is a wide range of available third-party ODBC drivers each of which may have different levels of quality and compatibility with Dundas BI. Even for the example data sources listed above, Dundas cannot guarantee any specific third-party ODBC driver will work perfectly with Dundas BI. If you do encounter connection issues with a specific driver, feel free to contact Dundas Support and we'll do our best to help you investigate the problem.

2. Background

2.1. Data Source Name (DSN)

An ODBC Data Source Name (DSN) contains the information necessary to make a connection to a data source via ODBC. This information is stored in Windows and may include the database name, driver, and user credentials. Once a DSN is created for a data source, the DSN can be used by client applications to access data.

A System DSN is a DSN that can be used by anyone who has access to the computer. Use the ODBC Data Source Administrator program in Windows to create and manage System DSNs on your computer. For example, on Windows, go to your Control Panel and open Administrative Tools to find the Data Sources (ODBC) shortcut.

Data Sources (ODBC) shortcut in Administrative Tools
Data Sources (ODBC) shortcut in Administrative Tools

Double-click this shortcut to launch the ODBC Data Source Administrator.

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

2.2. DSN-less connections

Dundas BI also lets you connect to ODBC data sources using a DSN-less connection. In this case, you must specify the connection information which is specific to each type of data source and driver.

The advantage of DSN-less connections is that they are not machine-specific and will still work if you move the application to another server. On the other hand, to set up a DSN-less connection requires more knowledge about the ODBC driver.

3. Connect using ODBC System DSN

To connect using an ODBC System DSN, first create a new data connector from the main menu.

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

Select the Use System DSN option and click the System DSN dropdown to choose an existing system DSN. Enter a password if necessary.

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

Click Submit to connect and begin discovery. 

Go to Explore and locate your newly created data connector. Expand it and drag a discovered table to the dashboard canvas.

Drag a table from the ODBC data connector
Drag a table from the ODBC data connector

4. Connect without DSN

To make a DSN-less connection, uncheck the Use System DSN option in the New Data Connector dialog.

Click the ODBC Driver dropdown and select a driver.

Select the ODBC driver
Select the ODBC driver

Enter the ODBC connection string which consists of one or more name-value pairs separated by semi-colons. The required information here is different depending on the specific driver/database.

Enter the ODBC connection string
Enter the ODBC connection string

5. Advanced Options

5.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, go to the Advanced section and select the Specify Driver Capabilities option in order to configure precisely what SQL functionality the ODBC driver does support. Doing so will help to avoid errors when you try to use the data connector to build metric sets and dashboards.

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

Specifiy ODBC driver capabilities
Specifiy ODBC driver capabilities

As an example, suppose you have created an ODBC data connector and initiated discovery successfully. Then you drag one of the discovered tables to the canvas only to see the following error:

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 indicates a problem with the use of ORDER BY. Therefore, to avoid this, create a new ODBC data connector but this time, enable the Specify Driver Capabilities option and uncheck ORDER BY.

Uncheck ORDER BY
Uncheck ORDER BY

6. 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: 7am-6pm, ET, Mon-Fri