How to access to VDP from OBIEE via ODBC in Linux

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5
Last modified on: 25 Sep 2018
Tags: OBIEE Connectivity ODBC driver

Download document

Introduction

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.

linux-windows_repo.png

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.

Create a new Oracle BI metadata repository.

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.

Create Windows DSN

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).

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

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).

[Denodo70]

Driver=/home/oracle/Oracle/Middleware/Oracle_Home/bi/modules/oracle.bi.datadirect.odbc/7.1.4/lib/ARpsql26.so

Description=DataDirect 7.1 PostgreSQL For accessing Denodo

AlternateServers=

ApplicationUsingThreads=1

AuthenticationMethod=0

ConnectionReset=0

ConnectionRetryCount=0

ConnectionRetryDelay=3

Database=<Denodo VDP database name>

EnableDescribeParam=1

EnableKeysetCursors=0

EncryptionMethod=0

ExtendedColumnMetaData=0

FailoverGranularity=0

FailoverMode=0

FailoverPreconnect=0

FetchTSWTZasTimestamp=0

FetchTWFSasTime=0

GSSClient=native

HostName=<Denodo VDP host name>

HostNameInCertificate=

InitializationString=

KeepAlive=0

KeyPassword=

KeysetCursorOptions=0

Keysetstore=

KeystorePassword=

LoadBalanceTimeout=0

LoadBalancing=0

LoginTimeout=15

LogonID=<Denodo VDP login>

MaxLongVarcharSize=<maximum size of SQL_LONGVARCHAR>

MaxVarcharSize=<maximum size of SQL_VARCHAR>

MaxPoolSize=100

MinPoolSize=0

Password=<Denodo VDP password>

Pooling=0

PortNumber=<Denodo VDP port, by default 9996>

QueryTimeout=0

ReportCodepageConversionErrors=0

ServicePrincipalName=

SSLLibName=

TransactionErrorBehavior=1

TrustStore=

TrustStorePassword=

UnboundedNumericPrecision=1000

UnboundedNumericScale=6

ValidateServerCertificate=0

XMLDescribeType=-10

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.

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 <repository to upload> [-W < RPD Password >] -SI < Service Instance name > -U < FMW Admin User > [-P < FMW Admin Passowrd >] [-S < Host > ] [ -N < Port > ] [-SSL] [-Y]

Example:  

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

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training