MDX学习笔记（整理） MDX语法

1.1、members和Children的用法。

select [Measures].[Internet Sales Count] on columns,

[客户].[全名] on rows

from [Adventure Works DW]

默认显示的是[全部客户的总销售量]，即只有一个数据（前提是这个度量下有多个值）

如果要显示所有客户的销售量,在要全部显示具体的那个轴上加上.members：

select [Measures].[Internet Sales Count] on columns,

[客户].[全名].members on rows

from [Adventure Works DW]

显示结果包括总的销售量和每个人的销售量

若只要具体的销售量不需要总的销售量，就用Children

select [Measures].[Internet Sales Count] on columns,

[客户].[全名].children on rows

from [Adventure Works DW]

若不要那些为NULL的记录，那么就在该轴的定义前加个 NON EMPTY

select [Measures].[Internet Sales Count] on columns,

NON EMPTY [客户].[全名].children on rows

from [Adventure Works DW]

1.2、一个轴上显示该维中多个元组

一轴中可以有多列数据，没列数据叫一个元组，每个元组用逗号隔开，且最后的结果集用｛｝合并：｛｝中必须是同一维的元组

select

{[Measures].[Internet Sales Count],[Measures].[Sales Amount]} on columns,

NON EMPTY [客户].[全名].children on rows

from [Adventure Works DW]

1.3、添加一个维以不同角度来显示结果

如何将[Measures].[Internet Sales Count]和[Measures].[Sales Amount]按客户维的[客户].[全名]和产品维的[产品].[Model Name]来划分。

意思就是将[客户].[全名]和[产品].[Model Name]作为横轴参数，将[Measures].[Internet Sales Count]和[Measures].[Sales Amount]按纵轴根据两个横轴参数得到相应的结果，当然两个横轴都要为Children

select

{[Measures].[Internet Sales Count],

[Measures].[Sales Amount]} on columns,

NON EMPTY

[客户].[全名].children*              --方法一是根据*来实现

[产品].[Model Name].children on rows

/*

(

[客户].[全名].children,              --方法二是根据()来实现

[产品].[Model Name].children

) on rows

*/

from [Adventure Works DW]

感想：其实轴和OLAP中的维度变量没有直接的关系，以为一个轴可以填充多个维的元组，如示例三

1.4、Order排序

Order函数的格式：Order(显示成员名，依据排序的度量，升/降)

select

{

[Measures].[Internet Sales Count],

[Measures].[Sales Amount]

} on columns,

NON EMPTY

order(

[客户].[全名].children*              --方法一是根据*来实现

[产品].[Model Name].children,[Measures].[Sales Amount],DESC)

on rows

from [Adventure Works DW]

上面的MDX语句有点类似于T-SQL的：

Order by [客户].[全名], [产品].[Model Name] DESC,先是相同的[客户].[全名]进行排序，然后是当个[客户].[全名]里相同的[产品].[Model Name]进行排序.

1.5、Rows轴的所有元组作为一列显示

/*

下面的结果将显示:

[产品].[Product Model Lines].[Model Name]紧跟着

[产品].[Product Model Lines].[Product Line]的数据后面显示

整体为一列

注意一点,和并列两个不同列的情况不同,若有多个元组作为一列，那么Rows轴的所有元组都必须是一个层次下的元组。？？？？？

*/

select

{

[Measures].[Internet Sales Count],

[Measures].[Sales Amount]

}on columns,

non empty{

[产品].[Product Model Lines].[Product Line],

[产品].[Product Model Lines].[Model Name]

}on rows

from [Adventure Works DW]

1.6、使用Filter和Where语句

/*

Filter和Where的区别：

Filter是针对度量(或元组，貌似元组的结果也是一个度量)筛选结果,只有一种情况Filter会使用维度,如实例八所示。

Where是针对维度筛选结构，比如某个维度需要满足什么条件。

实例：

1、选择网络销售额和总销售额。

2、条件是根据总销售额>1000，网络销售额>1500，产品系列为M到S(即M,R,S)中的S。

3、最后对结果进行对网络销售额进行降序

*/

select

{

[Measures].[Internet Sales Count],

[Measures].[Sales Amount]

}on columns,

non empty

order(

filter

(

[产品].[Product Model Lines].[Product Line].&[M]:[产品].[Product Model Lines].[Product Line].&[S]*

[产品].[Model Name].children,

[Measures].[Sales Amount]>10000 and

[Measures].[Internet Sales Count]>1500

),[Measures].[Internet Sales Count],DESC)on rows

from [Adventure Works DW]

where ([产品].[Product Line].&[S])

