Information Design Tool Linux Business Objects 4.2

Share this article:
✉ Send Feedback for this Article.

Vertica Integration with SAP Business Objects: 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 PartnerProduct: Latest Versions Tested

Software	Version
Partner Product	SAP Business Objects 4.2SP07
Desktop Platform	Windows Server 2012 R2
Vertica Client	9.2.1
Vertica Server	9.2.1

SAP Business Objects Overview

SAP Business Objects (SAP BO) is a suite of front-end applications that allow business users to view, sort, and analyze business intelligence data. The SAP BO tools are optimally suited for ad-hoc and parameterized reporting, which allows users to create and alter reports from the business layer or semantic layer created by database experts.

The information in this document was developed using 4.2SP07 Information Design Tool (IDT) and Web Intelligence (WEBI) for creating and publishing the reports. This document is based on the results of testing SAP BO 4.2SP07 with Vertica client 9.2.1 and Vertica server 9.2.1. You can deploy SAP BO on Windows and Linux operating systems using the JDBC or ODBC client drivers. This document discusses deployment on a Windows operating system.

For details about SAP Business Objects, see the SAP Business Objects Website.

Install SAP BO

Install SAP BO on a Linux or Windows environment. For detailed instructions on installing SAP BO, see the SAP help portal.

Download and Install the Vertica Client Drivers

Before you can connect to Vertica using SAP BO, you must install the Vertica client package. This package includes the JDBC and ODBC client drivers that SAP BO uses to connect to Vertica.

Note As of Vertica 9.2.x, the JDBC driver for Windows is a separate distribution and must be downloaded separately.

Download Vertica Client Drivers

Go to the Vertica Client Drivers.
Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

Depending on which client package you downloaded, follow the steps for installation from the Vertica documentation.

Save the JDBC Client Driver .jar File

After you download the vertica-jdbc-x.x-x.jar file, you must save the file in a location where SAP BO can locate it. Save the file in the following location:

<SAPBO_SERVER>\SAP Business Objects\SAP BusinessObjects Enterprise 4.0\dataAccess\connectionServer\jdbc\drivers

Alternatively, you can copy the .jar file to a location in the Java CLASSPATH or add its current location to the Java CLASSPATH.

After you save the .jar file, you must update the vertica.sbo file with the following parameter values:

Location:

<SAPBO_SERVER>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\vertica.sbo file\

Classpath:

Connect SAP BO to Vertica

SAP BO connects to Vertica using either the JDBC or ODBC client driver. This section explains how you can connect SAP to your Vertica database using both drivers. While you can use either the IDT or Universe Designer to create a connection, this document explains how to connect using the IDT.

This section assumes you have installed the Business Objects Integration Kit for SAP and have access to the SAP BO server.

Connect Using JDBC or ODBC

Start the SAP BO server.
From the programs list, open Information Design Tools.
In the Repository Resources tab, click Insert Session. The New Session window opens.
Enter your system name, user name, password, and authentication information.
Click OK. Your SAP BO server information appears.
Right-click the Connections folder and select Insert Relational Connection. The New Relational Connection window appears.
Enter your Resource Name and Description.
Click Next. The Database Middleware Driver Selection list displays.
Scroll down to Hewlett Packard and expand the node to view HP Vertica options.
Select the Vertica driver that corresponds with your system architecture.

Enter your Vertica parameters. If you are using an ODBC driver, enter the system DSN.
Click Test Connection.
After the connection is validated, click Finish.

Create a Business Objects Universe

To report data using the Business Objects reporting tools, you must create a universe on top of the underlying database.

A universe is a business-oriented mapping of the data structure found in your database. Universe components include classes, objects, joins, contexts, and more. Reporting Tools creates a model around facts, dimensions, and measures, and this model is known as a universe. To create a universe, you must choose tables from your database and define the relationship between these tables.

To create a universe, do the following:

Create a project
Create a relational connection
Create a data foundation
Create a business layer

Create a Project

In IDT, select Window > Local Projects.
Click File > New Project.
Name your project.
Click OK.

Create a Relational Connection

