I saw an interesting question on OTN.
I’m so tied up at work and don’t get much time to do that often now, however when I do I try to go through ones that interest me.
For those who are interested can read it here. I gave an idea and that idea actually consumed me.
With all the work going on, I was thinking about my idea and it grow in me and now it is out. (Kind of like watching “Inception” and feeling that it can be real!!!!)
What Anubhav (I don’t know him personally) was looking for is a way to get the text data from Planning and load that into a Reporting ASO cube.
As you all know in Planning you can have free form text (Text data type) and one using Smart List. There is an option (In Map to Reporting) where you can convert Smart List to a dimension in your reporting application (Pretty neat stuff, if you ask me)
This is based out of a Text data type measure. How Planning handles text measure is interesting, if you’ve looked at the backend tables, you’ll find a table named HSP_TEXT_CELL_VALUE
Now as you see there is no dimensional element in there, just an ID and a value, what happens is the ID gets stored in Essbase as a number and when the form (Smart View planning connection) opens, it runs a query (if it is a text measure) matches the Essbase value with the ID in this table and shows the text.
Now the idea is getting more clear, all I’ve to do is get an extract of account that I’m going against and does the mapping Planning does and get a list of all Text components.
Do I need to do that or is there an easier way. (I always like easy 😉 )
If you’ve looked at OutlineLoad utility in the later releases (I think this was added from 11.1.2.2.300+), you’ve an option to extract data.
I used OutlineLoad to extract the data.
I got all Text values in a file now 🙂
I can now create a TextList (I still don’t know what it is Smart List in one and Text List in another) from this file.
Oh I forgot to add that there is no MaxL command for importing a Smart List, however there is a JAVA API component which can do it. From here it was easy.
Do some data massaging and load the Text List into Essbase.
I created a bat file which will allow you to do this.
Bit of background on OutlineLoad parameters and Data.
This is how my form and data looks like
I’m using Account as my Load Member for the extract (the dimension which has Text Datatype)
I’m using Period as my Data member (I choose this because, that is one which I felt could be same across all places and will need less customization). The tool scans for 12 data fields only, if you need more then edit the FOR loop to add more tokens)
1 thing that I noted during the extract is it doesn’t like /EDD switch if you are not using a properties file. No matter what you try it keeps telling that /EDD requires 4 arguments and I supplied more, add the same to a properties file and it works like charm.
Things to keep in mind
If you are planning to add a roll up concatenated string, then you should update the member aggregation properties in Essbase. Automatically Essbase tags a Text measure as never consolidate.
I created a jar file for importing Smart List (Text List) and you can use that for just importing a Text List to Essbase.
Let’s look at the bat execution.
After the successful extraction of Text data using OutlineLoad, the tool will prompt you for addition input, If you want to add a roll up text then you’ll have to add it manually in the TextList file.
So in my case I want the roll up to show a concatenated value of my two children.
The tool assigns the IDs automatically, As you saw P1 and P2 roll up to ProfitCenter (so the total is 3) I can add it manually in the file save it and press any key to upload the Text List.
My friend/colleague Kishore Mukkamala was asking what if I’ve sub roll ups. He did gave me the idea of updating that in the blog post (I forgot about this one)
If you’ve have roll ups, the toll won’t know where it is, it’ll sequentially add IDs to all text lists
You’ll have to change the IDs and add roll up concatenation.
E.g If my hierarchy resembles this
Tool is going to produce a Text List file as shown below
You’ll have to look at the Planning extracts and see what is the comment associated to P1 and P2
I know that it is 1 and 2 so 3 will become my concatenation, here is how the final file will look like
Save the file and press any key and you’ll see a message that TextList import was successful.
Here is my Essbase pull
You can download the bat file from here.
HTH
