About
The choice between sparse and dense dimension have a great influence on the storage and therefore on the performance of Essbase.
Essbase creates a data block for each unique combination of sparse standard dimension members (providing that at least one data value exists for the sparse dimension member combination). The data block represents all the dense dimension members for its combination of sparse dimension members.
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.
The goal is to concentrates all sparseness into the index and concentrates all data into fully utilized blocks to provide efficient data storage and retrieval.
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.
Normally, you have approximately equal numbers of sparse and dense standard dimensions. If not, you are probably working with a nontypical data set, and you must do more tuning to define the 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 :