1.7、连续选择某个区间的维度值(注意是‘值’)时，可以使用符号’:’

如上面的例子，’:’也可以用于时间值的范围。对于时间值的范围，它都是计算两个时间的中间值，所有无所谓先后顺序问题。

1.8、查看某维度的相关信息，维度Name.CurrentMember.Name/Value等

/*

只有一种情况Filter会使用维度。

给Filter语句中添加一个选择条件：用InStr函数来筛选指定维度的Name要包含字符'S'

*/

select

{

[Measures].[Internet Sales Count],

[Measures].[Sales Amount]

}on columns,

non empty

order(

filter

(

[产品].[Product Model Lines].[Product Line].&[M]:[产品].[Product Model Lines].[Product Line].&[S]*

[产品].[Model Name].children,

[Measures].[Sales Amount]>10000 and

[Measures].[Internet Sales Count]>1500

and InStr([产品].[Model Name].CurrentMember.Name,'S')>0-CurrentMember.Name

),[Measures].[Internet Sales Count],DESC)on rows

from [Adventure Works DW]

where ([产品].[Product Line].&[S])

1.9、创建临时计算成员(它是一个度量,而命名集合是一个维度成员组)，以作为查询和排序之用

/*

创建临时的两个度量：[Measures].[My BiLi]和[Measures].[My Beishu]

在一个With语句下，如要定义多个计算成员或者命名查询，那么只要一个With关键字即可。

*/

with member [Measures].[My BiLi] as

[Measures].[Internet Sales Count]/[Measures].[Sales Amount]

--,FORMAT_STRING=‘Currency’

member [Measures].[My Beishu] as

[Measures].[Sales Amount]+[Measures].[Internet Sales Count]

--format_string =“Percent‘

select

{

[Measures].[Internet Sales Count],

[Measures].[Sales Amount],

[Measures].[My BiLi],

[Measures].[My Beishu]

}on columns,

non empty order(

[产品].[Product Model Lines].[Model Name]

,[Measures].[My Beishu],DESC)

on rows

from [Adventure Works DW]

where ([产品].[Product Line].&[S])

1.10、创建临时命名集合

创建命名查询(返回的结果就是命名集合)方法有：

With Member as ….和Create Member as…

With Set … As和Create Set…. As。

前者With是创建一个临时的计算成员或者命名集合，后者Create是创建持久的计算成员或命名集合。

Set和Member的区别是：

Member只能操作一个维度并返回，而Set可以操作多个维度并返回。

/*

set 操作的对象是维度，固所查询的命名集合是一个维度组，该维度组可以构成rows轴。

filter(维度组,度量组条件/一些特殊的维度组条件，比如InStr函数)

*/

with member [Measures].[My BiLi] as

[Measures].[Internet Sales Count]/[Measures].[Sales Amount],

format_string='Percent'--注意这里是字母格式下的单引号

member [Measures].[My Beishu] as

[Measures].[Sales Amount]+[Measures].[Internet Sales Count]

--,FORMAT_STRING ='Currency'

--定义一个维度组，该维度[产品].[Product Model Lines].[Model Name]，

--自定义计算成员(度量)[Measures].[My Beishu]>222000且[产品].[Model Name]名称包含'RO'

set [heihei] as

filter(order(

[产品].[Product Model Lines].[Model Name]

,[Measures].[My Beishu],DESC)

,[Measures].[My Beishu]>222000

and InStr([产品].[Model Name].CurrentMember.Name,'Ro')>0

)

select

{

[Measures].[Internet Sales Count]

,[Measures].[Sales Amount]

,[Measures].[My BiLi]

,[Measures].[My Beishu]

}on columns,

non empty [heihei] on rows

from [Adventure Works DW]

//where ([产品].[Product Line].&[S])

1.11、TopCount函数

操作的对象也是维度，固返回的对象也是维度。固其所定义的对象可以直接作为rows轴使用。

/*

topcount(维度，前N项，依据的度量)，如

topcount([产品].[Model Name].children,10,[Measures].[Sales Amount])

*/

with

member [allsalses] as

[Measures].[Sales Amount],format_string='Currency'

set [MyTopCount] as

topcount

(

[产品].[Model Name].children*[客户].[客户所在地域].[地区区域_市],

10,

[Measures].[Sales Amount]

)

select

{

[Measures].[Internet Sales Count],

[allsalses]

} on columns,

non empty [MyTopCount]

--non empty order([产品].[Model Name].children,[Measures].[Sales Amount],DESC)

on rows

from

[Adventure Works DW]

