Aggregating Data, 5 of 12

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