|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Aggregating Data, 12 of 12
You will need to use multiple aggregation maps for a single variable when you use alternative ways to pre-aggregate data (in addition to the
AGGREGATE command) over one or more dimensions. These alternative ways to aggregate data can include:
For example, suppose the sales variable is dimensioned by geography, product, channel, and time. You If you aggregate some of a variable's data with
AGGREGATE, and you aggregate other data for that same variable with a forecast or DML program, then you need to take extra steps to make sure all of the aggregated data will be correct.
When the aggregation map is compiled for use by the
If you cannot answer "yes" to this question with confidence, you should not use the
Ideally, you will use the same aggregation map with both the command and the function to aggregate data for the same variable or group of variables. However, it may be necessary to use two or more aggregation map with the command and a different aggregation map with the function to assure that the results will be correct.
The reason for using multiple aggregation maps is that each one performs a different task, and thus produces a different status list.
AGGREGATE command and the
AGGREGATE function use the same aggregation map, then there is no problem; they will be using the same status list, because only one status list exists.
The problem occurs when you use more than one aggregation map with the
AGGREGATE command for the same variable (or group of variables). Each one produces a different status list, and none of them alone may correctly identify the current status for the
If you use more than one aggregation map to pre-calculate data, then you must:
AGGREGATEfunction, which will be used for user queries.
If you use a forecast, you must make sure that all of the input data that is required by that forecast has been pre-calculated. Otherwise, the forecast will use incorrect or nonexistent data.
For example, suppose your forecast requires that all line items are aggregated. Using a
budget variable that is dimensioned by
and division, a typical approach would be to perform a complete aggregation of the
line dimension, forecast the
time dimension, and then aggregate the remaining dimension,
division. Define the first aggregation map, named
forecast.agg1, which aggregates the data needed by the forecast. It contains the following command:
Define the second aggregation map, named
forecast.agg2, which aggregates the data generated using the first aggregation map and the forecast. It contains the following command:
Define the third aggregation map, named
forecast.agg3, which contains the contents of the first two aggregation maps:
If your forecast is in a program named
fore.prg, then you would use these commands to aggregate the data:
AGGREGATE budget USING forecast.agg1 "Aggregate over LINE CALL fore.prg "Forecast over TIME AGGREGATE budget USING forecast.agg2 "Aggregate over DIVISION "Compile the limit map for LINE and DIVISION COMPILE forecast.agg3 "Use the combined aggregation map for the AGGREGATE function CONSIDER budget PROPERTY 'NATRIGGER' 'AGGREGATE(budget USING forecast.agg3)'