ODI Series Part 6 - Data load to planning

Just as I thought I would be leaving planning and ODI alone for a while a question has popped up on the Oracle forums that is probably easier to answer through a blog.

Basically the question is how is it possible to load data into planning that has a member type of Text. I did skip a blog about loading data into planning through ODI because I felt that it is probably more appropriate to load data directly into essbase using the essbase KMs, no matter which method you use it all ends up in essbase anyway.

Seeing as the question has been asked I will show how data can be loaded using the planning KM and then move on to the Text problem.

As usual I am going to demonstrate on the planning sample application so it can be easily replicated. When you load data into planning you first need to set what the data load dimension will be plus you will need a driver dimension and its members you want to load data for.

I am going to set Account as the Data Load Dimension and use Segment as the Driver dimension against member “MP3”

To do this choose Administration > Data Load Administration from planning web.



If you are interested in what happens when you hit save then basically it writes information by object id into the planning app db table HSP_DRIVER_MEMBER



So with a quick bit of SQL



You can output the information you entered into planning web.



Anyway once you have set these details you can follow the reverse engineering process for a planning application (if you need a refresher look HERE)

I created a new Model to generate the reverse for.





After running the “Reverse” you will have extra columns in the Account DataStore than if you had not set up the data load settings in planning.



The extra columns are:- “Data Load Cube Name” which defines which essbase cube you will load the data to, “MP3” – this is the Segment member which we set in driver dimension set up and “Point-of-view” which is used to hold the remaining dimension combinations for the data load.


I added three account members MusicSales1 – 3 all with the data type of Text. . (You don’t have to set the member as text this is just ready for the next blog on loading text data)



Also I made sure that Account was selected in “Evaluation Order” as otherwise you will not be able to use the text functionality in web forms



I created a quick form to display data against the new members and for Segment MP3, now I can produce the flat file.



As I picked the data load dimension as Account then the data needed to be by account, column B is the “Data Load Cube Name” which in this case is the consol database, the POV was set to the remaining dimension members each seperated with a comma :-
Currency :- Local
Entity :- NY
Period :- Jan
Scenario :- Actual
Version :- FY08

The final column contains the data and is against MP3 as we set that as the driver dimension member.

Ok, back to the ODI developer and set up a new DataStore



I created this as a child of a flat file Model and pointed the Resource Name to the CSV file.



File Format of delimeted, the file has a row heading so that was set to 1, Field Seperator was set as a comma (,) and the Text Delimeter as quotation mark (“) because the CSV file has commas in it to separate the POV members and excel places quotation marks around the field.



The reverse generated the columns from the csv file.



A new interface was created, applied the context and set the staging area as the “Sunopsis Memory Engine”



On the diagram tab, I dragged the DataStore for the csv data load file into the source window and dragged the planning app DataStore for Account on the target.
Each column in the source was mapped to the target using the staging area for columns that have a different name between the source and the target.



The flow diagram should resemble the above.



Once I had executed the interface and checked the output log for any errors I ran the form again to check the data had been populated correctly.

So there we go a quick overview on how to load data into planning using ODI, I can now move on tackle the problem of loading text data, stay tuned!