Chris Webb's BI Blog

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

Unnecessary All Members and Performance Problems

with 4 comments

Maybe an obscure problem, this one, but worth recording nonetheless. The other week I was performance tuning some queries on a customer’s SSAS 2008R2 instance and came across a very strange issue related to the presence of unnecessary All Members in tuples. In this case it was in machine-generated MDX but it’s certainly the case the people new to MDX often include All Members in tuples when they are not actually needed; it’s a not good idea to do this because it can sometimes have unexpected effects as a result of attribute overwrite and because, as I found, it can also cause severe performance problems too.

The problem can be reproduced very easily against Adventure Works on the Customer dimension. Consider the following query that returns a list of customers who bought more than $1000 of goods in 2003:

with
set filteredcustomers as
filter(
[Customer].[Customer Geography].[Customer].members
, ([Measures].[Internet Sales Amount]
, [Date].[Calendar Year].&[2003])>1000)
select
{}
on columns,
filteredcustomers 
on rows
from [Adventure Works]

Pretty straightforward, and it returns instantly on my laptop as I’d expect. However, adding the All Member from the City hierarchy into the tuple used in the filter() function makes the query run very slowly indeed (I killed it after several minutes):

with
set filteredcustomers as
filter(
[Customer].[Customer Geography].[Customer].members
, ([Measures].[Internet Sales Amount]
, [Customer].[City].[All Customers]
, [Date].[Calendar Year].&[2003])>1000)
select
{}
on columns,
filteredcustomers 
on rows
from [Adventure Works]

The All Member here isn’t necessary at all; it won’t affect how the filter works or the set returned at all. Looking in Profiler it seems as though its presence triggers cell-by-cell mode, which is the cause of the awful performance. Interestingly, the performance got worse the more attributes were on the hierarchy – deleting attributes, even when they weren’t used in the query, improved query performance. I’m told the problem could be the result of attribute decoding (which Mosha referred to here, but which I don’t know much else about) as a result of attribute overwrite

Anyway in my case it wasn’t possible to change the MDX because it was being generated by a client tool – the All Member was there because the City hierarchy was being used as a parameter in the query, although in this case nothing had been selected on it. There was a workaround that I found though: it turns out the problem does not occur for user hierarchies that include the key attribute as their lowest level. So, I renamed the City attribute, hid it, and then created a new user hierarchy called City that had Customer as its lowest level:

image

With this done, both of the queries above return instantly.

Written by Chris Webb

December 22, 2012 at 2:52 pm

4 Responses

Subscribe to comments with RSS.

  1. Really interesting, and really bad that the performance issue is so big. I’ts good having a workaround for that, even if it could be hard to use if queries are generated dynamically and the parameter can change. Is there a Connect item for that? I would like to vote for it.

    Marco Russo

    December 24, 2012 at 7:09 am

  2. Interesting, I had a similar scenario happen to me at a client’s place. We were using excel to display results from the cube. The moment we brought an attribute to the filter (must say it had a lot of members), even though the value was set to All, the query performance went from 6s to 20s. I have moved on from the client but I still have the simplified version of the query saved with me

    SELECT
    {
    [Measures].[Invoice Freight Charged],
    [Measures].[Invoice Qty]
    } ON COLUMNS ,
    NON EMPTY
    Hierarchize({DrilldownLevel({[Ship To Customer].[Customer Num].[All]},,,INCLUDE_CALC_MEMBERS)})
    *Hierarchize({DrilldownLevel({[Ship To Customer].[Customer Desc].[All]},,,INCLUDE_CALC_MEMBERS)})
    //Removing Sales Document Below allows the extra WHERE set
    *Hierarchize({DrilldownLevel({[Ship To Customer].[Customer Region Desc].[All]},,,INCLUDE_CALC_MEMBERS)})
    *Hierarchize({DrilldownLevel({[Material Master].[Material Desc].[All]},,,INCLUDE_CALC_MEMBERS)})
    *Hierarchize({DrilldownLevel({[Billing Date].[Actual Date].[All]},,,INCLUDE_CALC_MEMBERS)})
    *Hierarchize({DrilldownLevel({[Sales Doc Information].[Inco Terms Desc].[All]},,,INCLUDE_CALC_MEMBERS)})
    *Hierarchize({DrilldownLevel({[Sales Doc Information].[Customer PO].[All]},,,INCLUDE_CALC_MEMBERS)})
    *Hierarchize({DrilldownLevel({[Sales Doc Information].[Sales Document].[All]},,,INCLUDE_CALC_MEMBERS)})
    ON 1
    FROM [Chem - Sales]
    WHERE ([Sold To Customer].[Customer Num].&[109012],[Created On].[Fiscal].[Fisc Year].&[2012]
    //,[Sales Doc Information].[Sales Doc Type].[All]
    )

    You can see the commented out line for Sales Doc Information, which was causing the issue. Do you think the cause could be the same issue that you mentioned?

    Jason Thomas

    December 26, 2012 at 1:13 pm

  3. I found another way of avoiding the poor performance: adding the CurrentMember from Customer Geography to the tuple ,as in the query below.

    with
    set filteredcustomers as
    filter(
    [Customer].[Customer Geography].[Customer].members
    , ([Measures].[Internet Sales Amount]
    , [Customer].[City].[All Customers]
    –Currentmember on Customer Geography added to tuple below
    , [Customer].[Customer Geography].currentmember
    , [Date].[Calendar Year].&[2003])>1000)
    select
    {}
    on columns,
    filteredcustomers
    on rows
    from [Adventure Works]

    Chris Webb

    January 1, 2013 at 10:46 pm


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,220 other followers

%d bloggers like this: