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.