|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Working with Expressions, 3 of 14
You can use OLAP DML objects and functions in expressions as described below:
In expressions, a dimension is referenced as a one-dimensional array.
If the dimension has a data type of TEXT, then, in most cases, the dimension values are referenced as text values.
However, dimension values are referenced by their positions (integers) in the dimension array and uses the values numerically when you do one of the following:
In these cases, the position number is based on the default status list, not on the current status.
Note: When you use a dimension with a data type of DATE, if you want the dimension value to be treated as an integer position, then you must use the CONVERT function.
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 variable's fastest-varying dimension vary first. For example, for the OPCOSTS variable that is dimensioned by MONTH and CITY, when you view the variable as REPORT command output, you will 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.
Note: 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 DIMENSION commands in the model.
You can uniquely and completely select any item of data within a multidimensional variable by using a QDR to specify one value from each of the variable's dimensions.
For example, if the OPCOSTS variable is dimensioned by MONTH and CITY, specifying Jan95 for the MONTH dimension and Boston for the CITY dimension uniquely specifies a single cell in the variable.
In most cases, when you use OLAP DML 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 composite's base dimensions 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 1995. Since no coupons were issued in March 1995, the report displays NA in that column.
limit month to 'JAN95' 'FEB95' 'MAR95' limit market to 'EAST' limit product to 'SPORTSWEAR' report coupons MARKET: EAST ------------COUPONS------------- -------------MONTH-------------- PRODUCT JAN95 FEB95 MAR95 -------------- ---------- ---------- ---------- SPORTSWEAR 1,000 1,000 NA
However, for performance reasons, you can change the default looping behavior for the REPORT, ROW, RETRIEVE, FETCH, and = commands so that those commands loop over the values in the composite rather than all of the base dimension values. For more information on these commands, see the entry for each command in the OLAP DML Reference.
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:
For more information, see the following table.
|IF you want documentation about . . .||THEN see . . .|
OLAP DML data objects,
using OLAP DML data objects in assignment statements,