O'Reilly logo

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX by Denny Guang-Yeu Lee, Robert Zare, Sethu Meenakshisundaram, Matt Carroll, Sivakumar Harinath

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

5.9. 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 and a parent can also be a child, because parents also have parents. In the data warehousing world such relationships are modeled as a Parent-Child dimension and in Analysis Services 2008 this type of relationship is modeled as a hierarchy called a 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 that 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required