SSAS——MDX基础

一、基本概念

　　MDX:一种查询语言，从多维的数据集单元格中检索数据。支持两种不同的模式：

　　1、表达式语言：定义和操纵Analysis Services对象和数据以计算值

　　2、查询语言：从Analysis Services中检索数据

　　层次结构和层次结构成员：多维数据集中的维度具有一个或者多个层次结构。并且每个层次结构包括一个或者多个级别。

　　例如：Date维度Calendar层次结构包括五个层次结构级别：Calendar Year，Calendar Semester,Calendar Quarter,Calendar Month,Calendar Date

　　成员：每个层次结构都包括一个或多个项，这些项被称为成员，且每个成员都对应于基础维度表中的一个活多个引用实例。

　　例如：Date维度Calendar层次结构的成员：CY2005,H1 CY 2005,Q1 CY 2005,Q2 CY 2005,H2 CY 2005 ；

　　　Date维度Calendar层次结构Quarter级别的成员，Q1 CY 2005,Q2 CY 2005,Q3 CY 2005,Q4 CY 2005。

　　引用：在MDX中，某一层次结构的每个特定成员都是通过唯一名称进行标识的。可以通过包含维度名称、层次结构名称以及级别名称的名称路径（使用该成员的名称）来访问某个维度的某个成员。亦可以通过键路径（使用该成员的键）来访问。例如Calendar层次结构中的成员Q1 CY 2006可以表示为以下形式：

　　a)[Date].[Calendar].[Q1 CY 2006]

　　b)[Date].[Calendar].[CY 2006].[H1 CY 2006].[Q1 CY 2006]

　　c)[Date].[Calendar].[Calendar Quartar].[Q1 CY 2006]

　　d)[Date].[Calendar].[Calendar Quarter].&[2006]&[1]

　　单元：是度量值维度成员的成员与多维数据集中各个属性层次结构的成员相交处所在的空间.多维数据集中的每个单元是通过维度成员来表示的。设下图有三个维度Product,Customer,Date,三个维度都只有一个层次，Product Line有四个成员 ，Calendar有四个成员（四个季度），County有六个成员，则共有4*4*6=96个单元。假设要检索多维数据集中阴影区域所显示的数据。该单元中的销售金额是966。该单元位于Product=Mountain、Date=Quartar 2、Country=Australia的交集位置。MDX查询

SELECT Measures.[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Quarter].&[2011]&[2],
[Product].[Product Line].[Mountain],
[Customer].[Country].[Australia] )

 　　元组：唯一标识多维数据集的一个单元或一部分的MDX表达式称为元组。元组通过每个维度中的一个成员标识，使用逗号隔开，并用括号括起来，元组并不是必须显示包含所有维度中的成员。由于一个元组唯一的表示一个单元，因此它不可以包含每个维度的多个成员。

　　例如：([Customer].[Country].[Australia])——这个是简单元组，只通过一个成员表示，因此可以不需要括号，也可以表示为Customer.Country.Australia；如果包含多个维度，则需要括号。

　　　　([Date].[Calendar].[2011].[H1 CY 2011].[Q1 CY 2011], [Customer].[Country].[Australia])

　　　　([Date].[Calendar].[2011].[H1 CY 2011].[Q1 CY 2011], [Product].[ProductLine].[Mountain], [Customer].[Country].[Australia])

　　用于检索元组Customer.Country.Australia表示的数据的值的MDX查询是