1．12、Generate函数，将第二个表达式的结果带入第一个表达式进行处理(前提是：在第二个表达式中要有第一个表达式的引用)

/*

实例实现要求：想得到销售额靠前的5个州，且输出各个州各自最热卖的5中产品

generate

(

Set_Expression1 第一个表达式

Set_Expression1 第二个表达式

[all]             若不设置该项，则删除重复的，若为all，则显示所有，包括重复的。

)

1、将第二个表达式的结果作为引用带入第一个表达式中计算，最后得到并集(前提是第二个表达式中有第一个表达式的元组的引用)，如第二中存在第一元组.CurrentMember

2、返回的最后结果为第二个表达式中的元组,如果第二个表达式中指定了第一个表达式的引用,即【第一个表达式元组.CurrentMember,其他元组】，则结果为第一和第二的并集，总之为第二个表达式的元组

3、有点类似于T-SQL中的outer Apply

*/

with member [mysale]

as

[Measures].[Sales Amount],format_string='Currency'

select [mysale] on columns,

generate

(

topcount([客户].[国家_地区区域].children,3,[Measures].[Sales Amount])

,(

[客户].[国家_地区区域].Currentmember,--这是引用，所有结果中也有这个列

topcount

(

[产品].[Product Model Lines].[Model Name],3,[Measures].[Sales Amount]

)

)

,all

) on rows

from [Adventure Works DW]

1.13、Rank指定元组在集合中的位置函数

/*

Rank:返回指定元组在指定集中的排名（排名从1 开始）。

Rank(Tuple_Expression, Set_Expression [ ,Numeric Expression ] )

Tuple_Expression：指定的元组

Set_Expression ：指定集

Return Value:是一个度量，指定元组在指定集中的当前位置

*/

with

set [SaleRankSet] as

filter(

order

(

([客户].[国家_地区区域].children,[产品].[Model Name].children)------------这个排序的元组列集合要一样

,[Measures].[Sales Amount],DESC)

,[Measures].[Sales Amount]<>0)

member [SaleRank] as

rank(

([客户].[国家_地区区域].currentmember,[产品].[Model Name].currentmember)--这个排序的元组列集合要一样

,[SaleRankSet])

--计算成员只是得到该元组在指定集合中的位置，Rank本身没有排序功能，隐藏的部分排序逻辑在命名集合中已经做好了

--由于有了第一个命名集合在该计算成员中的应用才真正得到了具体元组在集合中的正确排序序号。

select

{

[SaleRank],[Measures].[Sales Amount]

} on columns,

[SaleRankSet]

on rows

from [Adventure Works DW]

注：为了便于理解，我们有必要提一下mdx语句在解析执行过程中的顺序：from->where->with->select。

1.14、一些函数的介绍

1、 成员函数

1、 Ancestor( Member_Expression, Level_Expression ) 此函数返回指定成员在指定级别或距离处的祖先。

Ascendants返回成员祖先的集合，包括成员本身

如

select [Measures].[Internet Sales Count] on columns,

Ancestors

(

[客户].[客户所在地域].[全名].&[15019],

[客户].[客户所在地域].[国家]

)on rows

from [Adventure Works DW]

将返回 15019的国家：澳大利亚

2 、 ClosingPeriod(1,2)

返回指定成员在指定级别处的后代中的最后一个同级成员。即2下面的所有子节点中和1是同一个级别的最后的一个节点。

如：

SELECT ClosingPeriod ([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Year].&[2003]) ON 0

FROM [Adventure Works]

December, 2003 和 Tail(Descendants(2 ， 1), 1). 是等效的

3 、 Cousin(1 ， 2 ) 表示 1 这个结构层次位置在 2 中都有哪些，即计算 1 这个人在 2 这个家庭中的堂兄妹，表兄妹等。

4 、 LastPeriods(Index, Member_Expression)

表示该层中指定成员和该成员之前的 Index-1 个成员，这些成员都属于同一级。

CurrentMember:返回遍历过程中指定层次结构的当前成员，有点类似于each(function(i){})中的i，一般情况下应用于引用处理。

5 、 Descendants

/*

descendants(成员名，级别名，标记)

对某个成员名其下的所有子级别的数据，这个子级别是一直到级别名为止。

最后的数据都是成员名下面的数据这点很重要。

SELF_AND_BEFORE:

最后的数据包括成员自己，然后该成员对于目标级别这个中间级别的各项数据，然后知道目标级别的数据

即由很多级别所对应的数据构成

*/

select [Measures].[Internet Sales Count] on columns,

descendants(

[产品].[Product Model Lines].[Model Name].&[HL Mountain Frame]

,[产品].[Product Model Lines].[English Product Name]

,SElf_and_before

)

on rows

from [Adventure Works DW]

(

Member_Expression , 成员

Level_Expression, 级别

Tag 标记

)

1.15、LastPeriods 和 元组.LastChild

/*

最后四种型号的产品在最后个客户中的销售量和排名

自己写的，也不知道对不对？

*/

with

set [Last4Names] as

LastPeriods(4,[产品].[Model Name].lastChild)

set [OrdersNames] as

order([Last4Names],[Measures].[Sales Amount],DESC)

member [RanlNames] as--Model Name的排名表

rank([产品].[Model Name],[OrdersNames])

set [Last4Customer] as--最后的四个客户名称

LastPeriods(4,[客户].[全名].LastChild)

select {[RanlNames],[Measures].[Sales Amount]} on columns,

order(generate

(

[Last4Customer],

(

[客户].[全名]*[Last4Names]

),

all

)

,[RanlNames],ASC)on rows

from [Adventure Works DW]

1.16、Children和Members的区别

/*

members: 维度/级别

children:维度/成员(成员是只有具体值，下面可以有子节点)

注意点：

1、只有在维度上时，members有统计功能，而children没有统计功能

2、层次结构根部作为特殊的维度。

说明：

1、层次结构：即维度层次结构，如时间维度的层次结构：年-月-日

2、级别：层次结构中的某一层，如上面的层次结构中，年、月、日就是级别

3、成员：某一级别中的具体值，如上面的月级别中的2月、3月。或者年级别中的2001年、2002年等。

4、总共有分：维度、层次结构、级别、成员

*/

select [Measures].[Sales Amount] on columns,

non empty

[客户].[客户所在地域].[国家].&[Australia].children

//[客户].[客户所在地域].[国家].&[Australia].members--这时候会报错

on rows

from [Adventure Works DW]

1、members不会包含计算成员，如果要显示计算成员可以用 .AllMembers

1.17、几个重要的概念

1、 数据和元数据

事实数据表中的数据或行都是数据，而其他地方(在OLAP中定义的聚合，有点像计算列，是存储在磁盘上的，不要每次加载的时候都计算)的数据都是元数据。

2、因为事实表的大小通常很大，所有要设置分区来在不同磁盘上存储事实数据。

1.18、PeriodsToDate(Level_Expression,Member_Expression)的用法

1、有点类似于 Cousin ， 先回顾下 Cousin

Cousin(1 ， 2 ) 表示 1 这个结构层次位置在 2 中都有哪些，即计算 1 这个人在 2 这个家庭中的堂兄妹，表兄妹等。它是 全横和全纵关系， 而 PeriodsToDate是上纵关系

_|_

|

全纵是指【亲兄、亲弟】，横向是指【堂表兄、堂表弟】 Cousin

|

上纵是指【亲兄】 ( 包括自己 ) PeriodsToDate

2 、那么 PeriodsToDate(级别，成员)表示：

[1]、先获取该级别下的与该成员是同一级别的所有成员。

[2]、然后从第一个成员开始，到上面的指定成员结束

[3]、简单点就是计算指定成员在该成员所属的指定级别下的哥哥和自己的集合

如：

PeriodsToDate

(

[Due Date].[Calendar Date].[Calendar Semester],--级别是半年

[Due Date].[Calendar Date].[Calendar Month].&[August]&[2001]--成员是2001年的八月

)

1、计算成员所的在级别成员：计算属于2001.8成员的半年级别成员是2001下半年这个成员([Due Date].[Calendar Date].[Calendar Semester].&[2]&[2001])，

2、计算该级别成员下同指定成员是同一级别的成员(哥哥和自己)集合：于2001.8是同一级别的下半年兄长成员和本身成员为：2001.7，2001.8这两个成员。

所以PeriodsToDate这个函数可以用于累计计算，如十九实例所示

在 MDX 中计算累积值 (Accumulating) 尤其是按时间的累积是一个非常常见的需求

1.19、SUM

Sum(Set_Expression, Numeric_Expression)

计算Set_Expression集合中每个元素对Numeric_Expression度量的和的总计，如：

下例将返回截至 【2002 年 7 月 20 日】【 7 月份】的 Internet 销售运费之和。

WITH

MEMBER Measures.x AS SUM

