Vertica Integration with Business Intelligence and Reporting Tools (BIRT): Connection Guide

Applies to Vertica 7.1.x and earlier 

 

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 Business Intelligence Reporting Tools as of October, 2015.

Business Intelligence and Reporting Tools (BIRT) Overview

BIRT is an open source Eclipse-based system that has reporting and business intelligence capabilities. BIRT works with the Java Development Kit to generate data visualizations and reports. This document describes the connection mechanism to your Vertica database for BIRT. This connection is required to verify that BIRT interprets and displays the Vertica data types correctly. This document assumes readers are familiar with both Vertica and BIRT.

This document is based on results from testing Vertica 7.1.x with BIRT 4.2.2 on a Windows 2008 platform via the JDBC driver.

Download and Install BIRT

BIRT is a Java-based application available for Windows, Linux, and Mac OS X operating systems.

Download the Java Development Kit

Before installing BIRT, you must download and install version 1.6 or later of the Java Development Kit (JDK). Follow the instructions for downloading and installing from the Oracle website.

Download and Install BIRT

  1. Download BIRT from the Eclipse website. Download the All-in-One version. If you already have Eclipse installed, download the Framework version.
  2. For help with installing BIRT, go to Getting Started with Eclipse at the Eclipse website.

Create a Shortcut

After you download BIRT, open the BIRT Designer using the Eclipse exe.

  1. From your desktop, right click and choose New > Shortcut.
  2. Type the location: C:\Program Files\eclipse\eclipse.exe.
  3. Click Next.
  4. Enter Eclipse as the shortcut name.
  5. Open Eclipse using this shortcut and point to the correct BIRT workspace.

Download and Install the Vertica Client Drivers

Before you can connect to HP Vertica using BIRT, you must download and install the Vertica client package. This package includes the Vertica JDBC driver that BIRT uses to connect to Vertica.

Download HP Vertica Client Drivers

  1. Go to the Vertica Client Drivers
  2. Download the version of the Vertica client package that matches your operating system and Vertica server version.

Note HP Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more, see Client Driver and Server Version Compatibility in the HP Vertica documentation.

Install HP Vertica Client Drivers 

Based on the client package you downloaded and your operating system, follow the steps for installation from the Vertica documentation.

Build a Data Source

Before you begin designing your report in the layout editor, you must build a BIRT data source to connect your report to a database. You should specify connection information that is specific to that type of data source.  For Vertica, you must specify the driver .jar file for use with BIRT Report Designer. Follow these steps to build a data source.

  1. Select Window > Show View > Data Explorer.
  2. Right-click Data Sources and choose New Data Source from the context menu.
  3. Select JDBC Data Source from the list of data source types.
  4. Enter a Data Source Name and click Next.
  5. Enter the Connection information for the new data source. The following fields are required:
    • IP/Host name
    • Port
    • User
    • Password

  6. Click Manage Drivers and select the Vertica JDBC .jar.

    Birt2.png.

  7. Click OK
    8. .
  8. In the New JDBC Data Source Profile dialog box, the following fields are required:
    • Driver Class
    • Database URL
    • Username
    • Password

     Birt3.png

  9. Click Test Connection. If the connection is successful, a dialog box will appear.
  10. Click Finish.

The BIRT Report Designer creates a new data source that connects to your HP Vertica database. It appears within Data Sources in the Data Explorer.

Create a Project

Eclipse organizes files by projects. You can create one project to organize all of your reports or create multiple projects to organize your reports by category. Eclipse creates a directory in your file system for each project you create. To create a new project in BIRT, follow these steps:

Note If you are using BIRT Rich Client Platform (RCP) Report Designer, this task is not applicable.

  1. Choose File > New > Project.
  2. In the New Project dialog box, expand the Business and Intelligence Report Tools

  3. Select Report Project and click Next.

    Birt4.png

  4. Enter a project name and click Finish.

You should see the project in the Navigator view.

Create a Report

You can create a report in one of the following ways:

  • Start with a blank report design
  • Use a predefined report template

To create a blank report design, follow these steps:

  1. Select File > New > Report. A new report appears.

  2. You can add grids from the palette and format the report as shown below:

    Birt5.png

To create a report using a predefined template, see How to Use a Template Report Item in the BIRT documentation.  

Build a Data Set

A data set identifies the data to retrieve from a specified data source. For example, if your report connects to a JDBC data source, you would use a SQL SELECT statement to specify which data to retrieve. To build a data set, follow these steps:

  1. In the Data Explorer tab, right-click Data Sets.
  2. Select New Data Set from the context menu.

  3. In the New Data Set dialog box, specify the Data Set Type and Data Set Name.

    Birt6.png

  4. Click Next.

  5. From the list of Available Items, select the table you want to query and write the appropriate query.

    Birt7.png

  6. Click Finish.

You can create data sets for other tables and queries by following the same steps. After all data sets are created, you can use them to build and layout reports that represent the data.

Troubleshooting

When creating a sample listing report, you may get the following error message, known as an Invalid Fetch Size:

Birt8.png

You can fix this problem by changing Row Fetch Size of the data set to 10 as shown in the following graphic:

Birt9.png

For More Information

For More Information About… …See

BIRT

http://www.eclipse.org/birt/

Vertica Community Edition

https://vertica.com/community/

Vertica Documentation

https://vertica.com/documentation/vertica/

Big Data and Analytics Community

https://vertica.com/big-data-analytics-community-content/