MODEL

The MODEL command enters a completely new specification into a new or existing model object. When the model already has a specification, Oracle OLAP overwrites it. In order to use MODEL to assign an model specification to a model object, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

An alternative to a MODEL statement is an EDIT MODEL statement, which is available only in OLAP Worksheet. An EDIT MODEL statement opens an Edit window in which you can add, delete, or change the specification for a model object.

Adding a specification to a model object is just one step in modeling data which is discussed in Chapter 6, "Models".

Syntax

MODEL specification

Arguments

specification

A multiline text expression that contains one or more of the following OLAP DML statements:

Assignment statement (SET)

DIMENSION (in models)

INCLUDE

The maximum number of lines you can have in a model is 4,000. Each statement is a line of the multiline text expression. When coding an ALLOCMAP statement at the command line level, separate statements with newline delimiters (\n), or use JOINLINES.

For a discussion of designing a model specification, see "Model Specification".

Notes

Model Specification

The model specification consists of the following OLAP DML statements:

One of the following:
- Exactly one INCLUDE statement that specifies the name of another model to include. See "Nesting Models" for more information.
- One or more DIMENSION (in models) statements coded following the "Guidelines for Writing DIMENSION Statements in a Model".
  
  Note:
  When a model contains an INCLUDE statement, then it cannot contain any DIMENSION statements. However, the model referenced in the INCLUDE statement or the root model in a hierarchy must contain the DIMENSION statements needed by the parent model(s).
One or more SET commands or equations written following the "Rules for Equations in Models". The maximum number of lines you can have in a model is 4,000.

See also:
"Dimension Status and Model Equations" for information on how Oracle OLAP processes equations in a model.
A final END statement that indicates the end of the model specification. (Omit when coding the specification in an Edit window of the OLAP Worksheet.)

MODEL Statement in an Aggregation Specification

