In the world of Oracle EPM, the order in which you list your dimensions in the outline isn't just for organization—it is a performance blueprint.
1. What is the Hourglass?
The Hourglass refers to the ideal visual shape of your dimension statistics. In a perfectly tuned BSO cube, your dimensions should be ordered based on their sparsity and number of members.
Tutor's Rule: We order dimensions from the most dense to the most sparse, but with a specific twist for the sparse dimensions.
2. The Golden Rule of Ordering
Dense Dimensions (Smallest to Largest):
* Start with your smallest Dense dimension (e.g., Years or View).
* End with your largest Dense dimension (usually Accounts).
Sparse Dimensions (Largest to Smallest):
* Put your largest, most sparse dimensions (like Product or Customer) right after the Dense block.
* Put your smallest, least sparse dimensions (like Entity) at the very bottom.
3. Why This Order?
It’s all about how the Index is built. The index is like the "GPS" for the blocks. When you query data, Essbase searches the index from top to bottom. Identifying the big sparse dimensions early makes the search path significantly shorter.
4. The "Hourglass" Performance Test
Imagine you have a query for: New York -> iPhone -> January -> Actuals.
* Bad Order (Entity at top): Essbase finds "New York" (which has 5,000 products) and has to scan a massive list of products under New York.
* Good Order (Product at top): Essbase finds "iPhone" first. Since the iPhone index entry points directly to a smaller subset of entities that actually sell iPhones, the search is faster.