Sets in the Where Clause and Autoexists

I don’t usually blog about bugs, but there are some cases where the dividing line between what’s a bug and what is ‘by design’ is unclear – and in these cases, a warning to the user community is always helpful. This is one of those cases…

The other day I was talking to Peder Ekstrand of DSPanel and he showed me a pair of queries running on SSAS 2008 that had him confused and to be honest, to me looked clearly buggy. I managed to repro the behaviour on Adventure Works on 2008 (friends reproed it on 2005 too) and here are my queries. The first one returns a single cell containing the value $14,477.34, the value of Internet Sales on July 1st 2001, as you’d expect:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Fiscal].[All Periods] on 1
from [Adventure Works]

The second returns the value $29,358,677.22, the value of Internet Sales Amount across all time periods:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Fiscal].[All Periods] on 1
from [Adventure Works]

…which, incidentally, is the same value you’d get if you removed the Where clause completely. To me this second result makes no sense whatsoever and is extremely confusing.

The only difference between the two queries is that in the first the Where clause contains a single member whereas in the second that member is enclosed in braces, meaning it is now a set containing a single member. The second important thing to point out is that we have members from different hierarchies on the Date dimension on Rows and in the Where clause, meaning that auto-exists is coming into play (see this section on BOL for an explanation of what auto-exists is).

The third thing to note is that it only happens in some cases. So for example when you run the following query which has a member from the Calendar Year hierarchy rather than the Date hierarchy in the set:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Fiscal].[All Periods] on 1
from [Adventure Works]
where({[Date].[Calendar Year].&[2001]})

…you get the value you’d expect, ie the Internet Sales Amount for the Calendar Year 2001. This query, with a Fiscal Year on Rows, also returns the ‘correct’ result, $14,477.34:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Fiscal Year].&[2002] on 1
from [Adventure Works]

Most interestingly, where the members on Rows and in the Where clause from the second query above are swapped, also returns the ‘correct’ result:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Calendar].[Date].&[20010701] on 1
from [Adventure Works]
where({[Date].[Fiscal].[All Periods]})

What’s going on here? Clearly something to do with sets in the Where clause, auto-exists and probably attribute relationships, and something that could easily cause a lot of confusion for users and cube developers alike. I’ve been told that the current behaviour is ‘by design’ but the dev team are aware it’s less than ideal; it’s something to do with maintaining consistency with what happens when there are sets in the Where clause in some scenarios. But for this query:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Fiscal].[All Periods] on 1
from [Adventure Works]
where({[Date].[Calendar].[Date].&[20010701], [Date].[Calendar].[Date].&[20010702]})

…I would expect to see the aggregate of Internet Sales Amount for July 1st and July 2nd 2001.

Anyway, here’s the Connect I opened about this:

The more votes it gets, the more likely it’ll get fixed!

