Odi 12c And Push to DBaaS

Introduction 

This article describes how to connect from your on premise ODI 12c installation to Database as a Service (DBaaS) then push data from your on premise database to the cloud. We have achieved this via the native JDBC driver and the LKM Oracle to Oracle Push (DB Link).GLOBAL knowledge module. When loading data to the cloud, security and performance can be of concern however, we have not identified any security issues or decrease in performance when compared against on premise data loads.

Define a SSH tunnel

To connect ODI 12c to DBaaS, a SSH tunnel is required to create a secure path between the location on which your ODI Agent is running and DBaaS. During the configuration of DBaaS, SSH-2_RSA public and private keys are generated which are then used during the configuration of the SSH tunnel.

Create new connection in ODI Studio

Once the tunnel has been defined, a connection in ODI can be created using the native JDBC driver. The JDBC URL will include the port defined when configuring the SSH tunnel and the service name created when configuring DBaaS. A sample connection is shown below;

Once a successful connection is established, tables from DBaaS can be reverse engineered into ODI models which can then be used in mappings.

Oracle Platinum partner and recipients of the Oracle Ireland Partner of the year and Oracle Excellence award for the last two years, Vertice Cloud, has been an integral part of this successful transition of iCabbi’s vast amount of data to the Oracle Analytics Cloud. In the Video below CEO of Vertice, Tony Cassidy describes why Oracle Analytics iCabbi’s preferred BI Solution.

Create DB Link in Database

Before data can be pushed from the on premise database to the cloud, a DB Link between the database and DBaaS must be defined which will be used in the ODI mapping LKM. A sample DB Link definition is shown below, text highlighted in red italics is install specific.

CREATE DATABASE LINK “DBLINK_NAME”

connect to “SCHEMA_NAME” identified by “PASSWORD”

USING ‘MACHINE_NAME:PORT/DBAAS_SERVICE_NAME‘;

Create new mapping

A new mapping can now be created which we will use to load data from the on premise database to DBaaS using a DB Link over the SSH tunnel.

Above you can see the logical view of a simple mapping where the source is the on premise database and the target is DBaaS. The physical view of the same mapping is shown below;

We will be using the LKM Oracle to Oracle Push (DB Link).GLOBAL knowledge module where you define your DB Link.

We have used IKM Oracle Merge.GLOBAL with the following options for integration;