Best of both Worlds: An Essbase solution using both block and aggregate storage
Posted by jim omalley on Mon, Feb 01, 2010 @ 12:51 PM
Written By: Dennis Hogan
Best of Both Worlds:
An Essbase Solution Using Block and Aggregate Storage
Crown Partners recently undertook a complex project where we actually combined the use of Block and Aggregate storage to accomplish our client's goal. Here's what happened:
The Mission
1) Build a solution with 7 total dimensions (3 of which contain several thousand members);
2) Two of the dimensions share the same base level members. These cannot be alternate hierarchies-they must be presented to the user in separate dimensions;
3) Include complex calculation logic to perform allocations.
4) Include 10 scenarios that all need to have the calculations run against them;
5) Include 5 years of actual data for historical trending and one future year for plan and forecast results; and,
6) Process updates nightly in 4 hours or less.
The complex calculation logic suggests the need for the robust calculation engine that Block Storage (BSO) offers. But, the significant volume of historical data, dimension sizes, and processing window suggest the need for an Aggregate Storage (ASO) solution. Which one should you use? The answer is BOTH!
The Solution:
Here's a high-level view of how we made it happen.
Step 1: We created three BSO cubes. Each one contained a subset of the data:
Cube A: Current Year and Last Year - Actuals only
Cube B: All other History Years - Actuals only
Cube C: Forecast and Plan data for Current Year and Next Year.
Step 2: Each BSO cube contains one less dimension than defined in the mission. In the BSO cubes the two dimensions that share the same base are combined as alternate hierarchies. These BSO cubes are used for pre-processing only. Users do not see the combined dimension.
Step 3: We created one ASO cube. It contains all the data described above and includes separate dimensions for the items that were combined as alternate hierarchies in a single dimension for the BSO cubes.
Step 4: Only cube A processes nightly in the 4-hour window
Step 5: Cubes B and C are processed as needed when history, forecast, or plan data is revised. This processing includes all allocation logic. The results of the processing are then exported to text files using data export calculation scripts.
Step 6: Nightly processing runs Cube A and executes all of the complex allocation logic. Results are then exported out to text files using export calculation scripts.
Step 7: The text files from the nightly processing are then loaded into the ASO cube along with the files from the other cubes that were created prior to the nightly process launching.
Step 8: As part of the load process, the alternate hierarchy used in the BSO pre-processing cubes is split into 2 separate dimensions for presentation to the users.
Step 9: The end-to-end processing time for the nightly process, including significant levels of aggregation in the ASO cube, is just 2 - 2.5 hours.
Step 10: Users access only the ASO cube.
This is a high-level overview of our approach. Interested in the details? Feel free to contact me at dhogan@crownpartners.com.
Questions or comments?
info@crownpartners.com