Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

Part Number B28126-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

SET

The SET command, also called an assignment statement or the = command, assigns one or more values to a variable, option, relation, or dimension surrogate. When an object has one or more dimensions, the SET command loops over the values in status for each dimension of the target object and assigns a data value to the corresponding cell of the target object.

When the target is an object defined with a composite in its dimension list, Oracle OLAP automatically creates any missing target cells that are being assigned non-NA values. This step also adds to the composite all the dimension value combinations that correspond to those new cells. Thus, both the target object and the composite might be larger after an assignment. When you want to assign values only to cells that already exist in the target, use the ACROSS keyword.

Note:

You can use UNRAVEL in conjunction with SET to assign values of an expression into the cells of a variable when the dimensions of the expression are not the same as the dimensions of the variable

Syntax

[SET] target-name [=] expression [ACROSS composite]

Arguments

SET

SET is optional. It is an older command form of this functionality, and is included for compatibility.

target-name

The name of the target object where the data will be assigned and stored. For a list of analytic workspace objects that can be a target object, see Table 10-9, "Using Objects in Assignment Statements".

=

The = (assignment or equal) operator assigns one or more values to a variable, option, or relation. See also "Assignment Operator".

expression

The source of the data values to be assigned to the object, see Table 10-9, "Using Objects in Assignment Statements"

ACROSS composite

When you are assigning data to a variable dimensioned by a composite the default behavior is to loop over all the values in status for each of the base dimensions of the object. Oracle OLAP automatically creates any missing target cells that are being assigned non-NA values, and it automatically adds the required dimension value combinations to the composite.

When you want to assign values only to existing cells of a variable defined with a composite, use the ACROSS keyword, which causes = to change the way it loops for those dimensions of the target that are part of the composite. Instead of looping over all possible combinations of the values in the status of those dimensions, = loops only over those combinations of the values in the status that already exist in the composite.

The ACROSS keyword is intended for specifying a composite. However, when you specify a base dimension of the composite instead, be aware that the assignment statement could add many values to your composite.

Notes

Triggering Program Execution When an Assignment Statement Executes

Using the TRIGGER command, you can make a SET statement an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information

Dimensionality and Performance

When the target has more than one dimension, the = statement loops over the dimension values in the order in which they were added, regardless of their logical order as reflected by the default status. In a multidimensional case, the looping is over the compound dimension. The first dimension listed in the definition varies the fastest. When you are setting the target to the values of an expression, Oracle OLAP performs much more efficiently when the source expression has the same dimensions, in the same order, as the target.

Differently Dimensioned Variables in an Expression

When an assignment statement involves a number of differently dimensioned objects, the calculation can appear complicated. The following list outlines the process followed by a complicated assignment statement. When the statement is A = B, where A is the object being set to the expression B, Oracle OLAP first determines the dimensions of A. Then it determines the status of those dimensions. For each combination of dimension values in the status of those dimensions:

  1. Oracle OLAP determines which single value of A (sometimes called a cell) is going to be set.

  2. For each component of the expression B (each variable, formula, function, qualified data reference, or literal), Oracle OLAP determines the single value that corresponds to the cell of A that is being set. When a component of the expression is not dimensioned or is a literal, Oracle OLAP simply uses its value. When a component of the expression has dimensions different from A, Oracle OLAP uses the first value in the status of these dimensions.

  3. Oracle OLAP performs the specified calculation on the single values obtained in Step 2 and stores the result in the cell of A chosen in Step 1.

Using Objects in Assignment Statements

Table 10-9, "Using Objects in Assignment Statements" outlines the objects that you can use in assignment statements and indicates whether you can use them as a target or source expression.

Table 10-9 Using Objects in Assignment Statements

Object Target Source

Composite

No

Yes

Dimension

Only in models

Yes

Formula

Yes

Yes

Function (including OLAP DML program as function)

No

Yes

Relation

Yes

Yes

Surrogate

Yes

Yes

Valueset

No

Yes

Variable

Yes

Yes

Worksheet

Yes

Yes


Assigning Values to Variables

When you use an = (SET) statement to assign the value of a single-cell expression to a single cell, a single value is stored. However, when you use an = statement to assign the value of a single-cell expression to a target variable that has one or more dimensions, then the assignment loops over the values in status for each dimension of the target variable and assigns a data value to the corresponding cells of the variable.

When you assign a multiline value to a fixed-width text variable, then the variable is set to the first line only. To assign a multiline value to a fixed-width text variable, you use the JOINCHARS function to make the multiline value one line long. For example, suppose you have a non-fixed-width text variable called textvar. The statement

SHOW textvar

produces the following output, in which each line of the value in textvar is shown as a separate line.

This is a variable
that has a multiline
text value.

To assign this value to a variable called fixedtext with a fixed width of 60 bytes and show the value, you would use the following statements.

fixedtext = JOINCHARS(textvar)
SHOW fixedtext

These statements produce the following output, in which the value of textvar is shown as a single line.

This is a variable that has a multiline text value.

When the actual number of bytes in the textvar variable's value exceeds the width of the fixedtext variable, then the value of textvar will be truncated when it is stored in fixedtext.

Assigning Values to Relations

You can assign values to a relation using a SET statement as illustrated in Example 10-114, "Assigning Values to a Relation". When executing the assignment statement, a loop is performed over the values in status for each dimension of the target relation and assigns a data value to the corresponding cell of the target relation.

You can assign values to a relation with a text dimension by assigning one of the following:

Assigning Values to Dimensions

The only time you use an = statement to assign a value to a dimension is when the result of a calculation in a model equation is numeric. In this situation, you can use the = operator to assign the results to a dimension value. However, equations (that is, expressions) in models differ in several ways from expressions used in other contexts. See "Rules for Equations in Models" for information on using the assignment statement within models. See the MAINTAIN command for information on how to add values to dimensions in all other cases.

Assigning Values to Dimension Surrogates

You assign values to a dimension surrogate with an = (SET) statement. For example, the following statements define the dimension surrogate storename, which is a TEXT type surrogate for the NUMBER type dimension store_id, assign a value to the fourth position of storename, and then report the value of the surrogate for the fourth value of store_id, which is 100.

DEFINE storename SURROGATE store_id TEXT
storename(storename 4) = 'Molly\'s Emporium'
REPORT W 25 storename(store_id 100)
 
STORENAME(STORE_ID 100)
-------------------------
Molly's Emporium

For example, when you define the INTEGER dimension surrogate intsurr for a NUMBER dimension numdim that has five values, then a report of intsurr produces the following.

INTSURR
-------
      1
      2
      3
      4
      5

Like a dimension, the values of a dimension surrogate must be unique. However, unlike a dimension, a dimension surrogate can have NA values, unless it is an INTEGER type. The same value can be a value of the dimension and of any of its surrogates.

Using QDRs with the Target of an Assignment Statement

You can use a QDR with the target of an = (SET) statement. This lets you assign a value to specific cells in a variable or relation.

The following example assigns the value 10200 to the data cell of the sales variable that is specified in the qualified data reference. When the variable named sales does not already have a value in the cell associated with Boston, Tents, and Jan99, then the value is assigned to the cell and thus it is added to the variable. When a value already exists in the cell, the value 10200 overwrites the previous value.

sales(market 'Boston' product 'Tents' month 'Jan99')= 1020

Expressions Dimensioned Conjoint Dimensions

When an expression is dimensioned by a conjoint dimension, Oracle OLAP uses the dimension's relationship to its base dimension values to assign data to the correct cells. You can set the values of a variable dimensioned by a conjoint dimension to an expression dimensioned by one of its base dimensions. The converse is also true. See "Compacting Your Data".

Rules for Equations in Models

The equations in a model use an OLAP DML assignment statement to assign values to variables or dimension values. Equations in models differ in several ways from equations used in other contexts in Oracle OLAP:

Dimension Status and Model Equations When a model contains an assignment statement to assigns data to a dimension value, then the dimension is limited temporarily to that value, performs the calculation, and then restores the initial status of the dimension.

Formatting Conjoint Dimension Values A special format is required when dimension-based equations refer to values of a conjoint dimension:

For example, assume that item.org is a conjoint dimension with base dimensions item and org. In this case, you use the following format to refer to values of item.org.

'<Expenses, Direct Sales>'

Formatting Text Dimension Values When dimension-based equations refer to text dimension values with embedded blanks or mixed upper- and lowercase letters, enclose the dimension value in single quotes. Use the exact upper- and lowercase spelling for the value.

For example, assume that a text dimension named lineitem contains a value with an embedded blank. In this case, you use the following format.

'Software Revenue'

Specifying DAY, WEEK, MONTH, QUARTER, YEAR Values for a Model Equation When a model equation is based on a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you must use the dimension's VNF (value name format), rather than a date format, to specify the dimension's values. In addition, the VNF must format dimension values as follows:

When the WEEK, MONTH, QUARTER, YEAR dimension of type does not have a VNF assigned to it, you can use the default VNF for the dimension. The entry for the VNF command lists the default VNF for each of these dimension types, and it explains how to assign a VNF to a dimension.

The default VNF for DAY dimensions is not acceptable because it specifies a digit as the first character of each dimension value. For a DAY dimension, specify the dimension name and enclose the value in parentheses and single quotes.

For example, for a DAY dimension named daydim, you can use the following format.

daydim('01jul97')

Formatting INTEGER Dimension Values When dimension-based equations refer to values of an INTEGER dimension, enclose the dimension value in single quotes.

For example, for an INTEGER dimension named intdim, use the following format to refer to the first dimension value.

'1'

When the model is based on more than one dimension, the model compiler might not be able to correctly identify the dimension to which a literal INTEGER value belongs. In this case, specify the name of the dimension and enclose the value in parentheses and single quotes as described in "Formatting Ambiguous Dimension Values".

Formatting Ambiguous Dimension Values In some cases the model compiler might be unable to correctly identify the dimension to which a dimension value belongs. For instance, this can happen under the following circumstances:

In cases such as these, you can avoid ambiguity in model-based equations by following these rules:

For example, for an INTEGER dimension named intdim, use the following format to refer to the first dimension value.

intdim('1')

Examples

Example 10-113 Assigning Values to a Variable

For the first example, suppose you have defined two variables, units and price, that are both dimensioned by product. The following example calculates dollar sales (units times price) for each value in the product dimension. Using an assignment statement, it stores the result in the variable sales, which is also dimensioned by product.

sales = units*price

For another example, assume the choicedesc variable is dimensioned by choice. Before you enter data for the variable, the cells of the variable contain only NA values.

CHOICE           CHOICEDESC
-------------- --------------------
Report         NA
Graph          NA
Analyze        NA
Data           NA
Quit           NA

Suppose you initialize the choicedesc variable using the following statement.

choicedesc =  JOINCHARS ('Description for ' choice)

Now all of the choicedesc cells of the variable contain the appropriate values.

CHOICE             CHOICEDESC
-------------- -------------------------
Report         Description for Report
Graph          Description for Graph
Analyze        Description for Analyze
Data           Description for Data
Quit           Description for Quit

The next example shows an expression that is dimensioned by time, product, and district and is assigned to a new variable. The expression calculates a 2002 sales plan based on unit sales in 2001.

DEFINE units.plan INTEGER <month product district>
LIMIT month TO 'DEC02'
units.plan = LAG(units 12 month) * 1.15

Example 10-114 Assigning Values to a Relation

Assume that your analytic workspace contains the following definitions for a hierarchical dimension for Geography named geog and a relation named geog_parentrel that contains values that represent the child-parent relationships in the Geography hierarchy.

DEFINE geog DIMENSION TEXT
DEFINE geog_parentrel RELATION geog <geog>
 

You can use the following MAINTAIN ADD statements to populate the hierarchical dimension.

" Populate the geog dimension with values for all levels
MAINTAIN geog ADD 'North America' 'Europe' 'United States' 'Canada' 'France' 'Germany'
MAINTAIN geog ADD 'Massachusetts' 'California' 'Quebec' 'Ontario' 
MAINTAIN geog ADD 'Boston''Springfield' 'San Francisco''Los Angeles' 'Toronto' 'Ottawa' 
MAINTAIN geog ADD 'Montreal''Quebec City' 'Paris' 'Marseilles' 'Bonn' 'Berlin'
 

You can use the following assignments statements to populate geog_parentrel. Note that you must limit geog to the appropriate values before you assign values to geog_parentrel.

"  Limit geog (and therefore geog_parentrel) to countries and assign 
"   parent value (continent name) to those countries in geog_parentrel
LIMIT geog to 'United States' 'Canada'
geog_parentrel = 'North America'
LIMIT geog to ALL
LIMIT geog to 'France' 'Germany'
geog_parentrel = 'Europe'
 
"  Limit geog (and therefore geog_parentrel) to states or provinces and assign 
"   parent value (country name) to those states or provinces in geog_parentrel
LIMIT geog to ALL
LIMIT geog to 'Massachusetts' 'California'
geog_parentrel = 'United States'
LIMIT geog to ALL
LIMIT geog to 'Quebec' 'Ontario'
geog_Chapter 9, "OLAP DML Commands: A-G" = 'Canada'
 
