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!
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 :-(.
Hi Chris, here are my thoughs on the subject:http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!349.entryRegards,Tomislav
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]\’)}) ;
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.
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.
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]
)
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
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
Hi Chris:
Can we use calculated member in where clause, suppose strtomember() function ?
Yes, you can use calculated members in the Where clause and you can use the StrToMember function too
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.
Which version of SSAS are you using?
Hi Chris, we’re running SQL 2012 / SSAS (MD) .
I’m running SSAS 2014 and I can’t reproduce the problem on Adventure Works. Is there a direct or indirect attribute relationship between your Date and Month attributes?
Hi Chris,
Even after 10 years this issue still exists.
I can repeat it with the following query on AdventureWorks database.
SELECT
[Measures].[Internet Sales Amount] ON 0
,[Date].[Month].Members ON 1
FROM [Adventure Works]
WHERE {[Date].[Date].&[20130105],[Date].[Date].&[20130106]}
Returns $857,689.91 which is total sales for January 2013
Also select with range operator does the same.
SELECT
[Measures].[Internet Sales Amount] ON 0
,[Date].[Month].Members ON 1
FROM [Adventure Works]
WHERE {[Date].[Date].&[20130105] : [Date].[Date].&[20130106]}
Returns $857,689.91 which is total sales for January 2013
If where part consists of a single member then result is correct.
SELECT
[Measures].[Internet Sales Amount] ON 0
,[Date].[Month].Members ON 1
FROM [Adventure Works]
WHERE {[Date].[Date].&[20130105]}
Returns $32,681.44 which is sales for 5th of january, 2013
So the main question is why it happens and what rule dictates it to happen? I assume that the first query shoould return just sales for 5th and 6th of Janury which is $54,468.46
I am using SSAS 2016.