Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
The $AGGMAP property specifies that Oracle OLAP use the identified aggmap to automatically aggregate non-precomuted 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 nonprecomted 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) 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
A TEXT
expression that is the name of a previously defined aggmap object.
Examples
Example 8-1 Usaing $AGGMAP To Dynamically Aggregate Data
Assume that you have ta 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 8-2 The $AGGMAP Property Effect on an AGGREGATE Command
Example 8-3, "Using the $AGGREGATE_FROM Property" illustrates how the AGGREGATE command shown in Example 8-28, "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