(

--返回截至 【2002 年 7 月 20 日】【当前星期】的 Internet 销售运费之和

--WTD([Date].[Calendar].[Date].[July 20, 2002])

--返回截至 【2002 年 7 月 20 日】【 7月份】的 Internet 销售运费之和

MTD([Date].[Calendar].[Date].[July 20, 2002])

--返回截至 【2002 年 7 月 20 日】【第三季度】的 Internet 销售运费之和

--QTD([Date].[Calendar].[Date].[July 20, 2002])

--返回截至 【2002 年 7 月 20 日】【2002年】的 Internet 销售运费之和

--YTD([Date].[Calendar].[Date].[July 20, 2002])

, [Measures].[Internet Freight Cost]

)

SELECT Measures.x ON 0

FROM [Adventure Works]

通常， SUM 函数与 CURRENTMEMBER 函数或 YTD 之类的函数（返回根据层次结构的当前成员而变化的集合）一起使用。比如：

1.20、IIf(Logical_Expression, Expression1, Expression2)

如果逻辑表达式为True就执行第一个表达式，否则执行第二个。

如果逻辑表达式为零，那么就其返回False。

如：

IIF([Measures].[Internet Sales Amount]>10000

, "Sales Are High", "Sales Are Low")

1.21、ParallelPeriod( Level_Expression ,Index , Member_Expression )

表示Member_Expression向前回滚Index * Level_Expression的时间

SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter]

   , 3

   , [Date].[Calendar].[Month].[October 2003])

   ON 0

   FROM [Adventure Works]

表示2003年10月向前回滚3*季度(3*3=9)，即回滚9个月，结果为2003年1月

要知道这个函数可以嵌套，假设当前同是计算某个值时，那么要求去年同期和上个月同期等，可以再次利用当前这个新定义的度量

1.22、With Member

1、 当为一个参数时，就直接返回结果

2、 当为两个参数时，第一个为条件，第二个为目标值，如

MEMBER [Measures].[当月计划延保台次] AS

([目标类型].[目标类型名称].&[延保台次],[Measures].[门店月目标值])

表示：求延保台次的门店月目标值，而不是其他的门店目标值

注意：若存在两个参数，那么第一个参数必须为单一值，如果是集合，那么要用到聚合函数，比如：

member [Measures].[月达成率_整车销售] as

sum(mtd([日期].[年-月-日]),iif([Measures].[销售量]=null,0,[Measures].[销售量]))

(mtd([日期].[年-月-日]),[Measures].[销售量])--这个是错误的，因为第一个参数是一个集合

3、 参数的顺序没关系。

1.23、元组和集的概念

元组：

1、 用()表示，一个维度默认就是一个元组，只是这个元组没有’()’符号。

2、 元组中的成员必须来自不同的维度。因为如果是相同维度下的成员，如果这两个成员的值不同，那么整个元组的切片结果肯定不同，即没意义。

3、 一个多维数据集被一个元组中的所有成员进行分割，且结果唯一。假设是作用在Columns轴中，那么对于Rows中的每一行维度都唯一确定一个结果。

集:是元组的容器

1、 用{}表示，默认一个元组就是一个集，只是这个集没有{}标识。

2、 集中的每个元组必须来自相同的维度。这个相同的维度是指每个元组中的第N个成员的维度是一样的，且每个成员的顺序相对于维度相同性来说都是一一对应的，

3、 一个多维数据集被一个集中的不同元组分别进行切割，且结果个数为元组个数N。假设是作用在Columns轴中，那么对于Rows中的每一行都有N个横向的列，每一列对应一个元组与该行维度切片后的唯一结果。

举例说明:

1、([产品].[Model Name],[客户].[教育])

表示一个元组，且有两个维度成员。正确

显示：如果作用列轴，那么对于每一行都只有一个单元格的结果。

如果作用在行轴，那么对于每一列有且只有一个单元格的结果

2、{([产品].[Model Name],[客户].[教育])}

表示一个集，且该集中有一个元组，且该元组中有两个维度成员。该语句也正确

显示：同上

3、{[产品].[Model Name],[客户].[教育]}

表示一个集，且集中有两个元组，该语句是不正确的，因为两个元组具有不相同的维度。

假设该语句中具有两个相同维度的元组，那么该语句正确，且

显示： 如果作用列轴，那么对于每一行都有两个(因为有两个元组)单元格的结果。

如果作用在行轴，那么对于每一列都有两个个单元格的结果

一个元组中的多个维度成员共同进行切片，结果只有一个。而集中的多个元组分别进行切片，且分别显示结果。

1.24、CrossJoin(set,set)：用于构造集

使用场合：在用到三维或以上的时候就可以用这个函数来构造新的多维数据集。

原    理：对于第一个集中的每个结果都于第二个集中的每一个结果进行组合，得到最后的结果。有点类似于笛卡尔积，但是又不全是笛卡尔积。