Defining Variables

Definition: Variable

A variable is an OLAP DML object that stores data. All of the data in a variable represents the same unit of measurement with the same data type. Your business might have several categories of transactions -- measured in dollars, units, percentages, and so on -- and each category is stored in its own variable. For example, you might record sales data in dollars (a SALES variable) and units (a UNITS variable).

Typically, you use variables to contain data values that quantify a particular aspect of your business.

Types of variables

Variables can be either dimensioned or undimensioned:

• Dimensioned variables -- If a variable is an array with dimensions, then those dimensions organize its data, and there is one cell for each combination of dimension values. This type of variable is called a dimensioned variable. A variable can be dimensioned by up to 32 dimensions.

• Undimensioned variables -- If a variable has no dimensions, then it is a scalar, or single-cell variable, which contains one data value.

Variables that you define in an analytic workspace can be permanent, inplace, or temporary. You can also define variables in programs, as described in "Defining local variables".

Permanent variables

A permanent variable is a variable for which both the variable's values and definitions are stored on disk. The values of permanent variables are written to new pages in the analytic workspace as you make changes to the values of the variable. However, the stored values of the permanent variable are not actually changed when an UPDATE command is processed for the analytic workspace that contains the variable. Consequently, if an update of an analytic workspace is unsuccessful, then the original values of the permanent variable can be retrieved.

Inplace variables

Like permanent variables, both the values and definitions of inplace variables are stored on disk. The way that inplace variables are updated depends on how the analytic workspace is attached:

• When the analytic workspace is attached in read/write, non-exclusive mode, inplace variables are updated in the same way that permanent variables are updated.

• When the analytic workspace is attached in read/write, exclusive mode, the values of an inplace variable are stored whenever it writes pages to the disk. Additionally, when you change the values of inplace variable, the new changed values are stored over the inplace variable's old values. Consequently, if an update of an analytic workspace is unsuccessful, then the original values of an inplace variable might not still be stored in the analytic workspace, and it might not be possible to restore the variable's original values.

For more information on attaching analytic workspaces, see "Gaining Access to a Workspace from OLAP Worksheet".

Temporary variables

For more efficient use of disk space, the OLAP DML also lets you define temporary variables that have values only during the current OLAP Services session. When you update the analytic workspace, only the definitions of temporary variables are saved. When you exit from the analytic workspace, the data values are discarded.

When to use inplace variables

There are both advantages and disadvantages to using inplace variables:

• Advantages of inplace variables -- Once the values for an inplace variable have been stored, new analytic workspace pages are no longer created to store new, changed values. Consequently, the major advantage of using an inplace variable is that the analytic workspace that contains the variable grows at a slower rate and has fewer free pages then the analytic workspace would have if you defined the variable as a permanent variable.

• Disadvantages of inplace variables -- The major disadvantages of using inplace variables rather than a permanent variables are:
  • An inplace variable is always in an indeterminate state while it is being updated. Because the old values are overwritten when the new ones are stored, there is no way to know exactly what data is stored in the variable at any given moment. Depending on the other processing that is being performed, an inplace variable that is being updated can be computationally inconsistent. The values of an inplace variable are in a determinate state only after an UPDATE command is processed for the analytic workspace that contains the variable.

  • If an UPDATE command for an analytic workspace that contains an inplace variable fails, it might not be possible to restore the original values of the inplace variable if its stored values have been overwritten. Instead, you must explicitly restore the inplace variable's original values.

Recommendations for using inplace variables

Oracle Corporation recommends that you only use inplace variables if you can guarantee that the following conditions are met:

• You can reconstruct the data in the inplace variable if the update of the analytic workspace that contains the variable fails.

• You are the only user accessing the analytic workspace when you update the inplace variable, for example, when you explicitly attach an analytic workspace in read/write, exclusive mode as described in "Specifying the analytic workspace attachment mode".

How variable data is stored

The order in which you list the dimensions in a variable's definition determines how that variable's data will be stored and accessed. The first dimension that you list in the variable definition is referred to as the fastest-varying dimension, and the last dimension that you list is referred to as the slowest-varying dimension.

Example: How variable data is stored

Assume your analytic workspace has an OPCOSTS variable that contains the operating costs, by month, of each city in which you have offices. In the definition shown below for the OPCOSTS variable, MONTH is the fastest-varying dimension and CITY is the slowest-varying dimension.

define opcosts variable decimal <month city>

The data for a multidimensional variable is stored as a linear stream of values, in which the values of the fastest-varying dimension are clustered together. For example, for the OPCOSTS variable, the values for Boston for all the months are stored in a sequence, and then it stores the values for Chicago for all the months in a sequence, and so on. Thus the month values vary fastest, as shown below.

OPCOSTS variable
Dimension Values	JAN97 Boston	FEB97 Boston	. . . . . .	JAN97 Chicago	FEB97 Chicago	. . . . . .
Variable Values	16000.77	16000.28	. . . . . .	19000.21	19000.24	. . . . . .

Example: Three-dimensional variable

The demo analytic workspace contains the SALES variable, which is a three-dimensional array dimensioned by MONTH, PRODUCT, and DISTRICT.

Assume that the MONTH, PRODUCT, and DISTRICT dimensions have 36, 5, and 6 values, respectively, and that the SALES variable has the following definition.

define sales variable decimal <month product district>

The SALES variable contains 1,080 cells, which is the total number of cells in each dimension multiplied together or, in this case, 36 times 5 times 6.

DISTRICT: BOSTON
                     -------------------SALES-------------------
                     -------------------MONTH-------------------
PRODUCT       JAN96      FEB96      MAR96
-------------- ---------- ---------- ---------- ----------
TENTS                50,808.96      34,641.59      45,742.21       . . .
CANOES               70,489.44      82,237.68      97,622.28       . . .
RACQUETS             56,337.84      60,421.50      62,921.70       . . .
SPORTSWEAR           57,079.10      63,121.50      67,005.90       . . .
FOOTWEAR             95,986.32      101,115.36    103,679.88       . . .

DISTRICT: ATLANTA
                    -------------------SALES-------------------
                    -------------------MONTH-------------------
PRODUCT      JAN96     FEB96     MAR96
-------------- ---------- ---------- ---------- ----------
TENTS               46,174.92       50,553.52      58,787.82       . . .
CANOES              56,271.40       61,828.33      77,217.62       . . .
  . 
  . 
  . 

Related information

For more information, see the following table.

IF you want documentation about . . .	THEN see . . .
populating variables,	"Assigning Values to Data Objects"
using variables in expressions,	"Using OLAP DML Objects in Expressions"
defining variables,	the entry for the DEFINE VARIABLE command in THE OLAP DML Reference