Design hierarchies such that lower levels have more members than higher levels

This rule analyzes dimensions to determine whether any hierarchies have an attribute at a lower level in the hierarchy that contains fewer members than an attribute at the level above.

Best Practices Recommendations

A hierarchy where an attribute at a lower level has fewer members than an attribute at a higher level occurs because of one of the following reasons:

  • Frequently, this kind of hierarchy indicates that the levels are in the incorrect order. For example, a hierarchy where the [State] attribute is at a lower level than the [City] attribute does not have the attributes in correct order.

  • This kind of hierarchy might also indicate that the key columns of the lower level are missing a column. For example, suppose the [Year] attribute is at a higher level than the [Quarter Number] attribute. This hierarchy is missing a column and should instead have the [Year] attribute above the [Quarter with Year] attribute.

In either of these situations, this kind of hierarchy will lead to confusion for end users who are trying to use and understand the cube.

You should create hierarchies in such a way that attributes at lower levels of the hierarchy contain more members than attributes at the level above.

For More Information

For more information, see Defining User Hierarchies in SQL Server Books Online.