Allocation across a Parent-Child Hierarchy

NOTE This is part of a series of entries onthe topic of Building Writeback Applications with Analysis Services.

A parent-child hierarchy consists of members with which values are associated at various levels. A frequently cited example of such a hierarchy is the Employees hierarchy of an Employee dimension. Applied to a Salary measure, members up and down the hierarchy are associated with values. For managers, the associated salary value reflects not only that individual’s salary those of any subordinate members.

To differentiate between that part of the measure value that is directly attributable to the member and that which represents an aggregate of its subordinates, Analysis Services injects a special (leaf-level) member, the Member with Data member, directly under each member of the parent-child hierarchy. The Member with Data member holds the directly attributable portion of the value, leaving the member with which it is associated to present the aggregate of the Member with Data member and any of its other subordinates.

By default, the Member with Data member is not visible in a parent-child hierarchy but this can be changed through the hierarchy’s MembersWithData property setting. Visible or not, the Member with Data member is always present so that when data is written back to a member of a parent-child hierarchy, the Members With Data member is considered for allocation.

To illustrate the consequence of this, let’s say Lisa is a member of the Employees parent-child hierarchy and has two subordinates, John and Jane, neither of which has subordinates of their own. If we writeback a value of $150,000 to the Lisa member and use the default equal allocation (USE_EQUAL_ALLOCATION) technique, John and Jane each reflect $50,000 and Lisa reflects the $150,000 we wrote. The missing $50,000 has been assigned to Lisa’s Member with Data member.

If this is what we intended with the writeback, then great! But if it’s not what we intended, how else might we approach this problem?

First, we could write back only to members with no other subordinates other than their Member with Data member. If values need to be written up and down the hierarchy, this might require you to expose the Member with Data member through the MembersWithData property setting.

Alternatively, we might write back to the attribute hierarchy upon which the parent-child hierarchy is based. (For example, the Employees parent-child hierarchy is based on an Employee attribute hierarchy so that we might write back to it instead.) This works so long as navigation of the attribute-hierarchy is not problematic.

Finally, you might allow write back to any member in a parent-child hierarchy and take explicit control of the allocation process using a weighting expression. In the expression, you might identify the Member with Data member using the DATAMEMBER function.

Of course, there’s always the option of not using a parent-child hierarchy in your cube design and avoiding this complication altogether. However, parent-child hierarchies are very flexible making them ideal for performing dimension writeback, a topic I will present in the next few blog entries.