Essbase - Dense and Sparse Selection Effect on the data storage (performance)

Essbase Overview


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.

Essbase Database With All Sparse Standard Dimensions

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.

Essbase Database With All Dense Standard Dimensions

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 :

Essbase An Ideal Configuration With Combination Of Dense And Sparse Dimensions

Documentation / Reference

Discover More
Essbase Overview
Essbase - ASO/BSO Storage

Essbase support two storage type : block storage (BSO) and aggregate storage (ASO) as a persistence mechanism for multidimensional databases. How the data are internally stored is determined by...
Essbase Data Block For D22 E3
Essbase - Data block

A data block is a multidimensional array structure that contains the data. each unique combinationsparse standard dimension membersdense dimension members A data block is created if at least one...
Essbase Overview
Essbase - Outline Creation and Management using Outline Editor

This article provides an overview of creating outline using Outline Editor. For information Outline Editor, see the Oracle Essbase Administration Services Online Help. All examples in this chapter are...
Essbase Overview
Essbase - Selection of Dense and Sparse Dimensions

The setting of the dimension as sparse or dense have a lot of influence on the storage. See this article to understand how : . If you match patterns correctly, you can store the existing data in a reasonable...

Share this page:
Follow us:
Task Runner