By Bryan C. Smith
Take a moment to think about a typical organization. You have an employee. That employee has a supervisor who is also an employee who has a supervisor (who is also an employee) and so on and so on. (This is a classic parent-child hierarchy.)
If these employees are in a sales organization, sales quota may have been assigned. Those employees that are supervisors are responsible for the sales quotas of the employees below them but may also have sales quotas of their own.
Let's say you have an employee in a sales organization named Amy. She supervises three other employees, Jae, Rachel, and Ranjit. Amy is responsible for making sure these guys make their sales quotas of $13M, $3M, and $7M, respectively. Amy also has a personal sales target of $1M. We can breakdown Amy's sales quota like this:
- Amy $24M Total
- Amy (personal) $1M
- Jae $13M
- Rachel $3M
- Ranjit $7M
- Amy (personal) $1M
The $24M figure represents the total sales for which Amy has responsibility while she is only directly responsible for $1M of that. The remaining $23M represents the sales quotas she manages through her supervisees.
Let's now look at how SSAS 2005 handles all this. Open an MDX query window in SQL Server Management Studio, connect to the Adventure Works DW database, and execute the following query:
select
[Measures].[Sales Amount Quota] on 0,
DESCENDANTS([Employee].[Employees].[Amy E. Alberts], 1, SELF_AND_BEFORE) on 1
from [Adventure Works];
Sales Amount Quota | |
Amy E. Alberts | $24,202,000.00 |
Jae B. Pak | $12,547,100.00 |
Rachel B. Valdez | $3,269,800.00 |
Ranjit R. Varkey Chudukatil | $7,260,700.00 |
Through the DESCENDANTS
function, you asked SSAS to return the Sales Amount Quota for those employees one level below Amy E. Alberts and Amy herself. If you total the values for Jae, Rachel, and Ranjit, we come up with $23,077,600.00. That's $1,124,400.00 short of the total.
From these results, you can deduce the approximately $1M deficit represents Amy's personal quota. But how do you get SSAS to explicitly present this? You do it with the DataMember function:
with
member [Measures].[Personal Quota] as
([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),
format="Currency"
select
{[Measures].[Sales Amount Quota],[Measures].[Personal Quota]} on 0,
DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1
from [Adventure Works];
Sales Amount Quota | Personal Quota | |
Amy E. Alberts | $24,202,000.00 | $1,124,400.00 |
Jae B. Pak | $12,547,100.00 | $12,547,100.00 |
Rachel B. Valdez | $3,269,800.00 | $3,269,800.00 |
Ranjit R. Varkey Chudukatil | $7,260,700.00 | $7,260,700.00 |
Your results now show definitively that Amy owns a $1M personal sales quota which contributes to the $24M overall quota she is responsible for delivering. You also see that Jae, Rachel, and Ranjit, Amy's supervisees, are solely responsible for their own quotas. You cannot say Jae, Rachel, and Ranjit have no supervisees of their own (without submitting additional queries), but you can say none of these three guys is managing quota values from any supervisees they may have.
So, when you request a value for a member in a parent-child hierarchy, such as the Employees hierarchy of the Employee dimension, you need to be aware SSAS combines those values directly tied to that member with values associated with that member's children. You need to use the DataMember function to get at the value directly tied to a member.
It needs to be pointed out that you can tell SSAS to display a member's directly attributable data a little differently when that member also has children with values. In my next blog entry, we'll revisit Amy's sales team and take a look at those settings and how they impact these results.
