Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Working with Expressions, 7 of 14
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 temporarily reference a value that is not in the current status. Using a QDR, you can qualify a dimension (which allows 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 of the expression, and the dimexp argument is one of the following:
To qualify a complex expression, you should use the QUAL function. For more information, see the entry for QUAL.
You can qualify any or all of a variable's dimensions using either of the following techniques:
In the demonstration analytic workspace, demo
, the variable SALES has three dimensions, MONTH, PRODUCT, and DISTRICT. You might want to compare total sales in Boston to the total sales in all cities. In a single FETCH command, you want DISTRICT to be limited to two different values:
The command below lets you fetch this data by using a QDR.
fetch sales(district 'BOSTON')/total(sales)
When you use a relation as the qualifier in the QDR, you replace a dimension of the variable with the dimension(s) of the relation. The relation must be related to the variable's dimension you are qualifying, and it must be dimensioned by the replacement dimension.
Suppose you have two variables, SALES and QUOTA, which are dimensioned by MONTH, PRODUCT, and DISTRICT. A third variable, DIVISION.MGR, is dimensioned by MONTH and DIVISION. You also have a relation between DIVISION and PRODUCT, called DIVISION.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 command below produces the report following it.
report division.mgr -------------------DIVISION.MGR---------------------- ----------------------MONTH-------------------------- DIVISION JAN95 FEB95 MAR95 APR95 MAY95 JUN95 -------- -------- -------- -------- -------- -------- -------- 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 DIVISION.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 "SALES / QUOTA." The command below 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 ------ ----- ------- ----- ------- ----- ------- ----- -------- ----- -------- JAN95 1.00 Hawley 0.82 Hawley 1.02 Carey 0.91 Musgrave 0.92 Musgrave FEB95 0.84 Hawley 0.96 Hawley 1.00 Carey 0.80 Musgrave 1.07 Musgrave MAR95 0.87 Jones 0.95 Jones 0.87 Carey 0.88 Musgrave 0.91 Musgrave APR95 0.91 Jones 0.93 Jones 0.99 Carey 0.94 Musgrave 0.95 Musgrave . . .
You can qualify more than one of the dimensions of a variable. For example, if 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 JUN95, TENTS, and SEATTLE, use the following QDR.
fetch sales(month 'JUN95', product 'TENTS', district 'SEATTLE')
This command fetches the single value: 113,806.48.
You can also use a QDR to qualify a relation (which is really a special kind of variable).
Suppose the REGION.DISTRICT relation is dimensioned by DISTRICT. If 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 REGION.DISTRICT is as follows.
DEFINE REGION.DISTRICT RELATION REGION <DISTRICT> LD The region for each district
The command below fetches the value: WEST.
fetch region.district(district 'SEATTLE')
You can use a QDR to qualify the dimension itself, which allows 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 MYDISTRICT.
district(district mydistrict)
You can use a qualified data reference with the target expression of the = command. 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. If the composite named SALES does not already have a value for the combination BOSTON and 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 an ampersand with a QDR, you must enclose the whole expression in parentheses if 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'))
If you do not use parentheses and the variable that is specified in MYVAR is SALES, then you will get an error message that SALES is not dimensioned by REPTYPE.
Sometimes you will find that 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).
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.
These commands produce the report shown below them.
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 consider how you might view the same figures for MAX(ACTUAL,BUDGET) without changing the status of LINE or DIVISION.
allstat 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
If 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.
For more information, see the following table.
IF you want documentation about . . . | THEN see . . . |
---|---|
limiting dimensions, |
|
qualified data references, |
"Specifying a Single Value for the Dimension of an Expression" |
ampersand substitution, |
|
the QUAL function, |
the entry for the function in the OLAP DML Reference |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|