About Levels and Distances in Parent-Child Hierarchies

All the dimension members of a parent-child hierarchy occur in a single logical column.

In a parent-child hierarchy, the parent of a member is in another row in the same logical column, pointed to by the parent key. In a level-based hierarchy, the parent of a member is in a different logical column in the same row. Navigation in a parent-child hierarchy follows data values, while navigation in a level-based hierarchy follows the metadata structure.

In level-based hierarchies, each level is named, and occupies a position in the hierarchy that corresponds to a real-world attribute or category useful for analysis. In level-based hierarchies the number of levels is fixed at design time. There is no limit to the depth of a parent-child hierarchy, and the depth can change at run time due to new data.

Every parent-child hierarchy has two system-generated entities, Total and Detail, that are automatically defined when the logical hierarchy is created. The Detail entity contains all the hierarchy members. These two system-generated entities are different from the implicit, inter-member levels between ancestors and descendants in a parent-child hierarchy. The implicit levels are referred to as parent-child hierarchical levels.

Closely associated with levels is the concept of distance in parent-child hierarchies. The distance of one member from another is the number of parent-child hierarchical levels from the member to an ancestor or to a descendant. For example, the distance from a member to its parent is always 1. See About Parent-Child Hierarchies for an example.