Combining AGGREGATE with Forecasts and Programs

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:

• A forecast
• An OLAP DML program

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.

Important: You should not use the AGGREGATE function with multiple aggregation maps unless you feel comfortable answering the following question: When the aggregation map 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? If you cannot answer "yes" to this question with confidence, you should not use the AGGREGATE function with multiple aggregation maps.

When to Use Multiple Aggregation Maps

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.

Problem: Different Aggregation Maps Generate Different Status Lists

The reason for using multiple aggregation maps is that each one performs a different task, and thus produces a different status list.

If the 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 AGGREGATE function.

Solution: Create a Separate AGGMAP for the AGGREGATE Function

If you use more than one aggregation map to pre-calculate data, then you must:

1. Create another aggregation map for the AGGREGATE function, which will be used for user queries.
2. Make sure that the contents of the aggregation map for user queries combines the contents of the aggregation maps that you use to pre-calculate data. Refer to Example 12-17, "Using Multiple Aggregation Maps" for an example of how to do this.

Example 12-17 Using Multiple Aggregation Maps

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 time, line, 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:

RELATION line.parentrel

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:

RELATION division.parentrel PRECOMPUTE ('L3')

Define the third aggregation map, named forecast.agg3, which contains the contents of the first two aggregation maps:

RELATION line.parentrel
RELATION division.parentrel PRECOMPUTE ('L3')

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)'