Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


The KEY function returns the value of the specified base dimension for a value of a conjoint dimension or a composite.

Return Value

The return value depends on the data type of the specified base dimension.





An expression that specifies a value of a conjoint dimension or a composite. When you specify the conjoint dimension itself, KEY uses the first value in status. When you specify the composite itself, KEY uses the first value in status for every base dimension in the composite.


An expression that specifies the name of a base dimension of the previously specified conjoint dimension or composite for which you want to know the dimension value.


Example 7-139 Reporting with a Conjoint

Suppose you want to produce a report of data dimensioned by a conjoint dimension. You can label each row with the base values of each conjoint dimension value with the KEY function. Each base value occupies its own column and you have more control over the layout.

The following program excerpt loops over the conjoint dimension proddist, whose values are a combination of product and district. Assume also that there is a variable named dsales which is dimensioned by proddist.

DEFINE proddist DIMENSION <product district>
LD Conjoint dimension made up of combinations of product and district values
DEFINE dsales VARIABLE DECIMAL <month proddist>
LD Sparse sales data made dense by dimensioning by conjoint dimension proddist

The program excerpt shows dsales for three months. The base values of the conjoint dimension value each occupy their own column. For contrast, the second loop uses the conjoint dimension directly, without the KEY function. The conjoint dimension values are displayed in one column, with angle brackets.

FOR proddist
  ROW KEY(proddist district) KEY( proddist product) ACROSS month: dsales
FOR proddist
  ROW W 25 proddist ACROSS month: dsales

The program produces the following report.

Boston         Tents       32,153.52  32,536.30  43,062.75
Denver         Canoes      45,467.80  51,737.01  58,437.11
Atlanta        Sportswear 114,446.26 123,164.92 138,601.64
<Tents, Boston>            32,153.52  32,536.30  43,062.75
<Canoes, Denver>           45,467.80  51,737.01  58,437.11
<Sportswear, Atlanta>     114,446.26 123,164.92 138,601.64