OLAP DML Aggregation Objects

What is an OLAP DML Aggregation?

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 9-17, "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 9-16, "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 9-14, "Aggregating into a Different Variable" and Example 9-31, "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".

Aggregating Data Using the OLAP DML

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 the $AGGMAP property, 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".

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

3. Write the aggregation specification as described in AGGMAP.

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

5. When some or all of the data is to be aggregated using the AGGREGATE function:

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

   2. Add the triggering property, object, or event. For example, add a formula that has the AGGREGATE function as its expression and add $NATRIGGER property to the variable to trigger the execution of that formula in response to a runtime request for data.

6. When you want the aggmap object to be a permanent part of the analytic workspace, save the aggmap object using an UPDATE statement followed by COMMIT.

7. For data that is to be calculated using the AGGREGATE command:

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

   2. Use the AGGREGATE command, followed by UPDATE and COMMIT to precalculate the data and store it in the analytic workspace.

Compiling Aggregation Specifications

Compiling the aggmap object is important for aggregation performed at run-time using the AGGREGATE function. Unless the compiled version of the aggmap has been saved, the aggmap is recompiled by each session that uses it.

There are two ways you can compile an aggmap objects:

• Issue a COMPILE statement.

  A COMPILE statement is the only way to compile an aggmap object that will be used by an AGGREGATE function. Explicitly compiling an aggmap is also useful for finding syntax errors in the aggmap before attempting to use it to generate data. The following statement compiles the sales.agg aggmap.

  COMPILE gpct.aggmap
  
  

• When you aggregate the data using an AGGREGATE command, include the FUNCDATA phrase in the statement.

  When you use the FUNCDATA phrase in an AGGREGATE command, Oracle OLAP compiles the aggmap before it aggregates the data. For example, this statement compiles and precalculates the aggregate data.

  AGGREGATE sales USING gpct.aggmap FUNCDATA
  

  Important:

  When some of the data is calculated on the fly, then you must compile and save the aggmap after executing the AGGREGATE command.

Executing the Aggregation

The OLAP DML provides two ways to aggregate data:

• As a data maintenance procedure using the AGGREGATE command. If you want to use this method of aggregating data, then, within an aggregation specification, identify data that you want to aggregate in this manner using the PRECOMPUTE statement or PRECOMPUTE clause of the RELATION statement.

• At run-time when needed using the AGGREGATE function or adding an $AGGMAP property to the variable.

You 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. You can use a PRECOMPUTE statement or clause within your aggregation specification to ask Oracle OLAP to use special functionality called the Aggregate Advisor to automatically determine what values to aggregate as a data maintenance procedure using the AGGREGATE command, or to explicitly identify the values yourself.

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 Example 10-42, "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.