The AGGREGATE function calculates the data in the variable that is not specified as PRECOMPUTE in the specified aggmap. (For information about specifying precompute data, see PRECOMPUTE statement and the PRECOMPUTE clause of the RELATION (for aggregation) statement.) The aggregation is limited to those values that are currently in status.
See also:
AGGREGATE commandNote:
When the variable you want to aggregate has an $AGGMAP property, you do not have to use the AGGREGATE function to aggregate the data that has not been precomputed.AGGREGATE (var ... [USING aggmap] -
[FROM fromspec|FROMVAR textvar] [FORCECALC FORCEORDER] [COUNTVAR countvar])
The name of the variable whose data is calculated (if necessary) and returned.
This keyword indicates that the aggregation is performed using the specified aggmap.
The name of a previously-defined aggmap that specifies how the data is aggregated. For information about aggmaps, see DEFINE AGGMAP.
This keyword indicates that the detail data is obtained from a different object. A FROM clause is only one way in which you can specify the variable from which detail data should be obtained when performing aggregation. See "Ways of Specifying Where to Obtain Detail Data for Aggregation".
An arbitrarily dimensioned variable, formula, or relation from which the detail data for the aggregation is obtained.
This keyword indicates that the detail data is obtained from different objects to perform a capstone aggregation. A FROMVAR clause is only one way in which you can specify the variable from which detail data should be obtained when performing aggregation. See "Ways of Specifying Where to Obtain Detail Data for Aggregation".
An arbitrarily dimensioned variable used to resolve any leaf nodes. Specify NA to indicate that a node does not need detail data to calculate the value.
Specifies that any value that is not specified in a PRECOMPUTE clause of a RELATION statement that is in the aggmap should be recalculated, even when there is a value stored in the desired cell. Use the FORCECALC keyword when you want users to be able to change detail data cells and see the changed values reflected in dynamically-computed aggregate cells.
Note:
You can also set an $AGGREGATE_FORCECALC property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not have to include the FORCECALC keyword with the AGGREGATE function.Specifies that the calculation must be performed in the order in which the RELATION statements are listed in the aggmap. Use this option when you have changed some values calculated by the AGGREGATE command. Otherwise, the optimization methods used by the AGGREGATE function may cause the modified values to be ignored. FORCEORDER slows performance.
Note:
You can also set an $AGGREGATE_FORCEORDER property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not have to include the FORCEORDER keyword with the AGGREGATE function.Indicates that Oracle OLAP should use the user-defined variable specified by countvar to store the non-NA counts of the number of leaf nodes that contributed to aggregate values calculated for RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, HWAVERAGE, or WAVERAGE operator.
Note:
Typically, you do not use a user-defined Countvar variable to store the counts for average aggregations. Instead, you use an Oracle OLAP-created Aggcount variable. You must use an Aggcount variable when the aggregation specification includes a RELATION (for aggregation) statement with an average operator that is for a compressed composite.For more information on Aggcount variables, see "Aggcount Variables".
The countvar variable must be an INTEGER variable with the same dimensions in the same order as the dimensions of the variable specified by var. When you aggregate several variables together, you must define an INTEGER variable for each one to record the results.
Steps for Supporting Run-Time Calculations
Follow these steps when combining pre-aggregation with run-time aggregation:
Create an aggmap that limits the amount of data to be precalculated.
Execute the AGGREGATE command with the FUNCDATA argument.
When you have made any changes after executing the AGGREGATE command (see "Compiling the Aggmap"), recompile the aggmap with a COMPILE statement.
Add an $AGGREGATE_FROM property to the data variables (see "Using NA Values to Trigger Run-Time Calculations").
UPDATE and COMMIT the analytic workspace.
Be sure to compile the aggmap at the time you load data, either with an explicit COMPILE statement or with the FUNCDATA argument to the AGGREGATE command. Otherwise, the aggmap is recompiled at run time for each session in which the AGGREGATE function is used. Perform other calculations (such as calculating models) before you compile the aggmap.
You must recompile the aggmap after maintaining any of the dimensions in the aggmap definition or any of the relations that are included in the text of the aggmap.
Run-Time Changes to Data Values
When users are able to change data values at run time, then the data may get out of synchronization. You can prevent this problem in the following ways:
Use an ALLOCATE statement to distribute the data in a new aggregate to the contributing values lower in the hierarchy.
Do not precalculate the data that is subject to run-time changes because the stored aggregates cannot be altered to reflect changes made at run time to the contributing values.
Using NA Values to Trigger Run-Time Calculations
By adding an $NATRIGGER property to a variable, you can implicitly call the AGGREGATE function each time the data is queried. The following statements cause sales data to be aggregated using the sales.aggmap aggmap.
CONSIDER sales PROPERTY '$NATRIGGER' 'AGGREGATE(sales USING sales.aggmap)'
From now on, a statement such as REPORT SALES executes the AGGREGATE function, so that computed values are returned instead of NAs.
Using the AGGREGATE Function after Partial Rollups
When your batch window is not sufficiently long to preaggregate all of the data to generate, you can perform the aggregation in stages on consecutive days and use the AGGREGATE function to calculate the balance. For each stage, you must do the following:
Change the PRECOMPUTE phrase of the RELATION statement in the aggmap so that new data is aggregated.
Execute the AGGREGATE command with the FUNCDATA keyword.
Verify that the $NATRIGGER property is set on the variables so that the AGGREGATE function calculates the balance of the data.
Whenever possible, use only one aggmap to rollup a variable. However, in some situations, a variable requires multiple aggmaps to roll up the data in the desired manner. When a variable requires multiple aggmaps to rollup data problems are created when some data is calculated on the fly, because the metadata retained for the AGGREGATE function corresponds to the last aggmap. The AGGREGATE function needs metadata that is the union of all of the aggmaps used by the AGGREGATE command. The solution is to create an additional aggmap for use by the AGGREGATE function that correctly identifies the NA values. Be sure to compile this aggmap.
Do not use the AGGREGATE function with multiple aggmaps unless you feel comfortable answering the following question:
When you cannot answer "yes" to this question with confidence, do not use the AGGREGATE function with multiple aggmaps.
This section contains several examples of using the AGGREGATE function. For additional aggregation examples, see the examples for the AGGMAP command.
Example 7-5 Using the AGGREGATE Function as the Formula of an Expression
Example 9-32, "Capstone Aggregation" illustrates performing the final capstone aggregation using an AGGREGATE command. You could also perform the capstone aggregation at run time as the expression of a formula.
Assume that your analytic workspace contains the following object definitions.
DEFINE GEOG.D DIMENSION TEXT DEFINE GEOG.PARENTREL RELATION GEOG.D <GEOG.D> DEFINE TIME.D DIMENSION TEXT DEFINE TIME.PARENTREL RELATION TIME.D <TIME.D> DEFINE SALES_JAN76 VARIABLE INTEGER <GEOG.D> DEFINE SALES_FEB76 VARIABLE INTEGER <GEOG.D> DEFINE SALES_MAR76 VARIABLE INTEGER <GEOG.D> DEFINE SALES_CAPSTONE76 VARIABLE INTEGER <GEOG.D TIME.D> DEFINE CAPSTONE_SOURCE VARIABLE TEXT <TIME.D>
Now you create two aggmap objects with the following definitions. Note that in this case the capstone_aggmap consists of a RELATION statement with a PRECOMPUTE NA clause.
DEFINE LEAF_AGGMAP AGGMAP AGGMAP RELATION geog.parentrel OPERATOR SUM END DEFINE CAPSTONE_AGGMAP AGGMAP AGGMAP RELATION time.parentrel OPERATOR SUM PRECOMPUTE (NA) END
In Example 9-32, "Capstone Aggregation", the final capstone aggregation is performed using an AGGREGATE command. In this example, the capstone aggregation is defined as a formula named f_sales_capstone76 that has an AGGREGATE function as the expression of the formula.
DEFINE F_SALES_CAPSTONE76 FORMULA INTEGER <GEOG.D TIME.D> EQ AGGREGATE ( sales_capstone76 USING capstone_aggmap fromvar capstone_source)
When you report on the unaggregated variables and formulas in your analytic workspace, you see the following results.
GEOG.D SALES_JAN76 SALES_FEB76 SALES_MAR76
-------------- -------------- -------------- --------------
Boston 1,000 2,000 3,000
Medford 2,000 4,000 6,000
San Diego 3,000 6,000 9,000
Sunnydale 4,000 8,000 12,000
Massachusetts NA NA NA
California NA NA NA
United States NA NA NA
--------------------F_SALES_CAPSTONE76---------------------
--------------------------TIME.D---------------------------
GEOG.D Jan76 Feb76 Mar76 76Q1
-------------- -------------- -------------- -------------- --------------
Boston 1,000 2,000 3,000 6,000
Medford 2,000 4,000 6,000 12,000
San Diego 3,000 6,000 9,000 18,000
Sunnydale 4,000 8,000 12,000 24,000
Massachusetts NA NA NA NA
California NA NA NA NA
United States NA NA NA NA
---------------------SALES_CAPSTONE76----------------------
--------------------------TIME.D---------------------------
GEOG.D Jan76 Feb76 Mar76 76Q1
-------------- -------------- -------------- -------------- --------------
Boston 1,000 2,000 3,000 NA
Medford 2,000 4,000 6,000 NA
San Diego 3,000 6,000 9,000 NA
Sunnydale 4,000 8,000 12,000 NA
Massachusetts NA NA NA NA
California NA NA NA NA
United States NA NA NA NA
Now you aggregate the leaf variables using the following AGGREGATE statement.
AGGREGATE sales_jan76 sales_feb76 sales_mar76 USING leaf_aggmap
A report of the leaf variables shows that they are aggregated.
GEOG.D SALES_JAN76 SALES_FEB76 SALES_MAR76 -------------- -------------- -------------- -------------- Boston 1,000 2,000 3,000 Medford 2,000 4,000 6,000 San Diego 3,000 6,000 9,000 Sunnydale 4,000 8,000 12,000 Massachusetts 3,000 6,000 9,000 California 7,000 14,000 21,000 United States 10,000 20,000 30,000
A report of the f_sales_capstone76 formula shows the aggregated values for 76Q1.
--------------------F_SALES_CAPSTONE76---------------------
--------------------------TIME.D---------------------------
GEOG.D Jan76 Feb76 Mar76 76Q1
-------------- -------------- -------------- -------------- --------------
Boston 1,000 2,000 3,000 6,000
Medford 2,000 4,000 6,000 12,000
San Diego 3,000 6,000 9,000 18,000
Sunnydale 4,000 8,000 12,000 24,000
Massachusetts 3,000 6,000 9,000 18,000
California 7,000 14,000 21,000 42,000
United States 10,000 20,000 30,000 60,000
While a report of the sales_capstone76 variable does not show the aggregated values for 76Q1 because they are not stored in the variable.
---------------------SALES_CAPSTONE76----------------------
--------------------------TIME.D---------------------------
GEOG.D Jan76 Feb76 Mar76 76Q1
-------------- -------------- -------------- -------------- --------------
Boston 1,000 2,000 3,000 NA
Medford 2,000 4,000 6,000 NA
San Diego 3,000 6,000 9,000 NA
Sunnydale 4,000 8,000 12,000 NA
Massachusetts 3,000 6,000 9,000 NA
California 7,000 14,000 21,000 NA
United States 10,000 20,000 30,000 NA
Example 7-6 Aggregating Data on the Fly for a Report
The units variable is aggregated entirely on the fly using the tp.agg aggmap.
This is the object definitions for the variable units.
DEFINE units VARIABLE INTEGER <time product>
The parent relation for time contains these values.
---TIME.PARENTREL----
--TIME.HIERARCHIES---
TIME STANDARD YTD
---------- ---------- ----------
Jan01 Q1.01 Last.Ytd
Feb01 Q1.01 Last.Ytd
Mar01 Q1.01 Last.Ytd
Q1.01 2001 NA
The parent relation for the product dimension contains these values.
PRODUCT.PA
PRODUCT RENTREL
---------- ----------
Food Na
Snacks Food
Drinks Food
Popcorn Snacks
Cookies Snacks
Cakes Snacks
Soda Drinks
Juice Drinks
In the units variable, data is stored only at the lowest level of each dimension hierarchy.
-------------------UNITS------------------- -------------------TIME-------------------- PRODUCT Jan01 Feb01 Mar01 Q1.01 ----------- ---------- ---------- ---------- ---------- Food NA NA NA NA Snacks NA NA NA NA Drinks NA NA NA NA Popcorn 2 2 4 NA Cookies 3 6 3 NA Cakes 4 4 2 NA Soda 7 3 9 NA Juice 1 3 2 NA
The aggmap specifies that all data is calculated on the fly.
DEFINE tp.agg AGGMAP LD <time product> Aggmap AGGMAP RELATION time.parentrel PRECOMPUTE (NA) RELATION product.parentrel PRECOMPUTE (NA) END
The following REPORT statement uses the AGGREGATE function to calculate the data.
REPORT aggregate(units USING tp.agg) -------AGGREGATE(UNITS USING TP.AGG)------- -------------------TIME-------------------- PRODUCT Jan01 Feb01 Mar01 Q1.01 ----------- ---------- ---------- ---------- ---------- Food 17 18 20 55 Snacks 9 12 9 30 Drinks 8 6 11 25 Popcorn 2 2 4 8 Cookies 3 6 3 12 Cakes 4 4 2 10 Soda 7 3 9 19 Juice 1 3 2 6
Example 7-7 Using $NATRIGGER to Aggregate Data
When the AGGREGATE function is added to units in the $NATRIGGER property, a simple REPORT statement displays aggregated results.
CONSIDER units PROPERTY '$NATRIGGER' 'AGGREGATE(units USING tp.agg)' REPORT units -------------------UNITS------------------- -------------------TIME-------------------- PRODUCT Jan01 Feb01 Mar01 Q1.01 ----------- ---------- ---------- ---------- ---------- Food 17 18 20 55 Snacks 9 12 9 30
Example 7-8 Calculating all but one Value on the Fly
The AGGREGATE function calculates the complement of the data specified in the PRECOMPUTE clause of the RELATION statement. It returns those values that are currently in status.
For example, when you are using an aggmap that contains this RELATION statement.
RELATION letter.letter PRECOMPUTE ('AA')
Then the AGGREGATE function calculates all aggregations except AA, as shown here.
REPORT AGGREGATE(units USING letter.aggmap)
AGGREGATE(UNITS
LETTER USING LETTER.AGGMAP)
-------------- --------------------
A 3
AA NA
AB 3
AAB 2
ABA 1
ABB 2
AAAA 1
AABA 2
ABAA 1
ABBB 1
ABBA 1
...