Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
 Home Book List Contents Index Master Index Feedback

Working with Models, 2 of 8

## Using Models to Calculate Data

A model is a set of interrelated equations that can assign results either to a variable or to a dimension value. For example, in a financial model, you can assign values to specific line items, such as `gross.margin` or `net.income`.

```gross.margin = revenue - cogs

```

If an `=` command assigns data to a dimension value or refers to a dimension value in its calculations, then it is called a dimension-based equation. A dimension-based equation does not refer to the dimension itself, but only to the values of the dimension. Therefore, if the model contains any dimension-based equations, then you must specify the name of each of these dimensions in a `DIMENSION` command at the beginning of the model.

If a model contains any dimension-based equations, then you must supply the name of a solution variable when you run the model. The solution variable is both a source of data and the assignment target of model equations. It holds the input data used in dimension-based equations, and the calculated results are stored in designated values of the solution variable. For example, when you run a financial model based on the `line` dimension, you might specify `actual` as the solution variable.

Dimension-based equations provide flexibility in financial modeling. Since you do not need to specify the modeling variable until you solve a model, you can run the same model with the `actual` variable, the `budget` variable, or any other variable that is dimensioned by `line`.

#### Example 8-1 Creating a Model

Suppose that you define a model, called `income.calc`, that will calculate line items in the income statement.

```define income.calc model
ld Calculate line items in income statement

```

After defining the model, you can use the `MODEL` command or the OLAP Worksheet editor to specify the contents of the model. A model can contain `DIMENSION` commands, = commands, and comments. All the `DIMENSION` commands must come before the first equation. For the current example, you can specify the lines shown in the following model.

```DEFINE INCOME.CALC MODEL
LD Calculate line items in income statement
MODEL
DIMENSION line
net.income = opr.income - taxes
opr.income = gross.margin - (marketing + selling + r.d)
gross.margin = revenue - cogs
END

```

When you write the equations in a model, you can place them in any order. When you compile the model, either with the `COMPILE` command or by running the model, the order in which the model equations are solved is determined. If the calculated results of one equation are used as input to another equation, then the equations are solved in the order in which they are needed.

To run the `income.calc` model and use `actual` as the solution variable, you execute the following command.

```income.calc actual

```

If the solution variable has dimensions other than the dimensions on which model equations are based, then a loop is performed automatically over the current status list of each of these "extra" dimensions. For example, `actual` is dimensioned by `month` and `division`, as well as by `line`. If `division` is limited to `ALL`, and `month` is limited to `OCT96` to `DEC96`, then the `income.calc` model is solved for the three months in the status for each of the divisions.

### How Dimension Values Are Treated in a Model

If a model contains an = command that assigns data to a dimension value, then the dimension is limited temporarily to that value, performs the calculation, and then restores the initial status of the dimension.

For example, a model might have the following commands.

```DIMENSION line
gross.margin = revenue - cogs

```

If you specify `actual` as the solution variable when you run the model, then the following code is constructed and executed.

```PUSH line
LIMIT line TO gross.margin
actual = actual(line revenue) - actual(line cogs)
POP line

```

This behind-the-scenes construction lets you perform complex calculations with simple model equations. For example, line item data might be stored in the `actual` variable, which is dimensioned by `line`. However, detail line item data might be stored in a variable named `detail.data`, with a dimension named `detail.line`.

If your analytic workspace contains a relation between `line` and `detail.line`, which specifies the line item to which each detail item pertains, then you might write model equations such as the following ones.

```revenue = total(detail.data line)
expenses = total(detail.data line)

```

The relation between `detail.line` and `line` is used automatically to aggregate the detail data into the appropriate line items. The code that is constructed when the model is run ensures that the appropriate total is assigned to each value of the `line` dimension. For example, while the equation for the `revenue` item is calculated, `line` is temporarily limited to `revenue`, and the `TOTAL` function returns the total of detail items for the `revenue` value of `line`.