$AGGMAP

The $AGGMAP property specifies that Oracle OLAP use the identified aggmap to automatically aggregate non-precomputed data to substitute for NA values that are in the dimensioned variable, but not in the session cache for the variable (if any). Consequently, you do not need to explicitly use the AGGREGATE function to aggregate nonprecomputed data in a variable that has an $AGGMAP property.

Additionally, the aggmap specified in a variable's $AGGMAP property, is the aggmap that Oracle OLAP uses when the variable is the target of an AGGREGATE command that does not include a USING phrase.

Syntax

You add or delete an $AGGMAP property to the most recently defined or considered object (see DEFINE and CONSIDER commands) using a PROPERTY statement:

To add the property, issue the following statement.

PROPERTY '$AGGMAP' agggmap-name
To delete the property, issue the following statement.

PROPERTY DELETE '$AGGMAP'

Arguments

aggmap-name: A TEXT expression that is the name of a previously defined aggmap object.

Examples

Example 5-1 Using $AGGMAP To Dynamically Aggregate Data

Assume that you have a hierarchical dimension named geog,a simple dimension named year, and the following variable named sales which is dimensioned by both and which has data only at the detail level.

Assume that you want to explicitly specify the value of 8000 for the sales cell for Connecticut in 2005. To do this you issue the following assignment statement and a report of sales shows the value.

sales (geog 'Connecticut' year '2005') = 8000
REPORT sales;
 
               -----------SALES-----------
               -----------YEAR------------
GEOG            2004   2005   2006   2007
-------------- ------ ------ ------ ------
Toronto         1,000  1,333  1,954  1,260
Norfolk         1,131  1,867  1,843  1,767
Montreal        1,571  1,754  1,316  1,905
Quebec City     1,914  1,728  1,386  1,847
Hartford        1,870  1,943  1,085  1,335
New Haven       1,684  1,330  1,458  1,402
Springfield     1,630  1,116  1,897  1,690
Boston          1,780  1,310  1,368  1,581
Ontario            NA     NA     NA     NA
Quebec             NA     NA     NA     NA
Connecticut        NA  8,000     NA     NA
Massachusetts      NA     NA     NA     NA
Canada             NA     NA     NA     NA
USA                NA     NA     NA     NA
All Geog           NA     NA     NA     NA

Now assume that you define an aggmap for sales. The aggmap has the following definition which specifies that only the upper-level data for Canada and the top level (All Geog) be aggregated by the AGGREGATE command.

DEFINE MYAGGMAP AGGMAP
AGGMAP
RELATION geogParentrel PRECOMPUTE ('Quebec' 'Ontario' 'Canada' 'All Geog')
END

Now assume you issue the following statements:

CONSIDER sales
PROPERTY '$AGGMAP' 'Myaggmap'

As a result of using the $AGGMAP property to make myaggmap as the default aggmap for sales, a simple REPORT statement for sales causes Oracle OLAP to aggregate all of the data for the USA. (Note that only those values that were not specified as PRECOMPUTE and that previously had NA values are calculated. The 8,000 value for Connecticut in 2005 that was specifically assigned is not recalculated.)

REPORT sales
 
               -----------SALES-----------
               -----------YEAR------------
GEOG            2004   2005   2006   2007
-------------- ------ ------ ------ ------
Toronto         1,000  1,333  1,954  1,260
Norfolk         1,131  1,867  1,843  1,767
Montreal        1,571  1,754  1,316  1,905
Quebec City     1,914  1,728  1,386  1,847
Hartford        1,870  1,943  1,085  1,335
New Haven       1,684  1,330  1,458  1,402
Springfield     1,630  1,116  1,897  1,690
Boston          1,780  1,310  1,368  1,581
Ontario            NA     NA     NA     NA
Quebec             NA     NA     NA     NA
Connecticut     3,554  8,000  2,543  2,737
Massachusetts   3,410  2,426  3,265  3,271
Canada             NA     NA     NA     NA
USA             6,964  5,699  5,808  6,008
All Geog           NA     NA     NA     NA

Once you aggregate sales using the AGGREGATE command, Oracle OLAP aggregates values for all of the PRECOMPUTE cells in sales.

REPORT sales
 
               -----------SALES-----------
               -----------YEAR------------
GEOG            2004   2005   2006   2007
-------------- ------ ------ ------ ------
Toronto         1,000  1,333  1,954  1,260
Norfolk         1,131  1,867  1,843  1,767
Montreal        1,571  1,754  1,316  1,905
Quebec City     1,914  1,728  1,386  1,847
Hartford        1,870  1,943  1,085  1,335
New Haven       1,684  1,330  1,458  1,402
Springfield     1,630  1,116  1,897  1,690
Boston          1,780  1,310  1,368  1,581
Ontario         2,131  3,200  3,797  3,027
Quebec          3,485  3,482  2,702  3,752
Connecticut     3,554  8,000  2,543  2,737
Massachusetts   3,410  2,426  3,265  3,271
Canada          5,616  6,682  6,499  6,779
USA             6,964  5,699  5,808  6,008
All Geog       12,580 12,381 12,307 12,787

Example 5-2 The $AGGMAP Property Effect on an AGGREGATE Command

Example 5-3, "Using the $AGGREGATE_FROM Property" illustrates how the AGGREGATE command shown in Example 9-12, "Using a CACHE Statement in an Aggregation Specification" can be simplified to the following statement.

AGGREGATE sales_by_revenue USING revenue_aggmap

You can further simplify the AGGREGATE command if you place an $AGGMAP property on the sales_by_revenue variable. To define an $AGGMAP property on the sales_by_revenue variable, issue the following statements.

CONSIDER sales_by_revenue
PROPERTY '$AGGMAP' 'revenue_aggmap'

Now you can aggregate the data by issuing the following AGGREGATE command that does not include a USING clause.

AGGREGATE sales_by_revenue