SELECT Measures.[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE ([Customer].[Country].[Australia])

 　　集：一组元组构成一种新的对象，称为集。这组元组是使用在类型上和数量上均完全相同的一组维度定义的。对于元组和集，需要了解以下关键要点：

• 维度的成员([Date].[Calendar].[2008].[H1 CY 2008].[Q1 CY 2008])本身即构成一个元组和集。因此，可以在MDX 查询中将其按照元组和集的形式进行使用。
• 如果某个元组是仅通过一个层次结构指定的，那么不需要使用括号即可将其指定为集。该元组可以直接在MDX 查询中使用。

•  如果某个查询中仅指定了一个元组，那么不需要使用花括号来指示应该将其视为集。当执行该查询时，会隐式地将该元组转换为一个集。

例如：

{(Customer.Country.Australia), (Customer.Country.Canada)}

 

{
　　　　([Date].[Calendar].[2008].[H1 CY 2008].[Q1 CY 2008], [Product].[ProductLine].[Mountain], [Customer].[Country].[Australia]),
　　　　([Product].[ProductLine].[Mountain], [Customer].[Country].[Australia],[Date].[Calendar].[2006].[H1 CY 2006].[Q1 CY 2006]),
　　　　([Customer].[Country].[Australia], [Date].[Calendar].[2007].[H1 CY 2007].[Q1 CY 2007], [Product].[Product Line].[Mountain] )
　　}

 

 二、MDX查询

1、select语句

　　在SELECT 语句中，轴规范的语法如下：SELECT [<axis_expression>, [<axis_expression>...]]。

　　axis_expression 指代要检索的维度数据。这些维度中的数据将投影到对应的轴上。使用轴维度来检索结果集。定义该集(即由元组构成的一个集合)以构成一个轴维度。MDX最多允许在一个SELECT 语句中指定128 个轴。前5个轴具有别名，分别是COLUMNS、ROWS、PAGES、SECTIONS 和CHAPTERS。也可以将轴指定为数字，这样就可以在SELECT 语句中指定5 个以上的维度。以下面的语句为例：

SELECT Measures.[Internet Sales Amount] ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS,
[Product].[Product Line].MEMBERS ON PAGES
FROM [Adventure Works]

　　在上面的SELECT 语句中，指定了3 个轴。维度Measures、Customers 和Product 中的数据被映射到这3 个轴上，构成了轴维度。

2、from子句和多维数据及规范

　　from子句可确定要从中检索和分析数据的多维数据集。例如

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]

　　嵌套select的from子句,将查询限制为某个子多维数据集，而不是整个多维数据集。

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer Geography].[Country].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( { [Date].[Fiscal].[Fiscal Year].&[2008],
[Date].[Fiscal].[Fiscal Year].&[2009]
}
)
ON COLUMNS
FROM (
SELECT ( { [Product].[Product Categories].[Subcategory].&[26],
[Product].[Product ategories].[Subcategory].&[27] } )
ON COLUMNS
FROM [Adventure Works]
)
)
WHERE ( [Product].[Product Categories].CurrentMember,
[Date].[Fiscal].CurrentMember
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE

3、where子句和切片器规范

　　where语句可以通过特定条件限制返回的结果集。

SELECT Measures.[Sales] ON COLUMNS,
[Product].[Product Line].MEMBERS on ROWS
FROM [ProductsCube]
WHERE ([Product].[Color].[Silver])

4、with子句、命名集和计算成员

　　with子句提供了创建计算的功能，而这些计算必须在特定的作用域内使用公式来表示。创建的典型计算包括命名集和计算成员。可以在一个with子句中指定多个计算。

a）命名集

　　因为集表达式一般较长，可能会增加查询的复杂性。因此，通过动态定义集，为集分配一个名称，并在随后的查询中使用该名称。这样的集便成为命名集。例如，查询欧洲几个选定客户的销售信息：

SELECT Measures.[Internet Sales Amount] ON COLUMNS,
{[Customer].[Country].[Country].&[France],
[Customer].[Country].[Country].&[Germany],
[Customer].[Country].[Country].&[United Kingdom]} ON ROWS
FROM [Adventure Works]

可以通过命名集的方式，来简化查询：

WITH SET [EUROPE] AS '{[Customer].[Country].[Country].&[France],
[Customer].[Country].[Country].&[Germany],[Customer].[Country].[Country].
&[United Kingdom]}'
SELECT Measures.[Internet Sales Amount] ON COLUMNS,
[EUROPE] ON ROWS
FROM [Adventure Works]

