HIERCHECK

HIERCHECK checks the hierarchy in the specified relation or all of the relations of the specified aggmap to see if there is any circularity. You can also specify that HIERCHEK check the hierarchy for other conditions. HIERCHECK always stops in the first error message.

Note:

A hierarchical dimension's parent relation specifies the parent for each of the dimension's values. Circularity occurs when a dimension value has inadvertently been specified as its own ancestor or descendant in the parent relation. When you execute an AGGREGATE command that uses a parent relation with a loop, an error message will be returned when the loop is identified

Return Value

BOOLEAN

Syntax

As Command

HIERCHECK relation-name [STATUS|NOSTATUS|valueset-name] [MULTIPATH] [CONSISTENT]-

[BALANCED levelrelation-name]

HIERCHECK aggmap-name [MULTIPATH] [CONSISTENT]levelrelation-name]

Arguments

relation-name

A text expression indicating the name of the parent relation to be checked.

You can use OLAP DML statements to create a parent relation. To do so, you define a relation that relates a dimension to itself, and then you can specify the parent of each dimension value in the relation. This makes the dimension hierarchical.

aggmap-name

A text expression indicating the name of the aggmap. HIERCHECK checks all of the relations in the aggmap.

STATUS

Specifies that HIERCHECK uses the current status of the relation dimension.

valueset

Specifies the values of the relation dimension that HIERCHECK considers in status.

NOSTATUS

Specifies that HIERCHECK uses the default status of the relation dimension.

MULTIPATH

Specifies that HEIRCHECK checks whether there are multiple (more than one) paths from any child to its parent.

CONSISTENT

Specifies that HIERHECK checks whether the hierarchy is consistent. If the hierarchy is consistent, that means all nodes in the different hierarchies should have the same children.

BALANCED levelrel-name

Using the level relation identified by levelrel-name, specifies that HIERHECK checks to see if all of the following are true:

All of the elements of a hierarchy which have an NA level are either roots with no leaves or leaves.
All of the elements of a hierarchy at the same (non NA) level have the same depth from the root (roots) of the hierarchy.
Elements of a hierarchy for different levels (non NA) have different depth.

Notes

Why You Should Use HIERCHECK

It is a good strategy to use HIERCHECK at the time you build your hierarchies as a way to verify that they are valid. In other words, you should not attempt to roll up a variable's data unless you have already verified that its dimensions' hierarchies are structured correctly. For example, the AGGREGATE command uses HIERCHECK in order to prevent infinite looping once the statement has been executed.You should check a parent relation for loops after you set up the levels of a hierarchical dimension, before you load data into any variable that is dimensioned by the hierarchical dimension, or before you use the AGGREGATE command for the first time with a variable. Although it is possible to roll up a variable without first having checked the parent relations of all of its hierarchical dimensions with HIERCHECK, you should make it a practice to use HIERCHECK first.

Status When Using HEIRCHECK with an Aggmap

When there is any valueset inside a relation in aggmap, HIERCHECK uses this valueset to determine the status of the dimension of the relation. In all other cases, HIERCHECK uses the default status of the relation dimension.

For all dimensions other than relation dimensions, HIERCHECK uses the current status of the dimension.

Error Messages Triggered by HIERCHECK

When you use HIERCHECK, it signals an error when it finds a loop in the parent relation. The error message identifies the dimension values that are involved in the loop, the name of the hierarchy (referred to as the "extra dimension values") in which the loop occurs (when the parent relation has one or more named hierarchies), and the name of the parent relation in which the loop was found. When a parent relation has no loops, no message is displayed. See Example 16-9, "Checking for Loops".

Examples

Example 16-9 Checking for Loops

This example shows how to create a parent relation and check it for loops. You would begin by defining a dimension and adding values to it.

DEFINE geography DIMENSION ID
MAINTAIN geography ADD 'U.S.'
MAINTAIN geography ADD 'East' 'Central' 'West'
MAINTAIN geography ADD 'Boston' 'Atlanta' 'Chicago' 'Dallas' 'Denver' 'Seattle'

Next, relate the dimension to itself. The following statement defines a parent relation called GEOG.GEOG, which relates the GEOGRAPHY dimension to itself.

define geog.geog RELATION geography <geography>

You would then specify the hierarchy of the dimension values. In this example, there will be three levels in the hierarchy: country, regions, and cities. When you specify the hierarchy, you assign parent dimension values (such as East) to child dimension values (such as Boston) for every level except the highest level. To do this, you store values in the relation. First, group the children together with a LIMIT command, then assign a parent to those children.

LIMIT geography TO 'East' 'Central' 'West'
geog.geog = 'U.S.'
LIMIT geography TO 'Boston' 'Atlanta'
geog.geog = 'East'
LIMIT geography TO 'Chicago' 'Dallas'
geog.geog = 'Central'
LIMIT geography TO 'Denver' 'Seattle'
geog.geog = 'West'

Now you can check for loops in the parent relation geog.geog, as shown by the following statement.

HIERCHECK geog.geog

In this case, HIERCHECK produces no message output, which means there are no loops in geog.geog. It sets HIERCHK.LOOPFND to NO, and leaves HIERCHK.LOOPVALS and HIERCHK.XTRADIMS set to NA.

Now suppose the following mistake had been made in the storing of values in the relation.

LIMIT geography TO 'East' 'Central' 'West'
geog.geog = 'East'

The preceding statements inadvertently make East its own parent, which would cause an aggregation to loop infinitely. When you now check the geog.geog relation for loops, the following statement produces the following error message.

HIERCHECK geog.geog
ERROR: HIERCHECK has detected one or more loops in the hierarchy represented by GEOG.GEOG. The values involved are 'East'.