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.
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.
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|
Vertica 6.1 SP2 JDBC driver
Vertica 6.1 SP2 JDBC driver
Vertica 7.1 SP2 (07.01.0200)_JDBC driver
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.
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.
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.
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.
The format for the database URL is as follows:
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.
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.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:
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.
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.
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:
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.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.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:
- Modify the appropriate
.prmfile to include the settings for SQL generation.
- Modify the modeling tools parameter settings (for UDT or IDT):
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.
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:
HP Vertica and the Business Objects Expression Editor
You can build expressions with the Business Objects Universe Design Tool (UDT).
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:
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.
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:
C:\Program Files (x86)\SAP BusinessObjects\ SAP BusinessObjects Enterprise XI 4.0\ dataAccess\connectionServer\jdbc\extensions\qt
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.
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.
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.
To diagnose this issue:
- Check the
.prmfile being used by the connection. To view the
.prmfile value, you can check the UDT or IDT modeling tools.
- 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
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 220.127.116.118 Build 18.104.22.1687 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.
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.
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:
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.
You can test the performance of your reports after tuning. An example follows: