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

Dimension Hierarchies

The hierarchy, which can be seen as a navigational path, is the primary means of access to
the multidimensional model. It consists of attributes arranged in descending (or ascend-
ing, depending on the way you look at it) levels, with each level containing attribute
members. Efficient use of hierarchies significantly increases the effectiveness of the multi-
dimensional model from the point of view of both the client (the way the user can
manipulate the data) and of the server (in making the necessary calculations).

The task of defining a hierarchy is simply to define the hierarchy object itself, and to
specify the collection of all the levels and all their properties. Levels have two important
properties:

. SourceAttributeID —Defines the source for the level members. In the level, you
can see all the members from the source attribute, along with all their parameters
and properties. After the SourceAttributeID has defined a member as belonging to a
level, the member gains two characteristics:

. A parent member is a member from the level above, which current member

belongs to.

. Children are members from the next level that belongs to the current member.

. HideMemberIf —Defines the rules for displaying members for the client application.
Some members will be hidden so that not all the members of the source attribute
will be apparent in the hierarchy. Use of this property significantly diminishes the
effectiveness of the hierarchy and complicates the calculation algorithms. We rec-
ommend that you avoid using this parameter.

Types of Hierarchies
The definition of a hierarchy is typically very simple. However, hierarchies can differ from
each other, depending on the type of attributes they contain and the relationships
between them. Figure 5.4 shows an example of a simple geography-based hierarchy, based
on the attributes of our Customer dimension.

Natural Hierarchies and Their Levels
In the Customer dimension, dependencies exists between attributes. Because country is a
dependent attribute of the state, if you know the state, you can unambiguously determine
what country it belongs to. The same is true for city and state. Because the state is a
related attribute for the city, if you know the city you can unambiguously say what state it
belongs to.

In this hierarchy, the presence of a key attribute changes nothing. The entire structure of
the hierarchy is determined by the relationships between the attributes that are used as
levels in the hierarchy. Such hierarchy is called a natural hierarchy . All levels of a natural
hierarchy are built from related attributes, and the levels are located in correspondence with
the direction of the relationships of attributes. As its name suggests, it is the most effective
form of hierarchy. In Analysis Services 2000, this was the only possible type of hierarchy.