Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
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
Allows a detail data cell to aggregate in multiple paths to the same ancestor cell.
(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.