|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
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".
You can specify an analytic workspace object in an expression using the following syntax.
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,
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
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.
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.
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:
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.
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.
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.
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".
|Dimensions||As a one-dimensional array of data. When you use a |
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
|Valuesets||As a list of dimension values.|
|Dimension surrogates||As a one-dimensional array. When you use a |
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.|
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
For example, when the
opcosts variable is dimensioned by
Jan02 for the
month dimension and
Boston for the
city dimension uniquely specifies a single cell in the variable.
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.