Quite often you’ll find yourself writing calculations that need to sum up large sets; in fact, they might be summing up all of the members on a level apart from one or two. In that situation it’s worth using the structure of your dimension to your advantage. Take the following query, which sums up the Internet Sales of all customers apart from one and then does a TopCount based on this:
WITH
MEMBER MEASURES.TEST AS
SUM(
EXCEPT(
[Customer].[Customer].[Customer].MEMBERS
, {[Customer].[Customer].&[20075]})
, [Measures].[Internet Sales Amount])
SELECT MEASURES.TEST ON 0,
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 10, MEASURES.TEST) ON 1
FROM [Adventure Works]
On my laptop using AS2005 the query runs in 2 minutes 2 seconds on a cold cache (Katmai does no better with this query, incidentally). Yuck. But if we change the calculation around, so that rather than summing the customers we do want we take the sales for all customers and subtract the sales for the customer we don’t want (which is fine if the measure we’re looking at has an aggregate function of Sum) then we can do the following:
WITH
MEMBER MEASURES.TEST AS
[Measures].[Internet Sales Amount] –
([Customer].[Customer].&[20075], [Measures].[Internet Sales Amount])
SELECT MEASURES.TEST ON 0,
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 10, MEASURES.TEST) ON 1
FROM [Adventure Works]
…which executes in 1 second on a cold cache. Taking this further, if you have a set like this you’re frequently summing up in calculations it might be a good idea to create a new attribute on your dimension to avoid having to do any work in MDX at all. In the Adventure Works example above, maybe Customer 20075 is the Sultan of Brunei and he ordered 5000 new bikes for all his friends – which means that including him in your calculations would skew the results. What you could do is create a new boolean attribute on Customer called something like ‘Exclude from Calculations’, which would then mean you could rewrite the query like this:
SELECT [Measures].[Internet Sales Amount] ON 0,
TOPCOUNT(
[Date].[Date].[Date].MEMBERS
, 10, [Measures].[Internet Sales Amount]) ON 1
FROM [Adventure Works]
WHERE ([Customer].[ExcludeFromCalculations].&[False])
and probably get even better performance.
I was wondering how many customers are in the that cube?
A lot, but you can see the same effect on much smaller volumes.
Hi Chris,
This is my first time to leave message in here but I have been reading your blog for quite awhile. It helps me sooooo much to resolve MDX issues. Thanks. Now, this article once again give me some information on my existing issue. I\’m currently using sum(exclude([Dimension].[Attributes].[All].Children, [Dimension].[A])) to calculate the sub-total. However, as you said, it takes quite long to calculate the result and affect others\’ measures performance (becoz I set this calculation in Scope for the [Dimension].[Attribute].[All]… any smarter way!?). I have also tried to simply subtract the exclude member by the [All]. It improve quite alot but still affecting the other measures performance somehow. Unfortunately, I have difficulty of using your last suggestion. If I put the FALSE to filter clause, it filter the exclude member in the [All].children level. What my client want is show the member [A] in members listing but not include the value of [A] in sub-total. Do you have any suggestion about this? Anyway, thanks for your sharing.
Ivan
What you\’d have to do is not put the FALSE member in the Where clause, but in the calculation you\’re using in your subtotal. So something like
WITHMEMBER MEASURES.TEST AS([Customer].[IncludeInTotal].[FALSE], [Measures].[Internet Sales Amount])
would give you the total without all the customers who have IncludeInTotal set to False