Essbase - Dense and Sparse Selection Effect on the data storage (performance)
About
The choice between sparse and dense dimension have a great influence on the storage and therefore on the performance of Essbase.
By carefully selecting dense and sparse standard dimensions, you can ensure that data blocks do not contain many empty cells, minimizing disk storage requirements and improving performance. In Essbase, empty cells are known as #MISSING data.
Data blocks with many empty cells store data inefficiently.
Below three scenarios, the two first extreme and the last one the ideal.
Article Related
Scenario 1: All Sparse Standard Dimensions
If you make all dimensions sparse, Essbase creates data blocks that consist of single data cells that contain single data values. An index entry is created for each data block and, therefore, in this scenario, for each existing data value.
This configuration produces an index that requires a large memory. The more index entries, the longer Essbase searches for a specific block.
Scenario 2: All Dense Standard Dimensions
If you make all dimensions dense, Database with All Dense Standard Dimensions, Essbase creates one index entry and one large, sparse block. In most applications, this configuration requires thousands of times more storage than other configurations. Essbase must load the entire memory when it searches for a data value, which requires enormous memory.
Scenario 3: Dense and Sparse Standard Dimensions
Based on your knowledge of your company’s data, you have identified all your sparse and dense standard dimensions.
Essbase creates dense blocks that can fit into memory easily and creates a relatively small index, Your database runs efficiently using minimal resources.
An Ideal Configuration with Combination of Dense and Sparse Dimensions :
