HIERHEIGHT command

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 function.

Syntax

HIERHEIGHT familyrelation [(qdrlist)] INTO{hierheight-relation -

[USING level-relation[A | D]] [INHIERARCHY { inh-variable| inh-valueset}]

Arguments

family-relation

A child-parent self-relation for the hierarchical dimension. This relation can have multiple dimensions; however, one of the dimensions of family-relation must be the hierarchical dimension. The values of the family-relation are the values of the hierarchical dimension that is the parent of each set of dimension values

qdrlist

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

hierheight-relation

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 family-relation and one other dimension that represents the levels of the hierarchical dimension. The actual constuct 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 HIERHEIGHT 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 HIERHEIGHT 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 hierheight-relation is populated before the HIERHEIGHT command executes, the command depopulates it before computing new values.

level-relation

A relation that is a 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 relation are values of a dimension that represents the levels of the hierarchy. This dimension typically is a TEXT or ID dimension that has the names of the levels as values.

A

Ascending order.

D

Descending order. (Default)

inh-variable

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.

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.

Notes

HIERHEIGHT with the OLAP_TABLE Function

Typically, you use the HIERHEIGHT command when you are preparing an analytic workspace for access using the OLAP_TABLE function.

Examples

Example 14-10 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 the HIERHEIGHT command as illustrated in the following statement to populate the object.

HIERHEIGHT geog.parent INTO geog.familyrel USING geog.levelrel

By issuing the REPORT command, 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