By Bryan C. Smith
In my previous post, you took a look at Amy, an employee who supervises three other employees, Jae, Rachel, and Ranjit. Each of these four employees has sales quotas associated with them. Since Amy is the supervisor of Jae, Rachel, and Ranjit, her sales quota reflects her personal sales quota as well as those from Jae, Rachel, and Ranjit. You used the DataMember function to separate the sales quota directly attributable to Amy from her total sales quota figure:
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 |
Jae, Rachel, and Ranjit are referred to as leaf members of the hierarchy while Amy is referred to as a non-leaf member. Leaf members have no children while non-leaf members do. (At this point, you haven't actually proven Jae, Rachel, and Ranjit have no children, but you will in the next query.)
In a parent-child hierarchy it is common for both leaf and non-leaf members to have data in the fact table directly associated with them. The hierarchy's MembersWithData property determines how SSAS presents this data to you.
If the MembersWithData property is set to NonLeafMembersHidden, which is the default when designed in BIDS, you get the behavior we see above – the values directly attributed to a non-leaf member are simply presented as part of the total value for that member.
You've seen you can get to the value associated with the non-leaf member with the DataMember function. Still, SSAS has the ability to present this another way.
If you set the MembersWithData property to NonLeafMembersVisible, SSAS will present the value directly attributed to a non-leaf member as a child of that member. (Changing the value will require a re-deploy but not a re-process.) The easiest way to explain this is to simply demonstrate it:
with
member [Measures].[Personal Quota] as
([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),
format="Currency"
member [Measures].[Member Level] as
[Employee].[Employees].CurrentMember.Level.Name
member [Measures].[Number of Children] as
[Employee].[Employees].CurrentMember.Children.Count
select
{[Measures].[Sales Amount Quota],
[Measures].[Personal Quota],
[Measures].[Member Level],
[Measures].[Number of Children]} on 0,
DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1
from [Adventure Works];
Sales Amount Quota | Personal Quota | Member Level | Number of Children | |
Amy E. Alberts | $24,202,000.00 | $1,124,400.00 | Employee Level 04 | 4 |
Amy E. Alberts | $1,124,400.00 | $1,124,400.00 | Employee Level 05 | 0 |
Jae B. Pak | $12,547,100.00 | $12,547,100.00 | Employee Level 05 | 0 |
Rachel B. Valdez | $3,269,800.00 | $3,269,800.00 | Employee Level 05 | 0 |
Ranjit R. Varkey Chudukatil | $7,260,700.00 | $7,260,700.00 | Employee Level 05 | 0 |
Take a close look at the results. You've calculated the level name along with the number of children for each member. The number of children identifies non-leaf and leaf members. The member level helps you identify how these members are represented in the hierarchy.
With the MembersWithData property set to NonLeafMembersVisible, a leaf member representation of Amy is added to our results as a child of the Amy non-leaf member. The non-leaf Amy member is just as before and the leaf Amy member simply holds the quota value directly attributable to Amy.
Understanding the difference between these two representations of Amy without the benefit of a walk-through such as this is a little confusing. The MembersWithDataCaption property exists to address this problem.
The property accepts a string that serves as a template for the name of the SSAS-generated non-leaf member. An asterisk (*) in the string serves as a placeholder for the original member name. A common string used for MembersWithDataCaption property is * (data). Here are the results from the query above with the MembersWithDataCaption property set to this value:
Sales Amount Quota | Personal Quota | Member Level | Number of Children | |
Amy E. Alberts | $24,202,000.00 | $1,124,400.00 | Employee Level 04 | 4 |
Amy E. Alberts (data) | $1,124,400.00 | $1,124,400.00 | Employee Level 05 | 0 |
Jae B. Pak | $12,547,100.00 | $12,547,100.00 | Employee Level 05 | 0 |
Rachel B. Valdez | $3,269,800.00 | $3,269,800.00 | Employee Level 05 | 0 |
Ranjit R. Varkey Chudukatil | $7,260,700.00 | $7,260,700.00 | Employee Level 05 | 0 |
If you remember back to my previous blog entry, I started out with a simple breakdown of these sales quotas. Using the MembersWithData and MembersWithDataCaption properties, we've come full circle having SSAS provide us the data in a similar structure:
- Amy $24M Total
- Amy (personal) $1M
- Jae $13M
- Rachel $3M
- Ranjit $7M
So, what are the right settings for the MembersWithData and MembersWithDataCaption properties? It really depends on your requirements. I recommend fully understanding how data is presented in a parent-child hierarchy and then sitting down with your end-user representatives to discuss the options.
By their nature, parent-child hierarchies can be slightly difficult for users to wrap their heads around. Walking users through a common example of parent-child hierarchy, such as employees in an organization, helps to explain the basic concepts. Using sample data sets, such as the AdventureWorks DW database, to illustrate these concepts can also assist with their understanding.
It's also important to keep in mind these properties can be changed relatively quickly should you decide at a later date you would prefer your data presented another way. Remember that changing the MembersWithData property affects the number of children associated with a non-leaf member which can have implications for calculations across levels in the hierarchy. Thoroughly test any calculated members before and after changing this property, but also keep in mind calculated members can be re-deployed to your cube without re-processing.
2 comments:
Is there a way to exclude Amy's total(the manager total) while keeping Amy's individual number? I'm basically trying to get a flat list of all employees under Amy(any level below) and their individual numbers.
thanks,
It's also important to keep in mind these properties can be changed relatively quickly should you decide at a later date you would prefer your data presented another way. cotton bed mattress near me , double blanket , double bed razai , sofa cover fabric , velvet blanket Remember that changing the Members With Data property affects the number of children associated with a non-leaf member which can have implications for calculations across levels in the hierarchy. Thoroughly test any calculated members before and after changing this property, but also keep in mind calculated members can be re-deployed to your cube without re-processing.
Post a Comment