Empower Users To Improve Calculation Times

As an Essbase user, you have more power to improve performance than you think.  How many times do you lock and send data through Excel, SmartView, or web forms, that include zeros?  How many times do you allocate data to a finite level out of convenience?  Understanding what this does to Essbase is critical to understanding how a user can negatively impact performance without adding any value to the analysis or the results the database produces.

I analyzed a planning database used in one of the largest financial institutions in the world.  Over 60% of the values entered were zero.  Another 20% of the values were less than 1 dollar.  By eliminating the zeros, the total calculation time of the planning application was under 20 minutes.  With the zeros, it was nearly 2 hours.

There are two reasons for this.  First, there is a different between empty and zero.  Empty consumes no space to store whereas a zero consumes the same space as 1 billion.  Think of this as a grocery bag.  If you fill a grocery bag with nothing, it takes up no space.  If you fill it with empty cans (a zero), it consumes the same amount of space as if those cans were full (1 billion).

The example below is very common.  Assume that a forecast needs to be done for the last 3 months of the year.  Frequently, a spreadsheet would hold zeros for the first 9 months.  18 cells have zero and 6 cells have a positive value.  That means that 75% of your data could be eliminated by not loading zeros.

The same load with #Missing is more effective.

I highly recommend reading the article explaining dense and sparse to understand what a block is and what it represents before you continue this article.

There is also another very significant factor in loading zeros.  Loading a zero that creates a block just to hold a value of zero can explode the size of the database, as well as the time it takes to consolidate and execute business rules.  The more blocks that have to be loaded and consolidated, the longer it takes to finish.  If each block was a spreadsheet and you had to do this manually, you would have to open each spreadsheet and enter the number into a calculator to consolidate.  If 75% of the blocks you opened were zero, it wouldn’t change your total, but it would drastically increase the time it takes because you still have to open each spreadsheet.   If an Essbase database has 1,000 blocks, and 75% of them only hold zeros, it will likely take 2 or 3 times longer to calc the zeros because it still has to open the block and add the zero.  Remember, a zero acts no differently than a value of 100.

As an example to the above, the following example would create a block for South and West, inflating the database size.

Users can significantly reduce this unnecessary explosion in size by loading a blank as apposed to a zero.  If zeros are already in the database, leaving the cell blank will NOT overwrite the zeros.  If zeros are loaded inadvertently, a #Missing has to be used to remove them.

For all you users loading data, it can be a hassle removing the zeros.  Being responsible can significantly improve your experience with Essbase.  To make it easier, take a look at the function in the In2Hyperion Excel Ribbon that replaces all zeros with #Missing.