Chris Webb's BI Blog

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

Unnecessary All Members and Performance Problems

with 16 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

16 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

  4. Hi Chris,

    How can we avoid all member if it’s a calculated member. I have an attribute Aggregations in Date dimension with a single value “Current Date”. There are calculated members YTD, QTD, WTD in the Aggregations attribute. So when I pull Aggregations on Excel I see .AllMembers in SQL Profiler, Is there any way I can have it pull [Aggregations].[Current].(I have posted similar question in another thread, sorry about that. I just found this post)

    Vidya

    October 24, 2014 at 9:22 pm

  5. We are using Excel 2010 and I can select calculated members individually. However, irrespective of what I have selected, when I see the SQL Profile, the query pulls .Allmembers from the attribute containing the calculated members.
    For e.g. I have ‘Current Date’ as a value for attribute ‘Aggregation’ and the rest ‘YTD’,’QTD’,’MTD’ are calculated members. So when I pick any one – Current or YTD or MTD, the MDX in SQL Profiler shows [Aggregation].[AllMembers]. And I get ‘query optimizer generated too many subcubes’ error when I pick more than 6 attributes and 6 measures. I’m thinking if I can make sure that [Aggregations] attribute does not select .allmembers and only query the selected option (e.g [Aggregation.[YTD]) then maybe I will be able to avoid this error

    Vidya

    October 28, 2014 at 1:42 pm

  6. Please see below query which errors out if I try to add anything beyond existing –

    Query Text:
    SELECT {[Measures].[GR $],[Measures].[Net $],[Measures].[Msr3],[Measures].[Msr4],[Measures].[Msr5],[Measures].[Msr6],[Measures].[Msr7],
    [Measures].[Msr8]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS ,
    NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(
    Hierarchize(DrilldownMember({{[Date].[Date].[Calendar Name].AllMembers}}, {[Date].[Date].[Calendar Name].
    &[Season Calendar]},,,INCLUDE_CALC_MEMBERS)),
    Hierarchize({[Date].[Aggregations].[Aggregations].AllMembers})),
    Hierarchize({[Date].[Comparisons].[Comparisons].AllMembers})),
    Hierarchize({DrilldownLevel({[Advertiser].[Advertiser Name].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Agency].[Agency Name].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Seller].[Seller Sign].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Seller Attribute].[Seller Attribute Name].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Product].[Prod Name].[All]},,,INCLUDE_CALC_MEMBERS)}))
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Product Category].[Prod Cat Name].[Prod Cat Name].[Report Code],
    [Seller].[Seller Sign].[Seller Sign].[Seller Display Order],[Date].[Date].[Year].[Calendar Name],
    [Date].[Date].[Year].[YEAR START DATE],[Agency].[Agency Name].[Agency Name].[AAAA Code],[Agency].[Agency Name].
    [Agency Name].[Parent Agency],[Advertiser].[Advertiser Name].[Advertiser Name].[Parent Advertiser] ON ROWS
    FROM
    (SELECT ({[Date].[Date].[Year].&[Season Calendar]&[31015]},{[Business Date].[Comparisons].[PY]},
    {[Advertiser].[Advertiser Name].&[5672]},{[Seller].[Seller Sign].&[XYZ]}) ON COLUMNS FROM [Cube])
    CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
    SELECT {[Measures].[GR $],[Measures].[Net $],[Measures].[Msr3],[Measures].[Msr4],[Measures].[Msr5],[Measures].[Msr6],[Measures].[Msr7],
    [Measures].[Msr8]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS ,
    NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(
    Hierarchize(DrilldownMember({{[Date].[Date].[Calendar Name].AllMembers}}, {[Date].[Date].[Calendar Name].
    &[Season Calendar]},,,INCLUDE_CALC_MEMBERS)),
    Hierarchize({[Date].[Aggregations].[Aggregations].AllMembers})),
    Hierarchize({[Date].[Comparisons].[Comparisons].AllMembers})),
    Hierarchize({DrilldownLevel({[Advertiser].[Advertiser Name].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Agency].[Agency Name].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Seller].[Seller Sign].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Seller Attribute].[Seller Attribute Name].[All]},,,INCLUDE_CALC_MEMBERS)})),
    Hierarchize({DrilldownLevel({[Product].[Prod Name].[All]},,,INCLUDE_CALC_MEMBERS)}))
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Product Category].[Prod Cat Name].[Prod Cat Name].[Report Code],
    [Seller].[Seller Sign].[Seller Sign].[Seller Display Order],[Date].[Date].[Year].[Calendar Name],
    [Date].[Date].[Year].[YEAR START DATE],[Agency].[Agency Name].[Agency Name].[AAAA Code],[Agency].[Agency Name].
    [Agency Name].[Parent Agency],[Advertiser].[Advertiser Name].[Advertiser Name].[Parent Advertiser] ON ROWS
    FROM
    (SELECT ({[Date].[Date].[Year].&[Season Calendar]&[31015]},{[Business Date].[Comparisons].[PY]},
    {[Advertiser].[Advertiser Name].&[5672]},{[Seller].[Seller Sign].&[XYZ]}) ON COLUMNS FROM [Cube])
    CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    Vidya

    October 28, 2014 at 2:50 pm

    • Hi Vidya,

      Which version of SSAS are you using? I’ve tested this in Excel 2013 and SSAS 2014 and I can see queries being generated that do select individual members; I’m pretty sure that Excel 2010 and more recent versions of SSAS behave in the same way. However if you are using SSAS 2008 or earlier I can imagine that the MDX generated might be different.

      Chris

      Chris Webb

      October 29, 2014 at 9:52 pm

  7. We are using SQL Server 2012 and Excel 2010. From the above query I see that in the inner select statement it does select the individual member ({[Business Date].[Comparisons].[PY]}), however the outer select statement pulls .Allmembers (Hierarchize({[Date].[Comparisons].[Comparisons].AllMembers}))

    Can the outer select be modified to not pull .Allmembers. I’m not sure if that is the fix to the error I see in Excel. But I do know that this error occurs only when the Date Calculated Members are used.

    Vidya

    October 30, 2014 at 2:17 pm

    • The outer SELECT statement is irrelevant here: this is an MDX subselect, so it’s the inner SELECT that controls which members are chosen. That makes me think that Excel is selecting exactly what you have chosen in your PivotTable and the problem lies elsewhere.

      Chris Webb

      October 30, 2014 at 2:21 pm

  8. I just tried to construct the same MDX in SSMS and modified the outer select to pull [Business Date].[Comparisons].[PY] and [Aggregations].[YTD] and this did not fail. So looks like the .AllMembers in the outer select is the issue. Is there any way we could control this and not have Excel pull .Allmembers for Calculated Members.

    Vidya

    October 30, 2014 at 2:39 pm

  9. Looks like we were typing the same time! So I do get the correct results in Excel for what I choose for Aggregations and Comparisons. The issue I am having is that it errors out after choosing too many attributes and measure. And this is the error I get – ‘query optimizer generated too many subcubes’

    Hope I am not confusing you.

    Vidya

    October 30, 2014 at 2:41 pm

    • I think that you need to try to rewrite your calculations somehow to optimise them, but I can’t say how unfortunately.

      Chris Webb

      October 30, 2014 at 10:43 pm

  10. Hi Chris,

    I have unfortunatelly similar problem that was already mentioned in above discussion. I want to select calculated members so the allmembers is must but I am facing the performance issues when using 4 dimensions. Suspicious for me is that the fact table contains only 4000 records and number of calculated members is aprox. 30.

    Bellow MDX statement A is taking cc10 minutes to get some results whereas the statement not using allmbembers is taking only 4 seconds. Could you please get me some hint what could be the issue?

    select non empty
    {[DIM METRIC].[H_Metric].allmembers *
    [DIM PRODUCT].[H_Product].members *
    [DIM REPORTING DATE].[d Reporting].[d Reporting] *
    [DIM CHANNEL].[H_Channel].[Channel] *
    [DIM COUNTRY].[H_Country].[Country] *
    [DIM CURRENCY].[H_Currency].[Currency] *
    [DIM VOLUME TYPE].[H_Volume_Type].[Volume type]
    } on rows,
    [Measures].[Product Customer Amount] on columns
    from [product portfolio]

    Jan

    October 31, 2014 at 10:37 am

    • There’s nothing wrong with this query – the problem will be something to do with the definition of your calculations.

      Chris Webb

      October 31, 2014 at 12:11 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,310 other followers

%d bloggers like this: