Home Dashboard Directory Help
Search

MDX Where with multiselect and two hierarchies of same dimension gives wrong results by Elvis Long


Status: 

Active


4
0
Sign in
to vote
Type: Bug
ID: 790063
Opened: 6/13/2013 1:30:53 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

System is Analysis Services 2008 R2 Developer (Version 10.50.2500.0).

The Bug works with Dimensions that have multiple Hierarchies you can reproduce it in "Adventure Works DW 2008 R2" using the Date-Dimension.
SELECT [Measures].[Order Count] ON 0
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Month].&[2007]&[1],
    {[Date].[Month of Year].&[2],
     [Date].[Month of Year].&[3]})
It should show the orders for all time-periods which are Januar 2007 and one of the Month {February, March}, I expect the empty set. But it tells [Order Count]=309, that is the Count for Januar 2007.

If you use two hierarchies of the same dimension in the where-statement, but without multiselect it works. For example you get NULL as expected with:
SELECT [Measures].[Order Count] ON 0
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Month].&[2007]&[1],
     {[Date].[Month of Year].&[2]})
If you put one of the Hierarchies [Calendar] or [Month of Year] on Rows then autoexists removes all rows from the result, just as expected:
SELECT [Measures].[Order Count] ON 0
, [Date].[Calendar].[Month].&[2007]&[1] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Month of Year].&[2],
        [Date].[Month of Year].&[3]})
You can combine hierarchies of the same dimension by using sub-cubes in place of where. But I use generated MDX, so Microsoft please solve this bug.
Details
Sign in to post a comment.
Posted by Stefan Wilms on 8/20/2013 at 5:46 AM
Multiselects in WHERE show wrong results if the other Hierarchy of the same Dimension is on an axis, too. Examples on "Adventure Works DW 2008R2":

SELECT [Date].[Month of Year].Members ON 0
, [Measures].[Reseller Sales Amount] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Calendar].[Month].&[2007]&[1],[Date].[Calendar].[Month].&[2007]&[2]})
--shows [January]=$1,317,541.83, [February]=$2,384,846.59
--but [All Periods]=$5,266,343.51 error! that is the sum including [March]

SELECT [Date].[Month of Year].Members ON 0
, [Measures].[Reseller Sales Amount] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Calendar].[Month].&[2007]&[1]})
--shows [January] and [All Periods], both with $1,317,541.83 OK

SELECT [Date].[Month of Year].Members ON 0
, [Measures].[Reseller Sales Amount] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Calendar].[Month].&[2007]&[1],[Date].[Calendar].[Month].&[2007]&[1]})
--ugly 'where' with double [January 2007] shows single column [January 2007]=$1,317,541.83 OK
--but [All Periods]=$5,266,343.51 error! that is the sum of [Q1 CY 2007]

It isn't nice to repeat members in a set, but it should not affect the set. Just because the single Month is expressed as a multiselect the result becomes wrong.
Posted by Hans H Eriksen on 7/26/2013 at 6:41 AM
I've tested some more, this leads me to think this bug is in the calc engine optimization. Speculation follows:

With my real life project with week/day and month/day hierarchies, I believe an intersect of the two hierarchies
are computed the following way where weekday and monthday are the same day attribute:

(week, month) = Aggregate(Intersect(Descendants(week, weekday), Descendants(month, monthday)))

When the (week, month) intersection is calculated, it is obvious that both Descendants() steps
in the formula above need to be calculated.

However, when calculating (weekday, month) there is no need to perform the Descendants(week, weeekday) step
since we are already there. This might be where it all goes wrong, the optimizer not performing
the Descendants(month, monthdays) step and erroneously just returning month.
Posted by Hans H Eriksen on 7/26/2013 at 3:29 AM
I've experienced a similar problem involving a calculation, so it's not limited to the WHERE clause.

I' haven't got the AdventureWorks database, so this is untested:
WITH
    MEMBER [Date].[Calendar].[Jan 2007] AS [Date].[Calendar].[Month].&[2007]&[1]
    MEMBER [Measures].[Jan 2007 Order Count] AS ([Measures].[Order Count], [Date].[Calendar].[Jan 2007])
SELECT [Measures].[Jan 2007 Order Count] ON 0,
{[Date].[Month of Year].&[2],
    [Date].[Month of Year].&[3]} ON 1
FROM [Adventure Works]

I believe this will trigger the same wrong result as Elvis' code.

The bug is not triggered if I use cross join on axis 0 instead of a calculated member.

Also, I believe the bug depends on levels relations in the two hierarchies used.
My real life problem involves a week/day hierarchy and a month/day hierarchy.

If my query includes a month from month/day on axis 0 and both weeks and days from week/day on axis 1,
the weeks are calculated correctly and the days incorrectly; days return the value from the month/day hierarchy.
Sign in to post a workaround.