The HIERHEIGHT command populates a previously-defined relation with the values of a specified hierarchical dimension by level. Typically, you use the HIERHEIGHT command when you are preparing an analytic workspace for access using the OLAP_TABLE function.
To retrieve the value of a node (by level) for the value of a hierarchical dimension, use the HIERHEIGHT.
See also:
See "Familyrel Relation"HIERHEIGHT parentrelation [(qdrlist)] INTO{familyrel-
[USING level-relation | A | D] [INHIERARCHY { inh-variable | 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 HIERHEIGHT command populates when it executes. This relation can have multiple dimensions; however, it must be dimensioned by the dimensions of parentrel and one other dimension that represents the levels of the hierarchical dimension. The actual construct of the dimension that represents the levels of the hierarchical dimension varies depending on whether or not the HIERHEIGHT statement includes the USING phrase:
When the statement includes the USING phrase, the dimension that represents the levels of the hierarchical dimension is a dimension that contains the names of the levels.
When the statement does not include the USING phrase, the dimension that represents the levels of the hierarchical dimension is an INTEGER dimension that has as values the depth of the level.
When familyrel is populated before a HIERHEIGHT statement executes, the HIERHEIGHT statement depopulates it before computing new values.
Specifies that the relation is populated using the specified levelrel. See "Levelrel Relation" for more information.
Specifies that the relation is populated in ascending order.
(Default) Specifies that the relation is populated in descending order.
A BOOLEAN variable that is dimensioned by the hierarchical dimension and, when the hierarchical dimension is a multi-hierarchical dimension, by a dimension that is the names of the hierarchies. The values of the variable are TRUE when the dimension value is in a hierarchy and FALSE when it is not.
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.
HIERHEIGHT with the OLAP_TABLE Function
Typically, you use a HIERHEIGHT statement when you are preparing an analytic workspace for access using the OLAP_TABLE function.
Example 10-4 Creating a Relational Representation of a Geography Hierarchy
Assume that there is an analytic workspace named myaw that has a Geography hierarchy defined with analytic objects with the following definitions.
DEFINE geog.hierdim DIMENSION TEXT LD Hierarchy names for Geography hierarchies DEFINE geog.leveldim DIMENSION TEXT LD List of levels for GEOGRAPHY hierarchies DEFINE geography DIMENSION TEXT WIDTH 12 LD Values for the Geography hierarchies DEFINE geog.levelrel RELATION geog.leveldim <geography geog.hierdim> LD Level of each value in the Geography hierarchies DEFINE geog.parent RELATION geography <geography geog.hierdim> LD Child-parent relation for the Geography hierarchies DEFINE geog.familyrel RELATION geography <geography geog.leveldim geog.hierdim> LD Geography values by level and hierarchy
These objects have the following structures.
GEOGRAPHY
------------------
World
Americas
Canada
USA
Toronto
Montreal
Boston
LosAngeles
GEOG.HIERDIM
------------------
Standard
Consolidated
GEOG.LEVELDIM
------------------
World
Continent
Country
City
Consolidated
Continent
Consolidated
Country
------------GEOG.LEVELREL------------
------------GEOG.HIERDIM-------------
GEOGRAPHY Standard Consolidated
------------------ ------------------ ------------------
World World NA
Americas Continent Consolidated
Continent
Canada Country Consolidated
Country
USA Country Consolidated
Country
Toronto City NA
Montreal City NA
Boston City NA
LosAngeles City NA
-------------GEOG.PARENT-------------
------------GEOG.HIERDIM-------------
GEOGRAPHY Standard Consolidated
------------------ ------------------ ------------------
World NA NA
Americas World NA
Canada Americas Americas
USA Americas Americas
Toronto Canada NA
Montreal Canada NA
Boston USA NA
LosAngeles USA NA
To create a family relation of the Geography hierarchy you define an analytic workspace object with the following definition.
DEFINE geog.familyrel RELATION geography <geography geog.leveldim geog.hierdim> LD Geography values by level and hierarchy
Then you use a HIERHEIGHT statement as illustrated in the following statement to populate the object.
HIERHEIGHT geog.parent INTO geog.familyrel USING geog.levelrel
By issuing a REPORT statement, you can display the relational representations of both the Standard and Consolidated hierarchies of the geography dimension.
REPORT DOWN geography geog.familyrel
GEOG.HIERDIM: Standard
-------------------------------GEOG.FAMILYREL--------------------------------
--------------------------------GEOG.LEVELDIM--------------------------------
Consolidated Consolidated
GEOGRAPHY World Continent Country City Continent Country
------------ ------------ ------------ ------------ ------------ ------------ ------------
World World NA NA NA NA NA
Americas World Americas NA NA NA NA
Canada World Americas Canada NA NA NA
USA World Americas USA NA NA NA
Toronto World Americas Canada Toronto NA NA
Montreal World Americas Canada Montreal NA NA
Boston World Americas USA Boston NA NA
LosAngeles World Americas USA LosAngeles NA NA
GEOG.HIERDIM: Consolidated
-------------------------------GEOG.FAMILYREL--------------------------------
--------------------------------GEOG.LEVELDIM--------------------------------
Consolidated Consolidated
GEOGRAPHY World Continent Country City Continent Country
------------ ------------ ------------ ------------ ------------ ------------ ------------
World NA NA NA NA NA NA
Americas NA NA NA NA Americas NA
Canada NA NA NA NA Americas Canada
USA NA NA NA NA Americas USA
Toronto NA NA NA NA NA NA
Montreal NA NA NA NA NA NA
Boston NA NA NA NA NA NA
LosAngeles NA NA NA NA NA NA