A typical Profitability and Cost Management application contains one Measures dimension, one AllocationType dimension, several POV dimensions and a number of business dimensions. 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. |
The default recommendation to set the Dimension Storage Type property for these dimensions follows:
Set the Measures and the AllocationType dimensions to Dense
Set all business and POV dimensions to Sparse
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 that case, the AllocationType and Measures dimension should be set to Sparse.
Note: | The block size should still remain within the limits of the Essbase best practices recommendations. See the Oracle Essbase Administration Services Developer's Guide. |
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 calculate Customer x Product profitability), and it is the largest stage, then either the Customer or the Product dimension in this stage can be set to Dense. Set the associated Measures and AllocationType dimensions to Sparse. The decision to set Customers to Dense 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, and 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 remain within the limits of the Essbase best practices recommendations.
Note: | Dimensions with attribute associations cannot be set to Dense. Essbase allows attribute associations to be set as Sparse dimensions only. |