Context: Design of a new hyperion planning or essbase application
Objective : correctly organize dimensions in hyperion planning & essbase outline, for optimization of overall calculation & retrieval performances.
In this section, we’ll see how to organize dimensions in between them , in Planning or essbase application.
First, let’s consider a Budget reporting tool, including following analysis dimensions:
To order correctly dimensions, one should class them into 2 categories:
- dimensions to be aggregated
- dimensions not supposed to be aggregated, known as “flat”
Once categorized, dimensions should be ordered as follows in the database outline:
- First, put the dense dimensions to be aggregated, from the more dense to the less dense (consider the number of stored members to identify which dimension is more dense than the other)
- Second, put the sparse dimensions to be aggregated, from the less sparse to the more sparse (as for dense dimensions, consider number of stored members to see the less or more sparse dimension)
- Third, put the sparse dimensions not supposed to be aggregated, from the less sparse to the more sparse.
Considering example introduced above, that leads us to the following categorization:
Thus, we end up with the following dimension order:
- Account
- Period
- Entity
- Final Customer
- Market
- Business Line
- Consultant
- Scenario
- Version
- Currency
This organization will render best performance and best trade off between calculation & retrieval performances.
If you are more focused on retrieval performances, you can also try to position the largest sparse dimension at the first position of the sparse dimensions group, and then test potential improvements in retrieval performances. This is to be tested case by case.
