Aggregating Data, 5 of 12

About the RELATION Command

The RELATION command has the following basic syntax:

RELATION parent-rel [PRECOMPUTE (limit-phrase)] [OPERATOR opvar]

An aggregation map should have one RELATION command for each hierarchical dimension in the definition of the variable. To promote the best possible performance, list the RELATION commands in the same order as they appear in the variable definition. This order indicates the way the data is stored, from fastest varying dimension to slowest varying dimension as described in "How Variable Data Is Stored". When aggregating the data, it is much more efficient to aggregate the fastest varying dimension first and the slowest varying dimension last.

For example, if the sales variable is dimensioned by time and the prod.geog.chan composite like this:

<time prod.geog.chan <product, geography, channel>>

Then the first RELATION command should be for time, the second for product, the third for geography, and the fourth for channel.

Example 12-8 Aggregating with SUM or MAX

The following examples use the letter dimension, the letter.letter parent relation, and the units variable.

LETTER         LETTER.LETTER    UNITS
-------------- ------------- -------------
a              NA                       NA
aa             a                        NA
ab             a                        NA
aab            aa                       NA
aba            ab                       NA
abb            ab                       NA
aaaa           aa                        1
aaba           aab                       2
abaa           aba                       1
abbb           abb                       1
abba           abb                       1

The following illustration shows the relations defined by letter.letter.

Figure 12-1 Parent-Child Relationships in the LETTER Dimension

Text description of lettrdim.gif follows

Text description of the illustration lettrdim.gif

LETTER.AGGMAP uses SUM to calculate the value of aa.

DEFINE LETTER.AGGMAP AGGMAP
AGGMAP
RELATION letter.letter PRECOMPUTE ('aa')
END

When the data is aggregated, aa has a value of 3:

aa = (aab + aaaa) = (aaba + aaaa) = (2 + 1) = 3

Note that although aab is the parent of aaba and the child of aa, its value is not stored as the result of this calculation.

Specifying an Aggregation Method

The aggregation method for each dimension is specified in the RELATION command. The default aggregation method is SUM, which adds the values of the child cells and stores the total in the parent cell. However, there are other aggregation methods that you can use:

Sum (SUM)
Scaled Sum (SSUM)
Weighted Sum (WSUM)
Average (AVERAGE)
Hierarchical Average (HAVERAGE)
Weighted Average (WAVERAGE)
Hierarchical Weighted Average (HWAVERAGE)
Maximum (MAX)
Minimum (MIN)
First (FIRST)
Hierarchical First (HFIRST)
Last (LAST)
Hierarchical Last (HLAST)
And (AND)
Or (OR)

These aggregation methods are arguments to the RELATION command. For descriptions of these methods, refer to the RELATION command entry in Oracle9i OLAP DML Reference help. Do not confuse the RELATION aggregation methods with the DML aggregation functions.

Example 12-9 Specifying the Aggregation Method

The OPERATOR keyword in the following RELATION command changes the method of aggregation from the default SUM to MAX.

RELATION letter.letter PRECOMPUTE ('aa') OPERATOR MAX

When the data is aggregated with the modified aggregation map, aa has a value of 2, because 2 is the largest value contributing to aa, as shown in Figure 12-1, "Parent-Child Relationships in the LETTER Dimension".

Example 12-10 Using a Weighted Variable

Several aggregation methods use weighted variables: WSUM, WAVERAGE, and HWAVERAGE. You must first define a weighted variable, then specify it in the RELATION command using the ARGS WEIGHTBY argument.

The following aggregation map uses the weights defined in variable letter.weights to calculate the value of aa.

DEFINE LETTER.AGGMAP AGGMAP
AGGMAP
RELATION letter.letter PRECOMPUTE ('aa') OPERATOR WSUM -
     ARGS WEIGHTBY letter.weights
END

The output from this REPORT command shows the aggregation.

report down letter letter.weights units


LETTER          LETTER.LETTER  LETTER.WEIGHTS      UNITS
-------------- --------------- --------------- ---------------
a              NA                           NA              NA
aa             a                            NA               7
ab             a                            NA              NA
aab            aa                           NA              NA
aba            ab                           NA              NA
abb            ab                           NA              NA
aaaa           aa                            5               1
aaba           aab                          NA               2
abaa           aba                          NA               1
abbb           abb                          NA               1
abba           abb                          NA               1

The value of aa in the units variable is calculated in this way:

aa = ((5 * aaaa) + aab) = ((5*aaaa) + aaba) = (5*1) + 2 = 7

Selecting Data For Aggregation

The PRECOMPUTE clause limits the data that is aggregated by the AGGREGATE command. In its simplest form, the PRECOMPUTE clause is like a LIMIT dimension TO command. Notice that the default limit is on the dimension, which is not explicitly named in the RELATION command.

For example, this LIMIT command selects the AUDIODIV, VIDEODIV, and ACCDIV values of the product dimension:

limit product to 'audiodiv' 'videodiv' 'accdiv'

The equivalent RELATION command looks like this:

RELATION product.parentrel PRECOMPUTE ('AUDIODIV' 'VIDEODIV' 'ACCDIV')

Since these values are all at the same level of the product STANDARD hierarchy (L2), this LIMIT command yields the same results:

limit product to product.levelrel 'L2'

This is the equivalent RELATION command:

RELATION product.parentrel PRECOMPUTE (product.levelrel 'L2')

The TO clause may not always produce the results you want. To use the other selection clauses (such as KEEP, REMOVE, and COMPLEMENT), you must explicitly call the LIMIT function.

RELATION product.parentrel PRECOMPUTE (limit(product complement 'TOTALPROD'))

Example 12-11 Aggregation Map with PRECOMPUTE Clauses

This aggregation map uses PRECOMPUTE clauses to limit the data that is aggregated by the AGGREGATE command.

DEFINE GPCT.AGGMAP AGGMAP
LD Aggregation map for sales, units, quota, costs
AGGMAP
RELATION geography.parentrel PRECOMPUTE (geography.levelrel 'L3')
RELATION product.parentrel PRECOMPUTE (limit(product complement 'TOTALPROD'))
RELATION channel.parentrel
RELATION time.parentrel PRECOMPUTE (time ne '2001')
END

Caching Runtime Aggregates

The CACHE command in an aggregation map determines whether data that is calculated on the fly is available for the duration of a session. By default, the data must be recalculated each time it is queried. The user will experience faster querying time if the data is cached and simply retrieved for subsequent queries, however, maintaining a cache can have unwanted side-effects.

If users alter the data during their sessions (such as when running forecasts and what-if analysis), then data that was aggregated previously will not reflect the changes in the data. Having the data out of synchronization in this way means that users will view inaccurate data. Do not maintain a cache if users alter the data during their sessions.

If users have write access to the analytic workspace, then the runtime calculations will be saved along with other changes if a user issues UPDATE and COMMIT commands. This defeats the purpose of runtime aggregation, which is to save storage space.

If users can save their analytic workspaces, then create a cache using a CACHE SESSION command. If they cannot save their workspaces, then you can use either CACHE SESSION or CACHE STORE.

The effectiveness of a cache is tracked in the V$AW_CALC dynamic performance view. See the Oracle9i OLAP User's Guide for information about querying this view.