4.2.1 Aggregating Data

To aggregate data using the OLAP DML, take the following steps:

1. 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.

   
   

2. When the aggregation involves aggregating data up a variable dimensioned by a composite, ensure that the composite has a BTREE index.

3. Issue a DEFINE AGGMAP statement to define the aggmap object as type AGGMAP.

4. Write the aggregation specification as described in AGGMAP.

5. 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.

6. When some or all of the data is to be aggregated at runtime:

   1. Compile the aggmap object as described in "Compiling Aggregation Specifications".

   2. Save the aggmap object using an UPDATE command followed by COMMIT.

   3. (Optional) Add a $NATRIGGER property to the variable to trigger the AGGREGATE function in response to a runtime request for data.

7. (Optional) Add one or more of the following properties to variables that will use the aggmap object:

   • $AGGMAP to specify that the aggmap is the default aggmap for the variable.

   • $AGGREGATE_FROM or $AGGREGATE_FROMVAR to specify the location of the detail data when the detail data is not in the target variable.

8. For data that is to be precalculated:

   1. (Optional) Set the POUTFILEUNIT option so that you can monitor the progress of the aggregation.

   2. Use the AGGREGATE command with the aggmap to precalculate the data and store it in the database.

For brief descriptions of all of the OLAP DML statements that relate to aggregation, see "Aggregation Statements".

4.2.2 Executing the Aggregation

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".)

Aggregating Data as a Data Maintenance Procedure.

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).

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.

Aggregating Data at Run-Time When Needed.

You can use the AGGREGATE function in response to a runtime request for data. For example, an AGGREGATE function can be the expression of a $NATRIGGER property or a formula:

• As an expression of $NATRIGGER property, the AGGREGATE function is executed when a runtime requests data for NA or empty data cells.

• 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".

4.2.3 Creating Custom Aggregates

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.