什么是MDX

 

 

以前对于OLAP就一直不时地接触过，但是一般都是使用某种产品的图形界面去设计OLAP的分析模型，然后使用某种图形工具很容易地就能够把数据展示出来，并且根据图形的特点继续做一些数据的下钻等操作，这可能也是大部分人学习OLAP的方式了。当然，学习essbase的人可能会以另一种更加贴近业务操作的方式去查询和分析OLAP服务器里的数据--微软的Excel方式，但是无论是上面的哪种方式，对于OLAP数据的展现都是直观的，很容易能够得到对于OLAP里数据的一个直观印象，这些也是最容易入门和最容易理解的方式。

最近由于工作需要，需要使用某种API的方式去访问OLAP，而不是通过图形工具或者Excel，这些博客是我学习一本叫做MDX solutions的书的一些体会，虽然每个厂商都有自己的专用的一些api和编程接口，但是也注意到有多个厂商都提供了一个共同的接口，也就是MDX，全称是Multi Demensional eXpression，发现大部分MDX的资料都提到一个MDX和SQL的比喻，就是MDX之于OLAP服务，就如SQL之于关系数据库，不仅仅概念上如此，而且连一些关键词，也和SQL比较类似，比如说SELECT，或者FROM，都和SQL类似，为了让大家对MDX有一个直观的印象，下面是一个MDX查询的一个例子和查询结果：

SELECT
{ [Measures].[Dollar Sales], [Measures].[Unit Sales] }
on columns,
{ [Time].[Q1, 2005], [Time].[Q2, 2005] }
on rows
FROM [Sales]
WHERE ([Customer].[MA])

这个就是一个最简单的MDX查询，该查询返回的结果则是如下图：

 

我们经常使用立方体的概念来比喻OLAP的数据，而OLAP查询，或者说MDX查询，则是返回立方体的一部分，也就是说MDX的查询则是返回一个立方体或者立方体的一部分，比如上图，就是一个立方体的一个切片（当立方体的某一个维度上取固定的一个值得结果）

 

MDX是一种比较通用的OLAP接口，Hyperion的essbase，微软的analysis service或者SAS公司的产品都支持该API，当然，正如所有不同的厂商对于SQL的支持也不完全一样，MDX在不同厂商的产品也有一些差异，每个厂商都或多或少做了一些扩展，虽然如此，考虑到SQL在今天这么流行，也许MDX在将来的某一天也会就如今天的SQL一样流行。今天我们先介绍MDX的一些初步概念和如何使用essbase来做我们的MDX实验。需要额外说明的是，MDX，本身也支持今天比较流行的XMLA（XML FOR ANALYSIS）的API。

 

Essbase对于MDX的支持还是比较完善的，对于Essbase，可以在Essbase的C API，JAVA API或者通过maxL来调用MDX，如果大家熟悉Oracle的proc或者其他厂商的嵌入式SQL语言，应该对于如何在essbase或者Java里使用MAX并不会陌生，做个比喻来说，如果说C里的MDX就像是Oracle的proc的地位，则maxL就如Oracle的sqlplus，你可以在maxL的交互方式或者脚本方式执行MDX的脚步，需要说明的一点是，MDX本身只侧重于OLAP查询结果，但是并没有任何处理结果数据格式的符号（这个和essbase的report scripts有点像，而report scripts还包含如何格式化查询结果的各种标记）。

MDX的基本构成如下：
SELECT Axes...
FROM Cube
WHERE Slicer...

Select和From以及Where就不用多说了，和SQL完全一样，而Axes则需要说明一下，前面已经说了MDX的查询返回的是一个立方体，则Axes则就是描述立方体的各个维度，这些维度以Axes(n)来描述，但是对于前面几个，又有一些常用的名称，如Axes(0)的常用名称是Columns，Axes（1）的常用名词是Rows，Axes（2）的常用名称则是page，这些和Excel的描述是非常相像的，事实上多维数据库本来就起源于电子表格，所以这些相像也不是什么怪事。

而对于Cube则是要处理的立方体，到现在为止，Cube只能有一个，还不能支持多个Cube之间的查询，而SQL查询就不一样了，可以支持多个表之间的连接，也许将来某一天，MDX也可以支持多个Cube的连接。

而Slicer的描述则和SQL预计where的描述有较大的不同，现在我们来看一个最简单的MDX查询的例子：

select {[Market].[East]} on columns,
{[Accounts].[Profit].children} on rows
from [Demo].[Basic]
where ([Year].[Qtr1]);

大家可以对照我们刚才所说过的各种概念对照一下，这个例子中essbase的数据库的例子里是可以运行的，我们现在就来说明一下如何在essbase里运行该例子。对于熟悉Oracle数据库的人而言，要执行命令首先需要运行sqlplus，当然essbase的sqlplus就是essmsh.exe，在命令行运行该命令，则出来一个类似于sqlplus的界面，如下图：

