| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
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 gross.margin or net.income.
gross.margin = revenue - cogs
When an assignment statement 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, when the model contains any dimension-based equations, then you must specify the name of each of these dimensions in a DIMENSION statement at the beginning of the model.
When 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 line.
Models can be quite complex. You can:
Include one model within another model as discussed in "Nesting Models"
Use data from different time periods as discussed in "Using Data from Past and Future Time Periods"
Solve simultaneous equations as discussed in "Solving Simultaneous Equations"
Create models for different scenarios as described in "Modeling for Multiple Scenarios"
To create an OLAP DML model, take the following steps:
Issue a DEFINE MODEL statement to define the program object.
Add a specification to the model to specify the processing that you want performed as described in MODEL.
Compile the model as described in "Compiling a Model".
(Optional) If necessary, change the settings of model options listed in Table 17-1, "Model Options".
Execute the model as described in "Running a Model".
Debug the model as described in "Debugging a Model".
For an example of creating a model, see "Creating a Model".
You can include one model within another model by using an INCLUDE statement. The model that contains the INCLUDE statement is referred to as the parent model. The included model is referred to as the base model. 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.
When a model contains an INCLUDE statement, then it cannot contain any DIMENSION (in models) statements. A parent model inherits its dimensions, if any, from the DIMENSION statements in the root model of the included hierarchy. In the example just given, models myModel1 and myModel2 both inherit their dimensions from the DIMENSION statements in model myModel3.
The INCLUDE statement enables you to create modular models. When certain equations are common to several models, then you can place these equations in a separate model and include that model in 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. To support what-if analysis, you can use 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 or shown in the MODEL.COMPRPT report for a model
When a model contains an assignment statement to assign data to a dimension value, then the dimension is limited temporarily to that value, performs the calculation, and restores the initial status of the dimension.
For example, a model might have the following statements.
DIMENSION line gross.margin = revenue - cogs
If you specify actual as the solution variable when you run the model, then the following code is constructed and executed.
PUSH line LIMIT line TO gross.margin actual = actual(line revenue) - actual(line cogs) POP line
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 detail.line.
When your analytic workspace contains a relation between line and detail.line, which specifies the line item to which each detail item pertains, then you might write model equations such as the following ones.
revenue = total(detail.data line) expenses = total(detail.data line)
The relation between detail.line and 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 line.
Several OLAP DML functions make it easy for you to use data from past or future time periods. For example, the LAG function returns data from a specified previous time period, and the LEAD function returns data from a specified future period.
When you run a model that uses past or future data in its calculations, you must make sure that your solution variable contains the necessary past or future data. For example, a model might contain an assignment statement that bases an estimate of the revenue line item for the current month on the revenue line item for the previous month.
DIMENSION line month ... revenue = LAG(revenue, 1, month) * 1.05
When the month dimension is limited to Apr2004 to Jun2004 when you run the model, then you must be sure that the solution variable contains revenue data for Mar96.
When your model contains a LEAD function, then your solution variable must contain the necessary future data. For example, when you want to calculate data for the months of April through June of 2004, and when the model retrieves data from one month in the future, then the solution variable must contain data for July 2004 when you run the model.
Oracle OLAP observes the NASKIP2 option when it evaluates equations in a model. NASKIP2 controls how NA values are handled when + (plus) and - (minus) operations are performed. The setting of NASKIP2 is important when the solution variable contains NA values.
The results of a calculation may be NA not only when the solution variable contains an NA value that is used as input, but also when the target of a simultaneous equation is NA. Values in the solution variable are used as the initial values of the targets in the first iteration over a simultaneous block. Therefore, when the solution variable contains NA as the initial value of a target, an NA result may be produced in the first iteration, and the NA result may be perpetuated through subsequent iterations.
To avoid obtaining NA for the results, you can make sure that the solution variable does not contain NA values or you can set NASKIP2 to YES before running the model.
An iterative method is used to solve the equations in a simultaneous block. In each iteration, a value is calculated for each equation, and compares the new value to the value from the previous iteration. When the comparison falls within a specified tolerance, then the equation is considered to have converged to a solution. When the comparison exceeds a specified limit, then the equation is considered to have diverged.
When all the equations in the block converge, then the block is considered solved. When any equation diverges or fails to converge within a specified number of iterations, then the solution of the block (and the model) fails and an error occurs.
You can exercise control over the solution of simultaneous equations, use the OLAP DML options described in Table 17-1, "Model Options". For example, using these options, you can specify the solution method to use, the factors to use in testing for convergence and divergence, the maximum number of iterations to perform, and the action to take when the assignment statement diverges or fails to converge.
Instead of calculating a single set of figures for a month and division, you might want to calculate several sets of figures, each based on different assumptions.
You can define a scenario model that calculates and stores forecast or budget figures based on different sets of input figures. For example, you might want to calculate profit based on optimistic, pessimistic, and best-guess figures.
To build a scenario model, follow these steps.
Define a scenario dimension.
Define a solution variable dimensioned by the scenario dimension.
Enter input data into the solution variable.
Write a model to calculate results based on the input data.
For an example of building a scenario model see, Example 17-12, "Building a Scenario Model".
When you finish writing the statements in a model, you can use COMPILE to compile the model. During compilation, COMPILE checks for format errors, so you can use COMPILE to help debug your code before running a model. When you do not use COMPILE before you run the model, then the model is compiled automatically before it is solved.
When you compile a model, either by using a COMPILE statement or by running the model, the model compiler examines each equation to determine whether the assignment target and each data source is a variable or a dimension value.
After resolving each name reference, the model compiler analyzes dependencies between the equations in the model. A dependence exists when the expression on the right-hand side of the equal sign in one equation refers to the assignment target of another equation. When an assignment statement indirectly depends on itself as the result of the dependencies among equations, then a cyclic dependence exists between the equations.
The model compiler structures the equations into blocks and orders the equations within each block, and the blocks themselves, to reflect dependencies. The compiler can produce three types of solution blocks: simple blocks, step blocks, and simultaneous blocks as described in "Dependencies Between Equations".
The compiler does not analyze the contents of any programs or formulas that are used in model equations. Therefore, you must check the programs and formulas yourself to make sure they do not do any of the following:
Refer to the value of any variable used in the model.
Refer to the solution variable.
Limit any of the dimensions used in the model.
Invoke other models.
When a model or program violates any of these restrictions, the results of the model may be incorrect.
To run or solve a model, use the following syntax.
model-name [solution-variable] [NOWARN]
where:
model-name is the name of the model.
solution-variable is the name of a numeric variable that serves as both the source and the target of data in a model that contains dimension-based equations. The solution variable is usually dimensioned by all the dimensions on which model equations are based (as specified in explicit or included DIMENSION commands). The solution-variable argument is required when the model contains any dimension-based equations. When all the model equations are based only on variables, a solution variable is not needed and an error occurs when you supply this argument.
NOWARN is an optional argument that specifies that you do not want to be warned when the model contains a block of simultaneous equations.
When you run a model, you should keep these points in mind:
Before you run a model, the input data must be available in the solution variable.
Before running a model that contains a block of simultaneous equations, you might want to check or modify the values of some OLAP DML options that control the solution of simultaneous blocks. These options are described briefly in Table 17-1, "Model Options".
When your model contains any dimension-based equations, then you must provide a numeric solution variable that serves both as a source of data and as the assignment target for equation results. The solution variable is usually dimensioned by all of the dimensions on which model equations are based and also by the other dimensions of the solution variable on which you are not basing equations.
When you run a model, a loop is performed automatically over the values in the current status list of each of the dimensions of the solution variable on which you have not based equations.
When a model equation bases its calculations on data from previous time periods, then the solution variable must contain data for these previous periods. When it does not, or when the first value of the dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR is in status, then the results of the calculation are NA.
In a model with dimension-based equations, the solution variable is usually dimensioned by the dimensions on which model equations are based. Or, when a solution variable is dimensioned by a composite, the model equations can be based on base dimensions of the composite. The dimensions on which model equations are based are listed in explicit or inherited DIMENSION (in models) commands.
The following special cases regarding the dimensions of the solution variable can occur:
The solution variable can have dimensions that are not listed in DIMENSION commands. Oracle OLAP automatically loops over the values in the status of the extra dimensions. For example, the model might contain a DIMENSION command that lists the line and month dimensions, but you might specify a solution variable dimensioned by line, month, and division. Oracle OLAP automatically loops over the division dimension when you run the model. The solution variable can also be dimensioned by a composite that has one or more base dimensions that are not listed in DIMENSION commands. See "Solution Variables Dimensioned by a Composite".
When the solution variable has dimensions that are not listed in DIMENSION commands and when any of these other dimensions are the dimension of a step or simultaneous block, an error occurs.
Oracle OLAP loops over the values in the status of all the dimensions listed in DIMENSION commands, regardless of whether the solution variable is dimensioned by them. Therefore, Oracle OLAP will be doing extra, unnecessary work when the solution variable is not dimensioned by all the listed dimensions. Oracle OLAP warns you of this situation before it starts solving the model.
The inclusion of an unneeded dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR in a DIMENSION command causes incorrect results when you use a loan, depreciation, or aggregation function in a model equation. This happens because any component of a model equation that refers to the values of a model dimension behaves as if that component has all the dimensions of the model.
When a solution variable contains a composite in its dimension list, Oracle OLAP observes the sparsity of the composite whenever possible. As it solves the model, Oracle OLAP confines its loop over the composite to the values that exist in the composite. It observes the current status of the composite's base dimensions as it loops.
However, for proper solution of the model, Oracle OLAP must treat the following base dimensions of the composite as regular dimensions:
A base dimension that is listed in a DIMENSION (in models) command.
A base dimension that is implicated in a model equation created using SET (for example, an equation that assigns data to a variable dimensioned by the base dimension).
A base dimension that is also a base dimension of a different composite that is specified in the ACROSS phrase of an equation. (See SET for more information on assignment statements and the use of ACROSS phrase.)
When a base dimension of a solution variable's composite falls in any of the preceding three categories, Oracle OLAP treats that dimension as a regular dimension and loops over all the values that are in the current status.
When the solution variable's composite has other base dimensions that do not fall in the special three categories, Oracle OLAP creates a temporary composite of these extra base dimensions. The values of the temporary composite are the combinations that existed in the original composite. Oracle OLAP loops over the temporary composite as it solves the model.
The following tools are available for debugging models:
To see the order in which the equations in a model are solved, you can set the MODTRACE option to YES before you run the model.When you set MODTRACE to YES, you can use the DBGOUTFILE command to send debugging information to a file. The file produced by DBGOUTFILE interweaves each line of your model with its corresponding output.
You can use the MODEL.COMPRPT, MODEL.DEPRT, and MODEL.XEQRPT programs and the INFO function to obtain information about the structure of a compiled model and the solution status of a model you have run.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|
