Introduction

BusinessObjects supports both JDBC and ODBC access to databases. This document describes the setup using JDBC. The ODBC method does not require any special setting, just pointing to a valid Denodo DSN with the 32 or 64-bit driver depending on the BusinessObjects installation.

The configuration steps described in this document have been tested with SAP BusinessObjects Enterprise XI 4.0.

NOTE for Denodo 7.0 and newer version users: SAP BO versions under 4.2 use Java JDK 7 or lower, this means that you will get an “Unsupported major.minor version 52.0” when you connect by using the Denodo 7 JDBC driver. The reason is that the Denodo JDBC driver is compiled for more recent Java versions. To solve this issue a more recent SAP BO version must be used.

Denodo Connection Setup

SAP BusinessObjects (BO) allows defining new connection settings for databases that are not natively supported. This configuration requires adding the JDBC driver for the database and some additional files that specify the connection settings and database capabilities.

Installing the driver

Copy the Denodo JDBC driver that can be found in the folder <DENODO_HOME>/tools/client-drivers/jdbc/denodo-vdp-jdbcdriver.jar of a Denodo Platform installation to the folder <BO_HOME>/dataAccess/connectionServer/drivers/java.

Adding the configuration files

The BusinessObjects configuration files for non native databases need to be added to the following folder: <BO_HOME>/dataAccess/connectionServer/. To create these files the existing configuration files of databases supported by default can be taken as a starting point.

It is possible to use the files below as an example depending on the BusinessObjects version.

Follow the indications below to verify they will work with your configuration, some additional changes might be needed depending on the specific version or environment. For instance, in a Unicode environment, you may need another PRM file called an extension file and the following parameters to enclose element names in quotes:

The required parameters may vary depending on your environment.

See http://scn.sap.com/docs/DOC-49847 for more information about how to configure a new driver.

Several files need to be copied:

• Setup file (<BO_HOME>/dataAccess/connectionServer/setup/vdp_jdbc.setup): this file defines that Denodo uses a JDBC connection and points to the specific JDBC configuration files required for its configuration.
• JDBC configuration files. Specific files for JDBC connections that are located in <BO_HOME>/dataAccess/connectionServer/jdbc. These files contain information about the specific driver to use and how the corresponding SQL will be generated. Denodo adds the following files:

• vdp.sbo: Modify the ClassPath referenced for the Denodo JDBC driver to the appropriate location where the VDP JDBC driver has been installed. This file should contain the name of the Database. Use “Denodo” as the name.
• vdpen.cod
• vdpen.prm
• vdp.rss
• vdp.prm

Creating a connection from the Information Design Tool

1. Open the “Information Design Tool”.
2. Create a new project.
3. Right-click on the project name, and click on New > Relational Connection.

4. Give it a name, for example, Denodo, and click Next.

5. From the Driver Selection dialog, use the “Denodo” JDBC driver, which should now be available after the installation of the configuration files described in the point above. If the driver is not shown in the list, either the configuration files are wrong, e.g. wrong path to the driver, or the files cannot be interpreted due to DTD/XSD issues.

• If Denodo is not in the list of drivers, one of the causes can be the headers used in the configuration files. In order to solve the issue edit the following files:

• vdp.sbo
• vdpen.cod
• vdpen.prm
• vdp.rss
• vdp.prm
• vdp_jdbc.setup

modifying the XML headers and DTD references using the ones from any of the other database vendors accessed from SAP BusinessObjects such as Oracle or Microsoft SQL Server. For instance the headers

<?xml version="1.0" encoding="UTF-8"?>

or

                <!DOCTYPE Messages SYSTEM "../dbparameters_localization.dtd">

must be replaced with the equivalent ones coming from the equivalent configuration files for other databases.

6. Set up the connection parameters. Use “Use specified username and password” as an authentication mechanism and fill in the rest of the parameters and the connection details to the Virtual DataPort server.

7. Click on Test Connection. If everything is correct, a “Test Successful” popup will show up. Click on Finish to create the connection.
8. Right-click again on the project name, and choose New > Data Foundation.
9. Give it a name, and use the option “Single Source”.
10. Select the connection created before and click on “Finish”.

