Ragged Hierarchies

In Microsoft® SQL Server™ 2000 Analysis Services, ragged hierarchies are supported in regular and parent-child dimensions. A ragged hierarchy is unbalanced if the hierarchy is in a parent-child dimension.

In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. This can cause branches of the hierarchy to descend to different levels.

For example, in a Geography dimension defined with the levels Continent, Country, and City, in that order, the member Europe appears in the top level of the hierarchy, the member France appears in the middle level, and the member Paris appears in the bottom level. France is more specific than Europe, and Paris is more specific than France. To this regular hierarchy, the following changes are made:

• The Vatican City member is added to the Country level.
  
  
• Members are added to the City level and associated with the Vatican City member in the Country level.
  
  
• A level, named Province, is added between the Country and City levels.
  
  
• The Province level is populated with members associated with other members in the Country level, and members in the City level are associated with their corresponding members in the Province level. However, because the Vatican City member in the Country level has no associated members in the Province level, members must be associated from the City level directly to the Vatican City member in the Country level.

Because of the alterations, the hierarchy of the dimension is now ragged. The parent of the city Vatican City is the country Vatican City, which is not in the level immediately above the Vatican City member in the City level.

To make a hierarchy ragged, various methods can be selected in the Hide Member If property of a level in the properties pane of Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private).

Alternatively, if the ragged dimension is a parent-child dimension, the Skipped Levels Column property of the level is used to support the ragged structure. If you use this property, ensure that the dimension table contains a column that stores the number of intervening levels between each member and its parent.

Both properties can cause the logically missing members to be hidden from end users as they browse cubes. Neither property is supported in virtual dimensions.

Ragged hierarchies can appear ragged to end users only if certain criteria are met. That is, only if the criteria are met can members whose logical parents are not in the level immediately above them nevertheless appear immediately beneath their logical parents. These criteria are:

• The property that supports ragged hierarchies is correctly set. In a regular dimension, the level's Hide Member If property must be set to a value other than Never hidden. The selected value must be appropriate for the contents of the dimension's table. In a parent-child dimension, the level's Skipped Levels Column property must be set to a column containing integer values that indicate the number of intervening levels between members and their parents. These properties are set in Dimension Editor if the dimension is shared or Cube Editor if the dimension is private.
  
  
• The client application supports the display of ragged hierarchies.
  
  
• The MDX Compatibility property in the connection string from the client application to the Analysis server is set to 2, or the MDXCompatibilityValue registry setting for PivotTable® Service is set to 2.

See Also