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


For each hierarchy of relation, the HIERDEPTH command calculates the depth of that hierarchy from the top of all of the hierarchies and stores that value in a previously-defined relation sometimes called the depth relation.


HIERDEPTH parent-rel [(qdr-list)] INTO depth-rel [INHIERARCHY inh-valueset ]



A child-parent self-relation for the hierarchical dimension. See "Parentrel Relation" for more information.


A list of QDRs that limits the values of parentrel. Specify the QDRs as described in "Syntax of a Qualified Data Reference". When you do not specify a value for qdrlist, HIERHEIGHT uses the values of parentrel that are in current status.


A previously -defined relation that the HIERDEPTH command populates when it executes. This relation can have multiple dimensions; however, it must be dimensioned by the dimensions of parentrel and one other NUMBER dimension that represents the depth of the hierarchical dimension.

For example, assuming that parent-rel has two dimensions, dim1 and dim2, the following lines of code define a NUMBER dimension named numdim with 3 values and, then, define a depth relation named depthrel which is dimensioned by dim1 and dim2 that has as its values the values of numdim.

DEFINE numdim dimension NUMBER(3)
DEFINE depthrel RELATION numdim <dim1 dim2>

The name of a valueset object whose values are the hierarchical dimension values to be considered when creating grouping ids. Values not included in the valueset are ignored. See "Inhier Valueset or Variable" for more information.


Example 10-3 Creating and Populating a Depth Relation for Hierarchies

Assume that your analytic workspace contains the geog, geog_hierlist, and geog_parentrel objects described in Example 9-145, "Using GROUPINGID Command to Populate a Relation with Grouping Ids". You can create a depth relation for the hierarchies represented by these objects by issuing the following DML code.

"   Define the number dimension for the depthrel
"   Define the depth relation
"   Populate the depth relation
HIERDEPTH geog_parentrel INTO depthrel
REPORT DOWN geog depthrel
GEOG               Political_Geog           Sales_Geog
-------------- ---------------------- ----------------------
Boston         3                      3
Springfield    3                      3
Hartford       3                      3
Mansfield      3                      3
Montreal       3                      3
Walla Walla    3                      3
Portland       3                      3
Oakland        3                      3
San Diego      3                      3
MA             2                      2
CT             2                      2
WA             2                      2
CA             2                      2
Quebec         2                      2
East           0                      1
West           0                      1
All Regions    0                      0
USA            1                      0
Canada         1                      0
All Countries  0                      0