Vertica Integration with Oracle Business Intelligence Suite Enterprise Edition (OBIEE): 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. This document provides guidance using the latest versions described in the next section.

Vertica and OBIEE: Latest Versions Tested

Software Version
Partner Product

OBIEE 12c (12.2.1.4) 64-bit

Partner Product Platform

Window Server 2019

Vertica Client

Vertica ODBC 12.0.0

Vertica Server Vertica Analytic Database 12.0.0

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 Vertica ODBC driver to connect to your Vertica database.

Installing 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.

Download the latest version of OBIEE from the Oracle Business Intelligence 12c Downloads page.

You must Install OBIEE as an administrator. For detailed information on installing OBIEE, see the Oracle documentation.

Installing the Vertica Client Driver

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.

  1. Navigate to the Vertica Client Drivers page on the Vertica website.
  2. Download the version of the Vertica client package that is compatible with your Vertica server version.

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

  1. Follow the installation instructions for your client platform:

  1. Create a DSN as described in Creating an ODBC Data Source Name (DSN).

Connecting OBIEE to Vertica (Windows)

Installing 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.

Creating a New Repository

  1. Select Oracle_Home > user_projects > domains > bi > bitools > bin > admintool. The BI Administration Tool screen appears.
  2. Select File > New Repository.
  3. Enter the new repository details, including the following required fields:
    • Name
    • Location
    • Repository Password
    • Retype Password

 NewRepository.png

  1. Click Next.
  2. Select Vertica as the data source connection type 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.

    The imported schemas and underlying tables appear from the imported metadata objects.

Creating Table Joins

  1. Right-click a table from the Physical section of the Oracle BI Administration Tool and select Update Row Count.
  2. Right-click the table again and select Physical Diagram > Selected Object(s) Only to create a physical diagram for the selected tables. The tables appear on the physical diagram.

     physical.png

  1. Select the New Join icon to establish joins between the tables.

     newjoin.png

  1. Name your join and click Ok.

     namejoin.png

  1. The join appears on your physical diagram. Save the physical diagram.
  2. Click Yes when prompted to check for global consistency.

     global.png

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

Creating a New Business Model

  1. Right-click in the Business Model and Mapping section and select New Business Model.

     businessmodel.png

  1. Enter a name for your business model.

Creating a New Subject Area

  1. Right-click in the Presentation section and select New Subject Area.

     presentationlayer.png

  1. Name your subject area.

Creating a Business Model Diagram

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

     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

Creating a Repository (RPD) File

  1. Drag and drop tables from the Business Model and Mapping section to the Presentation section.
  2. Save the RPD file.
  3. Click Yes when prompted to check for global consistency.

Uploading a 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.

Connecting OBIEE to Vertica (Linux)

You can create a repository (RPD) file as described in Create a Repository (RPD) File. Uploading an RPD file in Linux is the same process described in Upload a Repository File.

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

Note In steps 2 and 5 below, you will see how the directory paths of ODBCInstLib and LD_LIBRARY_PATH have changed since OBIEE 12c release 12.2.1.4.

  1. Download the ODBC driver for Linux OS platform from the Vertica Client Drivers page. Follow the instructions on the Installing the Client Drivers on Linux and UNIX-Like Platforms page to install the driver.
  1. Create a vertica.ini file in a directory of your choosing. The following example uses the /home/obi directory. Check your OBIEE version and select the settings as shown:

    ODBCInstLib=/home/obi/Oracle/Middleware/Oracle_Home/bi/common/ODBC/Merant/7.1.4/lib/libodbcinst.so
    ErrorMessagesPath=/home/obi/vertica/opt/vertica/lib64
    LogPath=/tmp/temp
    LogLevel=6
    DriverManagerEncoding=UTF-16

    In OBIEE 12c release 12.2.1.4 and later, the directory Merant has been replaced by oracle.bi.datadirect.odbc. The directory path of ODBCInstLib is changed as shown in the following example:

    ODBCInstLib=/home/obi/Oracle/Middleware/Oracle_Home/bi/common/ODBC/oracle.bi.datadirect.odbc/7.1.4/lib/libodbcinst.so
    ErrorMessagesPath=/home/obi/vertica/opt/vertica/lib64
    LogPath=/tmp/temp
    LogLevel=6
    DriverManagerEncoding=UTF-16
  1. Create the DSN in the odbc.ini file located in your installation directory. The following example uses /home/obi/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/config/fmwconfig/bienv/core as the installation directory. Note that the DSN Name is vertica:
    vertica=Vertica DB
    Driver = /home/obi/vertica/opt/vertica/lib64/libverticaodbc.so
    Server=XX.XX.XX.XXX
    Database=vertica_db
    Port=5433
    UserName=dbadmin
    Password=vdb
    Locale=en_US
    OdbcConformanceLevel=3.0
    ODBC64SqlHandleSize=64
    ODBC64SqlLenSize=64
    RebrandedLib=TRUE
    DriverUnicodeType=
    LazyLoading= 
    
  2. Specify the vertica driver location in the odbcinst.ini file. The following example uses /home/orcl/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/config/fmwconfig/bienv/core/ as the driver location:

    [Vertica]
    Description=Vertica driver
    Driver=/home/obi/vertica/opt/vertica/lib64/libverticaodbc.so
  1. Edit the obis.properties file located in /home/obi/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/config/fmwconfig/bienv/OBIS/.

    The directory path of LD_LIBRARY_PATH in OBIEE 12c prior to release 12.2.1.4 is as shown:

    export LD_LIBRARY_PATH=/home/obi/Oracle/Middleware/Oracle_Home/bi/common/ODBC/Merant/
    7.1.4/lib:/home/obi/vertica/opt/vertica/lib64:$LD_LIBRARY_PATH
    export VERTICAINI=/home/obi/vertica/opt/vertica.ini
    export ODBCINI=/home/obi/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/config/fmw
    config/bienv/core/odbc.ini
    export ODBCINI

    The directory path of LD_LIBRARY_PATH in OBIEE 12.2.1.4 and later is as shown:

    export LD_LIBRARY_PATH=/home/obi/Oracle/Middleware/Oracle_Home/bi/common/ODBC/ oracle.bi.datadirect.odbc /7.1.4/lib:/home/obi/vertica/opt/vertica/lib64:$LD_LIBRARY_PATH
    export VERTICAINI=/home/obi/vertica/opt/vertica.ini
    export ODBCINI=/home/obi/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/config/fmw
    config/bienv/core/odbc.ini
    export ODBCINI
  2. Restart OBIEE services. Now you can upload the Repository (RPD) file that you created using the OBIEE administration tool. See Upload a Repository (RPD) File for information.

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

Optimizing 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. Restart all the OBIEE services.
  5. Run the dashboard using OBIEE to verify that the queries are executed in parallel.

  6. 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. Select the Require fully qualified table names checkbox.

     connectionpool.png

  1. Click OK.

Now you can view the table data.

Known Limitations

  • CHAR, VARCHAR and LONGVARCHAR data types displayed up to 32678 characters.
  • For Integer data types, values are rounded off after 14 digits.
  • For DECIMAL data types, values are not displayed. To display these values, select the Override Default Data Format option. Precision of up to 15 digits is displayed.
  • For TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types, milliseconds are truncated.
  • FOR TIMETZ and TIMESTAMPTZ data types, timezone is not displayed.

For More Information