|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
In the OLAP DML, when you want to calculate against a subset of data, you can specify the desired subset in one of the following ways:
Specify what dimension values (and, therefore, what variable values) are currently accessible or "in status" to all OLAP DML statements and expressions. For more information, see "Working with Dimension Status".
Within an expression, specify a single value or a subset of values. For more information, see "Specifying a List of Dimension Values for an Expression or Subexpression" or "Specifying a Single Data Value in an Expression".
The current status list of a dimension is an ordered list of currently accessible values for the dimension. Values that are in the current status list of a dimension are said to be "in status." The current status list of a dimension determines the selection of the data from all of the objects that are dimensioned by it.
For dimensions, only those dimension values that are in the current status list are accessed. For dimensioned objects, only those data values that are indexed by dimension values in the current status list are accessed. As a loop is performed through a dimensioned object, the order of the dimension values in the current status list is used to determine the order in which the values of the object are accessed.
Important:Whether or not a dimension value is in status merely restricts your view of the value during a given session; it does not permanently affect the values that are stored in the analytic workspace.
A dimension and any surrogate for that dimension share the same status. Setting the status of a dimension surrogate sets the status of its dimension and setting the status of a dimension sets the status of any dimension surrogates for it. In Part II of this manual, references to dimensions apply equally to dimension surrogates, except where noted. Also the phrase "setting status" includes assigning values to a valueset as well as setting the current status of a dimension. Composites are not dimensions, and therefore they do not have any independent status. The values of a composite that are "in status" are determined by the status of the base dimensions of the composite. In general, when statements deal with objects defined with composites, the default behavior is to treat those objects as if no SPARSE keyword or named composite had been used when the object was defined.
When you first attach an analytic workspace, the current status list of each dimension consists of all of the values of the dimension that have read permission, in the order in which the values are stored. This list of values is called the default status list for the dimension.
You can change the current status list for a dimension by using:
The LIMIT command to change the values and the order of the values in the current status list of a dimension.
The SORT command to arrange the order of values in the current status list of a dimension.
The PERMIT command to change the read permissions for dimension values.
You can change the default status list of a dimension in the following ways:
The OLAP DML provides a number of statements that you can use to identify and retrieve the status of dimension values These statements are listed in Table A-19, "Dimension and Composite Operation Statements".
When you want to save the current status for use in any session, then use a named valueset. Use a DEFINE VALUESET statement to define the valueset.
When you want to save, access, or update the current status for use in the current session, then use a named context. Use the CONTEXT command to define the context.
Using the CHGDIMS function, you can limit one element of an expression to only those values that are dimensioned by the specified dimension values. Using the CHGDIMS function in this manner limits the dimension to the specified values for the calculation without the current status of the dimension.
A qualified data reference (QDR) is a way of limiting one or more dimensions of an expression to a single value. QDRs are useful when you want to specify a single value without changing the current status. Using a QDR, you can qualify a dimension (which enables you to specify one dimension value in an expression) or one or more dimensions of a variable or relation.
A qualified data reference takes the following form.
expression(dimname1 dimexp1 [, dimname2 dimexp2. . .])
The dimname argument is the name of one of the dimensions, or a dimension surrogate of the dimension, of the expression and the dimexp argument is one of the following:
A value of dimname.
A text expression whose result is a value of dimname.
A numeric expression whose result is the logical position of a value of dimname.
A relation of dimname.
Note:To qualify a complex expression, use the QUAL function.
The QDR can temporarily limit a dimension of the variable by selecting one specified value of the dimension. This value can be outside the current status.
The QDR can replace a dimension of the variable with a less aggregate related dimension when you supply the name of an appropriate relation as the qualifier. The dimension is temporarily replaced by the dimension(s) of the relation.
For example, the variable
sales has three dimensions,
district. You might want to compare total sales in Boston to the total sales in all cities. In a single statement, you want
district to be limited to two different values:
For the numerator of the expression, you want the status of
district to be
For the denominator of the expression, you want the status of
district to be
The following statement lets you calculate this result by using a QDR.
SHOW sales(district 'Boston')/TOTAL(sales)
You can qualify more than one of the dimensions of a variable. For example, when you qualify all the dimensions of the
sales variable by specifying one dimension value of each dimension, then you narrow
sales down to a single–cell value.
To fetch sales for
Seattle, use the following QDR.
SHOW sales(month 'Jun02', product 'Tents', district 'Seattle')
This statement fetches a single value.
You can use a qualified data reference with the target expression of an assignment (SET) statement. This lets you assign a value to a specific cell in a data object.
The following example assigns the value 10200 to the data cell of the
sales composite that is specified in the qualified data reference. When the composite named
sales does not already have a value for the combination
Tents, then this value combination is added to the composite, thus adding the data cell.
sales(market 'Boston' product 'Tents' month 'Jan99')= 10200
When you use a relation as the qualifier in the QDR, you replace a dimension of the variable with the dimension or dimensions of the relation. The relation must be related to the dimension that you are qualifying, and it must be dimensioned by the replacement dimension.
Example 3-4 Replacing a Dimension in a Variable
Suppose you have two variables,
quota, which are dimensioned by
district. A third variable,
mgr, is dimensioned by
division. You also have a relation between
product. These objects have the following definitions.
DEFINE sales VARIABLE DECIMAL <month product district> LD Sales Revenue DEFINE quota VARIABLE DECIMAL <month product district> DEFINE division.mgr VARIABLE TEXT <month division> DEFINE division.product RELATION division <product> LD Division for each product
The following statement produces the report following it.
REPORT division.mgr -------------------DIVISION.MGR---------------------- ----------------------MONTH-------------------------- DIVISION JAn02 Feb02 Mar02 Apr02 May02 Jun02 -------- -------- -------- -------- -------- -------- -------- Camping Hawley Hawley Jones Jones Jones Jones Sporting Carey Carey Carey Carey Carey Musgrave Clothing Musgrave Musgrave Musgrave Musgrave Musgrave Wong
Suppose you want to obtain a report that shows the fraction by which sales have exceeded quota and you want to include the appropriate division manager for each product. You can show the division manager for each product by using the relation
product, which is related to
division and dimensioned by
product, as the qualifier. The QDR replaces the
division dimension with
product, so that it has the same dimensions as the other expression in the report
quota. The following statement produces the report following it.
REPORT DOWN month sales W 6 sales/quota W 8 HEADING - 'MANAGER' division.mgr(division division.product) DISTRICT: BOSTON -----------------------------PRODUCT------------------------------------ ----TEnts---- ---canoes---- --racquets--- --sportswear-- ---footwear--- Sales/ Sales/ Sales/ Sales/ Sales/ Month Quota Manager Quota Manager Quota Manager Quota Manager Quota Manager ------ ----- ------- ----- ------- ----- ------- ----- -------- ----- -------- Jan02 1.00 Hawley 0.82 Hawley 1.02 Carey 0.91 Musgrave 0.92 Musgrave Feb02 0.84 Hawley 0.96 Hawley 1.00 Carey 0.80 Musgrave 1.07 Musgrave Mar02 0.87 Jones 0.95 Jones 0.87 Carey 0.88 Musgrave 0.91 Musgrave Apr02 0.91 Jones 0.93 Jones 0.99 Carey 0.94 Musgrave 0.95 Musgrave ...
district relation is dimensioned by
district. When you qualify
district with the value
Seattle, then the value of the expression is the value of the relation for
Seattle. Because the QDR specifies one value of
district, the expression has a single–cell result.
The definition of
district is as follows.
DEFINE region.district RELATION region <district> LD The region for each district
The following statement displays the value
SHOW region.district(district 'Seattle')
You can use a QDR to qualify the dimension itself, which enables you to specify one dimension value in an expression. The following expression specifies one value of
district, the one contained in the single-cell variable
For a concat dimension, you can use a QDR to qualify the dimension by specifying a value from one of the base dimensions of the concat dimension. The following expression specifies one value of
reg.dist.ccdim, a concat dimension that has
district as its base dimensions. The costs variable is dimensioned by the
SHOW reg.dist.ccdim(district 'Boston')
The preceding expression produces the following result.
An ampersand character (
&) at the beginning of an expression substitutes the value of the expression for the expression itself in a statement.When you use an ampersand with a QDR, you must enclose the whole expression in parentheses when you want the variable to be qualified before the substitution is made.
Suppose you have a text variable named
myvar that is dimensioned by
reptype and that contains the names of variables. Remember that it is
myvar that is dimensioned by
reptype, not the variables named by
myvar. Therefore, you must use parentheses so that
myvar is qualified and the resulting value is used in the REPORT command.
REPORT &(myvar(reptype 'actual'))
When you do not use parentheses and the variable that is specified in
sales, then you get an error message that
sales is not dimensioned by
Sometimes you the syntax of a QDR is ambiguous and could either be misinterpreted or cause a syntax error. In this case, you can use the QUAL function to explicitly specify a qualified data reference (QDR).
Example 3-5 Using the QUAL Function
The following example first shows how you might view your data by limiting its dimensions, and then how you might view it by using QUAL.
Assume that you want to create a report of Cogs line items in the Sporting division from January 1996 through June 1996 with columns for month, the maximum value of either actual costs or budgeted costs or
MAX(actual,budget), actual costs for the month, and budgeted amount for the month. To create this report you can issue three LIMIT statements (one each for
division) and a REPORT statement.
LIMIT month TO 'Jan96' to 'Jun96' LIMIT line TO 'Cogs' LIMIT division TO 'Sporting' REPORT DOWN month W 11 MAX(actual,budget) W 11 actual W 11 budget DIVISION: SPORTING ---------------LINE---------------- ---------------COGS---------------- MAX(ACTUAL, MONTH budget) actual budget -------------- ----------- ----------- ----------- Jan96 287,557.87 287,557.87 279,773.01 Feb96 323,981.56 315,298.82 323,981.56 Mar96 326,184.87 326,184.87 302,177.88 Apr96 394,544.27 394,544.27 386,100.82 May96 449,862.25 449,862.25 433,997.89 Jun96 457,347.55 457,347.55 448,042.45
Now assume that you want a report on the same items and the same time period, but with only two columns: one for month and another for
MAX(actual,budget). In this case, you can issue merely one LIMIT statement for
month and use the QUAL function in your REPORT statement to limit calculation to Cogs line items in the Sporting division.
LIMIT month TO 'Jan96' to 'Jun96' REPORT HEADING 'For Cogs in Sporting Division' DOWN month - W 11 HEADING 'MAX(actual,budget)'- QUAL(MAX(actual,budget), line 'COGS', division 'SPORTING') For Cogs in Sporting MAX(ACTUAL, Division BUDGET) -------------- ----------- JAN96 287,557.87 FEB96 323,981.56 MAR96 326,184.87 APR96 394,544.27 MAY96 449,862.25 JUN96 457,347.55
When you attempt to produce the same report with standard QDR syntax, then an error is signalled.
REPORT HEADING 'For Cogs in Sporting Division' DOWN month - W 11 HEADING 'MAX(actual,budget)'- MAX(actual,budget) (line cogs, division sporting)
The following error message is produced.
ERROR: A right parenthesis or an operator is expected after LINE.