　　 全局命名集：在某个MDX查询内创建的命名集只能在该查询的作用域内进行访问。同一会话中的其他查询，或者不同会话中的其他用户无法在他们的查询中访问命名集。有些命名集可能对其他用户是有用的。create语句允许在某个会话或者整个多维数据集的作用域内创建集。需要将多维数据集的名称指定为前缀。

CREATE SET [Adventure Works].[Europe]
AS { [Customer].[Country].[Country].&[France],
[Customer].[Country].[Country].&[Germany],
[Customer].[Country].[Country].&[United Kingdom] };

在执行MDX查询时，命名集可以属于一下三种作用域之一：

• 查询作用域中。在MDX中使用WITH子句定义它们。
• 会话作用域中。在特定会话中，使用CREATE SET语句创建它们。
• 全局作用域中。在MDX脚本中，使用CREATE SET语句定义它们。

　　静态和动态命名集：

 　　静态命名集：

CREATE SET CURRENTCUBE.[Static Top 10 Customers]
AS TopCount
(
[Customer].[Customer].[Customer].MEMBERS,
10,
[Measures].[Internet Sales Amount]
) ;

　　动态命名集：

CREATE DYNAMIC SET CURRENTCUBE.[ Dynamic Top 10 Customers]
AS TopCount
(
[Customer].[Customer].[Customer].MEMBERS,
10,
[Measures].[Internet Sales Amount]
) ;

　　删除命名集：

DROP SET <setname>

 b)计算成员

　　计算成员是通过mdx表达式指定的计算。它们会解析为mdx表达式求解的结果，而不仅仅是通过检索原始事实数据来获得。例如本年度截止至今为止的产片销售量计算。计算成员的命名应该是完全限定的名称，其中包括要在其下创建该特定计算成员的维度、层次结构和级别。

WITH MEMBER MEASURES.[Profit] AS [Measures].[Internet Sales Amount]-
[Measures].[Internet Standard Product Cost]
SELECT measures.profit ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]

　　全局计算成员：与命名集相同，也可以使用CREATE关键字来创建计算成员，要为计算成员指定多维数据集和维度名称。前面命名集介绍的查询作用域，会话作用域，全局作用域同样适用于计算成员。

WITH MEMBER MEASURES.[Profit] AS [Measures].[Internet Sales Amount]-
[Measures].[Internet Standard Product Cost]
SELECT measures.profit ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]

　　在MDX脚本中，可以使用CURRENTCUBE关键字来代替多维数据集名称。

CREATE MEMBER CURRENTCUBE.[Measures].[Profit] AS
'([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])';

　　由于用户创建的最多的计算成员就是计算度量值，因此如果没有显示只是Measures前缀，那么认为该计算成员位于Measures维度中。

CREATE MEMBER [Profit]
AS '( [Measures].[Internet Sales Amount] - [Measures].[Total Product Cost] )';

　　创建计算成员以后，可以按照以下查询来使用它们。

SELECT [Measures].[Profit] ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]

　　在MDX脚本中创建全局计算成员还有另外一种方法。先声明一个不包含任何定义的成员，稍后在定义表达式。

CREATE MEMBER [Profit] AS NULL;
[Measures].[Profit] = [Measures].[Sales Amount] - [Measures].
[Total Product Cost];

　　删除计算成员

DROP MEMBER <member name>

 5、排名和排序

　　MDX提供了实现排名和排序功能的函数，例如TopCount、BottomCount、TopPercent、BottomPercent 和Rank

　　TopCount示例:按照在所有国家/地区的销售额查找排在前N 位的产品类别

SELECT [Measures].[Internet Sales Amount] ON COLUMNS,
TopCount( [Product].[Product Categories].[Category].MEMBERS, 3,
[Measures].[Internet Sales Amount] ) ON ROWS
FROM [Adventure Works]

　　按网络销售额排序处于最后10%的产品：

//进行网络销售的产品总数 – 159 种产品
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY [Product].[Product Categories].[Product].MEMBERS ON ROWS
FROM [Adventure Works]
//通过网络销售的产品中销售额排在最后10%的产品 – 95 种产品
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY BottomPercent(
[Product].[Product Categories].[Product].MEMBERS, 10,
[Measures].[Internet Sales Amount] ) ON ROWS
FROM [Adventure Works]

