AGGREGATE function

The AGGREGATE function calculates the data in the variable that is not specified as PRECOMPUTE in the specified aggmap. The aggregation is limited to those values that are currently in status.

See also:

For information about specifying precompute data, see PRECOMPUTE statement and the PRECOMPUTE clause of the RELATION (for aggregation).

Note:

When the variable you want to aggregate has an $AGGMAP property, you do not need to explicitly use the AGGREGATE function to aggregate nonprecomted data in that variable.

Return Value

The same data type as the aggregated variable.

Syntax

AGGREGATE (var [USING aggmap] [FROM fromspec|FROMVAR textvar] -

[FORCECALC FORCEORDER] [COUNTVAR countvar])

Arguments

var

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.

USING

This keyword indicates that the aggregation is performed using the specified aggmap.

aggmap

The name of a previously-defined aggmap that specifies how the data will be aggregated. For information about aggmaps, see DEFINE AGGMAP

FROM

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".

fromspec

An arbitrarily dimensioned variable, formula, or relation from which the detail data for the aggregation is obtained.

FROMVAR

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".

textvar

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.

FORCECALC

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.

FORCEORDER

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.

COUNTVAR countvar

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 the aggmap is compiled for use by the AGGREGATE function, does the status that results from each PRECOMPUTE clause accurately define the nodes within that dimension at which data has been pre-computed?

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 9-9 Using the AGGREGATE Function as the Formula of an Expression

Example 9-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 9-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 9-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 9-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 9-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
...