| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
The AGGREGATE function calculates the data of a variable at runtime, in response to a user's request. The AGGREGATE function returns the requested data by retrieving stored values and calculating the remaining values.
Return Value
The same data type as the aggregated variable.
Syntax
AGGREGATE (var [USING aggmap] [FROM fromspec|FROMVAR textvar] -
[FORCECALC FORCEORDER] [COUNTVAR countvar])
Arguments
The name of the variable whose data will be calculated (if necessary) and returned. It is frequently numeric, but can also be BOOLEAN, TEXT, DATETIME, or DATE depending on the operator specified in the RELATION (for aggregation) statements in the aggmap specification.
This keyword indicates that the aggregation is performed using the specified aggmap. When you do not include this phrase, the function uses the default aggmap for the variable as previously specified using an AGGMAP statement or the $AGGMAP property.
The name of a previously-defined aggmap that specifies how the data will be 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 (for aggregation) statement that are listed 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 a $AGGREGATE_FORCECALC property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not need to include the FORCECALC keyword with the AGGREGATE function. |
Specifies that the calculation must be performed in the order in which the RELATION (for aggregation) statements are listed in the aggmap. Use this option when you have changed some of the values calculated by the AGGREGATE command. Otherwise, the optimization methods used by the AGGREGATE function may cause the modified values to be ignored. FORCEORDER will slow performance.
|
Note: You can also set a $AGGREGATE_FORCEORDER property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not need 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 one of the average operators is for a compressed composite.For more information on Aggcount variables, see "Aggcount Variables". |
The countvar variable must be an INTEGER variable with exactly the same dimensions in exactly 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.
Notes
Steps for Supporting Runtime Calculations
Follow these steps when combining pre-aggregation with runtime 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 Runtime Calculations").
UPDATE and COMMIT the analytic workspace.
Compiling the Aggmap
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 will be recompiled at runtime for each session in which the AGGREGATE function is used. Perform other calculations (such as calculating models) before you compile the aggmap.
You need to 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.
Runtime Changes to Data Values
When users are able to change data values at runtime, 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 runtime changes, since the stored aggregates cannot be altered to reflect changes made at runtime to the contributing values.
Using NA Values to Trigger Runtime 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)'
A statement such as REPORT SALES will now execute 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 that you want 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 will calculate the balance of the data.
Using Multiple Aggmaps
Whenever possible, you should only use one aggmap to rollup a variable. However, in some situations, a variable requires more than one aggmap to roll up the data in the desired manner. This can create problems when some of the 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.
You should 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, you should not use the AGGREGATE function with multiple aggmaps.
Examples
This section contains several examples of using the AGGREGATE function. For additional aggregation examples, see the examples in AGGMAP.
Example 7-9 Using the AGGREGATE Function as the Formula of an Expression
Example 7-7, "Capstone Aggregation" illustrates performing the final capstone aggregation using an AGGREGATE command. You could also perform the capstone aggregation at runtime 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 (for aggregation) 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 7-7, "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 since 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-10 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 will be 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-11 Using $NATRIGGER to Aggregate Data
When the AGGREGATE function is added to units in the $NATRIGGER property, a simple REPORT statement will display 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-12 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
...
