Vertica Integration with SAP Business Objects: Tips and Techniques

Applies to Vertica 7.1.x and earlier 

About Vertica Tips and Techniques

Vertica develops Best Practices to provide you with the information you need to use Vertica with third-party products. This document provides guidance using one specific version of Vertica and one version of the vendor’s software. While other combinations are likely to work, the specific versions you are using together may not have been tested.

Overview

This document describes best practices for configuring SAP Business Objects BI Platform to connect to and work with Vertica. This document does not cover related Business Objects products, such as Business Objects Data Integrator, and it does not address integration with the Business Objects metadata modeling client tool, Business Viewer Manager.

Business Objects has a multi-tier architecture that includes both client and server components. This document describes Vertica integration with two Business Objects client tools for metadata modeling: Universe Design Tool (UDT) and Information Design Tool (IDT).

Note This document assumes that you are familiar with both the SAP Business Objects BI Platform and with Vertica. 

Certifications

Business Objects has certified Vertica 6.1.x drivers from Business Objects 4.1 SP02 and has certified Vertica 7.1.x drivers with Business Objects 4.1 SP07. Since Vertica drivers are forward compatible, SAP is also compatible with later versions of Vertica from Business Objects 4.1 SP02 onwards. The Support Type field indicates:

  • Direct: Driver has been tested and certified
  • Compatible: Driver has not been tested but is assumed to be compatible.
Vertica Release Driver Type Driver Version Support Type

6.1

JDBC

Vertica 6.1 SP2 JDBC driver

Direct

6.1

ODBC

Vertica 6.01.02.00

Direct

7.0

JDBC

Vertica 6.1 SP2 JDBC driver

Compatible

7.0

ODBC

Vertica 6.01.02.00

Compatible

7.1

JDBC

Vertica 7.1 SP2 (07.01.0200)_JDBC driver

Direct

7.1

ODBC

Vertica 7.01.02.00

Direct

Note  Business Objects has only certified Vertica 6.1.x drivers from 4.1 SP02 onward and 7.1.x drivers from 4.1 SP07 onward. If you have an earlier version of Business Objects, refer to the instructions for using a generic or a custom connection that are included in this document.

Connecting to Vertica

The Business Objects metadata framework allows you to create a connection to a Vertica database. You can use any of the following methods:

  • Out-of-the-box Vertica configuration files for Business Objects version 4.1 SP02 onward for Vertica 6.1.x drivers and SP07 onwards for Vertica 7.1.x drivers.
  • Out-of-the-box generic ODBC or JDBC drivers.
  • Custom configuration.

ODBC is the most popular connectivity method.

Business Objects BI Server is both 32-bit and 64- bit. The Universe Design Tool (UDT) and Information Design Tool (IDT) are 32-bit; if you have both the client and the server on the same machine you need both drivers. Install the HP Vertica driver that matches the bitness of your machine. The Vertica 64-bit Windows ODBC installer includes both the 32-bit and the 64-bit drivers.

Business Objects Data Federator allows you to create a unified view of your data from multiple systems. For an overview of Data Federator, refer to the Business Objects document, Business Objects Data Federator. Vertica has not been tested with Data Federator.

Using the Out-of-the-Box Connectors for Vertica

The following screenshot shows the out-of-the-box Vertica configuration files for Business Objects version 4.1 SP07, which can use 6.1.x or 7.1.x drivers.

sap_update.png

You can use either UDT or IDT to create the connection.

For JDBC connections, modify the ClassPath and URL in the file vertica.sbo as shown.

<ClassPath>
<Path>your_jar_or_class_files_directory</​Path>
</ClassPath>
<Parameter Name="U Format">jdbc:vertica://
   $DATASOURCE$/$DATABASE$</Parameter>

Using the Generic ODBC or JDBC Out-of-the Box Connectors

You can use the generic drivers for JDBC or ODBC to connect Business Objects to Vertica. You can use either UDT or IDT to create the connection.

As shown in the following screenshot from UDT, the Define a new connection dialog box provides the ability to connect to a database through a generic ODBC data source or a generic JDBC data source.

image003.jpg

You can also customize the interface to include a Vertica data source.

Generic ODBC Example

This example uses a generic ODBC connection to connect to a Vertica data source that you previously defined using the ODBC admin tool. To connect with ODBC, enter a user name, password, and data source name.

image004.jpg

Generic JDBC Example

This example uses a generic JDBC connection to connect to a Vertica data source. To connect with JDBC, enter a user name, password, database URL, and JDBC class.

image005.jpg

 The format for the database URL is as follows:

<pre>jdbc:vertica://<ipaddress>:5433/<nameofdatabase>

You can quickly test your connection to Vertica by selecting Generic JDBC data source. In this case, you do not need to make changes to configuration files, but you are limited to the out-of-the-box capabilities defined in the default jdbc.prm file. You could possibly modify the jdbc.prm file for the generic driver, but your modifications will impact any other database that uses the out-of-the-box driver.

If you use the generic JDBC driver that is specific to Vertica, you can modify the jdbc.sbo file to include the location of the Vertica JDBC driver. Edit the file jdbc.sbo, adding the following lines under the Database section of the file.

