A typical Profitability and Cost Management application contains one Measures dimension, one AllocationType dimension, several POV dimensions, and a number of business dimensions. The Dense and Sparse settings must be managed to reduce the size of Essbase databases and to avoid writing the same block multiple times in Essbase, which may cause fragmentation.
Profitability and Cost Management duplicates business dimensions if they are used in more than one stage. This process increases the Sparsity of the Essbase Calculation Cube outline generated by the application, and may have a performance impact when the calculation scripts are run.
Some optimization can be achieved simply by changing the Dimension Storage Type directly for the generated Calculation Cube outline, using Oracle Essbase Administration Services (EAS) console.
Caution! | Changes to the Dimension Storage Type property should only be performed by a Database Administrator (DBA). For detailed instructions, refer to the Oracle Essbase Administration Services Developer's Guide. |
For Profitability and Cost Management, the default recommendation to set the Dimension Storage Type property for these dimensions follows:
Set the Measures and the AllocationType dimensions to Dense as the default.
Note: | The exception to this rule would be if the largest dimension in the largest stage is set to Dense, then the Measures and AllocationType dimensions should then be set to Sparse. |
Set all business and POV dimensions to Sparse
Note: | Dimensions with attribute associations must be set as Sparse dimensions only. |
This default setting results in an Essbase calculation cube with the block size of about 3K, and a large number of potential blocks based on the dimensionality of the Sparse dimensions. The most dense dimension (based on the existence of data) in the largest stage should be set to Dense. In this instance, the Measures and AllocationType dimensions should be set to Sparse.
Example of Dense/Sparse Settings
The following quick example may help make this decision.
The largest stage is defined by the largest number of potential nodes in the stage. If a typical Profitability and Cost Management model has its last stage defined as Customers X Products (to be able to calculate Customer x Product Profitability), and it is typically the largest stage, then either the Customer or the Product dimension in this stage can be set to Dense. If the largest dimension in the largest stage is set to Dense, then the Measures and AllocationType dimensions should be set to Sparse.
The decision on whether to set Customers or Products to Dense is based on the data density of the governing drivers. For example:
If Sales Volume is the driver that is used to allocate to this stage (from various sources in previous stages), if the Customers dimension has more dense data than the Products dimension (few Products sold to more Customers), the Customers dimension can be set to Dense.
If more Products are sold to fewer Customers), then Products can be set to Dense.
In either case, the Essbase block size should be within the limits of what Essbase best practices recommend. See the Oracle Essbase Administration Services Developer's Guide.