Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)

Part Number A86720-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to beginning of chapter Go to next page

Working with Expressions, 3 of 14

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:

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:

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:

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:

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
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:

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" 

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index