Aggregating Non-Hierarchical Data

Some dimensions, such as line items, do not have a hierarchical structure. Instead, individual line items are calculated, sometimes with complex formulas, from one or more other line items or workspace objects. Models are needed to solve the data over this type of a dimension.

To execute a model, you include a MODEL command within the aggmap. It has the following basic syntax:

MODEL modelname [PRECOMPUTE ALL|NA]

Where:

modelname is the name of an existing MODEL object that calculates values for one or more dimensions of the aggregation map.

PRECOMPUTE ALL indicates that the AGGREGATE command will execute the model as a data maintenance step. Any RELATION or MODEL commands that precede it in the aggregation map must also be specified as PRECOMPUTE ALL. However, any RELATION or MODEL commands that follow it in the aggregation map can either be specified as PRECOMPUTE ALL or PRECOMPUTE NA.

PRECOMPUTE NA indicates that the AGGREGATE function will execute the model at runtime. The following conditions must be met for runtime execution:

• All RELATION commands in the aggmap must appear before the MODEL command specified as PRECOMPUTE NA.
• Any additional MODEL commands that follow it must also be specified as PRECOMPUTE NA.
• The model cannot solve simultaneous equations or time series (such as LEAD and LAG functions).
• The model cannot reference an object that invokes the AGGREGATE function. For example, the model can contain an equation such as TAX=PROFIT*RATE where RATE is a variable or formula. However, RATE cannot require runtime aggregation.

  See Also: Chapter 8, "Working with Models"

Example 12-12 Solving a Model in an Aggregation

This example uses the budget variable:

DEFINE BUDGET VARIABLE DECIMAL <LINE TIME>
LD Budgeted $ Financial

The time dimension has two hierarchies (STANDARD and YTD) and a parent relation named time.parentrel as follows:

               -----TIME.PARENTREL------
               ----TIME.HIERARCHIES-----
TIME             STANDARD       YTD
-------------- ------------ ------------
LAST.YTD       NA           NA
CURRENT.YTD    NA           NA
JAN01          Q1.01        LAST.YTD
FEB01          Q1.01        LAST.YTD
MAR01          Q1.01        LAST.YTD
APR01          Q2.01        LAST.YTD
MAY01          Q2.01        LAST.YTD
JUN01          Q2.01        LAST.YTD
JUL01          Q3.01        LAST.YTD
AUG01          Q3.01        LAST.YTD
SEP01          Q3.01        LAST.YTD
OCT01          Q4.01        LAST.YTD
NOV01          Q4.01        LAST.YTD
DEC01          Q4.01        LAST.YTD
JAN02          Q1.02        CURRENT.YTD
FEB02          Q1.02        CURRENT.YTD
MAR02          Q1.02        CURRENT.YTD
APR02          Q2.02        CURRENT.YTD
MAY02          Q2.02        CURRENT.YTD
Q1.01          2001         NA
Q2.01          2001         NA
Q3.01          2001         NA
Q4.01          2001         NA
Q1.02          2002         NA
Q2.02          2002         NA
2001           NA           NA
2002           NA           NA

The relationships among line items are defined in the following model.

DEFINE INCOME.BUDGET MODEL
MODEL
dimension line time
opr.income = gross.margin - marketing
gross.margin = revenue - cogs
revenue = lag(revenue, 12, time) * 1.02
cogs = lag(cogs, 1, time) * 1.01
marketing = lag(opr.income, 1, time) * 0.20
END

The following aggregation map pre-aggregates all of the data. Note that all of the data must be pre-aggregated because the model includes both LAG functions and a simultaneous equation.

DEFINE BUDGET.AGGMAP1 AGGMAP
AGGMAP
MODEL income.budget
RELATION time.parentrel
END