Dimension Design - Professional SQL Server Analysis Services 2005 with MDX - page 211

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 re-
lationship 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 relation-
ships 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 man-
ager 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 ma-
nagerial 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 topic The
Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Busi-
ness Intelligence Toolset by Joy Mundy et al. (Wiley, 2006).
The Employee table in AdventureWorksDW has a Parent-Child relationship be-
cause 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 de-
faults on each screen of the Dimension Wizard. On the Define Parent-Child Rela-
tionship 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.