3.2 Using Workspace Objects in Expressions

You can use an analytic workspace data object in an expression by specifying its name as described in "Syntax for Specifying an Object in an Expression". When calculating the expression, Oracle OLAP uses the data in the specified object as described in "How Objects Behave in Expressions".

3.2.1 Syntax for Specifying an Object in an Expression

You can specify an analytic workspace object in an expression using the following syntax.

[[schema-name.]analytic-workspace-name!]object-name

where:

schema-name is the name of the schema in which the analytic workspace was defined when it was created. By default, a workspace is created in the schema for the database user ID of the user issuing the AW CREATE statement. In almost any DML statement, you can specify the full name of a workspace (for example, Scott.demo). When the workspace is in your schema, you can specify only the name (for example, demo) instead.

analytic-workspace-name is the name of the workspace that contains the desired object. By specify the analytic workspace name along with the object name you create a qualified object name (QON) for the object. Using a QON for an object is recommended except in those sitatuations described in "When Not to Use Qualified Object Names".

You can specify the value for analytic-workspace-name in any of the following ways:

The name of an analytic workspace. A workspace name is assigned when a workspace is created with an AW CREATE statement.
The alias name of an analytic workspace. An analytic workspace alias is an alternative name for an attached analytic workspace. You can assign or delete an alias with an AW ALIASLIST statement. An alias is in effect from the time it is assigned to the time that the workspace is detached (or until the alias is deleted). Therefore, each time you attach an unattached workspace, you must reassign its aliases.

One reason for assigning an alias is to have a short way to reference a workspace that belongs to a schema that is not yours. For example, you can use the alias in qualified object names and statements that reference such a workspace. Another reason for assigning an alias is to write generic code that includes a reference to a workspace but does not hard-code its name. With the alias providing a generic reference, you can assign the alias and run the code on different workspaces at different times.
Within anm aggregation specification, model, or program, you can use THIS_AW to qualify an object name. When Oracle OLAP compiles an object, it interprets any occurrence of THIS_AW as the name of the workspace in which the object is being compiled. Thus if you have a workspace named myworkspace that contains a program named myprog and a variable named myvar, Oracle OLAP interprets a statement myvar=1 as though it was written myworkspace!myvar=1. Within a program, you can retrieve the value of THIS_AW using the THIS_AW option.

When you do not specify a value for analytic-workspace-name, Oracle OLAP assumes that the specified object is in the urrent analytic workspace. The current analytic workspace is the first analytic workspace in the list of the active analytic workspaces that you view with an AW LIST statement. You can retrieve the name of the current analytic workspace by using the AW function with the NAME keyword.

Note:

Your session does not have to have a current analytic workspace. When you start Oracle OLAP without specifying an analytic workspace name, then the EXPRESS analytic workspace is first on the list. However, in this case, the EXPRESS analytic workspace is not current; there is no current analytic workspace until you specify one with the AW command.

object-name is the name of the object.

Objects with the same name in different workspaces are treated as completely separate objects, and no similarity or relationship is assumed to exist between them. Any OLAP DML language restrictions that apply between objects in different workspaces apply even when the objects have the same name. For example, you cannot dimension an object in one workspace by a dimension that resides in another workspace, even when both workspaces have dimensions with the same name.

3.2.1.1 Considerations When Creating and Using Qualified Object Names

Although the use of qualified object names for objects is typical, there are a number of considerations to keep in mind:

There are some situations where you cannot use a qualified object name or do not need to use a qualified object name. See "When Not to Use Qualified Object Names" for more information
Before you use ampersand substitution when creating a qualified object name you need to understand how and when the substitution occurs. See "Using Ampersand Substitution for Workspace and Object Names" for more information.
Special considerations apply when passing a qualified object name as an argument to a program. See "Passing Qualified Object Names to Programs" for more information.

3.2.1.1.1 When Not to Use Qualified Object Names

Generally it is good practice to use a qualified object name in an expression. However, there are some situations where you cannot use a qualified object name or when a qualified object name is not necessary:

The following objects cannot have qualified object names:
- An object that is local to a particular program because it was created by the ARGUMENT or VARIABLE command.
- The NAME dimension of any given workspace. When you reference the NAME dimension, Oracle OLAP always uses the NAME dimension of the current workspace.
You do not need to use a qualified object name in the following circumstances:
- In the qualifiers of a qualified data reference (QDR). Only the object being qualified needs to be named with a qualified object name. Any unqualified names are assumed to apply to objects in the same workspace as the object being qualified.
- In an unnamed composite, when you specify one base dimension as a qualified object name, then all the others are assumed to come from the same workspace.
- In a named composite, when the name is a qualified object name then its base dimensions are assumed to come from the same workspace.
- In a model, when you specify the solution variable as a qualified object name, then all the dimensions named in DIMENSION (in models) statements are assumed to come from the same workspace.

3.2.1.1.2 Using Ampersand Substitution for Workspace and Object Names

