Share this article:

Vertica Integration with Logi Info: 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 Logi Info as of December, 2017.

Logi Info Overview

Logi Info is a business intelligence tool that provides wonderful insights for fast, visual dashboards. Business users can interact with data for data blending and analysis across disparate data sources. For more information, visit the Logi Info website. To start learning about Logi Info, see the Tutorials and User Guides documents.

Vertica and Logi Info Software Versions

This document provides guidance using the software listed as follows:

Software Version
Partner Client

Logi Info 12.2.116

Desktop Platform

Microsoft Windows 10

Client ado.net, JDBC
Vertica Client

Vertica 9.0.1

Vertica Server

Vertica Server versions 9.0.1

Server Platform CentOS 7.3

Install Logi Info

Note You must register on the Logi Analytics website before downloading the software.

  1. Go to the Logi Analytics download page.
  2. Complete the member registration information and click Join. You will receive a password via email shortly after completing the registration.
  3. Log in to the DevNet site. You will be asked to change your password.
  4. Follow the instructions documented here to install Logi Info on Windows.

Install the Vertica Client Driver

Logi Info uses ADO.NET and JDBC drivers to connect to Vertica using IIS Web Services and Web Server. Before connecting Logi to Vertica, you must download and install the Vertica client package that includes the ADO.NET and JDBC drivers. Download and install the Vertica client driver as follows:

  1. Go to the Vertica Client Drivers page on MyVertica.
  2. Download the Vertica client drivers that match your operating system and the Vertica version that you are using.
  3. Follow the client installation instructions in the Vertica documentation.

Connect to Vertica

With Logi Info running, do the following to connect to Vertica:

  1. Select Studio from the Start menu:

    Note The first time you access Studio, a Getting Started screen appears. Close this to continue using Studio.

  2. From the Welcome screen, select New Application:



    You can also select New Application from the ribbon menu on the Logi Info Studio main page:



    A pop-up appears with the following options for creating a new application:



    Microsoft.Net

    Java
  3. Select the option for which you want to create an application and follow the instructions below.

Creating an Application for Microsoft.Net (IIS)

Follow the steps below to create an application for Microsoft.Net:

  1. Select Microsoft .Net from the pop-up window and click OK.
  2. Enter the Application name for the IIS server and click OK.



    The New Application Wizard appears.
  3. Run the New Application Wizard to create your application.
  4. After creating the application, locate the Vertica.Data.dll file. For example, for a 64-bit driver package installed on Windows, you can find the .dll at C:\Program Files\Vertica Systems\ADO.NET\Vertica.Data.dll.
  5. Copy Vertica.Data.dll to to the Logi product bin directory that contains .dll files. For example, the default location on Windows is C:\<Application Folder path>\VerticaNet\bin.

    This new application has a default report associated with it. You can see the report in the application's left pane. For example:


    Rename the report to fit your organization's requirements. You will need this report going forward when using Logi Info.

  6. Under the Settings tab, right-click Connection > Element Wizard > Add a Database Connection.



    The Connection Wizard appears. Enter the following information to complete the Connection wizard:

    Connection ID: Enter a unique connection name.

    Connection Type: Enter Vertica as the connection type.

    Vertica Server Name: Enter the name of your Vertica server.

    Vertica Database: Enter the name of your Vertica database.

    Port number: Enter the port number.

    Vertica User Name: Enter the username you use to log into the Vertica database.

    Vertica Password: Enter the password you use to log into the Vertica database.

  7. Logi Studio tests the connection. If it is successful, the following message appears:

    Test results for Vertica connection:

    Connection Successful.


  8. Click Next and Finish to close the wizard.

Creating an Application for Java

Follow the steps below to create an application Java:

Note Before you begin you must have an Apache Tomcat server or comparable Java application server on your environment.

  1. Select Java from the popup window and click OK.
  2. Enter the application name for the IIS server and click OK.



    The New Application Wizard appears.
  3. Run the New Application Wizard to create your application.
  4. After creating the application, locate the Vertica.jar file.
  5. Copy Vertica.jar into the Apache Tomcat lib directory that contains JARs. For example, the location on Windows is C:\<Apache Tomcat Application Folder path>\Apache Tomcat\lib.

    This new application has a default report associated with it. You can see the report in the application's left pane. For example:


    Rename the report to fit your organization's requirements. You will need this report going forward when using Logi Info.

  1. Under the Settings tab right-click Connection > Element Wizard > Add a Database Connection.



    The Connection Wizard appears. Enter the following information to complete the Connection wizard:

    Connection ID: Enter a unique connection name.

    Connection Type: Enter Vertica as the connection type.

    Vertica Server Name: Enter the name of your Vertica server.

    Vertica Database: Enter the name of your Vertica database.

    Port number: Enter the port number.

    Vertica User Name: Enter the username you use to log into the Vertica database.

    Vertica Password: Enter the password you use to log into the Vertica database.

  1. Logi Studio tests the connection. If it is successful, the following message appears:

    Test results for Vertica connection:

    Connection Successful.


  2. Click Next and Finish to close the wizard.

View Data in Vertica

View data residing in the Vertica database by creating a sample report as follows:

  1. Open the Default report that was created in the steps above:



  2. Right-click Body and select Element Wizard > Add a Data Table:

  3. Select your Vertica connection from the Data Table popup dialog:



  4. Write a custom query to retrieve data from the table or select multiple tables from the SQL Query Builder wizard. Click Next.



    The report appears in the wizard.



  5. Click Next and Finish.

You can also run this report on web browser.


Known Limitations

Before using Logi Info with Vertica, be aware of the following known limitations:

JDBC Limitations

  • Time values are truncated into Timestamp data type.
  • All test cases failed for TimeTZ and TimestampTZ because incorrect data are displayed.
  • Binary, VarBinay and Long Varbinary datatype values are displayed. but in hex vs how they show in vsql as octal. For example:

    \377\000\000\000 appears as

    FF000000 in Logi.

ADO.Net

  • All test cases for IntervalMonth failed because values are incorrectly displayed.
  • Millisecond values are truncated in TimeTZ and TimeStampTZ.
  • Time values are truncated into Timestamp data type.
  • Binary, VarBinay and Long Varbinary datatype values are displayed. but in hex vs how they show in vsql as octal. For example:

    \377\000\000\000 appears as

    FF000000 in Logi.

 

Share this article: