|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Working with Expressions, 5 of 11
You can use objects in expressions as described below:
In expressions, a dimension or dimension surrogate is referenced as a one-dimensional array.
If the dimension or surrogate has a data type of
TEXT, then, in most cases, its values are referenced as text values.
NUMBER dimension values are always referenced by the value itself.
However, for dimension types other than
NUMBER dimension values are referenced by their numeric positions in the dimension array when you do one of the following:
TEXTin a numeric expression
In these cases, the integer position number is based on the default status list, not on the current status.
In expressions, composites behave much the same way that dimensions do and, generally, you can use a composite in an expression anywhere you can use a dimension:
In expressions, a variable is referenced as an array containing values of the specified data type.
When you assign values to a variable or when you use
REPORT or another command or function that loops over the dimensions of a variable, the values of the fastest-varying dimension of the variable vary first. For example, for the
opcosts variable that is dimensioned by
city, when you view the variable as
REPORT command output, you see the data for all months for the first city before you see any data for the second city. In this case,
month is the fastest-varying dimension because its values change before those of
city. When you write programs that loop over a multidimensional variable in this way, try to maximize performance by matching the fastest-varying dimension with the inner loop.
When you use a variable as the solution variable in a model, the model will execute most efficiently if the order of the dimensions in the definition of the solution variable matches the order of the dimensions in the
You can uniquely and completely select any item of data within a multidimensional variable by using a qualified data reference (QDR) to specify one value from each of the dimensions of the variable.
For example, if the
opcosts variable is dimensioned by
'JAN02' for the
month dimension and
'BOSTON' for the
city dimension uniquely specifies a single cell in the variable.
In most cases, when you use functions and commands with variables that are defined with composites, the functions and commands treat those variables as if they were defined with base dimensions:
When you use the
REPORT command or any other command that loops over a variable that uses a composite, the default behavior is to evaluate all the combinations of the values of the base dimensions of the composite that are in status. Any combinations that do not exist in the composite display NA for their associated data.
For example, the following commands create a report for the East region that shows the number of coupons issued for sportswear from January through March 2002. Since no coupons were issued in March 2002, the report displays NA in that column.
LIMIT month TO 'JAN02' 'FEB02' 'MAR02' LIMIT market TO 'EAST' LIMIT product TO 'SPORTSWEAR' REPORT coupons MARKET: EAST ------------COUPONS------------- -------------MONTH-------------- PRODUCT JAN02 FEB02 MAR02 -------------- ---------- ---------- ---------- SPORTSWEAR 1,000 1,000 NA
However, for performance reasons, you can change the default looping behavior for commands such as
= so that they loop over the values in the composite rather than all of the base dimension values.
A relation is, in many ways, just a special type of variable. Instead of holding general data values, a relation contains values of the related dimension. Consequently, in an expression, a relation behaves somewhat like a variable and somewhat like a dimension:
A function is a predefined calculation that returns a value. A number of built-in functions are provided, including: