Database Reference
In-Depth Information
Avoid using parent/child hierarchies!
For all of the reasons discussed, we recommend that you avoid using
parent/child hierarchies unless you have absolutely no other choice but
to use them. In some cases you will have to use them, and in some cases
using them will not cause any problems, but in our experience when
problems occur with parent/child hierarchies (and this is usually when
they have a large number of members) then they can be quite serious. In
situations where you know there is a fixed maximum number of levels
in the hierarchy, you should attempt to flatten out the relational data
so that you can build separate attributes for each level. The Analysis
Services
Parent/Child Dimension Naturalizer
utility, available for
download at
http://tinyurl.com/pcdimnat
and also incorporated
in BIDS Helper, will help you do this. Note that designing a flattened
dimension equivalent to a parent/child dimension can be a very time-
consuming task, so consider building a true parent/child hierarchy, then
using the discussed tool to naturalize it.
Ragged hierarchies with HideMemberIf
The alternative to using a parent/child hierarchy is to build a regular user
hierarchy and then hide certain members in it to make it look ragged by setting the
HideMemberIf
property on the levels of the user hierarchy.
HideMemberIf
has a
number of possible settings: hide a member if it has no name, hide a member if it has
the same name as its parent, hide a member if it has no name and is the only child,
and hide a member if it has the same name as its parent and is the only child. A
common situation when you would want to use this functionality is on a geography
hierarchy with levels such as
Country
,
State
,
City
and
Customer
; some countries are
subdivided into states but some aren't, so it would make no sense to show the
State
level for those countries; some countries are so small that it makes no sense to show
either
State
or
City
.
The confusing thing about this functionality is that it needs the data in a dimension
to be modeled in a particular way for it to work properly in all client tools. Here's an
example of the relational source data for a dimension modeled in the correct way, as
shown in the following screenshot:
Search WWH ::
Custom Search