三、MDX表达式

　　mdx表达式不是完整的mdx语句，它可以理解为求解一个值。　　

　　示例1：该示例返回为Customer 维度的Customer Geography 层次结构指定的默认成员。

　　Customer.[Customer Geography].DEFAULTMEMBER

　　示例2：该MDX 表达式将不同国家/地区的客户的销售额与澳大利亚客户的销售额进行比较。

　　(Customer.[Customer Geography].CURRENTMEMBER, Measures.[Sales Amount]) -
　　(Customer.[Customer Geography].Australia, Measures.[Sales Amount])

　　示例3：该示例是一个MDX 单元安全表达式，允许员工查看他们自己产生的销售额信息，或者他们的下属员工产生的销售额信息，但不能查看其他员工的相应信息

COUNT(INTERSECT( DESCENDANTS( IIF( HIERARCHIZE(EXISTS[Employee].
[Employee].MEMBERS,
STRTOMEMBER("[Employee].[login].[login].&["+USERNAME+"]")),
POST).ITEM(0).ITEM(0).PARENT.DATAMEMBER is
HIERARCHIZE(EXISTS([Employee].[Employee].MEMBERS,
STRTOMEMBER("[Employee].[login].[login].&["+USERNAME+"]")),
POST).ITEM(0).ITEM(0),
HIERARCHIZE(EXISTS([Employee].[Employee].MEMBERS,
STRTOMEMBER("[Employee].[login].[login].&["+username+"]")),
POST).ITEM(0).ITEM(0).PARENT,
HIERARCHIZE(EXISTS([Employee].[Employee].MEMBERS,
STRTOMEMBER("[Employee].[login].[login].&["+USERNAME+"]")),
POST).ITEM(0).ITEM(0))
).ITEM(0) , Employee.Employee.CURRENTMEMBER)) > 0

 四、MDX运算符

MDX 语言具有多种类型的运算符，其中包括算术运算符、逻辑运算符以及特殊MDX 运算符。运算符是执行特定操作、接受参数并返回结果的函数。

 1）算术运算符

　　可以在MDX 中使用常规的算术运算符，例如+、–、*以及/。

2）集运算符

　　对于+、–和*运算符，除了可以作为算术运算符以外，还可以针对MDX 集执行运算。+运算符可以返回两个集的并集，–运算符返回两个集的差集，而*运算符返回两个集的叉积。两个集的叉积就是每个集中的元组的所有可能的组合情况，有助于以矩阵格式检索数据。例如，如果有两个集，{Male,Female}和{2007,2008,2009}，两个集的叉积表示为{Male,Female} * {2007,2008,2009}，其结果为{(Male,2007),(Male,2008),(Male,2009),(Female,2007),(Female,2008), (Female,2009)}。下面的示例显示的是使用了集运算符的MDX 表达式：

　　示例1：对于以下MDX 表达式：

{[Customer].[Country].[Australia]} + {[Customer].[Country].[Canada]}

　　其结果是两个集的并集，如下所示：

{[Customer].[Country].[Australia], [Customer].[Country].[Canada]}

　　示例2：对于以下MDX 表达式：

{[Customer].[Country].[Australia],[Customer].[Country].[Canada]}*
　　{[Product].[Product Line].[Mountain],[Product].[Product Line].[Road]}

　　其结果是两个集的叉积，如下所示：

　{([Customer].[Country].[Australia],[Product].[Product Line].[Mountain])
　　([Customer].[Country].[Australia],[Product].[Product Line].[Road])
　　([Customer].[Country].[Canada],[Product].[Product Line].[Mountain])
　　([Customer].[Country].[Canada],[Product].[Product Line].[Road])}

3） 比较运算符

　　MDX 支持以下比较运算符：<、<=、>、>=、=和<>。这些运算符采用两个MDX 表达式作为参数，根据比较每个表达式的值所产生的结果返回TRUE 或FALSE。

