|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
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
HIERCHECK parent-relation [STATUS|NOSTATUS|valueset-name] [MULTIPATH] [CONSISTENT]-
HIERCHECK aggmap-name [MULTIPATH] [CONSISTENT]levelrelation-name]
A text expression indicating the name of the parent relation to be checked.
A text expression indicating the name of the aggmap. HIERCHECK checks all of the relations in the aggmap.
Specifies that HIERCHECK uses the current status of the relation dimension.
Specifies the values of the relation dimension that HIERCHECK considers in status.
Specifies that HIERCHECK uses the default status of the relation dimension.
Specifies that HIERCHECK checks whether there are multiple paths from any child to its parent.
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.
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.
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".
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.
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
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'.