Formatted Hyperion Essbase Level-0 extract - The sorcery of report scripts

In one of my previous blogs, I had talked about the structure of level 0 data format in ASO and BSO. While these are easy to export, it takes some help to get it in a neat report structure. Also, in another one of my blogs, I had spoken how to validate files against the data loaded in the system. Well, as it often happens in life, two different ideas came together and I got an idea on how to format level-0 data for export in a relational table format… This comes with a statutory disclosure as always… There is a reason why the level-0 data export is fast… It does not care if you can read the export or not…All that matters is that Essbase is able to write it quick and Essbase is able to read it quick… Now if you want a system that formats the data in a presentable format, you will have to compromise a bit on the performance… Report scripts are slower because each of them undergoes through a set of stages…If you try this on a massive system, chances are the efforts might not be worth it and the report may run forever, metaphorically speaking…

Below is the sample outline of the BSOSAMP application that I love to work on… The application has four dimensions, namely, Account, Period, Entity and Year.

 

 

Below is the step 1 of the report script that I have created… It suppresses missing rows and will produce a non-indented tab delimited file.

I want Account, Entity, Year and Period in a row…which is specified in the <ROW section.

I have chosen four level-0 members, one from each dimension… This is as follows:-

• ·         Representing Account I have “Sales” (I imagine I am introducing Miss Universe contestants when I am saying this)
• ·         “Jan” from Period dimension
• ·         “E1” from Entity dimension
• ·         “FY16” from the Year dimension.

Any Essbase cube will usually have a level-0 placeholder member (Planning folks will immediately relate BegBalance here). You can very well use this…

Before each of these member I use a member selection function <ONSAMELEVELAS as shown in the below snapshot…

 

 

The report script basically now reads as follows:-

Give me the non-missing data in a tab delimited format with all the members of Account dimension which are at a same level as Sales (which in turn is level 0) across members of Period dimension which are at same level as Jan across Level-0 entity dimension members across level-0 year dimension members… 

So by just some manipulations I now have the extract in a neat report format as shown in the below snapshot…

On a side, many a times you will have aggregate storage cubes that store the granular data and you can have a summary cube that has the high level members for dashboarding…If you are not in a mood for doing a partition to get data through, you can always use a structure similar like the above report script, replace the <ONSAMELEVELAS with <ONSAMEGENAS and voila, you have a summary extract…