Connecting to ODBC
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.
Here are some examples of data sources that have an ODBC driver:
- Adaptive Server (Sybase)
- Informix (IBM)
- Pervasive PSQL (Pervasive)
- Proficy Historian (GE)
- Spark SQL
- SQL Anywhere (Sybase)
There are some limitations to be aware of when connecting via ODBC. See Dundas BI - Product Notes for more details.
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. If someone else is hosting your Dundas BI instance for you, you can check whether drivers have already been installed using the ODBC Driver dropdown shown below.
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.
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.
Optionally click Test connection above. In case of issues, see the section below for more information about DSN connections.
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.
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):
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
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.
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.
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.
In some cases, some information registered with a DSN may not be taken into account by the driver when testing or using it in Dundas BI, for example an error message may indicate the user name is missing or blank. If necessary, in Dundas BI 10 or higher you can expand the Advanced section of the data connector settings and enter Additional Connection Parameters in a format similar to an ODBC connection string, such as UID=MyUserName to specify the username, and/or the data connector's dedicated Password field can be filled in. Alternatively, you can uncheck the System DSN option and enter all connection details required by the ODBC driver into the connection string.
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.