Share this article:

Vertica Integration with OBIEE: Connection Guide

Applies to Vertica 7.2.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 OBIEE as of May, 2016.

OBIEE Overview

Oracle Business Intelligence Enterprise Edition (OBIEE) is a Business Intelligence platform that includes capabilities such as ad hoc querying, interactive dashboards, and mobile analytics. OBIEE helps businesses analyze data to make strategic business decisions. OBIEE is available as a Windows 32-bit or 64-bit application. OBIEE uses the ODBC driver to connect to your Vertica database.

This document is based on the results of testing with the following versions:

  • Vertica 7.2.x with OBIEE 12c (12.2.1) 64-bit on Windows
  • Vertica 7.2.x with OBIEE 11g (11.1.1.7) 64-bit on Windows
  • Vertica 7.1.x with OBIEE 11g (11.1.1.7 and 11.1.1.6) 64-bit on Windows

Download and Install OBIEE

OBIEE is part of the Oracle Fusion Middleware Software group. Before you install OBIEE, review the system requirements and prerequisites for installation from the Oracle Fusion Middleware System Requirements and Specifications page.

You can download the latest version of OBIEE from the Oracle Business Intelligence 12c Downloads page.

Install OBIEE as an administrator and follow the instructions for installation.

Vertica Client Drivers

Before you can connect to Vertica using OBIEE, you must install the Vertica client driver package. This package includes the ODBC client driver that OBIEE uses to connect to Vertica.

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers
  2. 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 Vertica Client Drivers

Based on the client driver package you downloaded, follow the steps for installation from the Vertica documentation.

Connect OBIEE to Vertica

Follow the steps in this section to create a connection from OBIEE to Vertica. 

Install the VMart Example Database

This document uses the Vertica VMart example database to create a connection from OBIEE to Vertica.  You can optionally install VMart by following the tutorial in the Vertica documentation for Installing and Connecting to the VMart Example Database.

The VMart example database includes three schemas: Public, Online Sales, and Store. The schemas are interrelated and share many dimensions. For details, see Appendix: VMart Example Database Schema, Tables, and Scripts in the Vertica documentation.

Create a New Repository

  1. Launch the OBIEE administration tool.
  2. Navigate to Oracle_Home > user_projects > domains > bi > bitools > bin > administrators. The BI Administration Tool window opens.
  3. Click File > New Repository.
  4. Enter the new repository details, including the following required fields:
    • Name
    • Location
    • Repository Password
    • Retype Password

 NewRepository.png

  1. Click Next.
  2. Select the data source connection type. In this case, choose Vertica and click Next.
  3. Select the metadata types you want to import from your selected data source and click Next.
  4. Select the metadata objects you want to import from your selected data source.
  5. Click Finish. Now the imported schemas and underlying tables appear from the imported metadata objects.

Create Table Joins

  1. From the physical section of the Oracle BI Administration Tool, select a table.
  2. Right-click the selected table and update the row count for the tables.
  3. To create a physical diagram for the selected tables, right-click and select Physical Diagram > Selected Object(s) Only. The tables appear on the physical diagram.

 physical.png

  1. Establish joins between the tables by clicking the New Join icon.

 newjoin.png

  1. Name your join. Click Ok.

 namejoin.png

  1. The join appears on your physical diagram. Save the physical diagram.
  2. When prompted to check for global consistency, click Yes.

 global.png

  1. Fix any errors. You can ignore the warnings.

Create a New Business Model

  1. In the Business Model and Mapping layer, right-click and select New Business Model.

 businessmodel.png

  1. Enter a name for your business model.

Create a New Subject Area

  1. In the Presentation layer, right-click and select New Subject Area.

 presentationlayer.png

  1. Name your subject area.

Create a Business Model Diagram

  1. Drag and drop the tables with a physical join into the Business Model and Mapping layer.
  2. Right-click the selected tables and select Business Model Diagram > Selected Tables and Direct Joins.
  3. Save the business model diagram.
  4. When prompted to check for global consistency, click Yes.
  5. When prompted to mark the business model as available for queries, click Yes.

 queries.png

  1. Fix the errors and ignore the warnings.
  1. Right-click each column that contains measure values and select Properties.
  2. Apply the corresponding aggregation rule to each column.
  3. After you have applied the aggregation rules, the icon next to each column changes, as shown in the following:

iconchanges.png

Create Repository (RPD) File

  1. Drag and drop tables from the Business Model and Mapping layer to the Presentation layer.
  2. Save the RPD file.
  3. When prompted to check for global consistency, click Yes.

Upload Repository (RPD) File

After you save the RPD file, you must upload the RPD. The Enterprise Manager does not have an option to deploy the RPD. You should use a weblogic script command named “uploadrpd” to upload the repository to the Oracle BI Server.

  1. Execute the utility using a launcher script located here:
    <Oracle_Home>/user_projects/domains/bi/bitools/bin
  2. Execute the following script:
    uploadrpd -I <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

You can now access the analytics web to create reports and dashboards.

Optimized Settings with OBIEE

Configure your database feature settings in OBIEE for optimal efficiency when connecting to Vertica. If you experience issues with parallel execution of OBIEE queries in Vertica, use the following workaround. These changes are specific to the Progress DataDirect Driver Manager that is bundled with OBIEE:

  1. Log on to the machine where OBIEE is installed.
  2. Navigate to the lib directory where the Progress DataDirect Driver Manager is installed. For this document: /home/oracle/middlewarehome/Oracle_BI1/common/ODBC/Merant/7.1.6/lib.
  3. Rename the file odbccurs.so to odbccurs.so.OLD.
  4. Navigate to the directory where opmnctl is located.
  5. Execute the ./opmnctl stopall command. 
  6. After the process shuts down, execute the ./opmnctl startall command. 
  7. Run the dashboard using OBIEE to verify that the queries are executed in parallel.
  8. Run the following command on the OBIEE environment. This will make sure that the "cursor" library is not loaded. 
lsof –u oracle | grep odbccur

For more information, see the Vertica Knowledge Base.

Troubleshooting

You may encounter some issues when connecting to Vertica using OBIEE. The following section describes known issues and workarounds.

Table View Error

If you try to view the table data in the Oracle BI Administration Tool, you may encounter the following error:

[NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
            

The tool does not let you view the table data. To resolve this issue, follow these steps:

  1. In the Physical layer, right-click Connection Pool and select Properties.
  2. Check the Require fully qualified table names box.

 connectionpool.png

  1. Click OK.

Now you can view the table data.

Known Limitations

The following is a list of known data type limitations using OBIEE with the ODBC driver:

  • OBIEE does not support Vertica BINARY, VARBINARY, LONG VARBINARY, TIMESTAMP TZ, TIME TZ, INTERVALMONTH, INTERVALSECOND, and LONGVARCHAR data types.
  • OBIEE rounds money data types to maximum and minimum data type values.
  • OBIEE does not support Vertica HOURTOSECOND, HOURTOMINUTE, DAYTOSECOND, and YEARTOMONTH data types.

For More Information

For More Information About… See…

OBIEE

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/overview/index.html

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

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

Share this article: