A common problem that I've had to deal with is how to handle the sales of a parent member in a hierarchy. Under normal SSAS conditions the parent will be the sum of the children. However this is often not the case. A manager of a team may make sales in his own right and we may want to see his own sales, and not the sum of his team members' sales.
In SSAS every non-leaf member on a parent/child hierarchy has an extra system-generated child called a datamember. Assuming the fact table contains values for a non-leaf member on a parent/child hierarchy, then these values will in fact be assigned to the member's datamember.
We can then use the MDX DATAMEMBER function. This function causes the relevant measure to display the measure value associated datamember, and not aggregate the values of the children.
Using this within a SCOPE statment in the cube calculation script will define this functionality for the specified measure:
SCOPE([Measures].[Sales]);
THIS=[Employee].[Employees].CURRENTMEMBER.DATAMEMBER;
END SCOPE;
Now the Sales measure will always show the value assigned to the datamember, instead the aggregated value.
More detail, and a good example of this can be found here: http://www.packtpub.com/article/measures-and-measure-groups-microsoft-analysis-services-part1
Thanks for this article. I've got a problem that is rather the opposite:
ReplyDeleteI'm desperately trying to map one element to another. The source is a node with a lot of child elements. But when I write something like this:
SCOPE([Dim].[NewAccount]);
THIS=[Dim].[OldAccount];
END SCOPE;
The Scope always refers to the datamember of "OldAccount" and since this is empty the wohle result is empty.
But I don't want to have the value of the datamember but the sum of the children of "OldAccount" - which would be the expected behaviour in a regular dimension.