Fine-Tuning Aggregate View Selection

The default selection of aggregate views proposed by Essbase provides excellent performance. However, accepting all aggregate views in the selection list does not guarantee optimum performance. For the default selection, Essbase analyzes stored hierarchies and assumes an equal chance that any aggregate cell will be retrieved. Essbase cannot account for external factors such as the amount of available memory at the time of a query. Available memory can be affected by such factors as the cache memory definition at retrieval time, or the memory other concurrent processes require.

You may want to track which data is most queried and include the results and alternate views in the aggregate view selection process. See Selecting Views Based on Usage.

As you tune and test aggregations, consider the following points:

To estimate the size of aggregate views, you can use the ASOSAMPLESIZEPERCENT configuration setting in essbase.cfg to specify the number of sample cells Essbase uses. The sample size is specified as a percentage of input-level data. The default, and minimum, sample size is 1 million (1,000,000) cells. See the Oracle Essbase Technical Reference.

Essbase provides information to help you select and store the right balance of aggregate views for your database. Weigh this information against what you know about your database retrieval requirements and environment. Use the following information to help you select aggregate views for an aggregation:

The following process is recommended for fine-tuning aggregations:

  1. Perform the default aggregations described in Performing Database Aggregations.

  2. Save the default selection in an aggregation script. See Working with Aggregation Scripts.

  3. Turn on query tracking. See Selecting Views Based on Usage.

  4. Have users perform their usual queries against the database or perform the batch query operations for which the aggregation is being designed. Queries from all query tools are tracked.

  5. After sufficient time to capture data retrieval requirements, perform another aggregation including tracked data.

  6. Analyze the proposed list of aggregate views to be stored, and select the aggregate views that you determine provide the best balance of system resources and retrieval performance.

  7. Materialize the selected aggregate views and, if desired, save the selection in an aggregation script.

  8. Working with aggregation scripts and various selection criteria, repeat the process until you think you have the optimum selection of aggregate views for your situation.

Note:

To optimize aggregations for different database retrieval situations, such as for generating reports or user queries, you may need to repeat the tuning process, creating an aggregation script for each situation. See Working with Aggregation Scripts.