Monday, 13 October 2008

ODI - Getting text data into planning

Today’s blog is a little detour but still on the ODI front, in the last blog I mentioned about load text data into planning using ODI, now that I have gone through how to load data I can attempt to tackle this problem.

I must stress this blog is not an official way of getting data into planning and it is up to yourself if you want to take onboard these ideas

Unfortunately if you want to load text you can’t just use the usual method of loading data through ODI, if you do try then you will probably get no errors but when you look in the essbase app log you will see it has loaded no data.

First thing you need to know is that planning stores the text values in a table called HSP_TEXT_CELL_VALUE

Each time you enter text data into a form if the text doesn’t exist in the above table then it is added, the text_id increments by one each time, the text_id relates to the value that is stored in essbase.

Entering the above into a form and saving produces

In the planning repository ^

Values stored in essbase ^

Now if you want to load text data the first thing you need to be able to do is get the Text information into the planning repository

As this is all about ODI then we will use a load from a text file, for this example I am going to clear out the HSP_TEXT_CELL_VALUE table.

If you have been keeping up with all the blogs then you find it easy to load data into a db table, I will briefly go over it.

Step 1 – Create Text File

Step 2- Create DataStore pointing to the text file

Files tab, File Format – Delimited, Heading = 1, Field Separator - comma (,)
Columns Tab – Reverse the fields.

Step 3 – Create new physical schema in the topology manager to point to the PLANSAMP database tables, added a new logical schema in context tab (PLANSAMPSQLSCHEMA)

Step 3 – Create new model using SQL server technology

Reverse tab set the context, selective reverse tab – selected just HSP_TEXT_CELL_VALUE and ran the reverse.

Step 4 – Create interface to load flat file into database table.

Set Staging area as “Sunopsis Memory Engine”

In the diagram dragged the text file DataStore to the source and the HSP_TEXT_CELL_VALUE DataStore to the target.

The orange warning is only to say that the text_id field is smaller on the target and may be truncated and can be removed by changing the length in the file DataStore setup.

In the flow options set DELETE_ALL to “Yes” so the table will be cleared out.

After running the interface the db table is populated

Here is where the solution has a bit of a let down, planning caches objects which are held in the repository so even though we have loaded the values into the table planning still doesn’t believe they exist, the quick way of getting over this is by restarting the planning server. I know this will be an issue for some as you can’t keep restarting planning but you could schedule these jobs to run just before a nightly refresh of the services, all depends on your implementation.

Hopefully Oracle will one day release a planning API so we could easily deal with situations like this but I don’t hold my breath.

Now what we want to do is load a data file but have the data field as text and not numeric. In my previous blog I created three members with the data type as text, I am going to use the same load file and DataStore but alter the data and insert text values corresponding to the values above.

As you can’t load text data directly into planning what we are trying to achieve is load the equivalent numeric value and this can be done by matching the text data in the flat file with the text in the database table and retrieving the number.

A new interface was created; the first item to drag into the source was the DataSource pointing to the data load file, next the DataSource pointing to the HSP_TEXT_CELL_VALUE table was dragged onto the source.

To create the join “MP3” was dragged onto “VALUE”

The DataStore linked to the plansamp account dimension was dragged onto the target.

Each column was mapped from the source to the target; the “VALUE” column in the source was mapped to the “MP3” column on the target.

So from the diagram you can see that the text members will be mapped but the numeric value will be loaded to the target planning dimension.

After executing and checking for any errors you can see that the data has been loaded in by running the form again.

There we have it, one way of loading text data into planning using ODI, OK it is not the most elegant way because of the way the planning cache works but it certainly overcomes the original problem.


Dain Hansen said...


Quite comprehensive! We've posted a blog summary on, also we'll be promoting your blog as a must-read in one of our newsletters. Really good stuff. Looking forward to the finale.


Pierre-Jean said...

Hello John !
First of all, congratulation ! you do a great job in making Planning and Hyperion tools easier for consultants. I have checked your comments/adds even on Oracle forum and you sound very commmitted to this task.
On the serie ODI integration to Planning, will you cover 'delta load' and 'deleting mass members in dimension' points ? I have some issues on these. I think its a regular use for an ODI/Planning architecture and Oracle does not five much information about it ;-)
looking forward from hearing from you,

John Goodwin said...

Hi Dain, Thanks for the comments and excellent write up on your blog, most impressed that I will be included in one of your newsletters, I will continue the ODI series as soon as I have some spare time. Cheers John

John Goodwin said...

Hi Pierre-Jean, I must also thank you for the comments and I am glad the blogs are being beneficial.
If possible could you explain in more detail what you are trying to achieve with 'delta load' and 'deleting mass members in dimension points' and then once I have a better understanding I can try and help further.

GlennS said...

This is a great series. For all us ODI neophytes it's great. In one of your series, can you go over data manipluation especially from flat file sources. As example for planning dim builds joining two columns or grabing the first character of a column to be the parent. Keep up the good work


John Goodwin said...

Hi Glenn,
The file manipulation items you have raised are quite easy in ODI, I will create a quick blog outlining the ones you mentioned and if you require any additional ones just let me know and I will gladly help.

Anonymous said...

Hi John,

do you know how to display the text data to web analysis?



V D Reddy said...

Hi John,

I am using ODI 10.1.3 for the first time to load metadata (flat file) to Hyperion Planning. Can you please explian right from the beginning assuming i am a new person to ODI.

V D Reddy

John Goodwin said...

If you are new to loading a metadata into planning then start at :-