Working with Ragged Hierarchies
Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated.
For client applications that support the display of ragged hierarchies, you can configure hierarchies to hide logically missing members. Depending on whether you are configuring a regular hierarchy or a parent-child hierarchy, two different properties can be set by using Dimension Designer.
In a ragged dimension's table, the logically missing members can be represented in different ways. The table cells can contain nulls or empty strings, or they can contain the same value as their parent to serve as a placeholder.
The representation of placeholders is determined by the placeholder status of child members and the MDX Compatibility connection string property for the client application.
Hiding Members in a Regular Hierarchy
For a regular hierarchy, use the use the HideMemberIf property of a level in a hierarchy to hide missing members from end users. The HideMemberIf property supports the settings that are described in the following table.
HideMemberIf Setting |
Description |
|---|---|
Never |
Level members are never hidden. |
OnlyChildWithNoName |
A level member is hidden when it is the only child of its parent and its name is null or an empty string. |
OnlyChildWithParentName |
A level member is hidden when it is the only child of its parent and its name is the same as the name of its parent. |
NoName |
A level member is hidden when its name is empty. |
ParentName |
A level member is hidden when its name is identical to that of its parent. |
To make a regular hierarchy appear ragged, set a HideMemberIf property of a level to an appropriate value for the underlying dimension table.
MDX Compatability Mode
The MDX Compatibility property in the connection string from the client application to the instance of Analysis Services must be set to 2 to display ragged hierarchies correctly.
The MDX Compatibility property determines how placeholder members in a ragged or unbalanced hierarchy are treated. If you set the MDX Compatibility property value to 1, you expose a placeholder member in a ragged hierarchy.