Database Active="Yes" Name="Vertica JDBC data source">
<JDBCDriver>
<ClassPath>
<Path>$ROOT$/drivers/java/dbd_jdbcwrapper.jar;C:/JDBC/
    vertica-jdk5-6.1.3- 0.jar</Path>
</ClassPath>
<Parameter Name="JDBC Wrapper">
   com.sap.connectivity.cs.java.drivers.jdbc.
   wrapper.JDBCWrapper</Parameter>
<Parameter Name="JDBC Class">$JDBCCLASS$</Parameter>
<Parameter Name = "URL Format">$DATASOURCE$$</Parameter>
</JDBCDriver>
<Parameter=Name="Array Fetch Size">10</Parameter>
</DataBase>

Once you have made the changes to jdbc.sbo, restart the Business Objects server. Then when you make the connection in UDT or IDT, you should see Vertica listed as a driver selection.

image006.jpg

Adding a Custom Category for JDBC

You can create a custom category for Vertica that is not under the Generic option. To have more control over configuration settings and Vertica capabilities, you can create custom Vertica configuration files. To add a Vertica category, you must create Vertica-specific configuration files. Create the files vertica.sbo, vertica.prm, and vertica.setup, and savethem in this directory:

C:\Program Files (x86)\SAP BusinessObjects\SA BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc

For descriptions of the various parameters in the configuration files, refer to SAP Business Objects Intelligence platform, Data Access Guide, located here:

https://help.sap.com/businessobject/product_guides/boexir4/en/xi4_data_acs_en.pdf

After creating the configuration files and adding them to the directory, restart your Business Objects server. After you connect through UDT or IDT, you should then see the Vertica custom driver.

image007.jpg

The example that follows shows a connection using the Vertica specific driver. It shows the Test Result screen that appears when you click Test Connection. The Test Result screen lists the configuration files and their locations.

image008.jpg

  

Modifying Configuration Files

You would make adjustments to the configuration files in the following scenarios:

  • You are using a Business Objects version prior to 4.1 SP02.
  • You want to use a feature of Vertica that Business Objects has not yet enabled.
  • You want to use Business Objects with a version of Vertica that has not been tested by Business Objects.

If possible, download Business Objects 4.1 SP02 and use the configuration files as a guide to configure a version prior to 4.1 SP02. Make backups of your current configuration files, and compare with the new files you have downloaded to ensure that the new files conform to the old ones. The new HP Vertica-specific configuration files included with Business Objects 4.1 SP02 are listed below:

vertica.prm

vertica.rss

vertica.sbo

verticaen.prm

Metadata Modeling

As a first step, use the UDT and IDT client tools on Business Objects projects to build metadata based on the Vertica tables. Once you have made the connections to Vertica using generic ODBC, generic JDBC, or a custom configuration, you can then publish the model to the Business Objects BI Server.

Business Objects also has another metadata framework called Business Views. This is a carryover from the Crystal product line, but it is no longer viewed as a strategic direction and it is not covered in this document.

Generating SQL Syntax

Business Objects uses configuration files to generate the proper SQL for a particular database. If you use the default generic connection options to connect to Vertica, some features and functions in Vertica might not work. For example, Vertica supports outer joins, but the jdbc.sbo file does not have them turned on by default. In this case, the Universe Designer cannot support outer joins. If you enable outer joins in the jdbc.sbo file, the setting then applies to all other databases that use the generic JDBC setting—not just Vertica.

Note Modify the appropriate .prm file to include the settings for SQL generation.

Modifying the files jdbc.sbo and jdbc.prm impacts any database that uses the generic JDBC option. If you are certain that Vertica is the only database using the generic option, then modifying the jdbc.sbo and jdbc.prm files does not impact other parts of the organization. As a best practice, create an Vertica specific configuration file to ensure that other databases are not impacted.

Perform the following to ensure that the modeling tools in Business Objects generate the appropriate SQL syntax for a database:

  1. Modify the appropriate .prm file to include the settings for SQL generation.
  2. Modify the modeling tools parameter settings (for UDT or IDT):

ANSI92=Yes

FILTER_IN_FROM=Yes

For a production environment, Vertica recommends that you create a custom configuration file for Vertica. With a custom Vertica configuration file, you have more control over configuration settings.

image009.jpg

For IDT, it is important that you modify parameters in the Data Foundation layer. (IDT supports parameters at both the business layer and the foundation layer.) A sample showing the Query Script Parameters dialog follows:

image010.png

 

HP Vertica and the Business Objects Expression Editor

You can build expressions with the Business Objects Universe Design Tool (UDT).

image011.jpg

If you find that a function or join is not working, you can create a derived table in the UDT Derived Tables dialog, which supports free form SQL. For example, if you wanted to use an add_months function, you could add it using a derived table. A sample follows:

image012.jpg

Note You can also use Vertica UDX functions in derived tables. 

Appendix A: Configuration File Information for Non-Windows ODBC

This section includes Linux configuration file information.

Linux Example

