ODI & Planning - Brief look into file manipulation

Just when you thought the blog about ODI and essbase was going to happen another quick sidetrack comes along, after my previous blog I had some really positive feedback and a request to run through file manipulation, so I am just going to go over briefly a few of the areas that Glenn has raised.

Ok, I think it is best to show the file I have quickly created, I am not going to bother with any of the planning member properties as I have covered all this before.



The logic is the in column A the first item in each cell will be the parent, the child will be the remaining string, the alias will be a concatenation of the child and product code in column B.

The first step as always was to create a DataStore pointing the csv file and reverse it to bring back the columns.



I am going to use the same DataStore I have used for previous examples for the target planning application.



This time I am going to load the data into the Segments dimension, I added a couple of members in planning so the metadata file could be loaded against them.


So now we have our source and target we can create the interface to bring them together.

As usual in the definition of the interface name it, set the context and use “Sunopsis Memory Engine” as the staging area.

In the diagram drag the PRODFILE DataStore into the source and the Segments DataStore into the target.



Usually you would just map the source straight to the target but we have to do a little bit of manipulation to get it into the format we want.

Lets start with the Parent, this will be formed from the Product column in the source. If you select the Parent column in the target it will display the mapping window, from here you can click the “Launch Expression Editor” button.

In the Expression editor there are a number of functions available, we are going to concentrate on the Strings functions.



For the parent we need part of the Product string so the ideal function is SUBSTRING.

The format of the function is SUBSTRING(<string>, <start>[, <length>])

An example of one of the rows from the product file is:- “LCD Sony Bravia 40”

In our case the <string> element will be in the Product column.
The <start> is a numerical value of where you want the function to begin; as we want to start from the beginning of the string it will be 1.

The <length> of the string we want to extract is varied so we can’t just enter a numerical value, so at the moment our function will be :-

SUBSTRING(PRO.Product, 1, <length>)

To find the length we need to use another function, we are trying to find the first occurrence of a space in the string and to achieve this the LOCATE function can be used.

The format is :- LOCATE(<string1>, <string2>[, <start>])

<string1> is the string you are trying to locate
<string2> is the string which you want to find <string1> in
<start> is a numerical value of the location in the <string2> where you want to start from.

From this we can generate:- LOCATE(<string1>, PRO."Product", 1)

The first time I used this function to locate a space character I just used :-
LOCATE(’ ‘, PRO."Product", 1) but unfortunately this invalid, luckily there is a function called SPACE

SPACE(<count>) – where equals the number of character spaces, so we just want 1.

This gives us :- LOCATE(SPACE(1), PRO."Product", 1)

And if we add this to our original SUBSTRING function we come up with.

SUBSTRING(PRO.Product, 1, LOCATE(SPACE(1), PRO.Product, 1))

On the example from the flat file:- “LCD Sony Bravia 40” this will extract “LCD” exactly what we want for the parent.

Right lets move on to mapping the child (Segments), once again we can the same sort of format with the SUBSTRING function, the only difference this time is we want to extract from the first occurrence of a space character and exclude the beginning of the string.

With the use of the LOCATE that we used in the previous example then we come up with.

SUBSTRING(PRO.Product, LOCATE(SPACE(1), PRO.Product, 1))

With the SUBSTRING function you have to specify the <length>, if you don’t include specify it will extract to the end of the string.

On “LCD Sony Bravia 40” it will extract “Sony Bravia 40” which is correct for the child member.

Finally we have to generate the Alias which is a concatenation of the child member and the product code column, in this example I am going to separate the two strings with “ – “.

As we know how to extract the child member we can use that in our formula.

Once again there is a function we can use and this time it is CONCAT

CONCAT(<string1>, <string2>) which is self-explanatory how it works.

To start with we want to concatenate the child string with ‘ – ‘ and this produces.

CONCAT(SUBSTRING(PRO.Product, LOCATE(SPACE(1), PRO.Product, 1)),' - ')