然后同样的，就需要执行login语句，我们执行的是：

login hypuser identfied by oracle;

接着就可以把我们的MDX语句粘贴进去执行了，结果如下图：

这样我们得到了我们第一个MDX查询的结果。不是很困难吧，以后我们就可以使用这个界面去练习各种MDX的语句，就如我们第一次学习SQL语句一样！

 

就如任何语言有一些基本的概念，MDX也不例外，本篇就来解释MDX的一些基本概念，为了容易理解，我们以essbase安装后的demo.basic数据库的一个MDX查询为例子来解释这些基本概念。

该MDX查询如下：

select {[East].children} on rows,
{[Profit].children} on columns
from [Demo].[Basic]
where
([Year].[Qtr1]);

查询结果如下图：

我们需要解释的一些概念如下：

1. [ 。。。]是成员名，中括号里既可以仅是成员名，如[East]，也可以是带父代的成员名，如[Market].[East]。

2. 可以使用children函数来引用一个父代成员下的所有成员，如[East].children，则结果列出所有东部的城市，NewYork, Boston和Chicago。又或者可以使用另一个类似的函数Descentdant([member],generation)来引用所有的成员。

3. 只需要互换on Columns和on Rows的位置，就相当于执行了一次旋转操作。

 

Tuple和Set

Tuple和Set大概是MDX里最基础的概念了，所谓Tuple就是对于立方体所做的一些切片操作，这个切片的操作既可以是使用所有立方体的成员定位成的一个Cell（比如三维立方体使用x，y，z就可以定位到空间的一个单元格上），也可以是使用不住够的成员来定位成一些数据片，如对于三维立方体只适用x成员也可以定义一个Tuple，tuple在MDX查询里使用括号(  )来定义。使用我们上述的例子如([Chicago]，[Margin])就定义了一个Tuple。

而在明白了Tuple的概念后，Set的概念就不难理解了，因为Set就是由多个Tuple组成，可以空也可以重复，而Set的概念的最直接使用就是在MDX查询里，Set在MDX查询里使用符合{  }来定义，MDX查询的最终格式就是：

Select {  } on columns,

{  } on rows。。。

 

在MDX里可以通过WITH来定义计算成员或者Set（集合），以下是一个定义一个计算成员的例子，请注意，我们仍然使用Essbase安装后自带的例子数据库Demo.Basic。

我们使用的MDX查询如下：
WITH
MEMBER [Accounts].[avg_val] AS 'Avg (
{[East].children},
[Margin].[Sales]
)',
SOLVE_ORDER=1
MEMBER [Accounts].[Sum_val] AS 'Sum (
{[East].children},
[Margin].[Sales]
)',
SOLVE_ORDER=2
MEMBER [Accounts].[var_val] AS '[Margin].[Sales]-[Accounts].[avg_val]',
SOLVE_ORDER=0
select {[East].children
} on columns,
{ [Accounts].[avg_val], [Accounts].[Sum_val],[Accounts].[var_val]} on rows
from [Demo].[Basic]
where
([Year].[Qtr1]);
该查询的运行结果如下：

查询看起来虽然复杂，但是需要说明的地方只有三个，第一个是WITH MEMBER，第二个是SOLVE_ORDER。

第三个则是Avg，Sum等函数，首先我们先来看WITH MEMBER的意义，

 

WITH
MEMBER [Accounts].[avg_val] AS 'Avg ({[East].children},[Margin].[Sales])',
SOLVE_ORDER=1
的一般语法是 WITH MEMBER member_name AS '。。。'   ，SOLVE_ORDER不是必须的，它的意义后文再解释。

如果大家知道Essbase里的Calculation Member的含义，对这里的定义将不难理解，也就是通过WITH 定义出一个临时的计算成员，然后在后面的MDX查询中可以直接引用，就好象在编程的时候定义了一个变量一样。

 

要理解SOLVE_ORDER的含义，需要我们先看看下一个MDX查询例子：

WITH
MEMBER [Measures].[Avg Sales Price] AS
‘[Measures].[Dollar Sales] / [Measures].[Unit Sales]’,
SOLVE_ORDER = 0
MEMBER [Time].[Q1 to Q2 Growth] AS
‘[Time].[Q2, 2005] - [Time].[Q1, 2005]’,
SOLVE_ORDER = 1
SELECT
{ [Measures].[Dollar Sales], [Measures].[Unit Sales],
[Measures].[Avg Sales Price]
}
on columns,
{ [Time].[Q1, 2005], [Time].[Q2, 2005], [Time].[Q1 to Q2 Growth] }
on rows
FROM [Sales]
WHERE ([Customer].[MA])

