Chris Webb's BI Blog

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

Sets in the Where Clause and Autoexists

with 10 comments

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]
where([Date].[Calendar].[Date].&[20010701])

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]
where({[Date].[Calendar].[Date].&[20010701]})

…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]
where({[Date].[Calendar].[Date].&[20010701]})

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:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=481774

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

Written by Chris Webb

August 8, 2009 at 11:08 pm

Posted in MDX

10 Responses

Subscribe to comments with RSS.

  1. Odd behaviour indeed. I logged the following bug, do you think it may be related? I suspected Auto-exists to be the culprit here too.https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473164Obviously the result is NULL in my case, but I\’d expect to get the tuple back either way. Something in the AS engine ate it :-(.

    Colin

    August 12, 2009 at 5:09 pm

  2. Tomislav

    August 14, 2009 at 7:17 am

  3. 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]\’)}) ;

    Tony

    August 16, 2009 at 2:51 pm

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

    Tomislav

    August 25, 2009 at 11:10 pm

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

    Chris

    August 26, 2009 at 9:57 pm

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

    SELECT
    {[Date].[Calendar].[Month].&[201109]:[Date].[Calendar].[Month].&[201208]}
    *
    {[ShoppingAreaOfCustomer].[Shopping Area].[ABC Shopping Area]}
    ON ROWS,
    {[Measures].[Amount]}
    ON COLUMNS

    FROM MyCube
    WHERE
    ([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

    SELECT
    {[ShoppingAreaOfCustomer].[Shopping Area]].[ABC Shopping Area]}
    ON ROWS,
    {[Measures].[Amount]}
    ON COLUMNS
    FROM MyCube
    WHERE
    (
    [ABC Centre Customers],
    {[Date].[Calendar].[Month].&[201109]:[Date].[Calendar].[Month].&[201208]}
    )

    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]
    AS
    CASE

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

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

    SET [ABC Centre Customers]
    AS EXTRACT(
    NONEMPTY (
    {[Shop].[Shopping Centre].[ABC Shopping Centre]}
    * {[Customer].[Customer ID].[Customer ID]}
    * {[Date].[Month].[Month]}
    ,[Measures].[IsActive]
    )
    ,[Customer].[Customer ID]
    ,[Date].[Month]
    )

    Min

    October 4, 2012 at 8:50 am

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

      Sorry,

      Chris

      Chris Webb

      October 8, 2012 at 9:13 pm

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

    Regards,
    Min

    Min

    October 9, 2012 at 3:09 am

  8. Hi Chris:

    Can we use calculated member in where clause, suppose strtomember() function ?

    Tanvir Zobair

    May 22, 2014 at 5:40 am

    • Yes, you can use calculated members in the Where clause and you can use the StrToMember function too

      Chris Webb

      May 22, 2014 at 6:49 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,070 other followers

%d bloggers like this: