Using OLAP DML Objects in Expressions

Overview: Using OLAP DML objects in expressions

You can use OLAP DML objects and functions in expressions as described below:

• You can use a dimension, a relation, or a variable as an array of data by specifying the name of the object.

• You can use a formula or a function as a subexpression or as an expression in a command or function by specifying the name of the formula or the function.

• You can use a valueset as a list of dimension values in an expression by specifying the name of the valueset.

• You can use various data objects as the target or source expression in an assignment statement as described in "Assigning Values to Data Objects".

Using dimensions in expressions

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:

• Use a dimension with a data type of TEXT in a numeric expression

• Compare one value in a dimension to another value in the same dimension

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.

Using composites in expressions

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:

• If the composite is named, then you specify its name.

• If the composite is unnamed, then you specify SPARSE <dimensions...>.

Using variables in expressions

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.

Using variables defined with composites in expressions

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:

• You can access a variable that is dimensioned by a composite by requesting any of the base dimension values.

• The values of a composite that are in status are determined by the status of the base dimensions of the composite. Composites are not dimensions, and therefore, they do not have any independent status.

Default behavior of commands that loop over a variable

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

Changing the default behavior of looping commands

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.

Using relations in expressions

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:

• When you use a relation in a text expression, the relation value is referenced as a text value. The values of the related dimension that is contained in the relation are converted into text, and you can use these values in an expression. You can also compare a text literal to a relation.

• When you use a relation in a numeric expression, the relation value is referenced by its position (an integer) in its related dimension array. You can use this numeric value in an expression. The position number is based on the default status list of the dimension, not the current status list of the dimension.

Related information

For more information, see the following table.

IF you want documentation about . . .	THEN see . . .
OLAP DML data objects,	Chapter 3
using OLAP DML data objects in assignment statements,	"Assigning Values to Data Objects"
user-defined functions,	"Writing User-Defined Functions"