的查询结果如下：

请注意图片上红笔标出的地方，如果改变上述查询的SOLVE_ORDER的0和1的位置，则查询会在0.3和29.19之间变化，为什么会这样呢，因为对于我们定义的公式，实际上Q1到Q2的增长在平均计算的单元格里可以有两种理解方法，一种理解是总的变动销售额除于总的卖出数量变动额，得出的含义是额外售出的产品的销售平均值；另一种理解是单纯地把Q2的平均值减到Q1的平均值，得出的是销售平均值的变化，这个其实是通过控制计算的优先级别来控制的，是先计算差额然后再除于个数，还是先除于个数再计算差值，这个就是可以通过SOLVE_ORDER来控制的地方。所以SOLVE_ORDER的意义是用于控制运算的优先级别来得到自己所希望的结果。

其实对照上面的结果，不难知道Avg，Sum等函数的意义，需要说明的一点是他们的一个普遍格式是：

函数名（Set参数，数值表达式）

对于MDX的函数都有一个普遍的特征，就是很多都是以一个Set作为参数，比如求一个Set的平均值，一个Set的最大值最小值，都是对于一个集合的运算，如果熟悉一般的SQL函数，则MDX很多都有对应的函数，这些函数基本上一看就知道功能是什么，如：

Avg

Count

Sum

Max。。。

等等

 

对于多维查询里经常需要使用到的一个场景是子成员所占父成员或者祖先成员的比率大小，举个例子来说，如一月份销售额占全年销售额的百分之几，又或者华东区域的销售额占全国销售额的百分之几等，这种类型的运算在标准SQL语句里一般都需要使用一个子查询来完成，如先使用一个

Select sum(sales) from sales_facts

来取得所有所有的销售额，然后再使用每个城市的销售额除以这个总销售额来得到这个比率。

但是在OLAP里，对于这种比率的运算反而要比SQL要简单得多，这个是因为OLAP的一个特征是一般而言，它是一个已经按照预定义的层次累计好的立方体，可以很容易地得到任何一个级别上的汇总。虽然概念上的确简单如此，可是第一次使用MDX查询去表达这种比例运算也不是很容易的事，因为还是需要先熟悉一些基本概念，请参考下图：

这个是我们想要得出的最终的结果，也就是每个城市的销售额占他所在的区域的一个百分比（请注意我们仍然使用essbase里自带的Demo.Basic数据库）。

其实运算的概念很简单，就是[城市的销售额]/[区域的销售额]，关键是如何在MDX里表达这些概念，所以就引出了MDX里进行这些运算所要熟悉的最重要的图如下：

对于OLAP而言，每个维度以及相关的度量，都是按照一定的层次关系组织好的，表达这些组织关系的关键词就是图上所见到的

parent

ancester

children

descendants

lag

lead

等等关键词，这些关键词的含义从名称上和上面的图片就能够猜到，大家可以仔细研究上面的图片，应该对于他们的含义会比较清楚，在有了这些概念之后，我们才可以开始计算我们所需要的所谓“父子关系”的比率。

针对我们的demo.basic数据库，最终的MDX如下：

WITH
MEMBER [Accounts].[city_proportion] AS
'[Accounts].[Sales]/([Accounts].[Sales],Ancestor([Market].CurrentMember, [Market].generations(2) ) )'
select {[Accounts].[city_proportion],[Accounts].[Sales]} on columns,
{[East].children} on rows
from [Demo].[Basic];

现在我们可以对上面的查询做一个解释：

首先需要注意到的是我们定义了一个计算成员[Accounts].[city_proportion]，这个成员的定义对于计算所谓的“父子比率”至关重要，其中关键的地方是如何表达父代的销售额，该表达如下：

([Accounts].[Sales],Ancestor([Market].CurrentMember, [Market].generations(2) ) )

就是引用了一个Ancestor函数，来得到父代的销售额，这个其实是一个Tuple的定义，对于Tuple不熟悉的朋友可以查看我以前的文章，在这个Tuple的定义里，最重要的应该是

[Market].CurrentMember

的定义，因为这个定义当随着OLAP服务器遍历Market的指定后代成员的时候，

[Market].CurrentMember所代表的成员是动态变化的，通过[Market].generations(2) 的参数，我们制定了我们的父代成员其实是成员[East]，当然也可以通过改变这些参数去获得我们所需要的其他比率关系（比如调整generation（1）我们就可以得到城市所占所有区域的销售额百分比而不是东部城市的销售额百分比）。

接下去的Select部分很简单，我们就不需要做过多的说明了！

还有一点需要说明的是，如果分母为零的时候，Essbase会自动处理并返回Null值，但是其他OLAP服务器就需要通过iif处理分母为零的情况了。