Skip Headers
Previous
Previous
 
Next
Next

What Are the Hierarchy Data Constraints?

OLAP imposes constraints on hierarchies by checking the dimension keys for circularity and consistency:

You can choose from these types of consistency:

OLAP checks all levels of consistency for circularity.

Consistent Hierarchies

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:

Solve Consistent Hierarchies

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:

Star Consistent Hierarchies

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