Connecting to ODBC
This applies to: Managed Dashboards, Managed Reports
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 Symphony 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.
Note: 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)
Note: insightsoftware does not guarantee that any specific third-party ODBC driver will work properly with Symphony. If you encounter any connection issues with a specific driver, technical support.
When you or your organization have your own Symphony installation, run the ODBC driver installer or follow the instructions provided for the driver to install it on the computer(s) whereSymphony is installed. If someone else is hosting your Symphony instance for you, you can check whether drivers have already been installed using the ODBC Driver drop-down shown below.
This topic contains the following sections:
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 drop-down to ODBC Generic.
Using a System DSN
If a system DSN has already been configured that you want to use, select Use System DSN.
Enter the System DSN or choose it from the drop-down that appears. Enter a Password if necessary.
Optionally select Test connection above. In case of issues, see the section below for more information about DSN connections.
Select Submit at the bottom of the dialog when finished.
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 drop-down 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 select Test connection above. Select Submit at the bottom of the dialog when finished.
Advanced Options
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, Symphony will perform all of the corresponding operations (other than selecting all data) in memory.
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 Symphony, the computer's applications can use it, and you can select it from a drop-down when creating a Symphony data connector as shown above.
In some cases, some information registered with a DSN may not be taken into account by the driver when testing or using it in Symphony, for example an error message may indicate the user name is missing or blank. If necessary, 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.
DSN-less Connections
Symphony also lets you 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 Symphony 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.