SSAS 2008 Unleashed - Chapter 8. Advanced Modeling

Chapter 8. Advanced Modeling

In the previous chapters, we covered basic aspects of multidimensional modeling. In many cases, it would be enough to build a sophisticated multidimensional model. However, some business problems may require more complex solutions. In this chapter, we discuss advanced functionality of Analysis Services.

Parent-Child Relationships

Often in the data model, a dimension is defined not by multiple attributes and relationship between them but by parent-child relationship inside one attribute. Let’s look at an Employees dimension as an example. It contains a list of all the employees in an organization. The hierarchy of the organization can be represented by levels of management, where employees of one level report to the employees of the other level. However, in the real-world organizations, structure is not that rigid—the office assistant can report to a vice president, same as the general manager, who has a few levels of reports. The structure of organization is often defined as parent-child, where each employee has a manager. Analysis Services provides parent-child relationship between attributes to enable this functionality.

To define parent-child relationship, you create two attributes: a key attribute that has a list of all the employees, and the parent attribute that contains an ID of the employee’s manager, as shown in Figure 8.1. Thus, from relational prospective, the parent attribute provides a self join to the key attribute. For example, for the Employees dimension, we define an Employee attribute as the key attribute and a Manager attribute as parent attribute. The employee ID of the manager references the ID in the Employee attribute, because every manager is also an employee of an organization.

To define an attribute as a parent, use the Usage parameter of an attribute. After you create a parent attribute and start to process a dimension, Analysis Services analyzes the structure of relationships between members in key and parent attributes. It detects relationship and recognizes that some members have no parents. Others might have only one parent (ancestor) above them, some might have two ancestors, and so on. At this time, the system creates physical attributes corresponding to each level of the parent-child relationship. The key attribute is divided into a set of attributes, according to their relationships. Each attribute will contain only members that have the same types of relationships with its siblings—and have the same number of ancestors (see Figure 8.2). You can use the Naming template and the Naming Template Translation parameters of the parent attribute to give names to these real attributes.

The members that belong to each real attribute share some specific characteristic. One layer contains all the employees. The other contains only managers (who are also employees and therefore contained in the Employee attribute). Some members (for example, managers) have two roles. In the first role, the member represents an individual employee. In the second role, it represents a manager. In that second role, the member is the parent to some other members—those in the Employee attribute.

Analysis Services provides an additional intrinsic property to distinguish these roles: DataMember. You can use DataMember property in calculations when you need to associate a calculation with the employee role of a manager. For example, you use the DataMember property to calculate the full salary of the department, including the salary of the manager.

Parent-Child Hierarchies

The last hierarchy we want to discuss is the hierarchy whose definition is based on the Parent attribute. (For details about Parent attributes, see the section “Parent Attributes” in Chapter 5, “Dimensions in the Conceptual Model.”) When you’re creating a parent-child hierarchy, the parent attribute goes on top of the regular attribute that will play the role of the child of the parent attribute.

The parent and child attributes form a special hierarchy. In the simplest case, the parent-child hierarchy contains only two attributes: the parent attribute, and the key attribute, which acts as a child attribute. At this time, this type of parent-child hierarchy is the only one supported by Analysis Services.

Figure 8.3 shows an example of a parent-child hierarchy based on our sample Employee attribute in the Employees dimension. In the figure, we can see the hierarchy built on this attribute in both a diagram and an actual hierarchy from FoodMart.

The definition of a parent-child hierarchy is based on the data that is loaded in the key attribute (as opposed to the typical hierarchy, which isn’t dependent on data being present in an attribute).

The hierarchy is built on a collection of attributes within attributes. The key attribute will be divided internally into several attributes, which contain members from the various levels. The hierarchy includes one level for each nested attribute that contains data. If more data is introduced, the number of levels can change. The parent-child hierarchy can be seen as a single hierarchy whose structure is determined by the data that populates it.

There are a couple of important characteristics of the parent-child hierarchy: First, it’s unbalanced; and second, it’s flexible. It’s independent of the parameters set for the relationships of the parent and child attributes.

Members of the key attribute for a parent-child hierarchy can be found on any level of the hierarchy. Data members, which are usually one level below the member for which they represent data, can also appear on any level of a parent-child hierarchy. Members on the lowest level of the hierarchy are the only ones that don’t have data members (which would be below them, which would be impossible). Truly speaking, they are data members of themselves.

The parent-child hierarchy is unbalanced because you can’t control which member has children and which doesn’t, or what level the parent member will be on. To produce a balanced parent-child hierarchy, the user must carefully craft the relationships of the members so that members from the lowest level of the hierarchy are the only ones that don’t have children.

In addition, members can change their positions in the hierarchy whenever you update the dimension. This capability makes the hierarchy flexible.

Parent-child hierarchies enable you to create a flexible data structure. However, to construct and maintain a parent-child hierarchy requires considerably more resources from the system. The complexity a parent-child hierarchy introduces into multidimensional cubes can greatly influence the effectiveness of the model and make it difficult for the user to understand the model. We recommend that you avoid using parent-child hierarchies except when they are absolutely necessary.