AdventureWorks date dimension shows different results depending on selected hierarchy

Question

Here are two simple queries which shows data on month level filtered by dates.

In the first query I am using Month level of "Date.Calendar" user hierarchy.

SELECT 
    NON EMPTY { [Measures].[Internet Sales Amount] } ON 0,
    NON EMPTY { [Date].[Calendar].[Month].&[2013]&[1] } ON 1
FROM    [Adventure Works]
WHERE   {[Date].[Date].&[20130105]:[Date].[Date].&[20130106]}

And recieved - January 2013 -> $857,689.91 Results

In the second query I am using "Date.Month of Year" attribute hierarchy.

SELECT 
    NON EMPTY { [Measures].[Internet Sales Amount]} ON 0,
    NON EMPTY { [Date].[Month of Year].&[1] } ON 1
FROM    [Adventure Works]
WHERE   { [Date].[Date].&[20130105] : [Date].[Date].&[20130106] }

And received - January -> $54,468.46 Results

I can not figure out why these two queries show different results. If the same dimension is used and data are filtered/sliced on the lovest possible level.

Here are values for each of these dates.

SELECT 
    NON EMPTY { [Measures].[Internet Sales Amount]} ON 0,
    NON EMPTY { [Date].[Calendar].[Date] } ON 1
FROM    [Adventure Works]
WHERE   { [Date].[Date].&[20130105] : [Date].[Date].&[20130106] }

January 5, 2013 $32,681.44

January 6, 2013 $21,787.02

Result

Total value for these two dates is equal with the second querie's result - $54,468.46

I understand that in the first query it is user hierarchy and the second query it is attribute hierarchy from the Date dimension but I can not figure out which rule(s) tells to calculate these values differently.

If someone could explain this logic behind - it would be very helpful. Any link to some resource which explains this logic also could help.

BTW: I have created simple cube with simple Date dimension which consists just of attribute hierrarchies (date, month, year) and it still works like in the first query so it is not clear why it behaves like that.

apologies. I have deleted my answer. I am trying to explore the reason for this. — MoazRub

MoazRub MoazRub · Accepted Answer · 2019-01-27T20:41:50

I explored the reason for this behavior, and I think I have figured out the reason. The explanation below is based on the book SQL SERVER 2008 MDX Step by Step pages 51-58(especially Avoiding Reference Conflicts).

Your problem is a typical Reference Conflict problem.In MDX a hierarchy cannot be used more than once in a given tuple, but if you are using a USER Hierarchy and its under lying Attribute Hierarchy, you essentially by-pass this check. This is what happened in your query

In your first query you are using the User Hierarchy

[Date].[Calendar].[Month].&[2013]&1

In MDX a User Hierarchy is translated to Attribute Hierarchies. In your first query

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON 0, NON EMPTY { [Date].[Calendar].[Month].&[2013]&1 } ON 1 FROM [Adventure Works] WHERE
{[Date].[Date].&[20130105]:[Date].[Date].&[20130106]}

you are using a User Hierarchy "[Date].[Calendar].[Month].&[2013]&1", which in its last level has "[Date].[Date]". Then in the where clause you use the same "[Date].[Date]" Attribute Hierarchy to filter. Since in the USER Hierarchy you have not used the leaf level, hence you have made a partial address, therefore the members and its ancestors are resolved. All the descendants are ignored in translation. Take a look at the below query(This is based on your first query,I have purposely removed your where clause).

with member [Measures].[CalendarYear] as [Date].[Calendar Year].currentmember.name
member [Measures].[CalendarSemester] as  [Date].[Calendar Semester of Year].currentmember.name
member [Measures].[CalendarQuater] as  [Date].[Calendar Quarter of Year].currentmember.name
member [Measures].[CalendarMonth] as  [Date].[Month of Year].currentmember.name
member [Measures].[CalendarDate] as [Date].[Date].currentmember.name
SELECT 
NON EMPTY { [Measures].[Internet Sales Amount] ,[Measures].[CalendarYear],[Measures].[CalendarSemester],[Measures].[CalendarQuater],[Measures].[CalendarMonth],[Measures].[CalendarDate]} ON 0,
NON EMPTY { [Date].[Calendar].[Month].&[2013]&[1] } ON 1
FROM    [Adventure Works]

Result.

Notice that Calendar year, Semester and quarter all are showing non-default values. But we never used them. This shows that translation of User Hierarchy is done into underlying Attribute Hierarchies. Now take a look at Calendar, it is still showing "All Period". Since it was ignored. Now if you add your where clause back, the Date still shows "All Period", there are two reasons 1) Because it was ignored in User Hierarchy translation , 2)You used a range in where. If you replace your row axis tuple with your where tuple it will still show "All Period" as a range is based. However while resolving it will take just two dates.

Based on this while resolving your query, it had two translation for Date attribute hierarchy, one said to ignore it based on User Hierarchy, the other provided a range. This is where due to conflict the result is in-correct.

Now lets consider the query you gave me in your comment earlier

with member [Measures].[CalendarYear] as [Date].[Calendar Year].currentmember.name
member [Measures].[CalendarSemester] as  [Date].[Calendar Semester of Year].currentmember.name
member [Measures].[CalendarQuater] as  [Date].[Calendar Quarter of Year].currentmember.name
member [Measures].[CalendarMonth] as  [Date].[Month of Year].currentmember.name
member [Measures].[CalendarDate] as [Date].[Date].currentmember.name
SELECT 
NON EMPTY { [Measures].[Internet Sales Amount] ,[Measures].[CalendarYear],[Measures].[CalendarSemester],[Measures].[CalendarQuater],[Measures].[CalendarMonth],[Measures].[CalendarDate]} ON 0,
NON EMPTY { [Date].[Calendar].[Month].&[2013]&[1] } ON 1
FROM    [Adventure Works]
WHERE   {[Date].[Date].&[20130105]}

Result:

Notice that this time the resolution of a single member, was used instead of the User hierarchy resolution. Now this behavior might be due to the fact that one translation is giving "All Period" and the next is giving a member, hence the member won.

To further confirm this, I made a change to my AdventureWorks sample. The Date attribute hierarchy is based on "Simple Date" column. I exposed "Simple Date" as a separate attribute and processed my cube.

Take a look at the "Simple Date" query and results.

with member [Measures].[CalendarYear] as [Date].[Calendar Year].currentmember.name
member [Measures].[CalendarSemester] as  [Date].[Calendar Semester of Year].currentmember.name
member [Measures].[CalendarQuater] as  [Date].[Calendar Quarter of Year].currentmember.name
member [Measures].[CalendarMonth] as  [Date].[Month of Year].currentmember.name
member [Measures].[CalendarDate] as [Date].[Date].currentmember.name
SELECT 
NON EMPTY { [Measures].[Internet Sales Amount] ,[Measures].[CalendarYear],[Measures].[CalendarSemester],[Measures].[CalendarQuater],[Measures].[CalendarMonth],[Measures].[CalendarDate]} ON 0,
NON EMPTY { [Date].[Calendar].[Month].&[2013]&[1] } ON 1
FROM    [Adventure Works]
WHERE   
--{[Date].[Date].&[20130105]}
{[Date].[Simple Date].&[20130105]:[Date].[Simple Date].&[20130106]}

Results:

AdventureWorks date dimension shows different results depending on selected hierarchy

1 Answers