AdventureWorks日期维度根据所选层次结构显示不同的结果 [英] AdventureWorks date dimension shows different results depending on selected hierarchy

问题描述

这是两个简单的查询，显示按日期过滤的月份级别的数据.

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

在第一个查询中，我使用"Date.Calendar"用户层次结构的月份级别.

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]}

又收到-2013年1月-> 857,689.91美元 > 结果

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

在第二个查询中，我使用"Date.Month of Year"属性层次结构.

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] }

收到-1月-> $ 54,468.46 > 结果

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.

以下是每个日期的值.

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] }

2013年1月5日$ 32,681.44

January 5, 2013 $32,681.44

2013年1月6日$ 21,787.02

January 6, 2013 $21,787.02

结果

这两个日期的总价值等于第二个查询对象的结果-$ 54,468.46

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

我了解在Date维度中，第一个查询是用户层次结构，第二个查询是属性层次结构，但是我无法弄清楚哪个规则告诉您如何不同地计算这些值.

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.

顺便说一句:我创建了具有简单Date维度的简单多维数据集，该多维数据集仅由属性层次结构(日期，月份，年份)组成，并且仍然像在第一个查询中一样工作，因此尚不清楚其行为为何.

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.

推荐答案

我探讨了此行为的原因，并且我想我已经找到了原因.以下说明基于《 SQL SERVER 2008 MDX分步指南》第51-58页(尤其是避免引用冲突).

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

您的问题是典型的参考冲突问题.在MDX中，一个给定的元组不能多次使用层次结构，但是如果您使用的是USER层次结构及其底层的属性层次结构，则实际上绕过了此检查.这就是您查询中发生的事情

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

在MDX中，用户层次结构转换为属性层次结构.在您的第一个查询

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

选择 NON EMPTY {[Measures .. [Internet Sales Amount]} ON 0， NON EMPTY {[Date].[Calendar].[Month].& [2013]& 1 }开启1 从[冒险工作]在哪里
{[Date].[Date].& [20130105]:[Date].[Date].& [20130106]}

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]}

您正在使用用户层次结构"[日期].[日历].[月].& [2013]& 1 "，在其最后一级具有"[Date].[Date]".然后，在where子句中使用相同的"[Date].[Date]"属性层次结构进行过滤.由于在USER层次结构中您尚未使用叶级别，因此已经做了部分地址，因此成员及其祖先都得到了解析.在翻译中，所有后代都将被忽略.看看下面的查询(这是基于您的第一个查询，我有意删除了where子句).

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] 

结果.

请注意，日历年，学期和季度都显示非默认值.但是我们从未使用过它们.这表明将用户层次结构转换为基础属性层次结构.现在看一下日历，它仍然显示所有期间".既然被忽略了. 现在，如果您将where子句添加回去，则日期仍显示为所有期间"，这有两个原因 1)由于在用户层次结构转换中被忽略， 2)您在哪里使用了范围.如果将行轴元组替换为where元组，则该行仍将显示所有期间"作为范围的基础.但是，解决该问题只需要两个日期.

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.

基于此，在解决查询时，它对Date属性层次结构进行了两种转换，一种表示基于用户层次结构将其忽略，另一种提供了范围.这是由于冲突导致结果不正确的地方.

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]}

结果:

请注意，这次使用的是单个成员的分辨率，而不是用户层次结构的分辨率.现在，此行为可能是由于一个翻译给出了所有期间"，而另一个翻译给出了一个成员，因此该成员获胜了.

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.

为了进一步确认这一点，我对AdventureWorks示例进行了更改.日期属性层次结构基于简单日期"列.我将简单日期"作为单独的属性公开并处理了我的多维数据集.

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]}

结果:

这篇关于AdventureWorks日期维度根据所选层次结构显示不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！