|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Working with Models, 2 of 8
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
= 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
Suppose that you define a model, called
income.calc, that will calculate line items in the 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.
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
division, as well as by
division is limited to
month is limited to
DEC96, then the
income.calc model is solved for the three months in the status for each of the divisions.
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.
If you specify
actual as the solution variable when you run the model, then the following code is constructed and executed.
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
If your analytic workspace contains a relation between
detail.line, which specifies the line item to which each detail item pertains, then you might write model equations such as the following ones.
The relation between
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