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 2008R2 - Scope and Assignments - "A set has been encountered that cannot contain calculated members."

In my previous post I discussed using the SCOPE statement in a cube.   I use it quite extensively in my designs and last week I came accross an unexpected issue with it.

I have a cube containing many measures for which I require different functionality under certain conditions.  Those conditions can actually be described in subcubes, and therefore the SCOPE statement addresses this requirement.

The functionality I want is the same for several measures and therefore I put them together in the same SCOPE block:

 SCOPE([Measures].[Area1],[Measures].[Area2]); THIS=(0); END SCOPE;

Where Area1 and Area2 are both measures defined in the cube.  The above scope would set them both to zero, but this is just for illustration.

The issue arises if the 2 measures above, Area1 and Area2, are actually calculated measures. If that is the case then when we deploy this MDX we get the following error:

 "A set has been encountered that cannot contain calculated members."

This can be confusing as the error message does not actually identify the SCOPE as the source of the issue.  But you can be fairly sure that that is where the problem is.

For some reason using more than one calculated measure in a single scope statment is a problem for SSAS.  If you have very few relevant measures then you could split them into individual scope statments, but this would be laborious if you have several. The solution is to use an assignment script instead. This is as simple as:

({[Measures].[Area1],[Measures].[Area2]}) = 0;

Type this in the calculation script and, hey presto, the error disappears and the MDX works as expected.

Chris Webb also covers this here.

Analysis Services MDX - SCOPE Statement

A question was put to me recently on how to setup a measure that performs differently based on different member selections in the cube.

One option is to use lots of nested IF statments. This may well work, but is quite untidy and is known to be a little slow.

Wherever you are considering using IF type logic in your calculation script, it would be a very good idea to consider using the much faster SCOPE statement instead.  The SCOPE statment is clearer and significantly more performant.

As an example of how to use the scope statment i'll use a geometry based illustration:  My cube contains a dimension called Shape that contains 2 members: Square and Circle.  I then have a number of measures:  Length and Radius.  I create a calculated measure called Area.  The calculation for area depends on what shape i have selected.  If i have selected square then Area = (Length)^2, while for Circle it would be Area=Pi*(Radius)^2.

Circle and Square can be thought of as subcubes.  I can therefore define the scope statement as follows:

SCOPE([Measures].[Area],[Shapes].[Circle]); THIS=(Pi*[Measures].[radius]*[Measures].[radius]);
END SCOPE;

SCOPE([Measures].[Area],[Shapes].[Square]); THIS=([Measures].[Length]*[Measures].[Length]);
END SCOPE;

 Note - I would still need to have defined the Area measure in some manner elsewhere in my script.  The above statments only refer to the measure, they dont actually create it.

That is essentially an introduction to how to use the SCOPE statment.  Next post will be on issues to watch out for when using it!

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