Connecting to Sisense via ODBC
Applicable to Sisense on Microsoft Windows
Sisense has its own ODBC driver that allows you to connect your tool of choice directly to the Sisense ElastiCube. Sisense also supports generic ODBC connections for third-party applications. This enables you to easily migrate from the Desktop ECM to Sisense. For more information, see Introduction to Data Sources.
Note: Generic ODBC is not supported on Linux.
Some use case examples:
- Connect a third-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 Sisense through the Help Center for more information for implementing this use case.
Note: 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 the 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 list. You will be prompted to enter your username and password.
- From the ElastiCube 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.