User-Defined Hierarchies - Ragged Hierarchies

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

A ragged hierarchy is a user-defined hierarchy that has an uneven number of levels. Common examples include an organizational chart where a high-level manager has both departmental managers and non-managers as direct reports, or geographic hierarchies composed of Country-Region-City, where some cities lack a parent State or Province, such as Washington D.C., Vatican City, or New Delhi.

For most hierarchies in a dimension, each level has the same number of members above it as any other member at the same level. A ragged hierarchy is different in that the logical parent 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. In a client application, this can make drilldown paths unnecessarily complicated.

Client applications vary in how well they handle a ragged hierarchy. If ragged hierarchies exist in your model, be prepared to do a little extra work to get the rendering behavior you expect.

As a first step, check the client application to see how it handles the drilldown path. For example, Excel repeats the parent names as placeholders for missing values. To see this behavior yourself, build a PivotTable using the Sales Territory dimension in the Adventure Works multidimensional model. In a PivotTable having the Sales Territory attributes Group, Country, and Region, you will see that countries/regions missing a region value will get a placeholder, in this case a repeat of the parent above it (Country name). This behavior derives from the MDX Compatibility=1 connection string property that is fixed within Excel. If the client does not naturally provide the drill-down behaviors you are looking for, you can set properties in the model to change at least some of those behaviors.

This topic contains the following sections:

• Approaches for modifying drilldown navigation in a ragged hierarchy

• Set HideMemberIf to hide members in a regular hierarchy

• Set MDX Compatibility to determine how placeholders are represented in client applications

Approaches for modifying drilldown navigation in a ragged hierarchy

The presence of a ragged hierarchy becomes a problem when drilldown navigation does not return expected values or is perceived as awkward to use. To fix navigation problems that result from ragged hierarchies, consider these options:

• Use a regular hierarchy but set the HideMemberIf property on each level to specify whether a missing level is visualized to the user. When setting HideMemberIf, you should also set MDXCompatibility on the connection string to override default navigation behaviors. Instructions for setting these properties are in this topic.

• Create a parent-child hierarchy that explicitly manages the level members. For an illustration of the technique, see Ragged Hierarchy in SSAS (blog post). For more information in Books Online, see Parent-Child Dimensions. Downsides to creating a parent-child hierarchy are that you can only have one per dimension, and you typically incur a performance penalty when calculating aggregations for intermediate members.

If your dimension contains more than one ragged hierarchy, you should use the first approach, setting HideMemberIf. BI Developers with practical experience in working with ragged hierarchies go further in advocating for additional changes in the physical data tables, creating separate tables for each level. See Martin Mason's the SSAS Financial Cube-Part 1a-Ragged Hierarchies (blog) for details about this technique.

Set HideMemberIf to hide members in a regular hierarchy

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, as determined by the HideMemberIf property, and the MDX Compatibility connection string property for the client application.

For client applications that support the display of ragged hierarchies, you can use these properties to hide logically missing members.

1. In SSDT, double-click a dimension to open it in Dimension Designer. The first tab, Dimension Structure, shows attribute hierarchies in the Hierarchies pane.

2. Right-click a member within the hierarchy and select Properties. Set HideMemberIf to one of the values described below.

   HideMemberIf Setting	Description
   Never	Level members are never hidden. This is the default value.
   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.

Set MDX Compatibility to determine how placeholders are represented in client applications

After setting HideMemberIf on a hierarchy level, you should also set the MDX Compatibility property in the connection string sent from the client application. The MDX Compatibility setting determines whether the HideMemberIf is used.

See Also

Create User-Defined Hierarchies
User Hierarchies
Parent-Child Dimensions
Connection String Properties (Analysis Services)