|Share this article:|
Vertica Integration with Sisense: Connection Guide
About Vertica Connection Guides
Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third-party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and Sisense as of April, 2016.
Sisense is a Business Intelligence tool that supports rapid preparation, visualization, and analysis of large and disperate data sets. Using proprietary In-Chip™ technology, Sisense processes ad-hoc queries without advance data preparation for each new query. With Sisense, you can build interactive dashboards and reports that can be shared quickly.
Sisense uses ODBC to connect to Vertica. This document is based on the results of testing the Vertica 7.2.x ODBC driver with Sisense 0.99.1.0.3.
Download and Install Sisense
To download and install Sisense:
- Check the system requirements described in the Sisense documentation.
- On the Sisense home page, select Start Free Trial or Test Drive on your Data.
Sisense installs these programs on your machine:
- Sisense ElastiCube Manager—for creating the model and defining the data source
- Sisense Server Console—for viewing the status of the ElastiCube
For details about installing Sisense, see:
Download and Install the Vertica Client Driver
Before you can connect to Vertica using Sisense, you must install a Vertica client package. This package includes the ODBC driver that Sisense uses to connect to Vertica.
Download the Vertica Client Driver
- Go to the Vertica Client Drivers page on my.vertica.com.
- Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.
Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
Install the Vertica Client Driver
Based on the client package you downloaded, follow the steps for installation from the Vertica documentation.
Define a Data Source Name
Sisense uses a generic ODBC driver to connect to Vertica. Before you can add Vertica as a data source, you must define a DSN (data source name) on the Windows machine where you installed Sisense.
To create a DSN, follow the instructions in Creating a Data Source Name in the Vertica documentation.
Connect Sisense to Vertica
Before you can create dashboards using Sisense, you must create ElastiCubes using ElastiCube Manager. The ElastiCube Manager is a visual environment for importing multiple data sources, creating relationships, and extracting and preparing data for analysis/visualization.
To connect Sisense to Vertica, follow these steps:
- Start Sisense ElastiCube Manager.
- In the Quick Start box, select New ElastiCube and type a new name.
- When you click the green check mark next to the new name, the ElastiCube Manager displays a blank canvas for the new ElastiCube project.
Click the Add Data button on the menu bar.
- From the Database servers list, select Generic ODBC Driver.
- On the ODBC Connectivity Properties page, select the Vertica DSN that you previously created.
Note If you did not define a DSN, as described in Define a Data Source Name, your database will not appear in the list.
- To test the connectivity, click Test Connection.
- If the connection is successful, click OK.
- On the Add tables from Vertica Tables page, select the tables and views that you want to use in reports.
You can change the query for a table or view by selecting it and clicking Edit.
You can view the data by selecting Preview.
- When you have selected the data and adjusted the queries as desired, click Add to add it to the ElastiCube project canvas.
- To create a relationship between two tables:
a. Click the key column in the first table.
b. Move your cursor to the Merge area.
c. Drag the key column to the target column in the second table.
The join relationships appear on the canvas as shown here:
- When you have finished creating the join relationships, click Build to start building the ElastiCube.
- Select the target server.
- If this is the first time you are building this ElastiCube, click Build Entire ElastiCube.
If you are making changes to an existing ElastiCube, select Build Schema Changes.
- Click Build.
- When the build is complete, start the Sisense application.
A browser opens to the URL of the Sisense web services.
- In the Dashboard panel on the left, click the + icon to add a new dashboard.
- Select your ElastiCube and change its title.
- Click Create.
- Under NEW WIDGET, click + Select Data.
- Select a column of data for analysis and reporting.
You can now begin analyzing and visualizing your data.
Vertica Data Types
Sisense supports most Vertica data types. Sisense does not support Vertica Interval and time zone data types.