[Hyperion] Design Essbase ASO Aggregation

Design Essbase ASO aggregation

ASO cubes are by default dynamic in nature i.e. in ASO all aggregations happen on the fly .. So data is stored only at Lev 0 .. For small ASO cubes it gives pretty good performance in reporting, but for huge ASO cubes ( Generally which is the case) report performance is a concern ..

'Design Aggregation' feature of ASO is a good option provided by Essbase .. In aggregation data is calculated at upper level and stored, so data retrieval becomes much faster.. Following are the steps for designing aggregation for ASO cubes

1. Enable 'Query Tracking' :  Query tracking will track data  combinations which have more data values and which requires Agg to improve performance..
e.g. If you have 2 parents with 100 children each, then Query tracking will keep track of which all children have data for some combinations and will suggest in future required aggregate views .

2. Go to 'Design Aggregation'

3.  First select  Use recommended views to materialize aggregation .. It will show all the views selected by system .. It is a good practice to include them first in Agg view definition ... Select all these views and save and materialize them .

4. Go back to 'Design Aggregation' and now select second option .

5. Select  'Select all recommended aggregate views' .. You can specify total storage space in this screen

6. Select 'Use query Tracking data during view selection' (Option present at bottom) and click on Start .. It will show all views tracked by system using Query tracking ..

7. Save and materialize views..

8. Repeat these steps once you load any new data and save new aggregate views in existing saved aggregation..

If you change any dimension name or change property of member like if stored becomes Label Only or calculated one , then entire Aggregation becomes invalid and you need to re-do this process..