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:
Improving retrieval performance can increase disk storage costs and the time it takes to materialize the aggregation.
Tracking queries may result in a set of proposed aggregate views that provide better performance for some queries than for others. Selecting proposed aggregate views can considerably improve performance time of some queries with others experiencing little improvement—but never worse—as long as query type and frequency are close to the type and frequency of queries performed during the tracking period.
Optimizing aggregations may require an iterative, fine-tuning process.
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 maximum storage requirement
You can specify a storage limit for selecting aggregate views in two ways:
When the aggregation selection is initiated, you specify a maximum storage stopping value. Aggregate views are selected until the specified storage limit is reached or there are no more views to select.
In Administration Services, the number you specify is the amount of storage in MB; in MaxL, the number is a factor times the size of the level 0 stored values. See the Oracle Essbase Administration Services Online Help and the Oracle Essbase Technical Reference.
After each analysis of the database, Essbase displays information about the level 0 input cell view followed by a list of suggested aggregate views. Displayed by each aggregate view is a storage number that includes that aggregate view and all other aggregate views it depends on. You can consider this storage number as you select the aggregate views to be included in the aggregation.
The relative “Query Cost” performance improvement
The Query Cost number that is displayed by each aggregate view in the list projects an average retrieval time for retrieving values from the associated aggregate view. The default view selection estimates the cost as the average of all possible queries. When using query tracking, the estimated cost is the average for all tracked queries. The cost number for a specific aggregate view can be different in different selection lists; for example, aggregate view 0, 0, 1/0, 2/0, 0 can show a different query cost in the default selection list than it would show in a selection that includes tracked queries in the analysis.
To compute the percentage improvement, divide the query cost value for the aggregate view into the query cost value shown for storing only level 0 input cells.
Before running an aggregate view selection, you can turn on query tracking to determine which data is retrieved most often. After some period of database activity, you can have Essbase include the usage statistics in the aggregation analysis process. See Selecting Views Based on Usage.
The time it takes to perform an aggregation after the selection process completes increases for each aggregate view materialized. To determine actual aggregation time, you must perform the aggregation.
The following process is recommended for fine-tuning aggregations:
Perform the default aggregations described in Performing Database Aggregations.
Save the default selection in an aggregation script. See Working with Aggregation Scripts.
Turn on query tracking. See Selecting Views Based on Usage.
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.
After sufficient time to capture data retrieval requirements, perform another aggregation including tracked data.
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.
Materialize the selected aggregate views and, if desired, save the selection in an aggregation script.
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.
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. |