Connecting to ODBC
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)
- Pervasive PSQL (Pervasive)
- Proficy Historian (GE)
- SQL Anywhere (Sybase)
There are some limitations to be aware of when connecting via ODBC. See Dundas BI - Product Notes for more details.
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.
Double-click this shortcut to launch the ODBC Data Source Administrator.
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.
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.
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.
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.
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.
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
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.