Creating a Parent-Child Hierarchy

In the real world you come across relationships such as that between managers and their direct reports. This relationship is similar to the relationship between a parent and child in that a parent can have several children. In the data warehousing world such relationships are modeled as a Parent-Child dimension and in Analysis Services 2005 the relationships are modeled as a hierarchy called the Parent-Child hierarchy. The key difference between this relationship and any other hierarchy with several levels is how this relationship is represented in the data source. Well, that and certain other properties which are unique to the Parent-Child design. Both of these are discussed in this section.

When you created the Geography dimension, you might have noticed that there were separate columns for Country, State, and City in the relational table. Similarly, the manager and direct report can be modeled by two columns, ManagerName and EmployeeName, where the EmployeeName column is used for the direct report. If there are five direct reports for a manager, there will be five rows in the relational table. The interesting part of the Manager-DirectReport relationship is that the manager is also an employee and is a direct report to another manager. This is unlike the Columns City, State, and Country in the Dim Geography table. It is probably rare at your company, but employees can sometimes have new managers due to managerial reorganization. The fact that an employee's manager can change at any time of the year is very interesting when you want to look at facts such as sales generated under a specific manager, which is the sum of sales generated by the manager's direct reports. A dimension modeling such a behavior is called a slowly changing dimension since the manager of an employee changes over time. You can learn slowly changing dimensions and different variations in detail in the book The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset by Joy Mundy et al. (Wiley, 2006).

The Employee table in AdventureWorksDW has a Parent-Child relationship because it has a join from ParentEmployeeKey to the EmployeeKey. You can use the Dimension Wizard to create a dimension on the DimEmployee table. Accept the defaults on each screen of the Dimension Wizard. On the Define Parent-Child Relationship screen shown in Figure 5-34, you will notice that the Dimension Wizard has identified a Parent-Child relationship as well as the Parent attribute based on a sample of the data. The wizard was able to identify the Parent-Child relationship due to join within the same table in the DSV.

image from book
Figure 5-34

On the Review New Hierarchies screen, shown in Figure 5-35, the Dimension Wizard shows the multilevel hierarchies detected from the data sample.

image from book
Figure 5-35

By default the Dimension Wizard defines the properties for the attribute modeling the Parent-Child hierarchy at the completion of the Dimension Wizard, as shown in Figure 5-36.

image from book
Figure 5-36

The Parent-Child hierarchy is actually a special attribute hierarchy because it can contain multiple levels, unlike the other attributes. The Parent-Child hierarchy that you created is the attribute ParentEmployeeKey. The Usage property for this attribute is set to Parent, which indicates that this attribute is a Parent-Child hierarchy. If you deploy the project and browse the Parent-Child hierarchy, you will notice that you see the ids of parent and employee as a multilevel hierarchy. Typically, you would want to see the names of the employees rather than their ids. You learned earlier that you can use the named column to specify the name that is shown in the browser and use the key column for ordering. Because the Parent-Child hierarchy retrieves all the information from the Key attribute, which is the DimEmployee attribute in this example, you need to modify the named column of the DimEmployee attribute rather than the named column of the Parent-Child hierarchy attribute. Change the named column of the Key attribute to LastName. When you deploy the project and browse the Parent-Child hierarchy, you will see the members of the hierarchy, as shown in Figure 5-37.

image from book
Figure 5-37

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2017.
If you may any questions please contact us: