Share this article:

Vertica Integration with Sisense: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and Sisense: Latest Versions Tested

Software Version
Partner Product

Sisense 8.2.0.10219

Desktop Platform

Windows Server 2016 Standard

Vertica Client

Vertica ODBC 9.3.1-0

Vertica JDBC 9.3.1-0

Vertica Server

Vertica Analytic Database 9.3.1-0

Sisense Overview

Sisense is a Business Intelligence tool that is widely used for data analytics. With Sisense, you can combine and analyze data from multiple sources. The Sisense ElastiCube Manager enables non-technical users to join large data sets across multiple data sources and create visualizations that are accessible on different devices, including mobile devices.

Installing Sisense

You can download a 14-day free trial of Sisense. Follow these steps:

  1. On the Sisense website, click on Test Drive on Your own Data.
  2. Provide the registration details.
  3. Click Start Your POC.
  4. Follow the installation instructions in the Sisense documentation.

Note  

Sisense installs the following programs on your computer:

  • Sisense—Weblink
  • Sisense ElastiCube Manager—For creating the model and defining the data source
  • Sisense Server Console—For viewing the status of the ElastiCube

Installing the Vertica Client Driver

Sisense uses the Vertica ODBC or JDBC driver to connect to your Vertica database. To download and install the Vertica client package:

  1. Navigate to the Vertica Client Drivers page.
  2. Download the ODBC Vertica client package that is compatible with the Vertica server version.

    Note  

    For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  3. Double-click the installer and follow the prompts. When prompted to select one or more drivers, select ODBC only.
  4. Follow the installation instructions in Installing the Client Drivers and Tools on Windows.
  5. Create an ODBC Data Source Name (DSN) as described in Creating an ODBC Data Source Name (DSN).
  6. If you want to connect using the JDBC driver, download the JDBC driver package that is compatible with the Vertica server version.

Note For ODBC connectivity, you need to use ElastiCube Manager to get data into Sisense and use Sisense Weblink to create visualizations. For JDBC connectivity, you need to use Sisense Weblink to get data into Sisense and create visualizations.

Connecting Sisense to Vertica

You can connect to Vertica using either ODBC or JDBC drivers.

Connecting to Vertica Using ODBC

  1. Start Sisense ElastiCube Manager from the Windows Start menu or click ElastiCubeManager.exe in the Sisense installation directory.

    The Sisense ElastiCube Manager home screen appears.

  2. On the File menu, click New ElastiCube File.

  3. Provide a name for the ElastiCube File and click the check mark.

  4. Click Add Data from the toolbar.
  5. Click Generic ODBC Driver.

  6. Select your DSN from the drop-down list and click Test Connection.
  7. When the connection is successful, click OK.

  8. Select the tables that contain the data you want to use and click Add.

    You can view the source data by selecting the Preview check box.

  9. Join the data using the common keys.

  10. Select Build from the toolbar menu and Build Schema Changes if you are changing an existing file, or Build Entire ElastiCube if you are creating a new ElastiCube. Click Build.

  11. When the build is complete, click File > Save ElastiCube and click the dashboard from the toolbar menu.

    The Sisense tool to create dashboards and widgets displays in a web browser:

    http://localhost:8081/app/main#/home

  12. Provide your credentials and click Login.

Connecting to Vertica Using JDBC

  1. Open Sisense in a web browser

    http://localhost:8081/app/main#/home

  2. Provide your credentials and click Login.

  3. Click Data on the Sisense home page and click Elasticube Models.

  4. Provide a name for the Elasticube and click Save.
  5. Click +Data tab to add data to the Elasticube.

  6. Select Generic JDBC from the list of available connectors.

  7. Provide the following details and click Next.

    • Connection String
    • JDBC JARs Folder (directory path where Vertica JDBC JAR file is placed)
    • Driver’s Class Name
    • Database User Name
    • Database Password

  8. Select Database > Schema > Table(s) from the navigation pane and click Done.

    Note Clicking the table name enables editing the source query to apply filters and select the required columns, relationships among tables.

  9. Click Build. Select Replace All if the table(s) imported need data to be refreshed completely. Select Changes Only if Changed Source Data needs to be refreshed and click Build again.

Note Sisense dashboards and widgets can be created from Analytics tab on Sisense WebLink using the Elasticubes created in both Elasticube Manager(ODBC) and Sisense WebLink(JDBC)

Known Limitations

  • BINARY, VARBINARY, and LONG VARBINARY data types are not supported in visualizations.
  • IINTERVALSECOND and INTERVALMONTH data types are supported in visualizations using JDBC only.
  • LONG VARCHAR data type values are displayed up to a string length of 65535.
  • DECIMAL data type is supported up to 15 digits beyond which the values are rounded off.
  • For TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ data types, milliseconds are truncated.
  • For TimeTZ and TimeStampTZ data types, time zone is not supported for ODBC connectivity.

For More Information

 

Share this article: