Home > About Dimensional Database ... > About Hierarchies > What Are the Hierarchy Data...
![]() Previous |
![]() Next |
OLAP imposes constraints on hierarchies by checking the dimension keys for circularity and consistency:
Circularity means that a value is its own parent and thus creates an infinite loop during aggregation. Circularity is not permitted. For example, a hierarchy is invalid if X is a child of Y is a child of Z is a child of X.
Consistency means that if any dimension member appears in multiple hierarchies, then its set of descendants at the detail level must be exactly the same in each hierarchy. Consistency is enforced, but you can choose how rigorously.
You can choose from these types of consistency:
OLAP checks all levels of consistency for circularity.
Consistency means that if any dimension member appears in multiple hierarchies, then its set of descendants at the detail level are exactly the same in each hierarchy. Consistency across all hierarchies ensures that the definition of an aggregate value is the same regardless of where it appears. For example, "Total Customer" represents the same list of customers in all measures.
For example, if Quarter_1 consists of January, February, and March at the detail level in the Calendar hierarchy, then Quarter_1 cannot consist of July, August, and September in the Fiscal hierarchy.
The members of one hierarchy can, however, have different parents in other hierarchies. For example, these Customer hierarchies are consistent if the "Total" member has all members at the SHIP_TO level in both Shipments and Market Segment.
Shipments Hierarchy:
TOTAL REGION WAREHOUSE SHIP_TO
Market Segment Hierarchy:
TOTAL MKT_SEGMENT ACCOUNT SHIP_TO
All hierarchies in a dimension must be consistent with each other. For example, if a dimension has hierarchies H1, H2, and H3, then:
H1 and H2 are consistent.
H2 and H3 are consistent.
H1 and H3 are consistent.
Solve consistency means that all hierarchies used in the aggregation of a single cube are consistent with each other. This is the minimal level of consistency and assures that the aggregated values of one hierarchy are not overwritten by the aggregated values of another hierarchy.
For example, suppose the Product dimension has hierarchies H1, H2, and H3, and Product is used in cubes C1 and C2. If C1 aggregates over H1 and H2, and C2 aggregates over H3, then:
H1 and H2 are consistent.
H3 does not need to be consistent with H1 or H2.
Star consistency means that all hierarchies of the dimension are consistent with each other. Moreover, if a parent has children at the same level in multiple hierarchies, those children must be identical.
For example, suppose a dimension has hierarchies H1 and H2 and level L3 appears in both of them. If parent X has children C1, C2, and C3 at level L3 in hierarchy H1, then X also has children C1, C2, and C3 at level L3 in hierarchy H2.
The following Customer hierarchies are star consistent only if REGION
has the same members in both hierarchies, (such as Americas, Europe, Pacific Rim), which roll up from the same SHIP_TO
members.
Shipments Hierarchy:
TOTAL REGION WAREHOUSE SHIP_TO
Countries Hierarchy:
TOTAL REGION SUBREGION STATE_PROVINCE SHIP_TO