Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

Part Number B14346-02
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

MULTIPATHHIER

The MULTIPATHHIER option specifies that a given cell that contains detail data can have more than one path into a cell that contains aggregated data. Certain calculations require this kind of multiple-path aggregation.

Syntax

MULTIPATHHIER = {YES|NO}

Arguments

YES

Allows a detail data cell to aggregate in multiple paths to the same ancestor cell.

NO

(Default) Disallows a detail data cell to aggregate in multiple paths to the same ancestor cell.

Notes

When to Use MULTIPATHHIER

The only time you should set the MULTIPATHHIER option to YES is when a calculation requires the use of multiple paths.

Interpreting an XSHIERCK01 Error Message

When you use the AGGREGATE command, dimension hierarchies are automatically checked for circularity. When MULTIPATHHIER is set to NO, or when the default of NO has not been changed, then the following error message is displayed when a detail data cell uses multiple paths to the same aggregate data cell.

ERROR: (XSHIERCK01) One or more loops have been detected 
in your hierarchy n over N. The loops include 2 items 
(UNDIRECTED: X and Y).

In the preceding error message, X is the name of the detail data cell, and Y is the name of the ancestor cell into which the detail data cell takes more than one path to aggregate. For more information, see Example 20-10, "Defining Multiple Paths in a Hierarchy".

This error message is displayed because the multiple paths taken by the detail data cell have been interpreted as a circular hierarchy. When this is a mistake and you did not intend to create multiple paths, then change the hierarchy. Otherwise, set the MULTIPATHHIER option to YES.

Examples

Example 20-10 Defining Multiple Paths in a Hierarchy

This example shows how you can define multiple paths in a hierarchy, the error message that results when you attempt to aggregate data, how to interpret that message, and how to resolve the problem.

The following statements create two paths from a detail data cell to an ancestor cell that contains aggregated data.

DEFINE geog TEXT DIMENSION
DEFINE path INTEGER DIMENSION
DEFINE geog.geog RELATION geog <geog path>
MAINTAIN geog ADD 'A1' 'b1' 'b2' 'Top'
MAINTAIN path ADD 2
geog.geog(geog 'A1' path 1) = 'B1'
geog.geog(geog 'A1' path 2) = 'B2'
geog.geog(geog 'B1' path 1) = 'Top'
geog.geog(geog 'B2' path 1) = 'Top'

First, a geography dimension named geog and a second dimension named path are defined.

A relation named geog.geog is defined, in which the geography dimension is dimensioned by itself and the path dimension.

Dimension values named A1, B1, B2, and Top are added to the geog dimension.

Two dimension values are added to the path dimension. Because path was defined with an INTEGER data type, the dimension values that are automatically assigned to it are the INTEGER values 1 and 2.

Finally, the hierarchy for the geog dimension is created. The A1 dimension value is the detail data. The B1 and B2 dimension values are the second level of the hierarchy. The Top dimension value is the top of the hierarchy.

A1 has two aggregation paths: A1 aggregates into B1, which aggregates into Top; A1 aggregates into B2, which aggregates into Top.

The following statements define a variable named myvar, assign a data value of 1 to its detail data level (A1), and define an aggmap for that variable.

DEFINE myvar INTEGER VARIABLE <geog>
myvar(geog 'A1') = 1
DEFINE myvar.aggmap <geog>
AGGMAP 'RELATION geog.geog'

An attempt to aggregate myvar generates the following error message.

AGGREGATE myvar USING myvar.aggmap
ERROR: (XSHIERCK01) One or more loops have been detected 
in your hierarchy GEOG.GEOG over GEOG. The loops include 2 
items (UNDIRECTED: A1 and TOP).

The multiple paths of aggregation that have been created for A1 have been interpreted as a circular hierarchy, because the MULTIPATHHIER option is currently set to NO.

When you had made a mistake and created these multiple paths by mistake, you would fix the problem in the hierarchy.

However, in this case, the multiple paths have been created because a calculation requires them. Therefore, the solution is to set MULTIPATHHIER to YES. Now you can execute the AGGREGATE command without error.