Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
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
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".
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
.
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
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.
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
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