In the Local Projects tab, right-click the project you created. Select New > Relational Connection.
Repeat the steps to connect to the JDBC or ODBC client drivers to create a local database connection.
In the Select Connection window, choose the .cnx extension.
Click Finish.
Right-click the new connection. Select Publish Connection to a Repository. The Publish Connection window appears.
Enter your system name, user name, password, and authentication information. Click Next.
Click Finish.

Create a Data Foundation

In the Local Projects tab, right-click the project you created. Select New > Data Foundation.
Repeat the steps to connect to the JDBC or ODBC client drivers to create a local database connection.
In the Select Connection window, choose the .cns extension.
Click Finish.
In the Data Foundation tab, under Connection, choose the tables or other object type to create the universe.

Create a Business Layer

In the Local Projects tab, right-click the project you created. Select New > Business Layer.
Repeat the steps to connect to the JDBC or ODBC client drivers to create a local database connection.
In the Select Data Foundation window, select the Vertica Data Foundation you previously created.
Click Next.
Click Finish. Note that you can manage the properties of the business layer in the Query Options tab.
Right-click the new business layer. Select Business Layer Node > Publish > To a Repository.
Enter your system name, user name, password, and authentication information. Click Next.
Click Finish.
Double-click the business layer to launch the Query Panel.

From the Query Panel, pull in objects from the universe to create reports and run queries against the database.

Access a Universe

Using Web Intelligence, you can display your data from your defined Universe in a table or in graphics. If the server where you publish reports is located on a different machine, you must copy the drivers on the server and create DSNs on the new machine.

You can perform an ad-hoc analysis by drilling down to the data you want, saving your results on the server, and creating a print version of the data. Follow these steps to access your data using the universe you created:

Open Web Intelligence.
Click the New Report icon.

Select the universe you created. A query panel appears.
Click Run Query.
Name the report and click Save.

The saved reports appear on the Documents tab in Web Intelligence for rescheduling, exporting and viewing.

Known Limitations

This section explains known limitations of using SAP BO with Vertica.

Vertica ODBC connection Error on Linux Environment

By default, SAPBO server does not connect with Vertica ODBC client and following is the error message.

To resolve this issue, replace a library in the vertic.sbo file.

The sbo file is located at: <SAPBO_SERVER>/ sap_bobj/enterprise_xi40/dataAccess/connectionServer/odbc/vertica.sbo

Replace <Library Platform="Unix">dbd_verticaodbc</Library> with <Library Platform="Unix">dbd_wuxodbc3</Library>

MARS Enabled JDBC Connection

You can only enable Multiple Active Results Set (MARS) when you connect to Vertica using JDBC. MARS allows the execution of multiple queries on a single connection. While the ResultBufferSize parameter sends the results of a query directly to the client, MARS stores the results first on the server. After query execution finishes and all of the results have been stored, you can make a retrieval request to the server to have rows returned to the client.

If you use MARS with JDBC, make the following changes to the Vertica.sbo file:

Set the ArraryFetchAvailable parameter to False: <Parameter Name="Arrary Fetch Available">False</Parameter>
Set the ArraryFetchSize parameter to zero: <Parameter Name="Arrary Fetch Size">0</Parameter>

For more information, see Multiple Active Result Sets (MARS) in the Vertica documentation.

Information Designer Tool

• UUID, Binary, VarBinary, LongVarbinary, and INTERVAL data types are not supported.

• Long Varchar data type supports a maximum length of 65536 for ODBC client only. But this data type works as expected with JDBC client.

• Numeric data type round of after 15 digits.

• Boolean data type is not supported in JDBC Client but supported with ODBC client.

• Default date '1970/01/01' is appended before Time and TimeTZ data type.

• Timezone is not supported for ODBC client but supported for JDBC client.

Universe Designer Tool

• UUID, Binary, VarBinary, LongVarbinary, and INTERVAL data types are not supported.

• Long Varchar data type is not supported with ODBC client. But this data type works as expected with JDBC client.

• Numeric data type round of after 15 digits.

• Boolean data type is not supported in JDBC Client but supported with ODBC client.

• Default date '1970/01/01' is appended before Time and TimeTZ data type.

• Timezone is not supported for ODBC client but supported for JDBC client.

For More Information

SAP Website
Vertica Integration with SAP Business Objects: Tips and Techniques
Vertica Community Edition
Vertica User Community
Vertica Documentation