A sample odbc.ini file follows.

[bods_src]

Driver = /home/boadmin/BOBJ/vertica/lib64/libverticaodbc.so
Server = 172.16.65.21
ServerName = 172.16.65.21
Database = bods_src
UserName = gltest
Password = abc
UID = gltest
Port = 5438
ColumnsAsChar=1

Appendix B: Troubleshooting

This section includes information about common issues.

Schema Names Not Recognized Starting with 4.1sp3

The solution for this issue is to add/update the following lines in the vertica.prm file on the client and server machines.

< Parameter Name="OWNER">Y</Parameter>
< Parameter Name="QUALIFIER">Y</Parameter>

The file can be found in the following locations for JDBC and ODBC drivers:

JDBC

C:\Program Files (x86)\SAP BusinessObjects\

   SAP BusinessObjects Enterprise XI 4.0\

   dataAccess\connectionServer\jdbc\extensions\qt

ODBC

C:\Program Files (x86)\SAP BusinessObjects\

   SAP BusinessObjects Enterprise XI 4.0\

   dataAccess\connectionServer\odbc\extensions\qt

After you update the vertica.prm file, you must restart the servers and restart the client tools.

Error Message if Using an Unsupported Version of HP Vertica

If you attempt to connect to an unsupported version of HP Vertica, you receive the following error:

Specified RDBMS is invalid: <HP Vertica version>

Checking for JDBC Drivers and Data Sources

Business Objects includes utilities that you can use to troubleshoot JDBC connectivity issues.

You can issue the command cscheck to list Java connectivity types on the local machine.

cscheck find -m java

Sample output for the command follows:

Local Java mode

JDBCDrivers

Data Federator Server XI R3
Data Federator Server XI R4
DB2 UDB v8
DB2 v9
Derby 10 Embedded
GreenPlum 3
PostgreSQL 8
HSQLDB 1.8 Embedded
Informix Dynamic Server 10
Informix Dynamic Server 11
Ingres Database 9
Vertica JDBC datasource
Generic JDBC datasource
Sample output follows:
CORBA mode
intvmw2k8-2
6060
JDBC Drivers
Data Federator Server XI R3
Data Federator Server XI R4
DB2 UDB v8
DB2 v9
Derby 10 Embedded
reenPlum 3
PostgreSQL 8
HSQLDB 1.8 Embedded
Informix Dynamic Server 10
Informix Dynamic Server 11
Ingres Database 9
Vertica JDBC data source
Generic JDBC data source

Example: How to Correct Invalid SQL

This section includes an example where an outer join does not generate correctly, because the parameter ANSI92=Yes was not applied to the model, or the .prm file was not properly modified to allow for outer joins. The model itself was configured correctly.

The screen that follows shows a sample join edited through the Universe Design Tool.

image013.png

The edited join generates the following query in Web Intelligence if you had set the parameter ANSI92=Yes and the .prm file has been modified to allow for outer joins.

image014.jpg

If you had not set the parameter ANSI92=Yes, or if you had not modified the .prm file to allow for outer joins, you would see that the right outer join is not created.

image015.jpg

 To diagnose this issue:

  1. Check the .prm file being used by the connection. To view the .prm file value, you can check the UDT or IDT modeling tools.
  2. Verify that the parameter ANSI92=Yes is set at the right level (Data Foundation) in IDT.

Required for jar File and Configuration Settings

Ensure that the following directory contains the file vertica-jdk5-6.1.3-0.jar:

C:\Business Objects_JDBC\webapps\p2pd\WEB-INF\lib

What follows is an example of configuration settings for a JDBC connection. You can check connection settings for a particular driver through IDT or UD.

BusinessObjects ConfigurationVersion 3.0.1.368

Build 14.0.1.287

Network Layer JDBC

DBMS Engine Vertica JDBC

Language en

Library C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\\drivers\java\dbd_jdbc.jar

SBO C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\vertica.sbo

RSS C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\jdbc.rss

PRM C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\jdbc.prm

Strategies C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\null.stg

Middleware and DBMS Configuration

Driver Name vertica.jar

Driver Version 05.01.0000

DBMS Name Vertica Database

DBMS version 07.00.0000

Appendix C: Unsupported Functions and Data Type Mappings

This section contains information about unsupported features and includes a reference to Data  Foundation Layer data type mappings.

Unsupported Functions

HP Vertica 6.1 introduced new capabilities, some of which are not supported by Business Objects. An example is the EXCEPT clause.

Using an unsupported feature causes an exception error.

image017.jpg

Data Type Mappings

To view a table that lists the data type mapping for JDBC in the Business Objects Data Foundation layer, refer to the document, SAP Business Objects, Data Access Guide, located here:

https://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp4_data_acs_en.pdf

Appendix D: Tuning Crystal Reports

Tune crystal reports through the Report Options dialog.

Note For ease of set-up, configuration, and performance reasons, HP Vertica recommends that you use a Business Objects Universe or JDBC as source when building reports using Crystal Reports.

image018.jpg

You can test the performance of your reports after tuning. An example follows:

image019.png