示例：下面的MDX 表达式使用大于比较运算符>：

Count (Customer.[Country].members) > 3

　　在上面的示例中，Count 是一个MDX 函数，可以计算Customer 维度的Country 层次结构中的成员数目。由于该层次结构中包含的成员数超过3 个，因此该MDX 表达式的结果为TRUE。

4） 逻辑运算符

　　MDX 中的逻辑运算符包括AND、OR、XOR、NOT 和IS，这些运算符分别用于逻辑与、逻辑或、逻辑异或、逻辑非和比较。这些运算符采用两个MDX 表达式作为参数，根据逻辑运算返回TRUE 或FALSE。

5） 特殊MDX 运算符——花括号、逗号和冒号

　　花括号：将一个元组或一组元组括起来，从而构成一个MDX 集。如果集中只包含一个元组，那么花括号是可选的，因为Analysis Services 会根据需要隐式地将单个元组转换为集。如果要表示为集的元组有多个，或者具有一个空集，

　　逗号：逗号字符可以构成一个包含多个成员的元组。通过执行此操作，可以创建多维数据集上的一个数据切片。此外，逗号字符还可以分隔指定用于定义集的多个元组。在集{(Male,2007),(Male,2008),(Male,2009),(Female,2007),(Female,2008),(Female,2009)}中，逗号字符不仅用于构成元组，还用于构成元组所组成的集。

　　冒号：用于定义某个集中一定范围内的成员。在集中的两个非连续成员之间使用冒号字符可以表示根据集顺序(基于键或基于名称)包含这两个成员之间的所有成员。

　　例如，如果具有以下集：

{[Customer].[Country].[Australia], [Customer].[Country].[Canada],[Customer].[Country].[France], [Customer].[Country].[Germany],[Customer].[Country].[United Kingdom], [Customer].[Country].[United States]}

　　那么对于以下MDX 表达式：

{[Customer].[Country].[Canada] : [Customer].[Country].[United Kingdom]}

　　将生成以下集：

{[Customer].[Country].[Canada], [Customer].[Country].[France],
　　[Customer].[Country].[Germany], [Customer].[Country].[United Kingdom]}

 

五、MDX函数

 1、集函数——针对集进行操作的函数，它们通常使用集作为参数，返回值也是集。

　　a)Crossjoin

 　　Crossjoin函数将返回其参数所指定的集的所有可能的组合。如下例可以生成Product维度中的每个成员与Customer维度中的每个成员的叉积以及销售额度量值。

SELECT Measures.[Internet Sales Amount] ON COLUMNS,
CROSSJOIN( {Product.[Product Line].MEMBERS},
{[Customer].[Country].MEMBERS}) on ROWS
FROM [Adventure Works]

　　b)NONEMPTYCROSSJOIN和NONEMPTY

　 可以使用关键字NON EMPTY来消除上例交叉连接产生的NULL值。也可以使用MDX函数NONEMPTYCROSSJOIN、NONEMPTY

　　使用NONEMPTY关键字：

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Customer].[Customer Geography].[Country].MEMBERS *
[Product].[Product Categories].MEMBERS ON 1
FROM [Adventure Works]

　　使用函数：

SELECT Measures.[Internet Sales Amount] ON COLUMNS,
NONEMPTYCROSSJOIN( {Product.[Product Line].[Product Line].MEMBERS},
{[Customer].[Country].MEMBERS},Measures.[Internet Sales Amount],2 ) ON ROWS
FROM [Adventure Works]

SELECT Measures.[Internet Sales Amount] ON COLUMNS,
NONEMPTY(CROSSJOIN ( {Product.[Product Line].[Product Line].MEMBERS},
{[Customer].[Country].MEMBERS}),Measures.[Internet Sales Amount]) ON ROWS
FROM [Adventure Works]

　　c)Filter和Having

　　Filter函数可帮助根据一个或多个条件来限制查询结果。采用两个参数，一个集表达式和一个逻辑表达式。将对集中地每一项应用逻辑表达式，并返回满足相应逻辑条件的一组项。