Open Database Connectivity

The ODBC provider allows access to a data source via an installed driver on the operating system regardless of the architecture of the data source.

In Sisense, it serves as a connector to data providers, such as MS Sql, MySql, Oracle, Hive and so forth.

DSN vs. Connection-String (DSN-Less)

A connection to the provider is configured by a connection-string. Microsoft windows allows you to store the connection string properties in its registry for further use. It supplies an ODBC configuration wizard and requests an identifying Data Source Name (DSN).

When you add an ODBC table in the ElastiCube Manager, the connection configuration dialog will provide you with two options:

  • DSN – choosing a data source name.
  • Connection String (DSN-Less) – typing the actual connection string

Adding a Table to the ElastiCube Manager:

  1. Click Add Data in the top menu of the ElastiCube Manager.
  2. Under the Database servers category, select Generic ODBC Driver.
  3. From the ODBC Connectivity Properties dialog choose DSN or Connection String (DSN-Less).

    • DSN: Choose one of the existing DSNs from the drop-down list (each item is represented by its name), or click Add DSN… to open the Create New Data Source window. In this window, you’ll be asked to select one of the installed ODBC drivers, select a name for the DSN, and configure your connection using the wizard steps. At the end, the newly added DSN will be added to the drop-down list as the selected item.
    • Connection String: Type the connection string in the input box. For known connection strings, click the Help link to navigate to Connection Strings (External site from Sisense). Click Test Connection to test the connection.Click OK to move to the final dialog. From the tables selection dialog, select tables that you want to add and click Add  to complete the wizard.

Note: You may need to update the executed table query.

Add ODBC

Setting the table query (Troubleshoot)

ODBC and OLE DB are generic drivers; therefore the SQL syntax being executed against the actual data source may vary from one provider to another.

Sisense sets by default the standard SQL for the ODBC/OLE DB tables which covers most of today’s known data providers. For cases where non-standard SQL must be supplied, the table addition dialog provides a quick preview of the current SQL query and an edit button that opens the Query Properties dialog.

In Query Properties, you can set the following options:

  • Auto Query Structure: Choosing the proper SQL syntax delimiter.
  • Manual Query Input: Type the desired query.


note

The automatic options can be applied on the current table or on all tables, while the manual option defines a single table and can only be applied on the current table. Apply All and Apply Table will be enabled according to the selection made.
Toggle