Introduction

This document describes how to configure Denodo Virtual DataPort as Analytic ODBC Data Source in an OBIEE 12c Linux installation.

To connect an Oracle Business Intelligence installation (from a Linux system) with Denodo VDP it is necessary to perform three steps: to create a new Oracle BI metadata repository, to configure the DSN on Linux and finally to import the repository in the Oracle BI instance.

Note: this article has been tested on an Oracle Linux OS with Oracle WebLogic Server 12.1.3, OBIEE 12c and Denodo 8.0, in a different system or installation the paths and URLs could be different.

Create a new Oracle BI metadata repository.

The repository files are created from the OBIEE Administration Tool. Since there is no OBIEE Administration Tool for Linux environments, the easiest way to deploy OBIEE in a Linux system is to first create the repository in a Windows machine, and then move it (deploy it) to the Linux server.

Note that the VDP instance can be hosted on the Windows system, the Linux system or another machine. The above diagram is a representation of the process where the location of the VDP Server has no influence on the process.

Create Windows DSN

The first step is to create the repository. As explained, the OBIEE Administration Tool is only available for Windows OS, therefore, it is necessary to create a DSN in Windows to connect the OBIEE Administration Tool with the VDP Server.

To create the DSN in Windows open the ODBC data sources control panel (as administrator) and then in the System DSN tab add and configure a new data source.

Important: The name of this ODBC DSN needs to be identical to the one defined (later on) on Linux in the odbc.ini (explained below).

Create RPD File

To create an RPD file the OBIEE Administration Tool must be installed and started. To install the Oracle BI Administration Tool the installer can be found in the Oracle BI distribution or downloaded from the Oracle Web Site.

After the ODBC DSN has been created and the Oracle BI Administration tool is installed,  it will be possible to create connections to the Denodo database following the next steps from the Oracle BI Administration tool:

Open the Administration Tool. 

1. Select File > New Repository.

Enter the repository information.

1. Enter a name for the repository. In this example the repository name is BISAMPLE.
2. Enter the location.
3. Leave Import Metadata set to Yes.
4. Enter and retype a password for the repository.
5. Click on Next.

Select the Data Source. 

1. Set the connection type to ODBC 3.5. 
2. Select the Denodo DSN previously configured.
3. Enter a username and password for the data source.
4. Click on Next.

Select the Metadata Types.

1. Check Views in addition to the default options.

Select the Metadata Objects.

1. In the Data source view, expand the schema.
2. Use Ctrl+Click to select the tables to use.
3. Click on the blue arrow to add the tables to the Repository View.
4. Click Finish to open the repository.

Configuring the Oracle BI metadata repository

By default the features (or query capabilities) reported by the driver regarding the VDP datasource do not support date literals. This may cause issues when working with conditions using date fields. Since OBIEE dates are treated as literals, OBIEE does not delegate operation involving dates, post processing the data instead.

To be able to delegate operations involving dates it is necessary to change the configuration to support dates as literals.  In the “Oracle BI Administration Tool”, after creating the repository, there is an option to retrieve the supported features by the source.

First, it is necessary to open the “Properties” wizard in the repository menu.

Then, under the “Features” tab in the “Query DBMS” option it is necessary to mark the following properties as checked:

DATE_LITERAL_SUPPORTED

TIME_LITERAL_SUPPORTED

DATE_TIME_LITERAL_SUPPORTED

Configure the DSN on Linux.

Oracle Business Intelligence bundles UNIX ODBC drivers (Merand a.k.a DataDirect a.k.a Progress Software) for some data sources including PostgreSql. As Denodo is compatible with the PostgreSql ODBC Driver it is possible to leverage the bundled driver. The driver is located at:

<OBIEE_HOME>/bi/modules/oracle.bi.datadirect.odbc/7.1.4/lib/ARpsql27.so

Note: this article shows how to connect OBIEE with Denodo using the PostgreSQL driver because this driver is already included within the OBIEE installation, however, the Denodo ODBC driver can be used as well.

As first step the Driver must be added to the path:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<OBIEE_HOME>/bi/modules/oracle.bi.datadirect.odbc/7.1.4/lib/; export LD_LIBRARY_PATH

In order to create the DSN it is necessary to modify the odbc.ini file adding a new entry for the Denodo database to access.

Important: make sure that the ODBC connection name is identical to the data source name specified in the connection pool defined in the OBIEE repository (created in the first step).

The Database parameter can be left empty only if the option Require fully qualified table names is selected in the General tab of the Connection Pool dialog for the data source in the OBIEE Administration Tool.

When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database. The fully qualified names are based on the physical object names in the repository.

.

After the configuration is completed the Oracle BI Server needs to be restarted.

Import the repository in the Oracle BI instance.

In OBIEE 12c it is no longer possible to upload an RPD from Enterprise Manager.  OBIEE 12c has moved away from EM to script based utilities.

$DOMAIN_HOME/bitools/bin/datamodel.sh uploadrpd -I <RPD filename> [-W <RPD password>] [-D] [KG <groups>] [-RG <groups>] -U <cred_username> [-P <cred_password>][SI <service_instance>] [-S <host>] [-N <port>] [-SSL] [-H]

Example:  

$DOMAIN_HOME/bitools/bin/datamodel.sh uploadrpd -I Denodo80.rpd -W admin000 -SI ssi -U weblogic -P admin123

Important: the -D option is required to replace the RPD completely, else it merges the new repository with the previous one.