Hyperion BSO Level 0 data analysis

In this blog, I am going to talk about the Level 0 data export for Hyperion Essbase BSO application and some cool things that I found out about them(usually by messing with them). So I created a BSO application with four dimensions as shown in the below snapshot.

 

The members in the account dimension is as shown in the below snapshot. 

The members in Period dimension is as shown in the below snapshot.

 

The members of Entity dimensions are split into the four co-ordinates as shown in the below snapshot.

The Year dimension is as shown in the below snapshot.

This is the dense-sparse settings for the application that I have kept for the time being. So I have kept Account as dense and Entity, Year and Period as sparse dimension.

 

Now, I loaded some dummy data using SmartView into the Essbase application BSOSamp. ( BSOSamp is my Sample.Basic. The name has a nice ring to it)

Once the data has been loaded into the system, I run a level-0 data export for the application.

The file that is exported is as shown in the below snapshot.

 

Now, let us analyze the file structure… We know that Level-0 data loads fast into an Essbase application. The reason for this can be understood by understanding the format of the level-0 data export.

The first line has the dense dimension listed. So we have Account dimension members spread on the first line. It is curious to note that though we specified the level 0 data, if you closely observe the first line, you have the upper level members like Margin, Profit, Total expenses and so on from Account dimension also listed in the export. This is because this export is mimicking the internal block structure of the dense dimension. Since we have just one dense dimension, the data block is a single array of all members of Account dimension.

The Second line of the export represents the index entry of “Jan” -> “E1” -> “FY15”

The third line has the data for the index entry. This data can be mapped one-to-one to the first line. So the first column value is the value of Sales, the second value is the value of Cost of Goods Sold and so on.

Now, I decided to change the dense sparse settings a bit as shown in the below snapshot.

 

I made the Account and Period dimension dense and Entity and Year are sparse as before.

I now wanted to see if the file import will fail because the dense sparse settings have been changed. I loaded the level-0 data extract and ran the import and it completed successfully.

I exported the level-0 data now from the system once again and the format of the export is as follows:-

 

Now in this export, the Period dimension is on the first line since Period was tagged as Dense.

The second line has the index entry of “E1” -> “FY15”

The third line onwards we have the Account dimension level-0 members in the format of the data block.

I changed the dense sparse settings once again and tagged Account as a sparse dimension and ran the data import once again.  This time also the file imported successfully.

So the findings that I have come to is as follows:-

• The export is highly optimized. This is basically done by using a technique similar to what is used in video encoding. (Movies are basically still images that are flashed at a rate of 24 plus still images per second. Due to persistence of vision, we get an illusion of a moving image. It is actually quite cool. I remember in my initial training days in Java, we had designed a system that refreshed images at a high rate and we had shown a rocket launch. It was pretty basic but at that time it felt awesome to design.)  
• The export needs just sufficient information of what changes line-by-line and it will map the data as per the first line. So long as the members are same, dense and sparse setting will not affect the data import.
• The first dense dimension needs to map all the dimension members. If you see, the first line of the level-0 exports, we basically have the IDescendants of one of the dense dimension.
• The data export is compact. You can have all the information in the system compressed and optimized, so it loads fast.