15 responses

  1. If the slicer portion is cast to a set with one member "Date].[Calendar].[Date].&[20010701]", the result of the 2nd query is the same as the 1srt query:select [Measures].[Internet Sales Amount] on 0,[Date].[Fiscal].[All Periods] on 1from [Adventure Works]where([Date].[Calendar].[Date].&[20010701]);select [Measures].[Internet Sales Amount] on 0,[Date].[Fiscal].[All Periods] on 1from [Adventure Works]where ({STRTOMEMBER(\'[Date].[Calendar].[Date].&[20010701]\’)}) ;

  2. Yes, I believe MDX parser recognizes certain patterns before it passes the expression tree further to the rest of the engine, and optimizes the MDX according to those patterns. StrToMember is a function that guarantees that the result will be a single member. Another one, that also works as expected, is .Item(0) around any set. As soon there\’s a function that returns a set (set functions of MDX vocabulary), like Head(x, 1), LastPeriods(1, x) or similar, although they return one member only, it is not being checked and the slicer is therefore not optimized. Set prevails and is overwritten with All member on that hierarchy because of attribute relations (and rule implemented in SSAS). In case of a single member in slicer, members from axis and slicer are treated equally inside a tuple/coordinate.Maybe the MDX parser will be optimized for these new patterns in the future also. That would be great.

  3. Actually, I think what Tony\’s found shows that this is an area of functionality that hasn\’t been \’finished\’ properly – there\’s no way that you should get different behaviour using StrToMember in the Where clause! I don\’t think there\’s any point trying to explain the way it works right now.

  4. Hi Chris,

    I have below two queries which user expect to return the same result, but not at the moment. I wonder it is correct or now, the difference is moved the date range from rows axis onto where clause set.

    –1st query

    {[ShoppingAreaOfCustomer].[Shopping Area].[ABC Shopping Area]}
    ON ROWS,

    FROM MyCube
    ([ABC Centre Customers])

    –note: [ABC Centre Customers] is a NamedSet defined in the cube which basically is a extract of some (CustomerID, Date.MonthID) based on their transaction behaviour

    — the sum of the amount returned by above query is higher than below query:

    –2nd query

    {[ShoppingAreaOfCustomer].[Shopping Area]].[ABC Shopping Area]}
    ON ROWS,
    FROM MyCube
    [ABC Centre Customers],

    I understand that the set in the where clause will affect the set on the row/column axis if they are from the same dimension; so the 1st query the where clause has Customer.CustomerID and Date.MonthID which would affect the ROWS axis, but I am 100% sure for the 2nd query how the where clause gets applied, you can see there are 2 sets in the 2nd query, would these two sets get evaluated first and then affect the row/column axes together or would it be done one by one?

    I am not certain they should be the same or not – I guess if the where clause try to evaluate into one set first then it may make sense, because if a customerID is not in the [ABC Centre Customers] set in those months, the spend will not be counted in the 2nd query, and that is what I gave the user’s explanation at the moment.

    if needed, below is the Named Set definition:
    with MEMBER [Measures].[Transaction Count Over 12 Months]

    WHEN INTERSECT([Date].[Month].CurrentMember, {[Date].[Month].&[201001]:[Date].[Month].&[201012]}).count > 0
    THEN SUM( {[Date].[Month].&[201001]:[Date].[Month].&[201012]} , [Measures].[Transaction Count] )
    SUM(LastPeriods (13, [Date].[Month].CurrentMember), [Measures].[Transaction Count] )
    – SUM( [Date].[Month].CurrentMember, [Measures].[Transaction Count] )

    MEMBER [Measures].[IsActive]
    AS IIF
    [Measures].[Transaction Count Over 12 Months] > 1 , 1 , null

    SET [ABC Centre Customers]
    {[Shop].[Shopping Centre].[ABC Shopping Centre]}
    * {[Customer].[Customer ID].[Customer ID]}
    * {[Date].[Month].[Month]}
    ,[Customer].[Customer ID]

    • Hi Min,

      It’s going to be almost impossible for me to say what’s going on here without spending a lot of time running queries on your cube; to be honest, even then, it might not be possible to work out what’s going on. It does look like you’re running into the issue described in this post, or maybe something else related to attribute overwrite.



  5. Hi Chris,

    Thanks for the response. I did try to filter down (filter function onto the [ABC Centre Customers] set) to one customer and check the difference, the sample of customers that I chose did not create difference.

    I will have a look at attribute override, as it is new ‘term’ for me; and there could be potential discovery there.


  6. I’m glad to find this post, as it confirms I may not be crazy.

    I’ve been trying to figure out a problem with a query like this:
    SELECT [Some Measures] ON 0
    ,[Date].[Month].[Month].Members ON 1
    FROM [Sales]
    WHERE [Date].[Date].&[Date Key 1] : [Date].[Date].&[Date Key 2]

    For some reason it seems to be including all date members that share the same day of month. For example, if I specify a date range of 4/30/2015 to 5/1/2015, the measure values include 4/1 and 4/30 for April, 5/1 and 5/30 for May. If the date range doesn’t cross months, it works correctly.

    The odd thing is it *used to* work correctly. I have a copy of our production cube project as of a month ago, and it works as expected. My first thought was someone may have changed attribute relationships in the date dimension, but after exhaustive comparison, the only changes to the entire SSAS database have been to one unrelated dimension.

    Even weirder is it works as expected if I take the data range out of the where clause and put it in the row axis, i.e. ([Date].[Month].[Month].Members, [Date].[Date].&[Date Key 1] : [Date].[Date].&[Date Key 2]) ON 1. It also works correctly if the range is in a subselect.

    I started to suspect some problem with the behavior of autoexists, and reading this lends credence to my suspicion. It’s just maddening that it used to work, and there must be some (unrelated) aspect of the database definition that triggered it.

  7. Pingback: MDX - Retrieving a level of members currently used in where clause - DexPage

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: