Share this article:

Vertica Integration with Pentaho Schema Workbench: 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 Pentaho Schema Workbench: Latest Versions Tested

Software Version
Partner Products

Pentaho Schema Workbench 3.14

Pentaho Server 8.1

Desktop Platform

Microsoft Windows Server 2012

Vertica Client

Vertica JDBC 9.2.0

Vertica Server Vertica Server 9.2.0

Pentaho Schema Workbench Overview

Pentaho Schema Workbench is a tool for designing logical schemas that map to multidimensional data in Pentaho Server, an OLAP engine also called Pentaho Mondrian. Schema Workbench generates Multi-Dimensional Expression (MDX) query language, an XML metadata standard for describing multidimensional data models.

To learn about Pentaho Schema Workbench and Pentaho Mondrian, see the Pentaho documentation:

Install Pentaho Schema Workbench and Server

Download the software components from SourceForge:

  1. Download Pentaho Schema Workbench.
  2. Download Pentaho Server.
  3. Extract the contents of the ZIP files.

Since Pentaho Schema Workbench and Pentaho Server are portable tools, no additional installation steps are required.

Install the Vertica Client Driver

Before you can connect to Vertica using Pentaho Schema Workbench and Pentaho Server, you must install the Vertica JDBC driver. Follow these steps:

  1. Navigate to the Client Drivers page on the Vertica website.
  2. Download the JDBC driver package.

    Note  

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

  3. Follow the installation instructions.

How Vertica Works with Pentaho Schema Workbench

Using Pentaho Schema Workbench, you can create a multi-dimensional model based on Vertica data. You can then deploy the model to Pentaho Server, where you can explore the data.

These are the basic steps:

  1. Create a connection from Pentaho Schema Workbench to Vertica.
  2. In Pentaho Schema Workbench, create a cube based on Vertica data.
  3. Deploy the cube to Pentaho Server.
  4. Create a connection from Pentaho Server to Vertica.
  5. In Pentaho Server, explore the Vertica data in the cube.

Connect Pentaho Schema Workbench to Vertica

  1. Navigate to the directory where Pentaho Schema Workbench is stored:

    C:\psw-ce-3.14.0.0-12
  2. Place the Vertica JDBC .jar file in the lib subdirectory. For example, if you downloaded the 9.2 driver:

    C:\psw-ce-3.14.0.0-12\lib\vertica-jdbc-9.2.0-0.jar
  3. Double-click workbench.bat to launch Pentaho Schema Workbench:

    C:\psw-ce-3.14.0.0-12\workbench.bat
  4. Select Options -> Connection.

  5. On the Database Connection page, provide or select the following information:

    • Connection Name: Type a name for the connection.

      Note The connection name should be the same in Schema Workbench and in Pentaho Server.

    • Connection Type: Select Vertica 5+.

    • Access: Select Native (JDBC).

    • Host Name: Type the IP Address of the Vertica server.

    • Database Name: Type the name of the database.

    • Port Number: Type the port number of the database.

    • User Name: Type the name of the database user.

    • Password: Type the database user password.

  6. Click Test to test the connection.
  7. When the connection is successful, click OK.

Connect Pentaho Server to Vertica

  1. Navigate to the directory where Pentaho Server is stored:

    C:\pentaho-server-ce-8.1.0.0-365
  2. Double-click set-pentaho-env.bat to set environment variables required by Pentaho Server:

    C:\pentaho-server-ce-8.1.0.0-365\set-pentaho-env.bat
  3. Place the Vertica JDBC .jar file in the tomcat\lib subdirectory. For example, if you downloaded the 9.2 driver:

    C:\pentaho-server-ce-8.1.0.0-365\tomcat\lib\vertica-jdbc-9.2.0-0.jar
  4. Double-click start-pentaho.bat to launch Pentaho Server:

    C:\pentaho-server-ce-8.1.0.0-365\start-pentaho.bat
  5. In your browser, navigate to this URL:

    http://localhost:8080/pentaho/Login
  6. On the login page, expand Login as an Evaluator, then click Go to log in to Pentaho Server as an Administrator:

  7. On the Home page of Pentaho Server, click Manage Data Sources

  8. On the Manage Data Sources page, click the gear icon and select New Connection.

  9. On the Database Connection page, provide or select the following information:

    • Connection Name: Type the name for the connection.

      Note The connection name should be the same in Pentaho Server and Schema Workbench.

    • Connection Type: Select Vertica 5+.

    • Access: Select Native (JDBC).

    • Host Name: Type the IP address of the Vertica server.

    • Database Name: Type the name of the database.

    • Port Number: Type the port number of the database.

    • User Name: Type the name of the database user.

    • Password: Type the database user password.

  10. Click Test to test the connection.
  11. When the connection is successful, click OK.

Create a Sample Cube in Pentaho Schema Workbench

The following example from Pentaho Schema Workbench shows a sample cube we created using the Vertica VMart example database. The cube contains a single dimension, Customer Location, and a single measure, Sales Quantity.

Publish the Sample Cube to Pentaho Server

To publish the cube:

  1. Verify that Pentaho Server is running.
  2. In Pentaho Schema Workbench, save the schema.
  3. Select File > Publish.

  4. On the Publish Schema page, provide following information:

    • Server URL: URL of Pentaho Server.

    • User: Pentaho Server user.

    • Password: Pentaho Server password.

    • Pentaho or JNDI Data Source: The name you specified for the Vertica connection.

  5. Click Publish to publish the project in Pentaho Server.

Explore the Published Project in Pentaho Server

Use JPivot View or any other third party tool to open the deployed project in Pentaho Server.

Known Limitations

Pentaho Schema Workbench only supports JDBC connections to Vertica.

Data type incompatibilities:

  • NUMERIC data type is supported up to 16 digits. Higher values are rounded off.

  • INTERVAL YEAR TO MONTH, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, and INTERVAL data types are not supported. These data types return a syntax error.
  • BINARY, VARBINARY, and LONGVARBINARY data types are not supported. Incorrect data is displayed.
  • TIME data type does not support milliseconds. The values are truncated.
  • TIMETZ data type does not support milliseconds. The values are rounded off.
  • TIMESTAMPTZ data type is not supported. The data is displayed as blank.

For More Information

 

Share this article: