| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
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.
|
See also: You can use UNRAVEL in conjunction with an assignment statement 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 is optional. It is an older command form of this functionality, and is included for compatibility.
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 21-6, "Using Objects in Assignment Statements".
The = (assignment or equal) operator assigns one or more values to a variable, option, or relation.
The source of the data values to be assigned to the object, see Table 21-6, "Using Objects in Assignment Statements"
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:
Oracle OLAP determines which single value of A (sometimes called a cell) is going to be set.
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.
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 21-6, "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 21-6 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 21-24, "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 MAINTAIN 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 (in models) 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
Example 21-23 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 21-24 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_parentrel = '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 21-25 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 21-26 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 21-27 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.
