![]() Previous |
![]() Next |
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. 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. For more information on models, see "OLAP DML Model Objects".
Syntax
MODEL specification
Parameters
A multiline text expression that contains one or more of the following OLAP DML statements:
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".
Usage 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".
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.)
The maximum number of lines you can have in a model is 32,000.
MODEL Statement in an Aggregation Specification
Within an aggmap, you can use a special MODEL statement to execute a predefined model. (See the MODEL (in an aggregation) statement under the AGGMAP command 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 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
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
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'
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, Oracle OLAP identifies the logical order in which the model equations are solved. 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
, division
, and 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.
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 must 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
and 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
.