HIERDEPTH

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.

Syntax

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

Arguments

parent-rel

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

qdr-list

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.

depth-rel

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>

inh-valueset

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.

Examples

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-144, "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 numdepth DIMENSION NUMBER (3)
"   Define the depth relation
"   Populate the depth relation
HIERDEPTH geog_parentrel INTO depthrel
REPORT DOWN geog depthrel
 
               ------------------DEPTHREL-------------------
               ----------------GEOG_HIERLIST----------------
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