Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

HIERCHECK

The HIERCHECK function checks the hierarchy in the specified relation or all of the relations of the specified aggmap to see if there is any circularity. 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. )

You can also specify that HIERCHEK check the hierarchy for other conditions.

See also:

HIERSHAPE function

Return Value

BOOLEAN

Syntax

As Command

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

[BALANCED levelrelation-name]

or

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

Parameters

parent-relation

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

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 HIERCHECK checks whether there are multiple paths from any child to its parent.

CONSISTENT

Specifies that HIERCHECK 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 HIERCHECK 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 a different depth.

Usage Notes

Why 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, do not attempt to roll up a variable's data unless you have verified that its dimensions' hierarchies are structured correctly. For example, the AGGREGATE command uses HIERCHECK to prevent infinite looping once the statement has been executed. 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, make it a practice to use HIERCHECK first.

Status When Using HIERCHECK 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 and stops execution (that is, HIERCHECK always stops in the first error message). 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 7-108, "Checking for Loops".

Examples

Example 7-108 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 are 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 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'.