We also need to concatenate the product code the CONCAT function is required again.

Producing :-
CONCAT(CONCAT(SUBSTRING(PRO.Product, LOCATE(SPACE(1), PRO.Product, 1)),' - '),PRO."ProdCode")



The flow diagram should resemble



Ready to execute and check the results.


All looks good in the Operator and the generated log files show no errors.



There we have it from 2 columns in a flat file and some quick manipulation we have generated a hierarchy with parent/child/alias.

Hopefully this will give you enough insight to start being able to manipulate various sources and begin to use some of the inbuilt functions.

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.

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!

ODI Series Part 5 - SQL to Planning

The ODI series continues, we are already up to part 5 and still lots to cover. In the last part we looked at loading hierarchies into planning from flat files but there are many occasions when the metadata can be sourced from database warehouses. Today I am going to cover loading metadata directly from db tables into planning.

In part 3 I set up a connection to SQL server and to a database named hypdata, if your warehouse is Oracle then it is pretty much the same set up just using different JDBC drivers.

For this exercise I have set up two simple db tables one containing the parent/child entity information and the other containing the member properties.





Now there are a few methods of getting them loaded into planning, the first and simplest is to create a view in SQL server or Oracle to join the tables by the child/members fields.



I need to insert a new model in the ODI designer to point the SQL server technology.



You have the option of manually inserting the datastores to point to the db tables/views or you can reverse everything from the Model Reverse tab.



For some reason it didn’t reverse the View so I just checked View, unchecked Table and reversed again, this time it reversed the view.



Ok nice and easy, we just need to create an interface to load the data from the view into the planning entity dimension.

Just before we do this we will need an extra Knowledge Module, we will need a LKM (Loading KM), we previously used “LKM File to SQL” but this time we are loading using SQL.

Import KM from the Project window in the Designer and choose “LKM SQL to SQL”



And it should appear in your LKM list in the designer.

Insert a new interface



Name it; set the context and the staging area, which once again we going to be using the “Sunopsis memory engine”.



In the Diagram drag the View as the source and the planning entity dimension as the target, map the fields and you should end up with something like the image above.

In the HIER_MEMBERS table and HIER_VIEW view there is a dimension field, currently there is only Entity information in there but say we started adding extra dimension member information then the above interface would try and load data that is not required for entities.

This can be easily resolved without having to go back and change the view and to be honest changing the view wouldn’t be a good idea because we would end up with a view for each dimension.

This is where filters come into play, in the diagram source window drag the dimension field off the window and this will create a filter, then you just need to set the filter to value in our case it will be entities.



To check the filter is correct you can click the “Check the expression in the DBMS” button that is on the right hand side of the implementation window. You can also create filters on the view in the model tab.

Your flow diagram should look like this.



I manually added information for the log details in the Options window, this time I have also set the REFRESH_DATABASE to Yes so after the metadata has been loaded into planning it will push it down to essbase.



Executing the interface produced no errors in the log and opening the outline in EAS displayed the new members so the refresh worked fine.

As I said early there were a few methods of loading from two tables one being with the use of a view, now say you didn’t have access to the SQL repository to create a view or for some reason it wasn’t deemed the right solution then you can achieve the same results by creating the join from within ODI.

To achieve this create a new interface and fill in the definition tab in the same way as before, in the diagram tab drag and drop both datastores HIER_MEMBERS & HIER_PROPERTIES into the source window.

Now in the source window drag CHILD from HIER_MEMBER onto MEMBER in HIER_PROPERTIES, this will create a join between the datastores. You can then just drag the planning entity dimension into the target and map all the fields.




And the final flow diagram should look like



I know this is quite a simple example but hopefully it does start to show what can be done with ODI and generate a few ideas how you can incorporate it into your own solutions.

I am going to leave it there for Planning and next time I will move on to using ODI with Essbase, I will start to look at reverse engineering a database outline and output to different formats.