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 non-precomputed data in a variable that has an $AGGMAP property.
Additionally, the aggmap specified in the $AGGMAP property of a variable is the aggmap that Oracle OLAP uses when the variable is the target of an AGGREGATE command that does not include a USING phrase.
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'
Example 4-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 4-2 The $AGGMAP Property Effect on an AGGREGATE Command
Example 4-3, "Using the $AGGREGATE_FROM Property" illustrates how the AGGREGATE command shown in Example 9-13, "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