When a client asked why their application was running slow, it was a good opportunity to determine if the dimension order in the outline was correctly set up. Although, there are many approaches to “fine tuning” an outline, I’ve found using the modified hourglass approach (also known as the “dress form” or “hourglass on a stick”) for dimension order can be instrumental in addressing overall performance in Essbase. This approach follows two main guidelines: 1. The Time dimension is always first, followed by the Accounts dimension, 2. Dimension order is based on density.
The “dress form” or “hourglass on a stick” for the dimension order is:
2. Largest dense dimensions
3. Smallest dense dimensions
4. Smallest aggregating sparse dimensions
5. Largest aggregating sparse dimensions
6. Non-aggregating sparse dimension
The goal is to define the dimension order based on density not on stored member count or the more commonly used hourglass order. The dimension order shown above would represent an “optimized” outline order of the dimensions based on the modified hourglass design.
Determine the Modified Hourglass Dimension Order
How do we define the density of each dimension?
Set each dimension to dense, while setting the remaining dimensions to sparse in the outline properties tab:
Then, note the block density after each load and after each calculation in the database in the database>edit>properties>statistics tab:
Do this for each dimension in the outline until all dimensions have had data loaded and calculated when set to dense. I used the Demo Basic “Data.txt” file for loading and calculating to determine the density for each dimension.
Based on the individual dimension’s density and applying the modified hourglass guidelines, the revised order of the Demo Basic outline should look like:
The revised order of the Demo Basic outline should look like:
This is just one tuning method that can be used to address overall Essbase performance and to set a base line of what the possible options are for ordering the dimensions in the most optimized way when using the modified hourglass outline order.
Blog entry by Daniel Santos of Key Performance Ideas