Within an aggmap, you can use a special MODEL statement to execute a predefined model. (See MODEL (in an aggregation) for more information.

Methods of Calculating Data Within a Variable

Both models and aggmap objects calculate data values within a variable based on relationships among dimension members. When a parent-child relationship exists among dimension members (that is, the dimension has a hierarchical structure) and all aggregate values can be calculated using the same method, then you can use a RELATION (for aggregation) statement within an aggregation specification to calculate the values. However, when the dimension is not hierarchical and different equations are needed to calculate the values, then you must define a model. You can use a MODEL (in an aggregation) to execute the MODEL within an aggregation specification or you can run a model at the command line using the syntax shown in "Running a Model".

Deleting a Model Specification

You can remove the specification of a model without deleting the model definition. Consider the model with a CONSIDER statement. Then issue a MODEL statement and enter the word END as the model specification.

Examples

Example 19-9 Model Specified in a Program

In the following example, a simple model is created (or overwritten) in a program called myprog. The first line in the program defines or considers the model. The second line contains the MODEL statement, which provides the lines of the model.

This model calculates the line items in a budget. The model equations are based on a line dimension.

DEFINE myprog PROGRAM
PROGRAM
IF NOT EXISTS('myModel')
  THEN DEFINE myModel
  ELSE CONSIDER myModel
MODEL JOINLINES(-
  'DIMENSION line month' -
  'Opr.Income = Gross.Margin - Marketing' -
  'Gross.Margin = Revenue - Cogs' -
  'Revenue = LAG(Revenue, 1, month) * 1.02' -
  'Cogs = LAG(Cogs, 1, MONTH) * 1.01' -
  'Marketing = LAG(Opr.Income, 1, month) * 0.20' -
  'END')
END

Example 19-10 Model from an Input File

This example presents the text of the same simple model, but it is stored in an ASCII disk file called budget.txt.

DEFINE income.budget MODEL
MODEL
DIMENSION line month
Opr.Income = Gross.Margin - Marketing
Gross.Margin = Revenue - Cogs
Revenue = LAG(Revenue, 1, month) * 1.02
Cogs = LAG(Cogs, 1, month) * 1.01
Marketing = LAG(Opr.Income, 1, month) * 0.20
END

To include the income.budget model in your analytic workspace, execute the following statement in which myinpfiles is a directory object.

INFILE 'myinpfiles/budget.txt'

Example 19-11 Creating a Model

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

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

After defining the model, you can use a MODEL statement or the OLAP Worksheet editor to enter the specification for the model. A model specification can contain DIMENSION commands, assignment statements 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 by issuing a COMPILE statement or by running the model, the order in which the model equations are solved is determined. When 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 statement.

income.calc actual

When 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 those dimensions. For example, actual is dimensioned by month and division, as well as by line. When 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.

Example 19-12 Building a Scenario Model

Suppose, for example, you want to calculate profit figures based on optimistic, pessimistic, and best-guess revenue figures for each division. The steps for building this scenario model are explained in the following example.

You can call the scenario dimension scenario and give it values that represent the scenarios you want to calculate.

These commands give scenario the values optimistic, pessimistic and bestguess.

DEFINE scenario DIMENSION TEXT
LD Names of scenarios
MAINTAIN scenario ADD optimistic pessimistic bestguess

These commands create a variable named plan dimensioned by three other dimensions (month, line, and division) in addition to the scenario dimension.

DEFINE plan DECIMAL <month line division scenario>
LD Scenarios for financials

For this example, you need to enter input data, such as revenue and cost of goods sold, into the plan variable.

For the best-guess data, you can use the data in the budget variable. Limit the line dimension to the input line items, and then copy the budget data into the plan variable.

LIMIT scenario TO 'BESTGUESS'
LIMIT line TO 'REVENUE' 'COGS' 'MARKETING' 'SELLING' 'R.D'
plan = budget

You might want to base the optimistic and pessimistic data on the best-guess data. For example, optimistic data might be fifteen percent higher than best-guess data, and pessimistic data might be twelve percent less than best-guess data. With line still limited to the input line items, execute the following commands.

plan(scenario 'OPTIMISTIC') = 1.15 * plan(scenario 'BESTGUESS')
plan(scenario 'PESSIMISTIC') = .88 * plan(scenario 'BESTGUESS')

The final step in building a scenario model is to write a model that calculates results based on input data. The model might contain calculations very similar to those in the budget.calc model shown earlier in this chapter.

You can use the same equations for each scenario or you can use different equations. For example, you might want to calculate the cost of goods sold and use a different constant factor in the calculation for each scenario. To use a different constant factor for each scenario, you can define a variable dimensioned by scenario and place the appropriate values in the variable. When the name of your variable is cogsval, then your model might include the following equation for calculating the cogs line item.

cogs = cogsval * revenue

By using variables dimensioned by scenario, you can introduce a great deal of flexibility into your scenario model.

Similarly, you might want to use a different constant factor for each division. You can define a variable dimensioned by division to hold the values for each division. For example, when labor costs vary from division to division, then you might dimension cogsval by division as well as by scenario.

When you run your model, you specify plan as the solution variable. For example, when your model is called scenario.calc, then you solve the model with this statement.

scenario.calc plan

A loop is performed automatically over the current status list of each of the dimensions of plan. Therefore, when the scenario dimension is limited to ALL when you run the scenario.calc model, then the model is solved for all three scenarios: optimistic, pessimistic, and bestguess.

DIMENSION (in models)

The DIMENSION statement at the beginning of a model tells Oracle OLAP the names of one or more dimensions to which the model assigns data or to which it refers in dimension-based equations. A dimension-based equation assigns the results of a calculation to a target that is represented by one or more values of a dimension.

Syntax

DIMENSION dimension1 [, dimensionN]

Arguments

dimension: One or more dimensions, including base dimensions of composites, on which model equations are based. You can specify the name of a dimension surrogate instead of the dimension for which is a surrogate. You can then use the values of the surrogate instead of the values of the dimension.

Notes

Dimension-Based Equations in Models

When an equation (SET) assigns data to a dimension value or refers to dimension values in its calculations, it is called a dimension-based equation. Note that a dimension-based equation does not need to refer to the dimension itself, but only to the values of the dimension. Therefore, when the model contains any dimension-based equations, you must specify the name of each of these dimensions in a DIMENSION statement at the beginning of the model. This allows Oracle OLAP to determine the dimension to which each dimension value belongs. You can specify the name of a dimension surrogate instead of the dimension for which it is a surrogate. You can then use the values of the surrogate instead of the values of the dimension.

In addition, when a model contains any dimension-based equations, you must supply the name of a solution variable when you run the model. The solution variable is both the source and the target of data for the model. It holds the input data used in dimension-based calculations, and Oracle OLAP stores the calculation results in designated values of the solution variable. The solution variable is generally dimensioned by all the dimensions on which the model equations are based. For example, in a financial application, the model might be based on the line dimension, and the solution variable might be actual, which has line as one of its dimensions.

Dimension-based equations provide flexibility in modeling. Since you do not need to specify the modeling variable until you solve a model, you can run the same model with different solution variables. For example, you might run the same model with the actual variable, with a "best case" budget variable, and with a "worst case" budget variable.

A dimension must be specified in a DIMENSION statement when a dimension-based equation refers to a value of the dimension either as a source of the data used in the calculation or as the target to which the results will be assigned. In the following example, Gross.Margin, Revenue, and Cogs are values of the line dimension, so line is specified in a DIMENSION statement.

DIMENSION line
Gross.Margin = Revenue - Cogs

Dimension is a Function Argument

A dimension must be specified in a DIMENSION statement when the dimension is an argument to a function that uses a dimension value as its data source. In the following example, month must be specified in a DIMENSION statement.

DIMENSION line, month
Revenue = lag(Revenue, 1, month) * 1.05

The writer of the preceding model expects to use a solution variable that is dimensioned by line and month. Therefore, when the model is run, the LAG function will operate on a solution variable that has the specified time dimension (month) as one of its dimensions. However, since the model compiler cannot anticipate the time dimension of the solution variable, you must specify it in a DIMENSION statement. When you fail to include month in a DIMENSION statement, an error occurs when you attempt to compile the model.

In a function that operates on time-series data (such as MOVINGTOTAL or LAG), the dimension argument is optional when the dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. For example, you can omit month from the LAG function in the preceding example. However, you must still specify the appropriate time dimension in a DIMENSION statement.

Solution Variable

When you run a model that contains dimension-based equations, you specify a solution variable, which is both the source and the target of data for the model. The solution variable is generally dimensioned by all the dimensions that are listed in the DIMENSION commands used in the model. Or, when a solution variable is dimensioned by a composite, the DIMENSION commands can list base dimensions of the composite. The DIMENSION commands can be explicit in the model or inherited through an included model. See "Incompatibility with INCLUDE".

Working with Composites

When you expect to run a model with a solution variable that has a composite in its dimension list, you can specify a base dimension of the composite in a DIMENSION statement. Your model equations will assign results to values of the base dimension. Oracle OLAP automatically creates any new values that are needed in the composite.

Multiple DIMENSION Commands

You can include a separate DIMENSION statement for every dimension referred to or used in dimension-based equations, or you can specify all the dimensions in a single DIMENSION statement.

Location of Commands

You must place all the DIMENSION commands at the beginning of the model, before any equations.

Incompatibility with INCLUDE

When a model contains an INCLUDE statement, it cannot contain any DIMENSION commands. The INCLUDE statement specifies another model to include in the current model. In this case, the current model inherits its DIMENSION commands, if any, from the included model. For more information in including models, see INCLUDE.

Inherited DIMENSION commands must satisfy all the requirements specified for explicit DIMENSION commands. See "Guidelines for Writing DIMENSION Statements in a Model".

Dimension Order

When more than one dimension is specified by the DIMENSION commands in a model, the order in which the dimensions are listed is important:

When a model equation contains a name that might be a dimension value, Oracle OLAP searches through the dimensions that appear in the model's explicit or inherited DIMENSION commands, in the order you list the dimensions, to determine whether the name matches a dimension value of a listed dimension. The search concludes as soon as a match is found. Therefore, when two or more listed dimensions have a dimension value with the same name, Oracle OLAP assumes that the value belongs to the dimension specified earliest in a DIMENSION statement. When the name does not match a value of a listed dimension, Oracle OLAP then searches through the variables in the attached workspaces to find a match.
When model equations assign results to values of a target dimension, Oracle OLAP constructs code that will loop over the values of the other, non-target, dimensions listed in the DIMENSION commands. The non-target dimension listed first in the DIMENSION commands is treated as the slowest-varying dimension. For example, when MONTH is the first non-target dimension listed in a DIMENSION statement and DIVISION is the second, Oracle OLAP loops through all the divisions for the first month, then all the divisions for the second month, and so on.

Guidelines for Writing DIMENSION Statements in a Model

When you write DIMENSION statements, you should keep these points in mind:

In the DIMENSION statements, you must list the names of all the dimensions on which model equations are based. In the following example, gross.margin, revenue, and cogs are values of the line dimension, so line is specified in a DIMENSION statement.
```
DIMENSION line
gross.margin = revenue - cogs
```

DIMENSION statements must also list any dimension that is an argument to a function that refers to a dimension value. In the following example, month must be specified in a DIMENSION statement.
```
DIMENSION line, month
revenue = LAG(revenue, 1, month) * 1.05
```

When a model equation assigns results to a dimension value, then code is constructed that loops over the values of any of the other nontarget dimensions listed in the DIMENSION statements. The nontarget dimension listed first in the DIMENSION statements is treated as the slowest-varying dimension.
A model executes most efficiently when you observe the following guidelines for coordinating the dimensions in DIMENSION statements and the dimensions of the solution variable:
- List the target dimension of the model as the first dimension in the DIMENSION statements and as the last dimension in the definition of the solution variable.
- In DIMENSION statements, list the nontarget dimensions in the reverse order of their appearance in the definition of the solution variable. This means that the fastest-varying and slowest-varying nontarget dimensions are in the same order in the model and in the solution variable.
When the solution variable has dimensions that are not used or referred to in model equations, then do not include them in DIMENSION statements.
When your analytic workspace contains a variable whose name is the same as a dimension value, or when the same dimension value exists in two different dimensions, then there could be ambiguities in your model equations. Since you can use a variable and a dimension value in exactly the same way in a model equation, a name might be the name of a variable, or it might be a value of any dimension in your analytic workspace.
Your DIMENSION statements are used to determine whether each name reference in an assignment statement is a variable or a dimension value. "Compiling Models" explains how the name references are resolved.
See Also:
Chapter 6, "Models", SET, and MODEL for information on:
- Entering statements in a model
- How to refer to values of dimensions
- Explanation of how Oracle OLAP constructs code from the statements
- Explanation of how Oracle OLAP handles the situation in which the solution variable has more dimensions or fewer dimensions than are listed in DIMENSION commands

Examples

Example 19-13 Simplified Model for Budget Estimates

The following statements define a simplified model that estimates budget values for the items on an income statement.

DEFINE income.budget MODEL
LD Model for estimating budget line items
MODEL
dimension line, month
Revenue = 1.05 * LAG(Revenue 1 month)
Gross.Margin = Revenue - Cogs
Opr.Income = Gross.Margin - (Marketing + Selling + R.D)
Net.Income = Opr.Income - Taxes
END

The model equations are based on the line dimension, so line is specified in the DIMENSION statement. The dimension month is the time dimension in the LAG function that operates on REVENUE values, so month is also specified in the DIMENSION statement.

When you run the model, Oracle OLAP loops over the values in the current status of the month dimension.

INCLUDE

The INCLUDE statement includes one model within another model. You can use the INCLUDE statement only within models.

Use INCLUDE to create modular models by placing equations that are common to several models, in a separate model for inclusion on other models as needed. The INCLUDE statement also facilitates what-if analyses. An experimental model can draw equations from a base model and selectively replace them with new equations

Syntax

INCLUDE model

Arguments

model: The name of a model to include in the current model. The current model is referred to as the parent model. The model that you include is referred to as the base model.

Notes

Guidelines for Coding INCLUDE Statements in a Model

Follow these guidelines for using INCLUDE statements in models:

A model can contain only one INCLUDE statement.
The INCLUDE statement must be before any equations in the model.
A model that contains an INCLUDE statement cannot contain any DIMENSION (in models) statements.

How to Nest Models

You can nest models by placing an INCLUDE statement in a base model. For example, model myModel1 can include model myModel2, and model myModel2 can include model myModel3. The nested models form a hierarchy. In this example, myModel1 is at the top of the hierarchy, and myModel3 is at the root. A base model cannot include a model at a higher level in the hierarchy. In the preceding example, myModel2 cannot include myModel1, and myModel3 cannot include myModel1 or myModel2.

Dependencies Among Equations

When compiling a model that contains an INCLUDE statement, the compiler considers the dependencies among the equations from all the included models when it orders and blocks the equations. Therefore, when you run the MODEL.COMPRPT program to examine the results of the compilation or when you set the MODTRACE option to YES before running the parent model, you might find that equations from different levels in the hierarchy of included models are interspersed. See Example 19-15, "Producing a Compilation Report".

When the compiler finds no dependencies among the equations from the included models, it executes the equations in the root model first and the equations in the parent model last.

Compiling a Parent Model

When you compile a parent model, the compiler will compile all the base models under it in the included hierarchy when compiled code does not already exist. When the compiler detects an error in an included model, neither it nor any model above it in the hierarchy is compiled. When the root model of the included hierarchy contains an error, the higher-level models are unable to inherit any DIMENSION (in models) statements from the root model. In this case, the compiler might report an error in a parent model when the source of the error is actually in the root model. For example, the compiler might report that a target dimension value does not exist in any attached analytic workspace. On the other hand, when the compiler detects an error in a parent model but finds no errors in the included models, the included models are compiled even though the parent model is not.

Masking Equations

To support what-if analyses, Oracle OLAP allows equations in a model to mask previous equations. The previous equations can come from the same model or from included models. A masked equation is not executed. When you run the MODEL.COMPRPT program after compiling the model, you will see that the masked equation is not shown in the report on the compiled model.

Masking can take place when an equation assigns a value to a variable or dimension value that is also the target of a previous equation. The masking rules are as follows:

When the target in the earlier equation is qualified exactly the same as the target in the later equation, the earlier equation is masked and the later equation is executed. The following example illustrates two equations with targets that are identically qualified.
```
Equation from a base model:      BUDGET(LINE REVENUE) = 5000
Equation from the parent model:  BUDGET(LINE REVENUE) = 3500
```
In this example, the equation from the base model is masked and the equation from the parent model is executed.
When the target in the earlier equation is more qualified than the target in the later equation, the earlier equation is masked. The later equation is executed.

The target that is more qualified is the one that will affect the fewest dimension values. Consider the following equations from a base model and a parent model.
```
Equation from a base model:      BUDGET(LINE REVENUE) = 2500
Equation from the parent model:  BUDGET = 4000
```
The equation from the base model is more qualified because it assigns data only for the REVENUE value of the LINE dimension. The equation from the parent model assigns data to all the values of the LINE dimension. In this example, the equation from the base model is masked and the equation from the parent model is executed.
When the target in the earlier equation is less qualified than the target in the later equation, no masking takes place. Both equations are executed.

Consider the following equations from a base model and a parent model.
```
Equation from a base model:      BUDGET = LAG(ACTUAL, 1, MONTH)
Equation from the parent model:  BUDGET(LINE REVENUE) = 6500
Equation from the parent model:  BUDGET(LINE COGS) = 4000
```
The equation from the base model assigns data to all the values of the LINE dimension. The equations from the parent model are more qualified because each assigns data only for a single value of the LINE dimension. In this example, the equation from the base model is executed first, and then the equations from the parent model are executed.

This functionality enables you to assign a large number of values with one equation and use subsequent equations to replace or test individual values.
When the target in the earlier equation is qualified differently from the target in the later equation, no masking takes place. Both equations are executed. In the following example, both equations are executed.
```
Equation from a base model:      BUDGET(LINE REVENUE) = 5000
Equation from the parent model:  BUDGET(LINE COGS) = 4500
```

Examples

Example 19-14 Including a Model

This example shows a parent model named income.plan that includes a base model named base.lines.

DEFINE income.plan MODEL
MODEL
INCLUDE base.lines
revenue = LAG(revenue, 1, month) * 1.02
cogs = LAG(cogs, 1, month) * 1.01
taxes = 0.3 * opr.income
END
 
DEFINE BASE.LINES MODEL
MODEL
DIMENSION line month
net.income = opr.income - taxes
opr.income = gross.margin - marketing
gross.margin = revenue - cogs
END

Example 19-15 Producing a Compilation Report

The following statements compile the parent model and produce a compilation report.

COMPILE income.plan
MODEL.COMPRPT income.plan

These statements produce the following output.

MODEL INCOME.PLAN <LINE MONTH>
                      BLOCK 1 (SIMPLE)
INCOME.PLAN     2:    revenue = lag(revenue, 1, month) * 1.02
INCOME.PLAN     3:    cogs = lag(cogs, 1, month) * 1.01
BASE.LINES      4:    gross.margin = revenue - cogs
BASE.LINES      3:    opr.income = gross.margin - marketing
INCOME.PLAN     4:    taxes = 0.3 * opr.income
BASE.LINES      2:    net.income = opr.income - taxes
                      END BLOCK 1