Connecting to Sisense via ODBC
Sisense has its own ODBC driver that allows you to connect your tool of choice directly to the Sisense ElastiCube.
Some use case examples:
- Connect a 3rd party visualization tool directly to the ElastiCube.
- Build pixel-perfect reports using your existing reporting tools by connecting directly to the ElastiCube
- Import data directly into Excel from ElastiCube tables
- Importing Sisense ElastiCube tables from one ElastiCube to another. Contact us through our Help Center for more information for implementing this use case.
Note: Once installed with your Administrator’s user credentials, any user can access the ElastiCube over ODBC. Connecting to Sisense via ODBC is not recommended for use cases that require data security.
This page will cover the following:
- Installing the Sisense ODBC driver
- Creating a new Sisense ODBC connection
- Use case example 1: Importing data from an ElastiCube into Excel
- Use case example 2: Importing data from an ElastiCube into Tableau
The Sisense ODBC Driver is licensed for use by the number of licensed Users (Administrator, Designer or Viewer) of the Software licensed hereunder. Only the number of licensed Users of the Software are entitled to use or view data or query results produced via the Sisense ODBC Driver in third party tools.
Installing the Sisense ODBC driver
Depending on whether you will be connecting to Sisense through a 32 or 64 bit application, download and install the appropriate driver:
Creating a New Sisense ODBC Connection
Sisense ODBC is a licensed feature.
Connections will only be accepted by the Sisense ElastiCube if the ODBC functionality has been enabled in your license and if you are using Sisense Version 6.0 or later. Please contact your customer support manager or open a request through our Help Center if your license currently does not include this. To upgrade to the latest version of Sisense, click here.
The following procedure explains how to create a new Sisense ODBC connection. Creating such a connection has to be done on every machine from which you want to connect to an ElastiCube via ODBC. Each connection is defined for a specific ElastiCube, so repeat this step for multiple ElastiCubes if necessary.
To create a new Sisense ODBC connection:
- From the Start menu, open the Windows ODBC Data Sources application. Notice that there are two different instances of it. One for 64 bit and another for 32 bit.
Tip for Windows 7 users: To quickly access the 32-bit ODBC window type "c:\Windows\SysWOW64\odbcad32.exe" from the Run… menu.
- Under the ‘System DSN’ tab click Add…, and select “Sisense ODBC Driver”. Click Finish.
- In the “New Sisense Datasource” window, fill in the following details:
In the Data Source Name field, enter a descriptive name for your connection.
(Optional) In the Description field, you can enter a description.
In the Server field, enter the Sisense server address.
- Click on the ElastiCube drop-down list. You will be prompted to enter your username and password.
- From the ElastiCube drop-down list, select the ElastiCube.
- If you are using a secured connection (SSL), select Use secure connection (SSL).
- Click Test to verify the connection to the ElastiCube.
- Click OK to save the connection.
Connecting to Sisense in Excel
In Excel, you can use the ElastiCube ODBC connector to import data from your ElastiCube into your Excel worksheet or pivot, and also through Microsoft Query. After downloading and adding the ODBC connector, you can use any of the following methods to import the data from the ElastiCube.
Method 1: Import data directly into a pivot table
- In Excel, create a Pivot Table by clicking Pivot Table under the Insert ribbon.
- Select Use an external data source. Click Choose Connection… to browse and locate the source table.
If the connection does not appear, click Browser for More…, and then the Connect to New Data Source option.
Select ODBC DSN, and then the data source. Enter your username and password in the appropriate fields, and click OK.
- Click OK to connect to the data.
Now you can drag and drop the fields into the relevant columns and rows, as you would for any pivot table in Excel.
Method 2: Import data using the Microsoft Query option
- Under the Data ribbon, select From Other Sources > From Microsoft Query.
- Select the database, and click OK.
- You can either click on the tables you want to add, or click Cancel to enter a visual environment and add the relevant tables.
- You can now drag and drop connectors between fields from the different tables to create relationships.
- Click on a field to add the data to Excel. Repeat for each field that you want to add.
- You can now import the data into the worksheet. Click on the import button, and select the import options, such as where you want to import the data in the worksheet.
Using Sisense as a Live Connection in Tableau
In Tableau, you can use the ElastiCube ODBC connector to run your queries directly in the Sisense ElastiCube instead of in Tableau’s in memory data engine. After downloading and adding the ODBC connector, follow these steps:
- In Tableau, in the Data tab, click Connect to Data > Other Databases (ODBC).
- From the DSN dropdown menu, select the ElastiCube, and click Connect.
- You can now begin working with the data and define relationships.
Add tables as you would for any other data source in Tableau, and click Update Now.
- Create a new worksheet in Tableau, and add the tables from the ElastiCube as needed to run queries and create visualizations.
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:
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.
After changing the value of IsAccumulativeSupported, save the file to update the supported build options in Sisense.