Essbase - understand storage for dense dimension and sparse dimension
In Essbase we could tag a dimension either 'dense dimension' or 'sparse dimension'. The definition of dense dimension is that the account dimension value must exist for almost every 'dense dimension' member. While for sparse dimension, there shouldn't be many value for 'sparse dimension' member.
Well this is easy to understand but you will not fully understand these two concept unless you 'r aware of how essbase use different storage machenism for these two dimension type.
It is all about storage efficiency!
Because essbase is an MOLAP product, it usually store value for members and their parents. For example, for a soft beverage sales analysis cube, It is not surprised that every country sell all the products.
The cell for storage of this cube is at least 6*2=12 and no storage is wasted.
Now we will see another example for a soft beverage sales analysis cube for children under the age of 10.
you could see that there 's no value for most of the cell (in fact no any value for beer sales at all).So if we still use the same cells storage allocation for this cube, It will be a total waste of storage that 's unreasonable.
How to avoid such waste of storage?
Essbase solve this problem through the introduction of 'dense dimension' and 'sparse dimension'.
For all the dense dimension , Essbase will form a small block which total cell is equals to all the mulplication of dense dimension.
For our example above, the total cell is 6*2=12
For all the sparse dimensions, Essbase will not allocate cell storage for them.
All the sparse dimension will only add entry to the storage structure called 'Essbase index' which point to the real storage block formed with dense dimension instead !
So It is very important to select the right dimension type for the outline design for storage efficency! It is not a merely name difference between 'dense dimension' and 'sparse dimension'!
Sparse and Dense Dimensions
Most multidimensional databases are inherently sparse: they lack data values for the majority of member combinations. A sparse dimension is a dimension with a low percentage of available data positions filled.
For example, the Sample Basic database shown in Sample Basic Database Outline includes the Year, Product, Market, Measures, and Scenario dimensions. Product represents the product units, Market represents the geographical regions in which the products are sold, and Measures represents the accounts data. Because not every product is sold in every market, Market and Product are chosen as sparse dimensions.
Most multidimensional databases also contain dense dimensions. A dense dimension is a dimension with a high probability that one or more data points is occupied in every combination of dimensions. For example, in the Sample Basic database, accounts data exists for almost all products in all markets, so Measures is chosen as a dense dimension. Year and Scenario are also chosen as dense dimensions. Year represents time in months, and Scenario represents whether the accounts values are budget or actual values.