|Oracle® OLAP DML Reference
10g Release 2 (10.2)
|PDF · Mobi · ePub|
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".
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".
The model specification consists of the following OLAP DML statements:
One of the following:
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).
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.)
Within an aggmap, you can use a special MODEL statement to execute a predefined model. (See MODEL (in an aggregation) for more information.
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".
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.
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
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
This example presents the text of the same simple model, but it is stored in an ASCII disk file called
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.
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.
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
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.
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
DEFINE scenario DIMENSION TEXT LD Names of scenarios MAINTAIN scenario ADD optimistic pessimistic bestguess
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
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
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
division as well as by
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.
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:
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.
DIMENSION dimension1 [, dimensionN]
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.
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,
Cogs are values of the
line dimension, so
line is specified in a DIMENSION statement.
DIMENSION line Gross.Margin = Revenue - Cogs
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
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.
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".
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.
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.
You must place all the DIMENSION commands at the beginning of the model, before any equations.
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".
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.
When you write
DIMENSION statements, you should keep these points in mind:
DIMENSION statements, you must list the names of all the dimensions on which model equations are based. In the following example,
cogs are values of the
line dimension, so
line is specified in a
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 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.
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.
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
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.
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
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
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
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.
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.
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
myModel3 cannot include
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.
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.
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
This example shows a parent model named
income.plan that includes a base model named
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
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