The workspace name, or the object name, or both can be supplied using ampersand substitution. However, take care when using a qualified object name with ampersand substitution because Oracle OLAP parses the qualified object name (with its exclamation point) before it resolves the ampersand reference. For example, in the expression &awname!objname, the ampersand (&) applies to the entire qualified object name, not just to the workspace name.

3.2.1.1.3 Passing Qualified Object Names to Programs

When you pass a qualified object name as an argument to a program and you use the ARGUMENT command and the ARCTAN2, ARGFR, and ARGS functions, the entire qualified object name is considered to be a single argument. Its component parts are not passed separately.

3.2.2 How Objects Behave in Expressions

Table 3-1 summarizes how Oracle OLAP uses the data in an object used as an argument in an expression.

Table 3-1 Objects in Expressions

Object	Use in Expressions
Variables	As an array of data. For example, as the target or source expression in an assignment statement as outlined in "Using Objects in Assignment Statements".
Relations	As an array of data. For example, as the target or source expression in an assignment statement as outlined in "Using Objects in Assignment Statements". When you use a relation in a text expression, the relation value is referenced as a text value. The values of the related dimension that is contained in the relation are converted into text, and you can use these values in an expression. You can also compare a text literal to a relation. When you use a relation in a numeric expression, the relation value is referenced by its position (an INTEGER) in its related dimension array. You can use this numeric value in an expression. The position number is based on the default status list of the dimension, not the current status list of the dimension.
Dimensions	As a one-dimensional array of data. When you use a `TEXT` dimensionvalue in a numeric expression or compare values in a non-numeric dimension, Oracle OLAP uses the `INTEGER` position number of the value in the array (as based on the default status list) rather than the value itself. Note: A dimension cannot be the target of an assignment statement; add values to dimensions using MAINTAIN.
Composites	You can use a composite wherever you can use a dimension. When you refer to an unnamed composite in an expression , specify `SPARSE <``dimensions``...>` ; for example, `SPARSE <product month>`.
Valuesets	As a list of dimension values.
Dimension surrogates	As a one-dimensional array. When you use a `TEXT` surrogate value in a numeric expression or compare values in a non-numeric surrogate, Oracle OLAP uses the `INTEGER` position number of the value in the array (as based on the default status list) rather than the value itself. Note: A surrogate cannot be a participant object in any argument in a DEFINE statement that defines another object.
Formulas	As a sub-expression or as an expression in a statement.
Programs	For a program that does not return a value, use the program name as you would an OLAP DML command. For a program that returns a value, invoke the program the same way you invoke an OLAP DML command— use the program name in then expression and enclose the program arguments, if any, in parentheses.

3.2.3 Using Variables in Expressions

In expressions, a variable is referenced as an array containing values of the specified data type.

When you assign values to a variable or when you use REPORT or another statement that loops over the dimensions of a variable, the values of the fastest-varying dimension of the variable vary first. For example, for the opcosts variable that is dimensioned by month and city, when you view the variable as REPORT output, you see the data for all months for the first city before you see any data for the second city. In this case, month is the fastest-varying dimension because its values change before those of city. When you write programs that loop over a multidimensional variable in this way, try to maximize performance by matching the fastest-varying dimension with the inner loop.

Note:

When you use a variable as the solution variable in a model, the model executes most efficiently when the order of the dimensions in the definition of the solution variable matches the order of the dimensions in the DIMENSION commands in the model.

You can uniquely and completely select any item of data within a multidimensional variable by using a qualified data reference (QDR) to specify one value from each of the dimensions of the variable.

For example, when the opcosts variable is dimensioned by month and city, specifying Jan02 for the month dimension and Boston for the city dimension uniquely specifies a single cell in the variable.

3.2.4 Using Variables Defined with Composites in Expressions

In most cases, when you use OLAP DML statements with variables that are defined with composites, the statements treat those variables as if they were defined with base dimensions:

You can access a variable that is dimensioned by a composite by requesting any of the base dimension values.
The values of a composite that are in status are determined by the status of the base dimensions of the composite. Composites are not dimensions, and therefore, they do not have any independent status.

When you use the REPORT command or any other statement that loops over a variable that uses a composite, the default behavior is to evaluate all the combinations of the values of the base dimensions of the composite that are in status. Any combinations that do not exist in the composite display NA for their associated data.

For example, the following statements create a report for the East region that shows the number of coupons issued for sportswear from January through March 2002. Since no coupons were issued in March 2002, the report displays NA in that column.

LIMIT month TO 'Jan02' 'Feb02' 'Mar02'
LIMIT market TO 'East'
LIMIT product TO 'Sportswear'
REPORT coupons
  
MARKET: EAST
               ------------COUPONS-------------
               -------------MONTH--------------
PRODUCT          Jan02      Feb02      Mar02
-------------- ---------- ---------- ----------
Sportswear          1,000      1,000         NA

However, for performance reasons, you can change the default looping behavior for statements such as REPORT, ROW, and the assignment statement (SET) so that they loop over the values in the composite rather than all of the base dimension values.