This document describes how to configure Denodo Virtual DataPort as Analytics ODBC Data Source in a 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 SO with Oracle WebLogic Server 12.1.3, OBIEE 12c and Denodo 7.0, in a different system or installation the paths and URLs could be different.
The repository files are created form 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 following diagram is a representation of the process. This representation is valid as the location of the VPD has no influence on the process.
In order to create a repository in the Windows system an ODBC DSN is needed. 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 in odbc.ini (explained below).
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.
Enter the repository information.
Select the Data Source.
Select the Metadata Types.
Select the Metadata Objects.
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:
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:
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).
Description=DataDirect 7.1 PostgreSQL For accessing Denodo
Database=<Denodo VDP database name>
HostName=<Denodo VDP host name>
LogonID=<Denodo VDP login>
MaxLongVarcharSize=<maximum size of SQL_LONGVARCHAR>
MaxVarcharSize=<maximum size of SQL_VARCHAR>
Password=<Denodo VDP password>
PortNumber=<Denodo VDP port, by default 9996>
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.
Note: it is very important to set values for the properties MaxLongVarcharSize and MaxVarcharSize. If these properties are empty then the default size will be set up to the maximum value, causing performance degradation as OBIEE will allocate more memory space than necessary.
In addition to this, in the odbc.ini file, it is possible add an entry to the section [ODBC Data Sources] with the details of the data source. The following example provides details for a Denodo data source with a data source name of “Denodo”.
After the configuration is completed the Oracle BI Server needs to be restarted.
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 <repository to upload> [-W < RPD Password >] -SI < Service Instance name > -U < FMW Admin User > [-P < FMW Admin Passowrd >] [-S < Host > ] [ -N < Port > ] [-SSL] [-Y]
$DOMAIN_HOME/bitools/bin/datamodel.sh uploadrpd -I Denodo70.rpd -W admin000 -SI ssi -U weblogic -P admin123