| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-03 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-03 |
|
|
View PDF |
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.]analytic-workspace-name!]object-name
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).
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 situations 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 an 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 current 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.The name of the object unless the object is an unnamed composite. When the object is an unnamed composite, use the following syntax.
SPARSE <basedims....>
For the basedims argument, specify the names of the dimensions, separated by spaces, for which the unnamed composite was created. For an example of using an unnamed composite in an OLAP DML statement, see "Reporting Data Dimensioned by Composites".
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 an ARGUMENT statement 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-4 summarizes how Oracle OLAP uses the data in an object used as an argument in an expression.
Table 3-4 Objects in Expressions
| Object | Use in Expressions |
|---|---|
|
Variables |
As a one-dimensional or multi-dimensional array of data, depending on its definition. For example, as the target or source expression in an assignment statement. See also: "Using Variables and Relations in Expressions" , "Using Objects Dimensioned by Composites in Expressions", and "Using Objects in Assignment Statements". |
|
Relations |
As a one-dimensional or multi-dimensional of data, depending on its definition. For example, as the target or source expression in an assignment statement as outlined in "Using Objects in Assignment Statements".
See also: "Using Variables and Relations in Expressions" , "Using Related Dimensions in Expressions". |
|
Dimensions |
As a one-dimensional array of data. When you use a See also: "Specifying a Value of a CONCAT Dimension" and "Using Related Dimensions in Expressions". |
|
Composites |
You can use a composite wherever you can use a dimension. See also: "Specifying a Value of a Composite" . |
|
Valuesets |
As a list of dimension values. See also: "Using Variables and Relations in Expressions" and "Using Objects Dimensioned by Composites in Expressions". |
|
Dimension surrogates |
As a one-dimensional array. A surrogate provides an alternative set of values for a dimension. When you use a Note: You can use a surrogate rather than a dimension in a model, in a LIMIT command, in a qualified data reference, or in data loading with statements such as FILEREAD, FILEVIEW, SQL FETCH, and SQL IMPORT. 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 function— use the program name in then expression and enclose the program arguments, if any, in parentheses. |
In most cases, you refer to the value of a dimension merely by specifying the value following the conventions for the datatype of the value. For example, assume that you have a TEXT dimension named geog. You can add the value "World" to the dimension by issuing the following statement.
MAINTAIN geog ADD 'World'
Note, however, that when you use a TEXT dimension value 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.
Special considerations apply to specifying the values of composites and concat dimensions.
You can specify a value of a composite in the following ways:
By specifying a set of values of the base dimensions of the composite using the following syntax.
{composite_name | SPARSE} {<base_dimension_name base_dimension_value }, ...>
(Named composites only) By specifying just the values of the composite using the following syntax.
composite_name <base_dimension_value ...>
where base_dimension_value is a set of values of the base dimensions, in the order in which they were defined in the composite, separated by spaces.
Once you have defined a unique CONCAT dimension, you can refer to its values simply by specifying the values of the base dimensions.
However, you must specify a value of a nonunique CONCAT dimension as a concatenation of the name of the base dimension and the base dimension value separated by a colon (:) and a space and enclosed in angle brackets(<>). In an expression, use the following format.
<BASE_DIMENSION_NAME: base_dimension value>
For example, assume that you have defined the base dimensions named city and state and, a CONCAT dimension for them named geog. When you report on the geog dimension, the values of geog include the names of the base dimensions along with the values.
DEFINE city DIMENSION TEXT DEFINE state DIMENSION TEXT DEFINE geog DIMENSION CONCAT(city state) MAINTAIN city ADD 'New York' MAINTAIN state ADD 'New York' REPORT geog GEOG ----------------------------------- <CITY: New York> <STATE: New York>
The syntax of some OLAP DML statements (for example, some variations of the LIMIT command) include two dimension arguments referred to as a dimension, and a related dimension. Other OLAP DML statements (for example, AVERAGE, ANY, COUNT, CUMSUM, NONE, LARGEST, SMALLEST, and TOTAL) allow you to specify the dimensionality of the result in terms of a related dimension. In these contexts, the related dimension is any dimension that shares a relation with another dimension.
Even though the value that you specify for the arguments in these statements is the name of a dimension, in actuality Oracle OLAP uses a relation between the dimensions to perform its calculations. When the two dimensions share only one relation, the behavior is clear. Oracle OLAP performs the calculation based on the values in that relation.
However, when two dimensions share more than one relation, then the behavior is less clear. In some cases, as with the LIMIT command (using LEVELREL) command, you can specify the shared relation you want Oracle OLAP to use. In other cases, the statement syntax does not allow you to specify the name of a relation. In this case, Oracle OLAP chooses among the multiple relations as described in "How Oracle OLAP Chooses Between Multiple Relations".
In expressions, a variable is referenced as an array containing values of the specified data type. A relation is referenced as an array containing values of the specified dimension. In most other respects, variables and relations (both typically multidimensional objects) share the same characteristics.
When you assign values to a variable or when you use REPORT or another statement that loops over the dimensions of a variable or relation, the values of the fastest-varying dimension of the object 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 object 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 theDIMENSION 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. (See "Specifying a Single Data Value in an Expression" for more information on QDRs.)
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.
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 a REPORT statement 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.
