ODI Series Part 1

Now that HAL has really ended its shelf live what to people turn to, well I see a lot of people assuming that EPMA must be the answer and end up wishing they have never taken that route. If you load hierarchies from files or manage them straight from a repository then ODI (Oracle Data Integrator) could be the way you want to go, it offers all this functionality and so much more.

ODI sits in Oracle fusion middleware suite of products and basically it’s an Extract Load and Transform (E-LT) tool, it used to be owned by Sunopsis until it swallowed by the hungry jaws of Oracle. It has built in connectivity to all the major databases and now has the added bonus of Essbase and Planning adaptors which can be plugged straight into it.

ODI includes something known as Knowledge Modules that are components, which contain the information for ODI to perform a specific set of tasks against a specific technology such as Planning.

There are 6 different types of KMs and some have already been created for use with planning and essbase.

The types of KMs you are likely to encounter are :

• RKM (Reverse Knowledge Modules) are used to perform a customized reverse-engineering of data models for a specific technology.

• LKM (Loading Knowledge Modules) are used to extract data from the source database
tables and other systems (files, middleware, mainframe, etc.).

• IKM (Integration Knowledge Modules) are used to integrate (load) data to the target tables.

Enough or the blurb I am going to try and cover off how you can use ODI with planning and essbase though as it is quite an in-depth topic I am going to break into down into sections though starting today with Installation and part of the configuration

First of all you will need to download the ODI installation package from edelivery, (Oracle Data Integrator, Oracle Data Profiling, and Oracle Data Quality for Data Integrator 10g (10.1.3.4.0) for Windows Platforms (CD) – 450M)

The Planning and Essbase ODI adaptors from edelivery –
Oracle Data Integrator Adapter for Hyperion Planning Release 9.3.1.1 Installation files
Oracle Data Integrator Knowledge Module for Essbase Release 9.3.1.1 Installation files

Updated 26/10/08 - I suggest installing ODI Version 10.1.3.4.5 or above, this will have all the latest updated KMs and includes all neccessary drivers, if you install the patch you will not need the Planning/Essbase ODI Installation files.

Download Version 10.1.3.5.0 for all the latest KMs

As ODI 11g is the last version then it is worth downloading and installing unless it is a requirement you need to use ODI 10g.

I have set up ODI against a 9.3.1 environment before but this time I am going to put it against a 11 environment, the setup shouldn’t be any different though I am hoping adapter files will still work as I have not seen any for 11 yet.

If you are using SQL server as I am for the database repository then you will also need JDBC drivers, I am going to use the 2005 drivers, which can easily be downloaded from Microsoft.

You will need JRE 1.4+ (ODI has JRE 1.4.2 included) or JDK 1.5+ if you are going to use the Web services of ODI. I intend to demonstrate the web service functionality to run ODI jobs so I also download tomcat (version 5.5 or 6 is fine) for the web server and Axis2 as the web services container. (It has to be Axis2 and version 1.2 and it is easiest to get the war version)

The installation of ODI is very simple just double click the setup.bat inside the \setup\Windows\ directory; you will be warned if your machine does not pass the pre-requisites. Once in the product selection choose “Oracle Data Integrator 10.1.3.4.0”, choose complete which just means the client and the server will be installed and select the ODI home and that’s pretty much it.

Next step is to copy over the drivers into the odi file structure (\OraHome_1\oracledi\drivers)

If you are using SQL server copy over sqljdbc.jar

For essbase copy over all the jar files from the drivers directory of “Oracle Data Integrator Knowledge Module for Essbase Release 9.3.1.1 Installation files”

Repeat for Planning, some of the files will already exist so just keep the latest ones.

So you should have something like this


To use ODI you will need to create two database repositories these are known as the Master Repository and the Work Repository.
The Master holds the structure of the different types of technologies, security and version management of projects and models
The Work holds all the information on data models, projects and how they are used.

If you are using Oracle grant schema user connect and resource, for SQL server I just made them dbo.

Once the databases have been created you can create the ODI master repository.


Now because I am using sql server 2005 I had to manually enter driver and url string as ODI only includes preset values for sql server 2000. If you are using Oracle you can easily select Driver and just update the URL.



We now have our Master repository created so we can move on into the topology manager. (I don’t think I will ever get used to that name)

The topology manager lets you manage all your technologies such as essbase and planning, so say for planning it is where you set up the connection details to your planning app (or in odi speak Data Server)

When you start up the topology manager you have to connect to a Master Repository, unfortunately this is more manual work entering all the JDBC details again.
Click the new button when the security repository connection window opens.

You need to give a login name, which can be anything and a username and password, the default is SUPERVISOR/SUNOPSIS



OK we are in and the first thing we need to set up is the work repository and you guessed it yet more entering of the connection details. There is a tab at the bottom of the topology manager with “AB” on it that means repositories.


Insert a new work repository and enter all the details in the definition and JDBC tabbed window.



When you click test you will be asked to pick an agent, you can just choose Local (No Agent) for now, details on agents shortly. You will then be asked for a name for the work repository and an id, the id can be any numeric value, I have always left the Type as development the other option is Execution.

In the topology manager the Hyperion Essbase technology has already been included but the planning needs adding in. Go to the first tab and choose to import topology.



Locate the impexp folder of the planning installation files.


All being well Hyperion Planning should then appear in the technologies list.

Well that is it for this session as I said I want to try and break it up into lots of bite size chunks instead of one endless blog.

Next session will be setting up of the agent and then configuring some of the technologies such as planning.