Vertica Integration with Oracle Business Intelligence Publisher: 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 Oracle BI Publisher: Latest Versions Tested

Software Version
Partner Product

Oracle Business Intelligence (BI) Publisher 12.2.1.4.0
Partner Product Platform

Windows Server 2019
Vertica Client

Vertica JDBC 12.0.1
Vertica Server Vertica Analytic Database 12.0.1
Vertica Server Platform RHEL 8.3

Oracle Business Intelligence Publisher Overview

Oracle Business Intelligence (BI) Publisher is a reporting tool widely used to create, manage, and deliver reports. You can design interactive reporting solutions easily using Oracle BI Publisher. It is widely used to view reports online or schedule to specific users via email.

Installing Oracle Business Intelligence Publisher

You can download Oracle Business Intelligence Publisher on the Oracle website and follow the steps for installation.

Installing the Vertica Client Driver

  1. Navigate to the Vertica Client Drivers page.

  2. Download the JDBC driver.

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

  3. Place the JDBC .jar file in the domain library directory.

    You need to place the jar file at InstallationDirectory\Oracle_Home\user_projects\domains\bi\lib. The jars located in this directory are dynamically added to the end of the server classpath at server startup. The jars are ordered lexically in the classpath.

  4. After you place the jar at the specified location, restart the Oracle BI Server as well as Admin Server.

Connecting Oracle Business Intelligence Publisher to Vertica

  1. Login to the Oracle Business Intelligence (BI) Publisher web interface http://hostname:9502/xmlpserver.

  2. Click New > Data Model.

  3. Click Manage Private Data Sources.

  4. Click JDBC > Add Data Source.

  5. Provide the Vertica connection details and click Test Connection. A connection successful message is displayed.

  6. Click Apply and then click Close.

  7. Click Diagram > SQL Query.

  8. Enter Name, Data Source, and Type of SQL, and then click Query Builder.

  9. Select the tables you want to use in your data model and define the conditions.

  10. You can see results of your data model in the Results tab. Click Ok.

  11. Click Save to save your Data Model.

  12. Click New > Report to create a sample report using this Data Model.

  13. Select the Data Model you want to use to create a report. The Data Model that we saved was Vertica.xdm.

  14. Select the Layout and click Next.

  15. Click Next.

  16. Click Finish.

  17. You can now see the report that is displayed.

Known Limitations

  • BINARY, VARBINARY, and LONG VARBINARY data types are not displayed.

  • For TIMETZ and TIMESTAMPTZ data types, time zone is truncated.

  • For TIME datatype, milliseconds are rounded off to 3 decimal places.

For More Information