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 Country Switzerland is added to France and Germany.
- Cities in Switzerland are added.
- The level Province is added between Country and City and populated with members except under Switzerland, which has no provinces.
Because of the alterations, the hierarchy of the dimension is now ragged. The parent of the city Geneva is the country Switzerland, which is not in the level immediately above Geneva.
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.