Connecting via ODBC Drivers

Applicable to Sisense on Linux and Microsoft Windows

Note: This topic describes how to import data using the desktop ElastiCube Manager.

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 MongoDB.

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 Sisense, the connection configuration dialog will provide you with two options:

Adding Tables to Sisense:

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

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

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:

Customizing Your ODBC Connection

Though there are hundreds of data sources available, the generic ODBC driver can help you connect to just about all of them. However, if the default settings do not fit your specific use-case, Sisense lets you customize your ODBC connection to give you the functionality you need.

When you add an ODBC connection to your Sisense configuration, Sisense saves the details of your connection in a file called odbcConfig.json. The odbcConfig.json is a configuration file written in JSON syntax located in the directory: …\Program Files\Sisense\DataConnectors\DotNetContainer\Connectors\GenericODBC You can edit this file to add functionality such as support for accumulative builds, increase query timeouts, or modify your query patterns.

The odbcConfig.json file contains the following objects that you can edit to modify your connectors behavior:

Name Type Mandatory Description
Provider String Yes The name of your data source. This name must be unique.
DriverNames String Yes An array of ODBC driver names. If you don't know a driver name, you could check "Connectors.log".
IsAccumulativeSupported Boolean No Indicates whether accumulative build is supported.Default value: false.
IsRelationsSupported Boolean No Enable/disables relations functionality in the ElastiCube Manager.Default value: false.
QueryTimeout Integer No Query timeout value. You can increase this value is your datasource takes longer to establish a connection.Default value: 0.
QuerySettings QueryConfig* No Datasource specific query settings (see QueryConfig Object table below).Default value: default query config values.
Extension String No A name of the assembly with the appropriate extension.Default value: none.

* The properties for the QueryConfig object are described in the table below:

QueryConfig Object

Name Type Mandatory Description
ColumnNamePattern String No Pattern which represents column name format (should include appropriate separators).To create a pattern, you can use the following keywords:[email protected]: Schema name (When schema is empty, table name will be using instead)[email protected]: Table [email protected]: Column name..Example: `@schema`.`@column`.Default value: [@column]
TableNamePattern String No Pattern which represents table name format (should include appropriate separators).To create a pattern, you can use the following keywords:@schema: Schema name (When schema is empty, table name will be using instead)[email protected]: Table name.Example: "@schema"."@table"Default value: [@schema].[@table] or [@table] in the case when schema name is missing.
PreviewSqlPattern String No Pattern which represents preview SQL format (should include appropriate SQL operators).To create a pattern, you can use the following keywords:@rowcount: The number of rows to [email protected]: Inner select.Example: SELECT * FROM (@sql) LIMIT @rowcountDefault value: SELECT TOP @rowcount x.* FROM (@sql) x
DateTimeFormat String No Format used to convert DateTime value to string for an accumulative workflow.
More info about standard formats here.
More info about defining a custom format here.
Default value: sortable format specifier.

Accumulative Build Support

The Sisense ODBC connector supports accumulative builds for certain data sources. Some of these are supported by default, while others, you must activate through the ODBC configuration file. The list of supported data sources is below. If you want to activate accumulative build support for a data source, open the file odbcConfig.json located in the directory:

C:\Program Files\Sisense\DataConnectors\DotNetContainer\Connectors\GenericODBC\

In the configuration file are JSON objects that represent each data source. Within each data source object is a boolean called “IsAccumulativeSupported”. When this value is true, the option to perform an accumulated build is available, and the value false removes this option. Below is an example object for MsSQL.


"Provider": "Mssql",

"DriverNames": [




"IsAccumulativeSupported": true


After changing the value of IsAccumulativeSupported, save the file to update the supported build options in Sisense.

Data Source IsAccumulativeSupported
MySQL true
MsSQL true
MongoDB true
BigQuery false
HiveODBC false
Snowflake false
MapR Drill false