HP Vertica Integration with SAP Business Objects: Tips and Techniques. HP Vertica Analytic Database

(1)

HP Vertica Integration with SAP

Business Objects:

Tips and Techniques

HP Vertica Analytic Database

HP Big Data

(2)

HP Vertica Integration with SAP Business Objects Page 2

Legal Notices

Warranty

The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein.

The information contained herein is subject to change without notice.

Restricted Rights Legend

Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license.

Copyright Notice

Trademark Notices

Adobe® is a trademark of Adobe Systems Incorporated.

Microsoft® and Windows® are U.S. registered trademarks of Microsoft Corporation. UNIX® is a registered trademark of The Open Group.

(3)

About HP Vertica Tips and Techniques ... 5

Overview ... 5

Certifications ... 5

Connecting to HP Vertica ... 5

Using the Out-of-the-Box Connectors for HP Vertica ... 6

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

Generic ODBC Example ………7

Generic JDBC Example ……….7

Adding a Custom Category for JDBC ... 9

Modifying Configuration Files ... 11

Metadata Modeling ... 11

Generating SQL Syntax ... 12

HP Vertica and the Business Objects Expression Editor ... 13

Appendix A: Configuration File Information for Non-Windows ODBC ... 15

Linux Example... 15

Appendix B: Troubleshooting ... 16

Schema Names Not Recognized Starting with 4.1sp3 ... 16

JDBC ………16

ODB ………16

Error Message if Using an Unsupported Version of HP Vertica ... 16

Checking for JDBC Drivers and Data Sources ... 16

Example: How to Correct Invalid SQL ... 17

Required for jar File and Configuration Settings ... 19

Appendix C: Unsupported Functions and Data Type Mappings ... 20

Unsupported Functions ... 20

Data Type Mappings ... 20

(4)

(5)

About HP Vertica Tips and Techniques

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

Overview

This document describes best practices for configuring SAP Business Objects BI Platform to connect to and work with HP 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 HP 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 HP Vertica.

Certifications

Business Objects has certified HP Vertica 6.1 drivers with Business Objects 4.1 sp02. Since HP Vertica drivers are forward compatible, Business Objects 4.1 sp02 is also compatible with later versions of HP Vertica. Business Objects uses the term “direct” to refer to support for the specific versions that it has tested and certified.

HP Vertica Release

_{Driver Type}

_{Driver Version}

_{Support Type}

6,1 JDBC HP Vertica 6.1 sp2 JDBC driver Direct

6.1 ODBC HP Vertica 6.01.02.00 Direct

7.0 JDBC HP Vertica 6.1 sp2 JDBC driver Compatible

7.0 ODBC HP Vertica 6.01.02.00 Compatible

7.1 JDBC HP Vertica 6.1 sp2 JDBC driver Compatible

7.1 ODBC HP Vertica 6.01.02.00 Compatible

Note: Business Objects has only certified HP Vertica 6.1 drivers. 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 HP Vertica

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

• Use out-of-the-box HP Vertica configuration files for Business Objects version 4.1 sp02. • Use out-of-the-box generic ODBC or JDBC drivers.

• Create a 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 HP Vertica 64-bit Windows ODBC installer includes both the 32-bit and the 64-bit drivers.

(6)

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

The following screenshot shows the out-of-the-box HP Vertica configuration files for Business Objects version 4.1 sp02.

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 HP Vertica. You can use either UDT or IDT to create the connection.

(7)

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

Generic ODBC Example

This example uses a generic ODBC connection to connect to an HP 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

(8)

jdbc:vertica://<ipaddress>:5433/<nameofdatabase> The format for the database URL is as follows:

You can quickly test your connection to HP 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 HP Vertica, you can modify the jdbc.sbo file to include the location of the HP 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>

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

(9)

Adding a Custom Category for JDBC

You can create a custom category for HP Vertica that is not under the Generic option. You would create custom HP Vertica configuration files to have more control over configuration settings and HP Vertica capabilities. To add an HP Vertica category, you must create HP Vertica-specific configuration files. Create the files vertica.sbo, vertica.prm, and vertica.setup, and place them 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

(10)

(11)

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 HP Vertica that Business Objects has not yet enabled.

 You want to use Business Objects with a version of HP 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 HP Vertica tables. Once you have made the connections to HP Vertica using generic ODBC, generic JDBC, or a custom configuration, you can then publish the model to the Business Objects BI Server.

(12)

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 HP Vertica, some features and functions in HP Vertica might not work. For example, HP Vertica supports outer joins, but the jdbc.sbo file does not have them turned on by default; in this case, the Universe Designer will not be able to 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 HP 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 HP 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 HP 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 .prm file to include the settings for SQL generation.

 Modify the modeling tools parameter settings (for UDT or IDT): ANSI92=Yes

FILTER_IN_FROM=Yes

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

(13)

HP Vertica and the Business Objects Expression Editor

(14)

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.

(15)

Appendix A: Configuration File Information for Non-Windows ODBC

This section includes Linux configuration file information.

Linux Example

(16)

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

(17)

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.

(18)

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:

(19)

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

(20)

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.

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:

(21)

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.

HP Vertica Integration with SAP Business Objects: Tips and Techniques. HP Vertica Analytic Database