Dimensions in the Conceptual Model - Microsoft SQL Server 2008 Analysis Services - page 56

In an unnatural hierarchy, therefore, you would get a unique identifier with the combina-
tion of the parent's unique name and the key of current member. This is one more differ-
ence between natural and unnatural hierarchies, which is essential to understand when
you use hierarchies in applications.
All Level and Key Level
An important characteristic of any hierarchy is the presence or absence of the All level
and the Key level. The All level is a level that unites all the members of the hierarchy in
one member at the top of the hierarchy. Omitting the All level can sometimes seem
convenient (for example, it doesn't make sense to aggregate currencies), but might make it
harder for the system to support the hierarchy. When the All level is omitted, the upper
level of the hierarchy has a group of members rather than one member. In this case,
understanding which member is the default one is not always easy. (For a detailed expla-
nation about default members, see Chapter 10.)
The Key level is a level based on the key attribute of the dimension. Including it doesn't
change the hierarchy, because internally every hierarchy contains a Key level, even though
it might not be visible. If you do include the Key level in the hierarchy, the user can drill
down to all elements of the Key level from the previous levels. If the key attribute is large,
containing a large number of members, including the Key level can cause a burden on the
system, and the drill down can even crash the user application.
An additional reason not to include the Key level is that the system needs to determine
the order of the members in the hierarchy. If the operation to determine the order is
performed over many members, it can require significant resources.
We recommend that you do include the All level in your hierarchy and do not include a
very large Key level.
Attribute Hierarchies
If you didn't build a user-defined hierarchy, or when you need to reference a member
outside the hierarchy, you can use an attribute hierarchy. Each attribute in the model has
an attribute hierarchy. Attribute hierarchy is defined by two levels: the ALL level and the
level that is based on the source attribute. The name of the attribute hierarchy is the same
as the name of the source attribute. If you include an attribute hierarchy, you can refer-
ence any member of the attribute without creating user-defined hierarchies. You don't
have a specific DDL object called an attribute hierarchy; the attribute properties include a
property for the attribute hierarchy.
Attribute hierarchies can seem small. For example, in the hierarchy for the attribute
Gender , we see only two members: both genders, male and female. However, in reality the
hierarchy includes the Key level, which for the Customer dimension, for example, can
contain an enormous number of members.
Building such hierarchies can consume a lot of resources, and if the user uses them, think-
ing that they're small, an operation can considerably decrease system performance. You can
disable attribute hierarchies to avoid this problem, but then the attribute hierarchy would