Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Defining Data Objects, 5 of 9
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.
Variables can be either dimensioned or undimensioned:
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".
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.
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:
For more information on attaching analytic workspaces, see "Gaining Access to a Workspace from OLAP Worksheet".
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.
There are both advantages and disadvantages to using inplace variables:
Oracle Corporation recommends that you only use inplace variables if you can guarantee that the following conditions are met:
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.
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 |
. . . |
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 . . . . . .
For more information, see the following table.
IF you want documentation about . . . | THEN see . . . |
---|---|
populating variables, |
|
using variables in expressions, |
|
defining variables, |
the entry for the DEFINE VARIABLE command in THE OLAP DML Reference |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|