Chapter 5. Dimensions in the Conceptual Model

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 ascending, 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 multidimensional 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 recommend 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.

Analysis Services 2005 introduced a new type of hierarchy, the unnatural hierarchy. We’ll use an example that has two attributes, Marital Status and Gender, which are not related to each other. If you used an earlier version of Analysis Services to create a two-level hierarchy that includes these two attributes, you would have to perform a whole series of crafty manipulations with the data. With the current version of Analysis Services, it is just as easy to create this type of hierarchy as it was in earlier versions to create a natural hierarchy.

The unnatural hierarchy differs from the natural hierarchy in the way it defines which members belong to which parents. Whereas the natural hierarchy defines its parent/child relationships by the relationships between members, the unnatural hierarchy defines its parent/child relationships by the relationships of the members to the key attribute.

Let’s look at the Customers dimension in terms of an unnatural hierarchy. We’ll use the attributes Marital Status and Gender. The key attribute in the dimension is Customer ID. Let’s say you have two groups of customers that are (1) male and married and (2) male and unmarried. Your hierarchy will contain a member “male” that has two children, “married” and “unmarried.” If you add some female members, some married and some unmarried, your hierarchy will change. For unnatural hierarchies, depending on what the key attribute contains, the view of the hierarchy can change.

Another difference between the two types of hierarchies is that the unnatural hierarchy can support many-to-many relationships (as well as one to many) among the members from different levels of the hierarchy. The natural hierarchy, in contrast, can support only a one-to-many relationship. So, in the Customer dimension, with an unnatural hierarchy the attribute member with Marital Status “married” could have two parents, Male and Female, as could the attribute member “unmarried.”

At first glance, natural and unnatural hierarchies appear to have the same look and same behavior. And they do—in many cases, they do look and behave the same. However, it’s best to use natural hierarchy unless you really need an unnatural hierarchy. Because of the types of relationships between attributes, you could see a drop in performance.

Hierarchy Member Reference

When you use hierarchies in a client application, it’s important to know how to reference the hierarchy members. The most general and reliable way is to reference a member through every level of the hierarchy by using the keys. Because each member key is unique within the parent, the full path of the keys is the unique identifier for each hierarchy member.

But using keys isn’t always that easy. Sometimes it makes more sense to define a full path using the names of the members. This way is easier, but it doesn’t always unambiguously identify a member if the member name isn’t unique. Two different members with the same name and same parent would have same path.

Using the full path (either names or keys) to reference members is not always effective. Analysis Services provides the Unique Name property, which gives each member of the hierarchy a unique name generated by the system. This Unique Name should be considered an internal system identifier; we don’t recommend that you to parse or generate unique names in your application.

In a natural hierarchy, the key of the member is a unique identifier within the level of the hierarchy. In most cases, the system will attempt to use the member key as the member unique name. In an unnatural hierarchy, because it can support a many-to-many relationship, such a member could be used multiple times in the same level for different parents.

In an unnatural hierarchy, therefore, you would get a unique identifier with the combination of the parent’s unique name and the key of current member. This is one more difference 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 explanation 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 reference 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, thinking 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 not be created at all. Alternatively, you can define the attribute hierarchy as unordered, in which case the members would not be sorted but would appear in the order defined by the system. Or you can define the attribute hierarchy as not optimized—it will be accessible for browsing, but the performance of browsing such attribute will still be suboptimal because the number of structures required for effective work will not be created.

The following list contains attribute properties that define the behavior of the attribute hierarchy:

• • IsAggregatable determines whether it’s possible to aggregate data associated to the attribute members. If data is aggregatable, the system will define an ALL level above the attribute level. We recommend that you avoid defining an attribute as not aggregatable because that creates a whole series of problems. For example, it’s probably necessary to create the Currency attribute as nonaggregatable because it doesn’t make sense to perform simultaneous operations in dollars and euros. On the other hand, to define the Year attribute in the Time dimension as not aggregatable isn’t logical because it does make sense to use it to sum across all years, even if it is almost never used.

  • AttributeHierarchyEnabled determines the presence or absence of an attribute hierarchy. By default, all attributes have an attribute hierarchy; you can use this property to disable the attribute hierarchy.

  • AttributeHierarchyOrdered defines an attribute hierarchy as unordered. Using this property is helpful if the attribute hierarchy is enabled but not used often, and the order of members doesn’t matter.

  • AttributeHierarchyOptimizedState turns off optimization for the hierarchy. This property is useful if the attribute hierarchy is not used often and the user will accept a slowdown in requests that use the hierarchy. Turning off optimization saves resources when you build the hierarchy.

  • AttributHierarchyVisible determines whether the attribute hierarchy is accessible through different discover queries. If you set this parameter so that the hierarchy is not visible, the client application will not be able to determine the presence of the hierarchy, although it will be able to use it.

  • AttributeHierarchyDisplayFolder allows you to enable the application to group hierarchies in folders. You can use it to assign a name to the folder that the attribute hierarchy will be placed in.

  When you’re designing a cube, you must figure out in detail what attribute hierarchies are necessary, and in what state they’re necessary. An excessive number of hierarchies can take up a significant amount of resources during the loading and storing of dimensions. It will create so many choices that the user can be confused.

  To make your application effective and efficient, we recommend that you design user hierarchies, which provide the user with a convenient way to access members.

  If the applications contain well-designed hierarchies, the system can perform more effectively. A lack of user hierarchies makes it difficult for the server to predict the methods of access to the members, in which case, the system doesn’t attempt to optimize anything.

  Note

  If the priorities for the hierarchies aren’t determined by the user, the hierarchy that appears first is given first priority in many systems’ operations. You should order hierarchies in DDL by their importance and assign first position to the most important hierarchy, one that is used most frequently.