About Aggregating Detail Data

Business analysis applications typically use hierarchical dimensions for their data. In Oracle OLAP, all members of a hierarchy, regardless of their level, are stored in a single dimension. A self-relation and a parent relation identify the parent-child relationships among the members. Other, nonhierarchical dimension (such as a line item dimension) may require a model to calculate the values.

Data at the detail level is typically acquired from another source (such as a transactional database or flat files), but the aggregate data must be calculated. These calculations can be done in two distinct ways:

• As a data maintenance procedure. 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 aggregates. It supports the fastest querying time, but increases the size of the analytic workspace and therefore the size of the relational database. The amount of precomputed data may also be limited by the amount of time available for the data task (often called a batch window).
• At run-time when needed. The cells for the aggregate values are NA (that is, they are empty) until a query requests the aggregate values. The aggregates are then computed in response to the query. The results can be stored in a temporary cache for use throughout the session. If 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. It slows querying time since the data must be calculated instead of just retrieved, but it does not require permanent storage for aggregate values.

Oracle OLAP supports both types of aggregation, and provides a mechanism for precomputing some values and calculating others at run-time within a single data variable.

Functionality Available with AGGREGATE

The OLAP DML supports a variety of aggregation methods including first, last, average, weighted average, and sum. In a multidimensional variable, the aggregation method can vary by dimension.

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. In contrast, users typically 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 an aggregation method that allows some of the data to be aggregated and stored, while other data is aggregated at runtime. The DBA can choose whatever method seems appropriate: by level, individual member, member attribute, time range, data value, or other criteria. A technique called "skip level" aggregation pre-aggregates every other level in a dimension hierarchy. It is described in "Calculating Data Using the Skip-Level Approach".

Table 12-1 lists commands that support aggregation in the OLAP DML.

Table 12-1 Commands That Support Aggregation

Command	Description
AGGREGATE command	Calculates data for permanent storage in the analytic workspace.
AGGREGATE function	Calculates data on-the-fly in response to a query.
AGGMAP command	Adds contents to an aggmap object that identify which aggregates are calculated by the AGGREGATE command and which ones are calculated by the AGGREGATE function. It also identifies whether the run-time aggregates are cached for use throughout the session. This decision has implications for whether run-time changes to the detail values are reflected in the aggregate values.
AGGMAPINFO command	Returns information about the contents of an aggregation map object, such as whether it contains commands for aggregation or allocation.
CLEAR command	Clears data values in the aggregate cache.
MULTIPATHHIER option	Controls whether detail data can be aggregated over multiple paths.
POUTFILEUNIT option	Identifies a location that receives information on the progress of an AGGREGATE command.
SESSCACHE option	Controls whether an aggregate cache persists throughout a session.
VARCACHE option	Controls how on-the-fly aggregates are stored.

Process Overview: Aggregation

These are the basic steps you need to follow to generate and manage aggregate data:

1. Perform the initial analysis of your data, as described in "Preliminary Steps Prior to Aggregation", to make sure that it is set up properly.
2. Create an aggregation map that identifies which data will be precalculated and which data will be calculated as needed. Identify variables that are dimensioned identically, because they can share an aggregation map.
3. Set the POUTFILEUNIT option so that you can monitor the progress of the aggregation.
4. Use the AGGREGATE command with the aggregation map to precalculate the data and store it in the database.
5. If the aggregation map specifies run-time calculations, then:
   1. Compile the aggregation map.
   2. Add a property to the variable that will trigger the AGGREGATE function in response to a runtime request for data.

These steps are described in detail in this chapter.