When an HPCM application is first created, it is likely that you will want to carry out some optimization on the HPCM application’s Essbase outline in order to improve calculation execution times.
There are several things that you may wish to consider when it comes to dimensions:
- Because at least one dense dimension for an application is required to deploy from HPCM to Essbase, “Measures” and “AllocationType”, as the only required dimensions in an HPCM application, are created dense by default.
- However, for optimization reasons, you may wish to consider changing this default dense/sparse configuration.
- In general, calculation scripts in HPCM execute best when they are targeting destinations with one or more dense dimensions.
Therefore, consider your largest target stage i.e. the stage with the most assignment destinations and choose that as a dense dimension.
- When optimizing an outline in this way, it is not possible to have a dense dimension in every target stage and so testing with the dense/sparse settings in every stage is the key to finding the best configuration for each individual application.
- It is not possible to change the dense/sparse setting of individual cloned dimensions from EPMA.
When a dimension that is to be repeated in multiple stages, and therefore cloned, is defined in EPMA, every instance of that dimension has the same storage setting. However, such manual changes may not be preserved in all cases.
- However, once the application has been deployed from EPMA to HPCM and from HPCM to Essbase, it is possible to make the dense/sparse changes to a cloned dimension directly in Essbase.
This can be done by editing the properties of the outline in Essbase Administration Services (EAS) and manually changing the dense/sparse settings of individual dimensions.
There are two methods of deployment from HPCM to Essbase from 22.214.171.124.
There is a “replace” deploy method and an “update” deploy method:
- “Replace” will delete the Essbase application and replace it.
If this method is chosen, then any changes made directly on the Essbase outline will be lost.
- “update” will preserve the Essbase outline, including any manual changes you have made (i.e. changes to dense/sparse settings of the cloned dimensions).
- If you are using the calculation optimization technique mentioned in a previous blog to calculate multiple POVs (https://blogs.oracle.com/pa/entry/hpcm_11_1_2_optimising) or Doc ID 1463827.1 and you are calculating all members of that POV dimension (e.g. all months in the Period dimension) then you could consider making that dimension dense.
- Always review Block sizes after all changes!
The maximum block size recommended in the Essbase Database Administrator’s Guide is 100k for 32 bit Essbase and 200k for 64 bit Essbase.
However, calculations may perform better with a larger than recommended block size provided that sufficient memory is available on the Essbase server.
- Test different configurations to determine the most optimal solution for your HPCM application.