SSAS MDX: [Dimension].[Attrubute].[Member]和 [Dimension].[Attrubute].&[Member]的区别
0
最近有人问我说,在MDX Query里面member的引用 “.” 和“.&”有什么区别, 因为他用到的一些维度用"."和".&"都能返回结果集,但是有些维度缺只能用".&"。在这里,和大家聊聊我所知道的东西,如果不全或者不对,请补充和指正:
在MDX query语法中,我们可以引用一个member name 或者 member key. ".&"语法是引用member key, 而"." 引用是的member name. 但是针对有些维度可以用"."和".&"都能返回结果集,有些不可以,请参考以下例子:
我拿SSAS Cube AdventureWorks数据库举个例子:
select [Measures].[Internet Sales Amount] on 0,
[Geography].[Country].&[Canada] on 1
from [Adventure Works]
GO
select [Measures].[Internet Sales Amount] on 0,
[Geography].[Country].[Canada] on 1
from [Adventure Works]
以上两个代码都能返回结果集,如图:
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].&[Bikes] on 1
from [Adventure Works]
GO
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].[Bikes] on 1
from [Adventure Works]
我们可以看到上图,只有第二段MDX Query返回了正确的结果集。这是为什么呢?
原因在于,我们在维度design的时候,你的member name和member key 设置的问题。Geography维度design的时候,member name和member key都是同样的设计,但是Product维度在design的时候,member key用的是ProductCategoryKey. 前面我们讲到,“.”的用法是引用维度的member name, ".&"的用法是引用维度的member key. 我们来看看以下结果:
[Geography]维度信息:
WITH
MEMBER [Measures].[Member ID] AS
[Geography].[Country].CurrentMember.PROPERTIES("ID")
MEMBER [Measures].[Member Key] AS
[Geography].[Country].CurrentMember.PROPERTIES("KEY")
MEMBER [Measures].[Member Name] AS
[Geography].[Country].CurrentMember.PROPERTIES("Name")
SELECT
{[Measures].[Member ID], [Measures].[Member Key], [Measures].[Member Name] } on COLUMNS,
[Geography].[Country].allmembers on ROWS
FROM [Adventure Works]
[Product]维度信息:
WITH
MEMBER [Measures].[Member ID] AS
[Product].[Product Categories].CurrentMember.PROPERTIES("ID")
MEMBER [Measures].[Member Key] AS
[Product].[Product Categories].CurrentMember.PROPERTIES("KEY")
MEMBER [Measures].[Member Name] AS
[Product].[Product Categories].CurrentMember.PROPERTIES("Name")
SELECT
{[Measures].[Member ID], [Measures].[Member Key], [Measures].[Member Name] } on COLUMNS,
[Product].[Category].allmembers on ROWS
FROM [Adventure Works]
从以上结果来看知晓:Geography维度的Member Key和Member Name信息是一致的,所以我们用"."和".&"都能返回正确的结果集。但是Product维度的信息Member Key和Member信息不一致,所以我们用“.”引用member name才能返回正确的结果集,如果需要用".&"返回正确的结果集,代码如下:
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].&[1] on 1
from [Adventure Works]
有关member key和member name相关信息,参考微软官方文档:
内部成员属性 (MDX): https://msdn.microsoft.com/zh-cn/library/ms145528.aspx
在MDX query语法中,我们可以引用一个member name 或者 member key. ".&"语法是引用member key, 而"." 引用是的member name. 但是针对有些维度可以用"."和".&"都能返回结果集,有些不可以,请参考以下例子:
我拿SSAS Cube AdventureWorks数据库举个例子:
select [Measures].[Internet Sales Amount] on 0,
[Geography].[Country].&[Canada] on 1
from [Adventure Works]
GO
select [Measures].[Internet Sales Amount] on 0,
[Geography].[Country].[Canada] on 1
from [Adventure Works]
以上两个代码都能返回结果集,如图:
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].&[Bikes] on 1
from [Adventure Works]
GO
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].[Bikes] on 1
from [Adventure Works]
我们可以看到上图,只有第二段MDX Query返回了正确的结果集。这是为什么呢?
原因在于,我们在维度design的时候,你的member name和member key 设置的问题。Geography维度design的时候,member name和member key都是同样的设计,但是Product维度在design的时候,member key用的是ProductCategoryKey. 前面我们讲到,“.”的用法是引用维度的member name, ".&"的用法是引用维度的member key. 我们来看看以下结果:
[Geography]维度信息:
WITH
MEMBER [Measures].[Member ID] AS
[Geography].[Country].CurrentMember.PROPERTIES("ID")
MEMBER [Measures].[Member Key] AS
[Geography].[Country].CurrentMember.PROPERTIES("KEY")
MEMBER [Measures].[Member Name] AS
[Geography].[Country].CurrentMember.PROPERTIES("Name")
SELECT
{[Measures].[Member ID], [Measures].[Member Key], [Measures].[Member Name] } on COLUMNS,
[Geography].[Country].allmembers on ROWS
FROM [Adventure Works]
[Product]维度信息:
WITH
MEMBER [Measures].[Member ID] AS
[Product].[Product Categories].CurrentMember.PROPERTIES("ID")
MEMBER [Measures].[Member Key] AS
[Product].[Product Categories].CurrentMember.PROPERTIES("KEY")
MEMBER [Measures].[Member Name] AS
[Product].[Product Categories].CurrentMember.PROPERTIES("Name")
SELECT
{[Measures].[Member ID], [Measures].[Member Key], [Measures].[Member Name] } on COLUMNS,
[Product].[Category].allmembers on ROWS
FROM [Adventure Works]
从以上结果来看知晓:Geography维度的Member Key和Member Name信息是一致的,所以我们用"."和".&"都能返回正确的结果集。但是Product维度的信息Member Key和Member信息不一致,所以我们用“.”引用member name才能返回正确的结果集,如果需要用".&"返回正确的结果集,代码如下:
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].&[1] on 1
from [Adventure Works]
有关member key和member name相关信息,参考微软官方文档:
内部成员属性 (MDX): https://msdn.microsoft.com/zh-cn/library/ms145528.aspx
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
1 个回复
minren118 2016-04-11 回答
赞同来自: 天善_小龙