Move Planning Text Data to ASO with String concatenation

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

jMnDLoader a graphical approach for loading data and metadata into Hyperion Planning

This one was there in my drafts for about 2 years. Now that a long time considering that I was trying to create a utility. Now what was/is this about, it is about a thread on Network54 where Cameron Lackpour talked about adding a graphical interface to the OutlineLoad.cmd. I thought well that it is a good tool to work on (considering the number of switches you have to remember to make it work).

Then started the work and I could finish that in two weeks time. (Now you would be imagining, didn't he said two years) It was done in VB and my employer at that time was not comfortable with me publishing it :(

After few months Oracle came up with a web interface for Outline Load and I shelved mine :(

I was surprised when I got a call from my ex-colleague, asking about the possibility of running that on a Linux machine. This prompted me to create a java version so that I can run that on Windows and *nix systems.

I was lazy to work on that one (there is a web one out there and who will be interested - was my reason)

Why now? Someone reminded me that they've seen my tool and they are on 11.1.1.4 version and I realized that there is no such thing as web interface for that version. (there are a lot of versions that we are dealing with and I can't remember what came in which version ;))

So here we are jMnDLoader, the tools provides you with a graphical interface for OutlineLoad utility. It has an option where you can generate MetaData.

This is not a client version, it needs to be executed on the Planning server.


Tool makes intelligent selections for you ;) as you can see for Dry run, it'll not lock the dimension and rest of the options are not enabled.

This is still on the 11.1.1.x format (it doesn't have an option to add properties), however the screen captures are from a 11.1.2.3.500 version (it works with 11.1.2.3.500, except for the properties part)


You can generate Metadata using the tool.






I've added a help file to the tool, you can find more details on how it works there.

I'm planning to integrate EPMA metadata generation and if all goes well, maybe an interface for the infamous EPMA batch client.

You can download jMnDLoader from here.

HTH

DATAEXPORT - FIX on DynamicCalc Sparse Member

You might have seen this and have a workaround for this. However it is a surprise that it cannot work.

Now what am I talking about?

Long gone are the days where you were using jexport, all of us moved to DATAEXPORT. (I still sometimes use Jexport, it is a cool CDF)

This one is about me getting a call from a colleague "I've an issue with DATAEXPORT command, no matter what I FIX it exports all the children."

I tried this on a modified version of Sample Basic.

I've Years in my outline, also Actual is a parent with two children.


You really don't need to mention DataExportLevel ALL; that is the default. I got the required output.


I gave him a call and told him that "It's is working fine for me." He informed that "Scenario" is a sparse dimension on his cube. Modified Sample Basic, ran the calc and what do I see.



It is exporting the values of Actual's children.


Not only it does that, it completely ignores the FIX statment and exports everything for Scenario. Is this documented somewhere.

DataExportDynamicCalc ON | OFF

ON—(Default) Dynamically calculated values are included in the export.

OFF—No dynamically calculated values are included in the report.

Description
Specifies whether a text data export excludes dynamically calculated data.

Notes:

Text data exports only. If DataExportDynamicCalc ON is encountered with a binary export (DATAEXPORT BINFILE …) it is ignored. No dynamically calculated data is exported.
The DataExportDynamicCalc option does not apply to attribute values.
If DataExportLevel INPUT is also specified and the FIX statement range includes sparse Dynamic Calc members, the FIX statement is ignored.

It says DataExportLevel INPUT is specified and range includes Sparse Dynamic Calc members, then the FIX statment is ignored. Is that what I'm seeing? But I've (this is why you see the DataExportLevel set to ALL earlier) set it to ALL. Could this be a bug, it maybe.

A dynamically calculated sparse member, doesn't involve in block creation, so if I try that with DataExportNonExistingBlocks ON; can it work?

The explanation for DataExportNonExistingBlocks is right after DataExportDynamicCalc 

DataExportNonExistingBlocks ON | OFF

ON—Data from all possible data blocks, including all combinations in sparse dimensions, are exported.

OFF—(Default) Only data from existing data blocks is exported.

Description
Specifies whether to export data from all possible data blocks. For large outlines with a large number of members in sparse dimensions, the number of potential data blocks can be very high. Exporting Dynamic Calc members from all possible blocks can significantly impact performance.

I think I just got lucky (I was going under the impression that it is a bug). It works with NonExistingBlocks


Update

I was working on this blog post yesterday and saw Glenn's update on similar (same?) issue today morning (I'm still going ahead with this post). Seems like we both got the call :)

Furthur testing reveals that with DataExportNonExistingBlocks and DynamicCalc Sparse parent member it won't export missing blocks.

Oh wait You don't believe me. Let's prove it.


