SSAS: Performance of MDX SCOPE() statement

Further to my recent posts on using SCOPE() statements in a cube and potential issues in using the SCOPE() statement, I came across another issue last week.

In a cube I was developing I had used a SCOPE() statement to ensure that when a particular measure was selected, the dimension member used (for one particular dimension) would be the datamember, so that the measure value displayed would be the "fact table" value and not the aggregated value of the children. (for more on using the DATAMEMBER function see this post).

In the calculation tab of the cube editor I wrote the following:

SCOPE([Measures].[MyMeasure]); THIS=[Portfolio].[Portfolio Name].CURRENTMEMBER.DATAMEMBER; END SCOPE;

However, in user testing accross large volumes of data this measure performed very slowly, taking over a minute to return results.

I tried experimenting with different approaches, and found that the desired results could be achieved without using the scope statement.  A calculated measure can be used, that contains the datamember function in its logic:
CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure2]
AS ([Measures].[MyMeasure,[Portfolio].[Portfolio Name].DATAMEMBER), VISIBLE = 1 ;

This measure produced the same result set but surprisingly the response time reduced to around ten seconds.

If anyone has had a similar experience and has explanation why using the DATAMEMBER function in a calculated measure rather than a SCOPE statement should perform faster please leave a comment and let me know!

SSAS - DATAMEMBER: Parent Member should not be the sum of it's children, but has it's own value

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