Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

24.4. Employee Scenario (P/C)

The employee scenario is a great example of the need for a parent/child dimension because the hierarchies involved are both unbalanced and ragged. For example, as noted in Figure 24-13, employee Wanida M. Benshoof is two levels away from Ken J. Sánchez, and the employee Ashvini R. Sharma is three levels away from Ken J. Sánchez. Employee scenarios often involve this type of hierarchy where it is best represented by using a parent/child dimension. Note that we also go over some more details on parent/child dimensions in Chapter 25 (involving the Web Analytics scenario — path navigation hierarchy).

Figure 24.13. Figure 24-13

Let's focus for a moment on the hierarchy reporting to Amy E. Alberts (who reports to Brian S. Welcker) for the calendar year 2004 for the sales quota (see Figure 24-14).

Figure 24.14. Figure 24-14

Ms. Alberts is the European Sales Manager with three people reporting to her. When we want to discover what her sales quota is, we can run the following MDX statement:

select {
 [Measures].[Sales Amount Quota]
} on columns, {
 [Employee].[Employees].[Amy E. Alberts].children,
 [Employee].[Employees].[Amy E. Alberts]

} on rows
from [Adventure Works]
where ([Date].[Calendar].[Calendar Year].[CY 2004])

This provides you with the output shown ...