My export must have 1941 lines (and should have a Row with all #Missing)


Nope I just have 1938 lines and nothing with all #Missing.



Nope no #Missing rows :) (It's strange I know, it think it is bug which happen to creep into from 11.1.2.3.501 - a good bug)

Did I prove Glenn wrong (no I didn't), however I like to think that I did so that I can complete a virtuous cycle (Glenn torturing Cameron, him torturing me and if and only if I can torture Glenn). Well that's lot of an ask :) and lot going on

.5 Million and counting

Yes you read it right, Day before yesterday marked 0.5 Million page views for this blog. I'm truly humbled by those numbers. (Never expected to see such figures)

This is also the year which celebrates the 5th birthday of this blog. It is all because of the you my dear reader.

It's the time to sit back and analyze some of the details.

Stats


Now that's the stat from blogger.com, as you can see the most read article is the one on Workspace customization, I still get some emails on customization stuff and I've a blog post coming up on the same topic.

You'll see a slight difference in the figures from next picture onwards (It's taken from Google Analytics), the reason behind that is because sometimes I forgot to add the code when I change the blog's look and feel ;)

Audience Overview


Now this one tells me that you, read 1.75 pages per session (I'm glad that some of you are landing here and reading more that one post)

I'm also glad that 40% of you come back to see what I've written and thanks to you new visitor.

Demograhics


Now, I'm kind of sad seeing the 16.1% representation there. (I hope to see that number growing)

Countries


US takes the 1st spot on the top ten list (which was kind of obvious). No I don't speak French and nor does this blog has a french version :)

I'm really glad that people around the world do read what I'm writing. (not just me, me and me)

Thank you my dear reader.

Traffic Sources


Google the demi god ranks top
Next is direct - which tells me that you come directly to this place searching for answers, I'll try to improve the quality of post that I write from now onwards :)

Keywords



Well this truly doesn't represent the keywords because I forgot to enable that feature :(, however It still gives you are picture of your search and how you end up reading this blog, I'm surprised that people search for the blog title :)

Thank you

I'm humbled and I don't have anything to say other than "Thank you".

Real Time data push from Planning to ASO

This is a follow up post on what was covered in here.

Lot of us might have asked this - "How can I move data from a Planning application to ASO in real time?"

Well this post tries to answer that same old query. You'll have to be on a Calc Manager version which is greater than 11.1.2.3.502.

There was an issue with 11.1.2.3.502 - When Planning sends a member from Page/POV it adds encloses the member name in double quotes. This was the reason why I didn't cover that in the previous blog. 

Now you'll ask what is wrong in it. Yes this is great on a BSO application, however ASO works differently (it uses double quotes only for literal strings (like in UDA function or a String Compare)). :) "" for BSO member enclose [] for ASO isn't it?

 Sree Menon and team worked on a patch and now that issue is solved.

The below given example is on 11.1.2.4 Planning + Calc Manager.

I'm using Sample Vision application and I've created an ASO replica of it (exception being a new Country dimension).

I created a Calc Manager Rule as shown below


As you can see I'm going to use Variables in this rule. It is easier to create this rule by looking at the form definition. This way you know what is in POV/Page/Row/Column.


Entity, Scenario, Version can be variables in Calc Manager rule.

I'm writing this using RUNJAVA, (Yes I know Cameron is going to object this method ;))


As you can see it is a bit of a mess if the variable needs to treated as set in MDX. (Note - the extra dimension member is covered in Target POV)

MDX set uses '{}' to denote a set and Calc Manager also uses '{}' to denote a variable. So double brackets to the rescue, {[{Scenario}]} is parsed correctly.

Note: Do not use NON EMPTY functions in MDX, this is done to make sure that data clears from Planning is accurately captured.

Now if you are wondering about the encryption of Calc Manager rule, here is how it is done.


There are some characters which cannot be used as key. I did receive an error about AES length not being met.

Let's attach this rule to the corresponding form.


Now let's change some data and see if it copies to ASO.

This is my ASO retrieval before the save.


Changing webform data.


Retrieval after data submit.


I did notice two bugs while preparing for writing this post.
  1. While creating an Application level variable in Calc Manager using Dimension Name option, it won't allow you to type in the custom dimension name. (just behaves like Dimension Type). I can go back to my hack till this one is fixed. :)
  2. Now this one is an issue if you are planning to implement using 11.1.2.4 (I couldn't test this on a version between 11.1.2.3.502 and 11.1.2.4). The MDX script that extract the data gets cached some where, and it won't extract correct data if the Page member is changed.
If I recycle the services and do a data submit then it will correctly move data using the member selected in page, however after the first data movement, the member gets cached. I would hold on for sometime till Calc Manager team fixes this issue.

HTH

Update 03/04/2015

Calc Manager team updated me about the issue with Member caching, they did fix this issue internally, so we might see this coming in a patch soon :)

Views discussed in this blog are my personal views.
They do not represent the view of organizations, businesses or institutions that I'm part of.