ORACLE HYPERION PRODUCT SET: ODI Series - Essbase Outline Extractor

I have at last got round to moving onto using ODI with Essbase, to start with I will be looking at reverse engineering an essbase cube and then extracting an outline into flat file format and also a database table.

Before I start I strongly suggest you download and install patch 10.1.3.4.5 or above from metalink3 as it addresses an issue with an essbase LKM.

Version 10.1.3.4.5
==================
New Features
------------
This section should list the new/updated KMs that are delivered with this version.
The following KMs have been updated and are delivered in this version:

* LKM Hyperion Essbase METADATA to SQL

Resolved Issues

---------------

7186129: LKM Hyperion Essbase METADATA to SQL does not extract data storage member property correctly.

If this is the first time you have read one of my blogs on ODI I would advise reading Series 1 and Series 3 as with this blog I am going to assume you have the required essbase KM’s and have set up in the topology manager a connection to the Sample/Basic essbase database. and

I also suggest if you have applied the patch to import the essbase technology again in the topology manager as it has been updated.
In the developer the first thing we want to do is import the various Knowledge Modules we require these should be in the impexp directory of the ODI installation:-

IKM SQL to Hyperion Essbase (DATA)

IKM SQL to Hyperion Essbase (METADATA)

LKM Hyperion Essbase DATA to SQL

LKM Hyperion Essbase METADATA to SQL

RKM Hyperion Essbase

Once that has been done I moved on to the models tab and inserted a new directory – ESSBASE_MODELS and inserted a new model using the essbase technology and logical schema that was set up in the topology manager.

In the reverse tab customized was chosen, the context selected and the KM set to RKM Hyperion Essbase

I have kept all the default options and will discuss them at a later stage when looking at data.

After reversing it produces the following DataStores.

Today’s example I will be extracting the Measures dimension but it is will be pretty much similar whatever dimension you choose.

Expanding the Measures columns displays all the available properties for each member.

The first extraction I want to run is to a flat file so I created a quick csv template with the column headings to match the ones above.

I created the second line with alphabetical values to match the number of columns, I have done this so when I reverse the flat file it reads in the column headings and sets them to being a string type, which saves having to enter them manually.

The file is saved to the same location as was set up in the topology manager in Series 3 of the ODI blogs.

I created a new model to hold any flat file DataStores that I will be using with essbase.

And then set up a new DataStore to point to the csv just created.

In the Files tab delimited was set as the file format, heading was set to 1and the field separator as comma “,”

As I set up the csv file earlier the reversing sets up all the correct columns, one thing to watch for is the physical/logical length is defaulted to 50, in the essbase reversed DataStore everything is set to 80 except the formula column which is set to 255, I replicated this just to stop any interface warnings and to make the extract will not fail, for example if you didn’t change the default and say you had a member name that was over 50 in length the extract would fail.

We can now create the interface to do the extracting as we have the source and target DataStores in place.

In the diagram tab the Measures DataStore was dragged onto the source and the file onto the target, due to the column headings being the same between the source and target everything was auto mapped.

In the Flow tab the LKM should be set to “LKM Hyperion Essbase METADATA to SQL”.

In the LKM Options there are a couple of settings to take note of :-

MEMBER_FILTER_VALUE :- This can be set as member name or a UDA value (if UDA is selected in the filter_criteria), if no value is entered which is the default then the dimension member is used.

MEMBER_FILTER_CRITERIA :- There are number of options available which are used against the filter_value, most of them are self-explanatory but can be very useful.

I stick with the defaults that will bring back everything in the hierarchy.

The IKM should be SQL to File Append, the only option in the IKM was I set TRUNCATE to Yes as I want the file to be cleared out each time.

Once the interface had been applied it could be executed

All green in the Operator so it should have extracted

And there we have it the measures dimension has been extracted, the only grudge I have is that the output is the reverse of how I would like it, I would prefer the root member to be first and the hierarchy extracts there down, something I am going to look at when I have a bit of spare time.

Extracting an outline to a database table is pretty much the same as the flat file, except you swap the technology to either SQL server or another database engine you are using such as Oracle.

First I created a SQL server table called Measures in a database named HYPDATA with columns to match the reversed essbase Measures dimension

In a previous blog I had created a physical schema in the topology manager pointing to the HYPDATA table using SQL Server technology

If you need the exact steps have a look back at ODI series part 3

In the designer I created a new model to point to the SQL server database

In the Reverse tab I selected the context; in the Selective Reverse tab I just selected the Measures table, to display the tables you need to check the "objects to reverse" box

On clicking reverse the table was reversed engineered

So like with the flat file previously a new interface is created to move the metadata from the source DataStore to the target DataStore.

In the Diagram tab the Measures essbase DataStore was dragged to the source and the Measures Db DataStore to the target, as the source and target naming conventions are the same all the columns are automapped.

In the flow tab the LKM is set to LKM Hyperion Essbase METADATA to SQL

This time I am going to extract all the level 0 members so in the options I set MEMBER_FILTER_CRITERIA to Level0

The IKM was set to IKM SQL to SQL Append (if you don’t have this IKM then you will need to import it in from the oracledi\impexp directory, in the options I set DELETE_ALL to Yes so the table will be cleared out each time.