Share this article:

Vertica Integration with SAP Business Objects: Connection Guide

Click here for a PDF version of this document.  

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and SAP Business Objects as of June, 2016.

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.1SP07 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.1SP07 with Vertica client 7.1 and Vertica server 7.2. 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.

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 7.2.x, the JDBC driver for Windows is a separate distribution and must be downloaded separately.

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers.
  2. 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:

classpath.png

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

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

newrelationalconnection.png 

  1. Enter your Vertica parameters. If you are using an ODBC driver, enter the system DSN.
  2. Click Test Connection.
  3. 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

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

Create a Relational Connection

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

Create a Data Foundation 

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

Create a Business Layer  

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

querypanel.png

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:

  1. Open Web Intelligence.
  2. Click the New Report icon. 

 newreport.png

  1. Select the universe you created. A query panel appears.
  2. Click Run Query.
  3. 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.

Missing Schema and Owner in Vertica

If you have issues accessing tables from schema other than the public schema, reset the OWNER and QUALIFIER parameters to “Yes” in the vertica.prm files for JDBC and OBDC, as described in the sections that follow.

Windows ODBC Driver

The prm file is located at: <SAPBO_SERVER>/SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc\extensions\qt\vertica.prm

  • Replace <Parameter Name =”OWNER”>N</Parameter> with <Parameter Name =”OWNER”>Yes</Parameter>.
  • Replace <Parameter Name =”QUALIFIER”>N</Parameter> with <Parameter Name =”QUALIFIER”>Yes</Parameter>

Windows JDBC Driver

The prm file is located at: <SAPBO_SERVER>/SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\extensions\qt\vertica.prm

  • Replace <Parameter Name =”OWNER”>N</Parameter> with <Parameter Name =”OWNER”>Yes</Parameter>.
  • Replace <Parameter Name =”QUALIFIER”>N</Parameter> with <Parameter Name =”QUALIFIER”>Yes</Parameter>

Linux ODBC Driver

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

  • Replace <Parameter Name =”OWNER”>N</Parameter> with <Parameter Name =”OWNER”>Yes</Parameter>.
  • Replace <Parameter Name =”QUALIFIER”>N</Parameter> with <Parameter Name =”QUALIFIER”>Yes</Parameter>

Linux JDBC Driver

The prm file is located at: <SAPBO_SERVER>/sap_bobj/enterprise_xi40/ dataAccess/connectionServer/jdbc/qt/vertica.prm

  • Replace <Parameter Name =”OWNER”>N</Parameter> with <Parameter Name =”OWNER”>Yes</Parameter>.
  • Replace <Parameter Name =”QUALIFIER”>N</Parameter> with <Parameter Name =”QUALIFIER”>Yes</Parameter>

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.

For More Information

For More Information About… See…

SAP BO

http://help.sap.com

SAP Best Practices Guide

Vertica Integration with SAP Business Objects: Tips and Techniques

 

https://my.vertica.com/big-data-analytics-community-content/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: