Sunday, 15 August 2010

ODI Series – issue with essbase multi-column data loads

Now I have known about this issue/feature for a long time but recently I have heard a few people mention it and I just wanted to very quickly highlight the problem if you were not aware of it.

First of all if you are going to use the essbase adaptor to perform data loads I always recommend using version 10.1.3.5.5 or newer and to use a load rule with the interface.

The issue only manifests itself when you are performing a data load with multiple data columns.

Let’s take an example in its simplest form.



In certain circumstances source data could be across a number of rows, yes you could group this information but sometimes you don’t want to add to existing values, anyway there shouldn’t be an issue loading this type of data into essbase.



Loading the data directly into essbase using a standard load rule produces a result that you would expect.



Now let’s load exactly the same data using a simple ODI interface with the same load rule set in the IKM options



Now you will notice the difference, the value for Sales is #Missing and you would expect the value to be 1.

When ODI loads data if it encounters an empty cell it treats and loads the value as #Missing to essbase.



This would be the equivalent load file if it were directly being loaded into essbase.

So is this a bug or just the way ODI requires to load data using the API, I will leave that for you to decide.

If you are going to load data in this manner then it is something you need to be aware of, if the data cannot be grouped it is worth considering an alternative approach such as having all the member information in the rows and having one data column.

2 comments:

cecilejp said...

Hi John - first let me indtroduce myself - I am Cecile working at Oracle Support for ODI
Your blog is a great reference to me and I am often looking at it.

I wanted to add a comment about multi-column ... there is a way to load data using such file.

What is needed is to do a specific reverse.

This is a copy-paste from my article 795244.1:

---------------------------------
First step is to reverse engineer the Essbase Model using ODI RKM Hyperion Essbase with the following options set this way:
"MULTIPLE_DATA_COLUMNS" = YES
"DATA_COLUMN_DIMENSION" = YOUR_DIMENSION
"DATA_COLUMN_MEMBERS" = YOUR_MEMBERS

YOUR_DIMENSION is the Name of the Dimension of the Members in your Source File (for example Scenario).
YOUR_MEMBERS is the list of the Members in your Source File (for example "actual,budget" )

The Datastore created by this reverse engineering for the Data extract / load will contain one column for each of the standard dimensions excluding the dimension specified by DATA_COLUMN_DIMENSION option and as many data columns as specified by the comma separated list for the DATA_COLUMN_MEMBERS option.

Once reversed, that Datastore can be used as the Integration Interface target of the multiple data columns source file.
----------------------------------
I will let you make it easier to understand!!

John Goodwin said...

Hi Cecile, I think you misunderstood what the blog is about, I know how to use multi column reverse, that is exactly what I do and did in the blog, the issue is how it loads missing cells to essbase.