---

HIERCHECK

The HIERCHECK program checks the parent relation of a hierarchical dimension to make sure it has no loops. A hierarchical dimension's parent relation specifies the parent for each of the dimension's values. A loop will occur when a dimension value has inadvertently been specified as its own ancestor or descendant in the parent relation. When you execute a ROLLUP command or a AGGREGATE command that uses a parent relation with a loop, an error message will be returned when the loop is identified.

You can call HIERCHECK as a command or as a Boolean function. When called as a function, HIERCHECK returns YES when the parent relation "passes" the check (for example, it contains no loops), and NO when it fails the check (it does contain loops).

Return Value

BOOLEAN

Syntax

When Used as a Command

HIERCHECK relation-name [NOSTATUS]

When Used as a Function or with CALL

HIERCHECK ('relation-name' [NOSTATUS])

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.

NOSTATUS

Specifies that the current status of any extra dimensions on a parent relation is ignored, so that all the hierarchies of a multi-dimensional parent relation will be checked for infinite loops.

When a parent relation has been defined with one or more extra dimensions (that is, with dimensions other than the required embedded total dimension), you can create and name more than one hierarchy within the parent relation. Each of the values of the extra dimension(s) can represent a different hierarchy. The hierarchies use the same dimension values, but the way in which those dimension values relate to each other is different in each hierarchy within the relation.

You can use the LIMIT command on the extra dimension(s) of a parent relation to select which of the parent relation's multiple hierarchies are in status. When a parent relation has multiple hierarchies and the current status of the extra dimension(s) on the parent relation does not include all of those hierarchies, NOSTATUS ignores the current status and checks every hierarchy of the parent relation for loops.

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.

Why ROLLUP and AGGREGATE Use HIERCHECK

The ROLLUP command and the AGGREGATE command both use HIERCHECK in order to prevent infinite looping once the command has been executed.

When to Use HIERCHECK

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 ROLLUP or 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.

For example, suppose you accidentally create a hierarchy that is invalid. You then fail to use HIERCHECK to check that hierarchy. Now, suppose you submit a rollup program as a batch job to run overnight. When you check on the batch job the next morning, you will see that the job failed to run because when the ROLLUP command was called, HIERCHECK detected a loop, which prevented the ROLLUP command from running to completion. In this case, you will have lost a night's work because you did not use HIERCHECK at the time when you created your hierarchies. In other words, using HIERCHECK yourself (instead of waiting for the ROLLUP or the AGGREGATE command to do it for you) will save you time and effort.

Using HIERCHECK as a Function

You may use HIERCHECK as a function. When the parent relation has no loops, the return value is YES. When HIERCHECK detects a loop, the return value is NO. When you call it as a function, HIERCHECK does not signal an error when it finds a loop.

Using HIERCHECK as a Command

When you use HIERCHECK as a command or with the CALL command, 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 14-9, "Checking for Loops".

Checking the Result

When you call HIERCHECK as a function, you get the result as a Boolean return value. When you call HIERCHECK as a command or with the CALL command, you can check a Boolean variable called HIERCHK.LOOPFND to determine the result. When a loop is found, the value is YES. When HIERCHECK did not terminate normally (for example, because a bad argument was passed in), the value is NA. When HIERCHECK runs successfully and no loops are found, the value is NO.

The Problem Dimension Values

When HIERCHECK finds a loop in a parent relation, the names of all dimension values that are involved in that loop are stored in a variable named HIERCHK.LOOPVALS. You can check the value of this variable and use this information to determine where the looping problem lies.

The Problem Hierarchy

When HIERCHECK finds a loop and your parent relation has more than one hierarchy, the name of the hierarchy in which a loop is found is stored in a variable called HIERCHK.XTRADIMS. You can check the value of this variable to find out which hierarchy you should check for the looping problem.

Multiple Loops

HIERCHECK detects the presence of loops, but it does not report multiple loops. While the name of every dimension value involved in a loop will be stored in HIERCHK.LOOPVALS, that does not mean that those dimension values are all part of the same loop; they may be involved in separate loops. Once you have detected and fixed a looping problem, it is important to use HIERCHECK again to check the parent relation until it is loop-free.

Examples

Example 14-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 a ROLLUP command 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'.