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:
With this done, both of the queries above return instantly.
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.
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?
It looks like it could be, yes
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]
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)
Hi Vidya, does the option mentioned in this post solve your problem? http://kkryczka.wordpress.com/2012/07/04/cannot-see-calculated-members-on-dimensions-in-excel-2007-power-pivot/
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
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
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
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.
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.
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.
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.
I think that you need to try to rewrite your calculations somehow to optimise them, but I can’t say how unfortunately.
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]
There’s nothing wrong with this query – the problem will be something to do with the definition of your calculations.
Hi Chris,
when i browse the dimension in data tools and select hierarchy i see an “ALL members” level Is there any way to hide it. Here in a talking about hiding it @ the hierarchy level and not the MDX.
You need to set the IsAggregatable property on the attribute to False