"  Limit geog (and therefore geog_parentrel) to cities and assign
"   parent value (state, province, or country) to those cities in geog_parentrel
LIMIT geog to ALL
LIMIT geog to 'Boston' 'Springfield' 
geog_parentrel = 'Massachusetts'
LIMIT geog to ALL
LIMIT geog to 'San Francisco' 'Los Angeles' 
geog_parentrel = 'California'
LIMIT geog to ALL
LIMIT geog to 'Montreal' 'Quebec City'
geog_parentrel = 'Quebec'
LIMIT geog to ALL
LIMIT geog to 'Toronto' 'Ottawa' 
geog_parentrel = 'Ontario'
LIMIT geog to ALL
LIMIT geog to 'Paris' 'Marseilles' 
geog_parentrel = 'France'
LIMIT geog to ALL
LIMIT geog to 'Bonn' 'Berlin'
geog_parentrel = 'Germany'
LIMIT geog to ALL
 

A report of geog_parentrel shows the values have been assigned.

COLWIDTH = 20
REPORT geog_parentrel
REPORT geog_parentrel
 
GEOG                GEOG_PARENTREL
---------------- --------------------
North America    NA
Europe           NA
United States    North America
Canada           North America
France           Europe
Germany          Europe
Massachusetts    United States
California       United States
Quebec           Canada
Ontario          Canada
Boston           Massachusetts
Springfield      Massachusetts
San Francisco    California
Los Angeles      California
Toronto          Ontario
Ottawa           Ontario
Montreal         Quebec
Quebec City      Quebec
Paris            France
Marseilles       France
Bonn             Germany
Berlin           Germany

Example 10-115 Using a Qualified Data Reference

This example uses an assignment statement with a qualified data reference to assign values to the variable budget. The values assigned to one budget line item (Net.Income) are calculated as the difference between two other line items (Opr.Income and Taxes), so you have to use a qualified data reference to obtain the correct data values.

budget(line Net.Income)= budget(line Opr.Income) - budget(line Taxes)

Example 10-116 Assigning Values to Variables with Composites

To have data assigned from sales only into existing data cells of sparse_sales, whose associated dimension values are in status, use the following statement.

sparse_sales = sales ACROSS SPARSE<product market>

The ACROSS keyword is particularly helpful when the source expression is a single value. When there are no limits on the dimensions of sparse_sales, then an assignment statement like the following creates cells for every combination of dimension values because there are no cases where the source expression is NA.

sparse_sales = 0

This defeats the purpose of a dimensioning a variable with a composite.

In contrast, the following statement sets only existing cells of sparse_sales to 0 (zero).

sparse_sales = 0 ACROSS SPARSE<product market>

Example 10-117 Compacting Your Data

Suppose you only sell some of your products in each district. You currently have a variable sales that has data for certain combinations of districts and products and NA values for the rest. You can create a dense array of sales data by defining a composite or a conjoint dimension and using it as a dimension of a new variable. Use an assignment statement to assign the data directly to the new variable. When the values of the composite or conjoint dimension include all the combinations with data, you can then delete the original variable and save space in the analytic workspace.

DEFINE proddist DIMENSION <product district>
MAINTAIN proddist ADD <'Tents' 'Boston'> <'Canoes' 'Seattle'> -
   <'Sportswear' 'Atlanta'>
DEFINE sales.dense DECIMAL <month proddist>
sales.dense = sales
LIMIT month TO FIRST 4

Issuing a REPORT sales.dense statement produces the following output.

----------------SALES.DENSE----------------
-----PRODDIST------ -------------------MONTH-------------------
PRODUCT    DISTRICT   Jan95      Feb95      Mar95      Apr95
-------- ---------- ---------- ---------- ---------- ----------
Tents      Boston    32,153.52  32,536.30  43,062.75  57,608.39
Canoes     Seattle   64,111.50  71,899.23  83,943.86  14,383.90
Sportswear Atlanta  114,446.26 123,164.92 138,601.64 141,365.66

An alternative method would be to use a composite instead of a conjoint dimension. In this case, you could use the following statements.

DEFINE sales.compact DECIMAL <month SPARSE <product district>>
sales.compact = sales

Oracle OLAP automatically creates the unnamed composite when you define sales.compact, and it automatically adds dimension value combinations to the composite when you use an assignment statement. Oracle OLAP creates dimension value combinations only for the non-NA values of sales.