![]() Previous |
![]() Next |
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]
Parameters
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.
Usage 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 have 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 s that Oracle OLAP can 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 have 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 are 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 operates 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 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 the INCLUDE statement under the MODEL command.
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 multiple dimensions are 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 loops 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, 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. Consequently, 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 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: "OLAP DML Model Objects", SET, and MODEL for information on:
|
Examples
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.