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.

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.

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:

• A text value of the dimension.

• An INTEGER that represents the position of the dimension value in the default status list of the dimension.

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:

• In a model equation, you can use the name of a dimension value anywhere you would normally use the name of a variable. You can base calculations on a dimension value, and you can assign the results of a calculation to a dimension value. When an equation refers directly to one or more dimension values, it is called a dimension-based equation.

• You cannot use ampersand substitution in model equations.

• You can include a program as a component in a calculation only when it is used as a function.

• Within a single dimension-based equation, all the dimension values must belong to the same dimension.

• When you assign the results of a calculation to a dimension value, the results must be numeric.

• Each dimension on which the model equations are based must be listed in a DIMENSION statement. When the model contains an INCLUDE statement, the appropriate DIMENSION statements must be inherited from the included model. When the model does not contain an INCLUDE statement, it must contain the appropriate DIMENSION statements. When you compile or run the model, Oracle OLAP searches through the dimensions listed in explicit or inherited DIMENSION statement to identify the dimension to which each dimension value belongs.

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:

• Enclose the entire dimension value specification in angle brackets and then enclose this entire specification in single quotes; do not enclose the individual values in single quotes.

• Use the exact upper- and lowercase spellings for the base dimension values.

• When the specification includes a text value with an embedded blank, you must separate the dimension values with commas.

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:

• The value must start with a letter.

• The value can only contain letters, digits, underscores, and periods.

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:

• Two or more dimensions have a dimension value with the same name.

• A DAY dimension uses the default VNF (which starts with a digit).

• An INTEGER value could be interpreted either as a position within a dimension or as a literal INTEGER value of a dimension.

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

• Enclose the dimension value in single quotes.

• Enclose the quoted value in parentheses.

• Precede the parentheses with the name of the dimension.

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

intdim('1')

Examples

sales = units*price

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

choicedesc =  JOINCHARS ('Description for ' choice)

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

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

DEFINE geog DIMENSION TEXT
DEFINE geog_parentrel RELATION geog <geog>
 

" 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'
 

"  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
 

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

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

sparse_sales = sales ACROSS SPARSE<product market>

sparse_sales = 0

sparse_sales = 0 ACROSS SPARSE<product market>

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

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

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