Changing Dense vs Sparse Settings for an Essbase/Planning App

As soon as a planning application is created or a BSO Essbase cube, dimensions are tagged as either dense or sparse. The dimension of the type Account and period are tagged as dense by default and all others are sparse. A sparse dimension is one with a low percentage of available data positions filled. A dense dimension has a high probability that one or more cells is occupied in every combination of dimensions. Typically all the account and time periods have data for all possible combinations so they are tagged as dense.

Now a memory block is created for every member combination from the sparse dimensions. Each of these memory blocks have a cell corresponding to the dense dimension.

Consider the following cube structure:

Following memory blocks will be formed as the data gets loaded/updated.

Memory block1 – Product1,Customer1

Memory block2 – Product2,Customer1

Memory block3 – Product3,Customer1

Memory block4 – Product1,Customer2

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Memory block5 – Product2,Customer2

Memory block6 – Product3,Customer2

So as is evident from top, BLOCK SIZE = Stored members of dense dim1 X stored members of dense dim2….* 8
Number of cells in our example = 3 X 3 = 9
Block size in our example = 3X3 X8 = 72 bytes

Number of blocks possible = number of stored member of sparse dim1 X number of stored members of sparse dim2 …

Number of blocks possible in our example = 3X2 = 6

How the scripts are written, data exports, performance of the cube in terms of script, webform, reports etc. are governed by this setting for dimensions. For example AGG can be done only on sparse dimensions. Syntax for Dense dimension requires – CALC DIM command.

So it gets important that while designing the cube structure, these performance settings in terms of dense and sparse are taken care of. If these settings are changed too late in the game, then it will require a thorough validation and retesting of all the other components of the application like calc scripts, web forms, reports etc. Calc scripts may throw validation errors and may even stop calculating the right answer, and there might be performance tuning needed for web forms and reports.

When changing sparse-dense settings of a dimension following items will need to be checked –

• Syntax errors in scripts– AGG command can only be used for Sparse dimension, CALC DIM can be used for dense dimension. One of the most important issues that we dealt with at a recent client was related to Two pass setting on a sparse dimension. For example – two pass members specifically played as important part at a client recently. We noticed that Variance calculation in Scenario dimension were not working correctly. We had Scenario dimension set to Sparse. Following is the explanation from oracle documentation

The order in which two-pass is calculated is as follows –

1. Two-pass members in the accounts dimension, if any exist
2. Two-pass members in the time dimension, if any exist
3. Two-pass members in the remaining dense dimensions in the order in which the dimensions appear in the outline

For example, in the Sample.Basic database, assume the following:

Margin% in the dense Measures dimension (the dimension tagged as accounts) is tagged as Dynamic Calc and two-pass.

Variance in the dense Scenario dimension is tagged as Dynamic Calc and two-pass. Essbase calculates the accounts dimension member first. So, Essbase calculates Margin% (from the Measures dimension) and then calculates Variance (from the Scenario dimension). If Scenario is a sparse dimension, Essbase calculates Variance first, following the regular calculation order for dynamic calculations. Essbase then calculates Margin%.

• The data export command follows the order in the outline. So data exports may get impacted if you change the dense vs sparse in the sense that the order of columns getting exported might change. In a calc script when you FIX to perform data export, it follows the outline order and not the order in the FIX.
• Calc script execution times may change

• Attribute dimensions can only be defined for sparse dimension. So if you change the dense-sparse settings, the attribute dimension may not work.
• Business rule will need to be revalidated and perhaps changed.
• Web forms and Reports – performance may be impacted. Best performance on a web form is if we can keep sparse dimension on the page or POV and have dense dimensions form the rows and columns. If there are sparse dimensions that need to be put in rows, then try to put sparse dimensions first followed by dense dimensions. That MAY help with performance. Similar concept can be applied on a report as well.

In conclusion, its better to set dense-sparse while designing the cube outline itself. But typically performance tuning and optimization takes a back seat when it comes to project timeline and tight schedules. So if the settings for Dense-Sparse need to be changed later, make sure you do a thorough regression testing before you proceed.