|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
Historically, aggregating data was summing detail data to provide subtotals and totals. However, using OLAP DML aggmap objects you can specify more complex aggregation calculation:
The summary data dimensioned by hierarchical dimension can be calculated using many different types of methods (for example, first, last, average, or weighted average). For an example of this type of aggregation, see Example 6-27, "Aggregating Up a Hierarchy".
The summary data dimensioned by a nonhierarchical dimension can be calculated using a model. This functionality is useful to calculate values for dimensions, such as line items, that do not have a hierarchical structure. Instead, you create a model to calculate the values of individual line items from one or more other line items or workspace objects. For an example of this type of aggregation, see Example 6-26, "Solving a Model in an Aggregation".
The detail data used to calculate the summary data can be in the variable that contains the summary data or in one or more other variables. The variable that contains the summary data does not have to have exactly the same dimensions as the variables that contain the detail data. For an examples of this type of aggregation, see Example 6-24, "Aggregating into a Different Variable"Example 6-24, "Aggregating into a Different Variable" , and Example 7-7, "Capstone Aggregation".
The data can be aggregated as a database maintenance procedure, in response to user requests for summarized data, or you can combine these approaches. See "Executing the Aggregation" for more information.
Data that is aggregated in response to user requests can be calculated each time it is requested or stored or cached in the analytic workspace for future queries.
The specification for the aggregation can be permanent or temporary as described in "Creating Custom Aggregates".
To aggregate data using the OLAP DML, take the following steps:
Decide if you want to aggregate all of the data as a database maintenance procedure using the AGGREGATE command or on-the-fly at runtime using the AGGREGATE function, or if you want to combine these approaches and precalculate some values and calculate others at run time. For a discussion of the various approaches, see "Executing the Aggregation".
Note:When the variable that contains the data you want to aggregate is dimensioned by a compressed composite, you must use the AGGREGATE command to aggregated the data. See "Aggregating Variables Dimensioned by Compressed Composites" for more information.
When the aggregation involves aggregating data up a variable dimensioned by a composite, ensure that the composite has a BTREE index.
Issue a DEFINE AGGMAP statement to define the aggmap object as type AGGMAP.
Write the aggregation specification as described in AGGMAP.
When aggregating a partitioned variable, run PARTITIONCHECK to check that the aggregation specification created in the previous step is compatible with the variable's partitioning. If it is not, either rewrite the aggregation specification or repartition the variable using CHGDFN.
(Optional) Add one or more of the following properties to variables that will use the aggmap object:
For data that is to be precalculated:
For brief descriptions of all of the OLAP DML statements that relate to aggregation, see "Aggregation Statements".
When variables are dimensioned with detailed, multilevel hierarchies, the number of cells of aggregate data can be many times greater than the number of cells of detail data. Users often query some levels of data heavily and other levels very infrequently. They tend to focus on top-level aggregates and only occasionally drill to middle-level aggregates, although the middle-level aggregates comprise the largest proportion of aggregate data.
For this reason, the OLAP DML provides two ways to aggregate data:
As a data maintenance procedure using the AGGREGATE command.
At run-time when needed using the AGGREGATE function.
The DBA can choose whatever method seems appropriate: by level, individual member, member attribute, time range, data value, or other criteria. You can also combine these approaches and precalculate some values and calculate others at run time. In this case, frequently, you use the same aggmap with the AGGREGATE command and the AGGREGATE function. However, in some cases you might use different aggmaps.
One step that you can take to achieve overall good performance is to balance the amount of the data that you aggregate and store in an analytic workspace with the amount of data that you specify for calculation on the fly. A technique called "skip level" aggregation pre-aggregates every other level in a dimension hierarchy. Good performance is a matter of trade-offs. (For more information about skip-level aggregation, see "Skip-Level Aggregation".)
Using the AGGREGATE command, the DBA acquires detail data, calculates the aggregate values, and stores them in the analytic workspace for all users to share. This type of aggregate data is sometimes call precomputed or stored aggregation.
Precomputed aggregation supports the fastest querying time, but increases the size of the analytic workspace and therefore the size of the Oracle Database. The amount of precomputed data can also be limited by the amount of time available for the data task (often called a batch window).
Note:You must aggregate data in variables dimensioned using compressed composites using the AGGREGATE command. See "Aggregating Variables Dimensioned by Compressed Composites" for more information
For an example of aggregating data as a batch job, see Example 7-2, "Precalculating Data in a Batch Job". When an AGGREGATE command executes, Oracle OLAP always stores the results of the calculation directly in the variable in the same way it stores the results of an assignment statement. Additionally, if you issue another AGGREGATE command Oracle OLAP always recalculates the aggregation.
As the expression of a formula, the AGGREGATE function is executed whenever the formula is executed.
In either case, the aggregates are computed in response to the query. The results can be stored in a temporary cache for use throughout the session. When the session has write access to the analytic worksheet, the results can also be stored permanently. This type of aggregate data is referred to as on-the-fly or run-time aggregates. Calculating aggregate data at runtime slows querying time since the data must be calculated instead of just retrieved, but it does not require permanent storage for aggregate values.
There are a number of aggregation features that you can specify when you use the AGGREGATE function to aggregate data on the fly. For example, you can specify:
Whether or not Oracle OLAP stores the results of the calculation directly in the variable or caches the data in the session cache. For a discussion of how to specify storage or caching, see "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data ".
Whether or not previously cached or stored data is recalculated by specifying or omitting FORCECALC keyword on the AGGREGATE function.
Whether or not any
NA values that result from the aggregation that are stored in the variable will cause an $NATRIGGER property to execute on future requests for
NA variable values. For a discussion of caching the
NA values which precludes $NATRIGGER execution for
NA values that result from the execution of an aggregation, see "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER".
The definitions for most aggregations persist from one session to another. However, you might need to create session-only aggregates at runtime for forecasting or what-if analysis, or just because you want to view the data in an unforeseen way. Adding session-only aggregates is sometimes called creating custom aggregates. You can create non-persistent aggregated data without permanently changing the specification for the aggregation in the following ways:
Using a MAINTAIN ADD SESSION statement, define temporary dimension members and include an aggregation specification as part of the definition of these members. The aggregation specification can either be a model or an aggmap. For an example of using this method to create a temporary aggregation, see "Creating Calculated Dimension Members with Aggregated Values" .
Create a model that specifies the aggregation. Use an AGGMAP ADD statement to add the model to an aggmap at run time. At the end of a session, Oracle OLAP automatically removes any models that you have added to an aggmap in this manner. See AGGMAP ADD or REMOVE model for more information.