11. From the Data Foundation workspace, open the “Connection” tab on the left side. You should see the Denodo connection and the existing views. You can drag them now onto the “Master” workspace at the right.

12. To check that the data is retrieved correctly, right-click on the table and choose “Show Table Values...”

Creating a Universe from the Universe Design Tool

1. Start the Universe Design Tool. If it is a standalone install, use the “Standalone (no CMS)” option for Authentication.
2. Click on File > New to start the universe creation wizard.

3. In the connection section, click on “New…” to define a new connection.
4. Give it a name and click on Next.
5. Use Denodo as your JDBC driver, and click Next.

6. Set up the connection parameters. Use “Use specified username and password” as an authentication mechanism and fill in the rest of the parameters and the connection details to the Virtual DataPort server. To avoid errors, provide the IP address of the VDP server instead of the hostname.
7. Define the connection pool parameters and click on Finish.
8. Click on “Test” to make sure the connection works properly.

9. Once the connection is set, it is time to define the Universe. Click on Insert > Tables to open the Table Browser, which will show all the views in the Denodo database. From there you can drag them onto the work area and to the left panel, where they become BO classes. Joins can also be defined in the workspace using drag and drop between columns.    
10. Once it is ready, click on Save.

Limitations and Known Issues

1. If you right-click on a table in a workspace, and you choose “Table Values” to preview the data, BO may fail with the message:

2. ODBC may return an error sometimes due to a bug in BO and the DSN must have the same name in both the 32 and the 64 bits DSN manager http://scn.sap.com/thread/3362374 .

Creating a report from a Universe with Web Intelligence Rich Client

1. Start the Web Intelligence Rich Client.
2. Select the Universe created in the previous section.
3. The Query Panel will pop up. Drag the fields for your report onto the “Result Objects” area.

4. To test it, click on “Refresh” in the “Data Preview” area at the bottom of the window.
5. Once the query is completed, click on “Run Query” to go to the report design section.

Limitations and known issues

Error with log4j

Some installations throw an error related to the log4j library when the queries are executed.

Database error: (CS) “Java Exception : java.lang.NoSuchMethodError: org.apache.log4j.Logger.isTraceEnabled() Z” . (IES 10901)

In order to solve it, the log4j jar file in the BO installation needs to be updated to version 1.2.15. To do so, replace the log4j.jar files in following the folders:

• <BO_HOME>/java/lib
• <BO_HOME>/java/lib/external

This error only happens with older Denodo versions. In Denodo 7.0 and newer versions, the JDBC driver is generated in a different way to avoid this problem with dependencies.

Filters with dates

Business Objects uses a date picker when a date field is used as a filter. The format for the date is specified in the file vdp.prm with the property:

<Parameter Name="USER_INPUT_DATE_FORMAT">'MMM d, yyyy h:mm:ss a'</Parameter>

This format must match the format of the locale configuration in the Virtual DataPort server. For a specific format, the file vdp.prm has to be modified. More details on the specific formats supported by Business Objects can be found in the “Data Access Guide” of SAP BO.

Moreover, the SQL query can be manually edited using the “Query Script Viewer”.

Denodo recommends using the TO_DATE function when using dates in the WHERE clause, in order to maximize compatibility with underlying systems.

Issues with Non-ANSI JOIN Syntax

BO by default uses the WHERE clause to specify join conditions. Denodo’s optimizer will work better when using the ANSI92 join syntax (i.e. ..JOIN ON…). This can be configured in the  BusinessObjects Universe Designer by setting a Universe Parameter.

Multisource Universes do not work

When you try to create a multisource universe in BO where one of the sources is Denodo, the following error message is thrown "This data source is not supported in multi source-enabled data foundations.”

More info about Multisource Universes at http://scn.sap.com/docs/DOC-38685.

In some environments, adding the Denodo JDBC driver JARs to the BO classpath makes the Multisource Universes to stop working altogether (whether they involve Denodo sources or not). If this happens, remove the denodo-vdp-jdbcdriver.jar file from the BO classpath, so only the vdp.sbo points to the jar file..

Export CLASSPATH=$CLASSPATH:/opt/bobj41/sap_bobj/enterprise_xi40/dataAccess/connectionServer/jdbc/drivers/denodo/denodo-vdp-jdbcdriver.jar