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.

How Vertica Works with Pentaho Mondrian using 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:

Create a connection from Pentaho Schema Workbench to Vertica.

In Pentaho Schema Workbench, create a cube based on Vertica data.

Deploy the cube to Pentaho Server.

Create a connection from Pentaho Server to Vertica.

In Pentaho Server, explore the Vertica data in the cube.

Connect Pentaho Schema Workbench to Vertica

Navigate to the directory where Pentaho Schema Workbench is stored:

C:\psw-ce-3.14.0.0-12

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

Double-click workbench.bat to launch Pentaho Schema Workbench:

C:\psw-ce-3.14.0.0-12\workbench.bat

Select Options -> Connection.

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.

Click Test to test the connection.

When the connection is successful, click OK.

Connect Pentaho Server to Vertica

Navigate to the directory where Pentaho Server is stored:

C:\pentaho-server-ce-8.1.0.0-365

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

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

Double-click start-pentaho.bat to launch Pentaho Server:

C:\pentaho-server-ce-8.1.0.0-365\start-pentaho.bat

In your browser, navigate to this URL:

http://localhost:8080/pentaho/Login

On the login page, expand Login as an Evaluator, then click Go to log in to Pentaho Server as an Administrator:

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

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

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.

Click Test to test the connection.

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:

Verify that Pentaho Server is running.

In Pentaho Schema Workbench, save the schema.

Select File > Publish.

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.

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.