Aggregating the Data in an Analytic Workspace

The DBMS_AWM package enables you to store aggregate data for level combinations of measures in a workspace cube.

Stored aggregates in an analytic workspace are similar to materialized views for relational data. However, a workspace cube is always presented as fully solved with embedded totals when queried by an application. If you do not preaggregate any of the workspace data, all the aggregate data is still available but it must be calculated on the fly.

Preaggregating some or all of your workspace data will improve query performance in most circumstances. For information on choosing an aggregation strategy, refer to the Oracle OLAP Application Developer's Guide

Note:

The aggregation process (AGGREGATE_AWCUBE) opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT.

The cube refresh process stores detail data in the workspace and sets up the structures to support dynamic aggregation. If you want to preaggregate some or all of your data, you must create an aggregation specification and run a separate aggregation procedure for the workspace cube.

Example 1-4 shows sample procedure calls for preaggregating the Costs and Quota measures of the analytic workspace cube AC2, which was created from XADEMO.ANALYTIC_CUBE.

The quarter totals (level 'L2' of TIME) for product groups (level 'L3' of PRODUCT), product divisions (level 'L2' of PRODUCT), and all channels (level 'STANDARD-2' of CHANNEL) are calculated and stored in the analytic workspace.

Example 1-4 Preaggregating Costs and Quota in an Analytic Workspace

execute dbms_awm.create_awcubeagg_spec
          ('AGG1', 'MYSCHEMA', 'MYAW', 'AC2');
execute dbms_awm.add_awcubeagg_spec_level
          ('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'PRODUCT', 'L3');
execute dbms_awm.add_awcubeagg_spec_level
          ('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'PRODUCT', 'L2');
execute dbms_awm.add_awcubeagg_spec_level
          ('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'CHANNEL', 'STANDARD_2');
execute dbms_awm.add_awcubeagg_spec_level
          ('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'TIME', 'L2');
execute dbms_awm.add_awcubeagg_spec_measure
          ('AGG1', 'XADEMOAW', 'UK', 'AC2', 'XXF_COSTS');
execute dbms_awm.add_awcubeagg_spec_measure
          ('AGG1', 'XADEMOAW', 'UK', 'AC2', 'XXF_QUOTA');
execute dbms_awm.aggregate_awcube('MYSCHEMA', 'MYAW', 'AC2', 'AGG1');

The following statements show the measures and the PRODUCT levels in the aggregation plan in the analytic workspace.

execute dbms_aw.execute  ('aw attach MYSCHEMA.MYAW ro');
execute dbms_aw.execute  ('fulldsc agg1');

DEFINE AGG1 DIMENSION TEXT
LD List of Measures which use this AggPlan
PROPERTY 'AW$CLASS' -'IMPLEMENTATION'
PROPERTY 'AW$CREATEDBY' -'AW$CREATE'
PROPERTY 'AW$LASTMODIFIED' -'.*
PROPERTY 'AW$LOGICAL_NAME' -'AGG1'
PROPERTY 'AW$PARENT_NAME' -'AC2'
PROPERTY 'AW$ROLE' -'AGGDEF'
PROPERTY 'AW$STATE' -'ACTIVE'

execute dbms_aw.execute('rpr agg1')

AGG1
--------------
XXF.COSTS
XXF.QUOTA

execute dbms_aw.execute('fulldsc agg1_product');

DEFINE AGG1_PRODUCT VALUESET PRODUCT_LEVELLIST
LD List of Levels for this AggPlan
PROPERTY 'AW$AGGOPERATOR' -'SUM'
PROPERTY 'AW$CLASS' -'IMPLEMENTATION'
PROPERTY 'AW$CREATEDBY' -'AW$CREATE'
PROPERTY 'AW$LASTMODIFIED' -'.*
PROPERTY 'AW$PARENT_CUBE' -'AC2'
PROPERTY 'AW$PARENT_DIM' -'PRODUCT'
PROPERTY 'AW$PARENT_NAME' -'AGG1'
PROPERTY 'AW$ROLE' -'AGGDEF_LEVELS'
PROPERTY 'AW$STATE' -'ACTIVE'

execute dbms_aw.execute('shw values(agg1_product)');

L3
L2

An aggregation method specifies the operation used to summarize the data by level. The default aggregation method is addition. For example, sales data is typically aggregated over time by adding the values for each time period.

The OLAP Catalog supports a set of aggregation methods, which may be included in the definition of a cube. These aggregation methods are listed in Table 1-10.

When a workspace cube is refreshed, the aggregation operators specified in the OLAP Catalog are converted to the corresponding operators supported by the OLAP DML RELATION command. These operators are incorporated in the aggregation map that controls dynamic aggregation for the cube.

To specify a different operator for your stored aggregates, you can use the SET_AWCUBEAGG_SPEC_AGGOP procedure. This procedure enables you to specify any of the operators supported by the OLAP DML RELATION command to preaggregate your data.

Note:

The DBMS_AWM package currently does not support weighted aggregation operators. For example, if the OLAP Catalog specifies a weighted sum or weighted average for aggregation along one of the cube's dimensions, it is converted to the scalar equivalent (sum or average) when the cube is refreshed in the analytic workspace. Weighted operators specified by SET_AWCUBEAGG_SPEC_AGGOP are similarly converted.

The OLAP Catalog and corresponding OLAP DML aggregation operators are described in Table 1-10.

Table 1-10 Aggregation Operators

OLAP Catalog	OLAP DML	DML Abbrv	Description
`SUM`	`SUM`	`SU`	Sum. Adds data values (default)
`SCALED SUM`	`SSUM`	`SS`	Converted to Sum.
`WEIGHTED SUM`	`WSUM`	`WS`	Converted to Sum.
`AVERAGE`	`AVERAGE`	`AV`	Average. Adds data values, then divides the sum by the number of data values that were added together.
`HIERARCHICAL AVERAGE`	`HAVERAGE`	`HA`	Hierarchical Average. Adds data values, then divides the sum by the number of the children in the dimension hierarchy.
`WEIGHTED AVERAGE`	`WAVERAGE`	`WA`	Converted to Average.
	`HWAVERAGE`	`HW`	Converted to Hierarchical Average.
`MAX`	`MAX`	`MA`	Maximum. The largest data value among the children of any parent data value.
`MIN`	`MIN`	`MI`	Minimum. The smallest data value among the children of any parent data value.
`FIRST`	`FIRST`	`FI`	First. The first non-NA data value.
	`HFIRST`	`HF`	Hierarchical First. The first data value that is specified by the hierarchy, even if that value is NA.
`LAST`	`LAST`	`LA`	Last. The last non-NA data value.
	`HLAST`	`HL`	Hierarchical Last. The last data value that is specified by the hierarchy, even if that value is NA.
`AND`	`AND`	`AN`	(Boolean variables only) If any child data value is FALSE, then the data value of its parent is FALSE. A parent is TRUE only when all of its children are TRUE.
`OR`	`OR`	`OR`	(Default for Boolean variables) If any child data value is TRUE, then the data value of its parent is TRUE. A parent is FALSE only when all of its children are FALSE.
`COUNT`		`NO`	Converted to `NOAGG`.
	`NOAGG`	`NO`	Do not aggregate any data for this dimension.