Connecting via OLE DB Drivers

Object Linking and Embedding Database

The OLE DB provides 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 that are not included in the common providers list, such as SQL Server, MySQL, Oracle and so forth.

Note:

Generic OLE DB is not supported on Linux. To create a new connections based on JDBC, you can use the Generic JDBC connector.

Adding a table to Sisense

  1. Click Add Data on the top menu of Sisense.

  2. Under the Database servers category select Generic OleDB Driver.

  3. In the Data Link Properties dialog, select a connection by choosing the relevant OLE DB driver and the connection properties.

    8-5ole-DB-1thumb0300.png

    Note:

    When the data source server requires a login credentials, you must select the Allow saving password checkbox.

  4. Follow all wizard steps and 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 described below.

    8-5ole-DB-2thumb0300.png

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 the other.

Sisense sets by default the standard SQL for the ODBC/OLE DB tables which covers most of the 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 which opens the Query Properties dialog.

In the Query Properties dialog, two options are available:

  • Auto Query Structure: Choose 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. The Apply All and Apply Table buttons are enabled according to the selection made.

Misc Troubleshooting:

  • When an ODBC table is added by a DSN connection, the DSN must exist on the target ElastiCube Server machine, or else the server will fail to connect and import data from that table. In general, setting a connection by a DSN (not by connection string) is not recommended practice.
  • Regarding ODBC only, ensure that the addressed ODBC driver platform (32 or 64 bit) matches the current installation of Sisense (Manager and Server). Platform mismatches will lead to connectivity errors.
  • When setting an OLE DB connection, and the data source server requires login credentials, the Allow saving password checkbox must be selected.