Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Can your sum be a subtraction? Or can you avoid it altogether?

with 4 comments

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.

Written by Chris Webb

April 3, 2008 at 6:21 pm

Posted in MDX

4 Responses

Subscribe to comments with RSS.

  1. I was wondering how many customers are in the that cube?

    Ar'ash

    April 5, 2008 at 4:32 pm

  2. A lot, but you can see the same effect on much smaller volumes.

    Chris

    April 8, 2008 at 6:42 am

  3. 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
     
     

    冰之子

    August 10, 2008 at 7:15 am

  4. 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

    Chris

    August 20, 2008 at 11:40 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers

%d bloggers like this: