|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Aggregating Data, 5 of 12
RELATION command has the following basic syntax:
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:
Then the first
RELATION command should be for
time, the second for
product, the third for
geography, and the fourth for
The following examples use the
letter dimension, the
letter.letter parent relation, and the
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
SUM to calculate the value of
When the data is aggregated,
aa has a value of
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.
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:
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.
OPERATOR keyword in the following
RELATION command changes the method of aggregation from the default
When the data is aggregated with the modified aggregation map,
aa has a value of
2 is the largest value contributing to
aa, as shown in Figure 12-1, "Parent-Child Relationships in the LETTER Dimension".
Several aggregation methods use weighted variables:
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
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:
PRECOMPUTE clause limits the data that is aggregated by the
AGGREGATE command. In its simplest form, the
PRECOMPUTE clause is like a
TO command. Notice that the default limit is on the dimension, which is not explicitly named in the
For example, this
LIMIT command selects the
ACCDIV values of the
RELATION command looks like this:
Since these values are all at the same level of the product
STANDARD hierarchy (
LIMIT command yields the same results:
This is the equivalent
RELATION product.parentrel PRECOMPUTE (product.levelrel 'L2')
TO clause may not always produce the results you want. To use the other selection clauses (such as
COMPLEMENT), you must explicitly call the
This aggregation map uses
PRECOMPUTE clauses to limit the data that is aggregated by the
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
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
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
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.