Use the aggregation tab to specify the various parameters that control the generation and storage of aggregate data for the cube. There are two subtabs:
The Rules subtab identifies the aggregation operator for each dimension of the cube.
Aggregation Order and Method
This table lists the dimensions of the cube. Select an operator for each dimension.
Order: The order in which the dimensions are aggregated. When the aggregation operators are the same across all dimensions, the order is not important. However, some combinations of operations produce different results depending on the order in which they are calculated. To change the order, select a dimension and use the arrow keys to move it up or down the list.
Dimension: The dimensions in the cube that will be aggregated.
Operator: Choose the type of calculation you wish to perform across each dimension. Some operators are not compressible because their values can change for every level: all weighted operators, all scaled operators, and the Hierarchical Weighted Average operator. When these operators are used in a cube, the aggregation engine compresses the dimensions that it can, but it cannot compress the entire cube. Refer to the table below for a description of the operators.
Based On: When using a weighted or scaled operator, select a measure that contains weight factors. The measure must be dimensioned by the one being aggregated, and can optionally be dimensioned by other dimensions in the cube.
| Operator | Description |
|---|---|
| Average | Adds data values, then divides the sum by the number of data values that were added together. |
| First Non-NA Data Value | The first real data value. |
| Hierarchical Average | Adds data values, then divides the sum by the number of the children in the dimension hierarchy. Unlike AVERAGE, which counts only non-NA children, HAVERAGE counts all of the logical children of a parent, regardless of whether each child does or does not have a value. |
| Hierarchical First Member | The first data value in the hierarchy, even when that value is NA. |
| Hierarchical Last Member | The last data value in the hierarchy, even when that value is NA. |
| Hierarchical Weighted Average | Multiplies non-NA child data values by their corresponding weight values, then divides the result by the sum of the weight values. Unlike WAVERAGE, HWAVERAGE includes weight values in the denominator sum even when the corresponding child values are NA. Identify the weight object in the Based On field. |
| Hierarchical Weighted First | The first data value in the hierarchy multiplied by its corresponding weight value, even when that value is NA. Identify the weight object in the Based On field. |
| Hierarchical Weighted Last | The last data value in the hierarchy multiplied by its corresponding weight value, even when that value is NA. Identify the weight object in the Based On field. |
| Last Non-NA Data Value | The last real data value. |
| Maximum | The largest data value among the children of each parent. |
| Minimum | The smallest data value among the children of each parent. |
| Nonadditive | Do not aggregate any data for this dimension. Use this keyword only in an operator variable. It has no effect otherwise. |
| Scaled Sum | Adds the value of a weight object to each data value, then adds the data values. Identify the weight object in the Based On field. |
| Sum | Adds data values. (Default) |
| Weighted Average | Multiplies each data value by a weight factor, adds the data values, and then divides that result by the sum of the weight factors. Identify the weight object in the Based On field. |
| Weighted First | The first non-NA data value multiplied by its corresponding weight value. Identify the weight object in the Based On field. |
| Weighted Last | The last non-NA data value multiplied by its corresponding weight value. Identify the weight object in the Based On field. |
| Weighted Sum | Multiplies each data value by a weight factor, then adds the data values. Identify the weight object in the Based On field. |
Aggregation Hierarchies
Select one or more hierarchies for each dimension being aggregated. If you omit a hierarchy, then no aggregate values are stored for it; they are always calculated in response to a query. Because this will degrade runtime performance, you should omit a hierarchy only if it is seldom used.
The Precompute subtab identifies the method used to decide which values are precalculated and stored in the cube during data maintenance, and which values are calculated on demand in response to a query.
Cost-based aggregation
Use this method to allow the OLAP engine to identify the most cost-effective areas of the data for precalculation.
Partition or Bottom Partition
Type a percentage (0 to 100) in the text box for the cube or for the lower partitions, that is, the levels at or below the selected partitioning level of the cube. You may want to adjust the percentages over time to balance runtime performance with maintenance restrictions on time and disk space. To tune your database, you should track both runtime performance and maintenance statistics at various percentages of precalculation.
A value of 0 does not create any aggregate values; they are calculated at run-time to provide the answer sets to queries. The result of 0% pre-aggregation is the fastest maintenance, the least storage space, but the slowest query response time. A value of 100 creates all of the aggregate values, which are simply fetched in response to queries. The result of 100% pre-aggregation is the longest maintenance, the most storage space, but the fastest query response time. Most DBAs choose values between these two extremes to balance the performance requirements for queries with the limitations of a data maintenance window.
A value of 1 only creates 1% of the aggregate values, but also creates the data structures for storing and tracking the aggregates. Thus, the amount of time to calculate this small percentage is correspondingly longer.
Top Partition
When the cube is partitioned, you can set the top partition to a different percentage than the rest of the cube. The top partition is typically the largest partition, and it is the one initially queried by many applications. It contains the levels above the partitioning level.
Level-based aggregation
For each dimension, select the levels that you want to precalculate. The base level should always be selected.
Dimension
Select each dimension to display its levels.
Levels
Select the levels you wish to calculate and store as part of the build process.
The best method for identifying levels for stored data is to determine the ratio of dimension members at each level, and to keep the ratio of members to be calculated at runtime at less than 10:1. This method assures that all answer sets can be returned quickly. Either the data is stored in the cube, or it can be calculated by rolling up 10 or fewer values into a single number. The time needed to roll up 10 values is trivial, and a well designed application will limit return sets to an amount of data that an analyst can scrutinize easily. You can modify this ratio using your judgment on how frequently a level is accessed.
Slower varying dimensions take longer to aggregate because the data is scattered throughout its storage space. If you are optimizing for data maintenance, then fully aggregate the faster varying dimensions and use skip-level aggregation on the slower varying dimensions.
Copyright © 2003, 2007 Oracle. All rights reserved.