Connecting to ODBC
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 you can 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)
- 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 Product Notes for more details.
Run the ODBC driver installer or follow the instructions provided for the driver to install it on the computer where the application or its gateway is installed. If someone else is hosting the application 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 Save 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):
UID=username;PORT=27015;DATABASE={databasename};SERVER={biconnector.etc.mongodb.net};
Optionally click Test connection above. Click the Save 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, the application 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 the server application, the computer's applications can use it, and you can select it from a dropdown when creating a data connector as shown above.
When the application is installed on Windows, you can use the ODBC Data Source Administrator (64-bit) to create and manage system DSNs on the computer where the application 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, for example an error message may indicate the user name is missing or blank. If necessary, in version 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
You can also connect to ODBC data sources without using a system DSN. In this case, you must specify the connection information in the 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 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.