如何使用MDX编写同比和环比

  在做BI项目中，用户会经常提出类似下面这样的需求：
1. 本月比上月的销售额增长了多少？百分比是多少？
2. 本季度第一个月比上季度第一个月的销售额增长了多少？百分比是多少？

其实这两个需求就是我们常说的同比和环比问题，它们用关系数据库的SQL语句难于实现，而用BI中的MDX来做却轻而易举。以下例子采用的是SQL Server2005的Analysis Services中的示例库。

环比
需求：本月比上个月的销售额增长了多少个百分点？
MDX语句如下：

WITH  MEMBER Measures. [ Amount Change Rate ]   AS  
   [ Measures ] . [ Internet Sales Amount ] /
  ( [ Measures ] . [ Internet Sales Amount ] , [ Date ] . [ Fiscal ] .CurrentMember.PrevMember)  - 1
  , FORMAT_STRING  =   ' 0% '
SELECT  
{ [ Measures ] . [ Internet Sales Amount ] , Measures. [ Amount Change Rate ] }  ON   0 ,
[ Date ] . [ Fiscal ] . [ Month ] .Members  ON   1
FROM   [ Adventure Works ]

结果如下：


上面的输出结果有一个问题，就是第一行数据会发生除0的情况，为了防止这种情况，MDX修改如下：

WITH  MEMBER Measures. [ Amount Change Rate ]   AS  
  IIF(ISEMPTY(( [ Measures ] . [ Internet Sales Amount ] , [ Date ] . [ Fiscal ] .CurrentMember.PrevMember)), "", 
    [ Measures ] . [ Internet Sales Amount ] /
  ( [ Measures ] . [ Internet Sales Amount ] , [ Date ] . [ Fiscal ] .CurrentMember.PrevMember)  - 1
  ), FORMAT_STRING  =   ' 0% '
SELECT  
{ [ Measures ] . [ Internet Sales Amount ] , Measures. [ Amount Change Rate ] }  ON   0 ,
[ Date ] . [ Fiscal ] . [ Month ] .Members
  ON   1
FROM   [ Adventure Works ]

如果发现0为空，则显示空字符串。输出结果如下：



同比
需求：本季度每个月比上个季度同期销售额增长了多少个百分点？

WITH  MEMBER Measures. [ Change Amount Rate ]   AS  
  [ Measures ] . [ Internet Sales Amount ] /
 (   [ Measures ] . [ Internet Sales Amount ] , 
    Cousin( [ Date ] . [ Fiscal ] .CurrentMember,  [ Date ] . [ Fiscal ] .CurrentMember.Parent.PrevMember)
 )  -   1 , FORMAT_STRING  =   ' 0% '    
SELECT  { [ Measures ] . [ Internet Sales Amount ] , Measures. [ Change Amount Rate ] }  ON   0 ,
[ Date ] . [ Fiscal ] . [ Month ] .Members  ON   1
FROM   [ Adventure Works ]

运行结果如下：


和环比相同，这里也出现了除0的情况，所以把MDX做如下修改：

WITH  MEMBER Measures. [ Change Amount Rate ]   AS  
  IIF(
    ( [ Measures ] . [ Internet Sales Amount ] , 
     Cousin( [ Date ] . [ Fiscal ] .CurrentMember,  [ Date ] . [ Fiscal ] .CurrentMember.Parent.PrevMember)
    )=0
    , "", 
      [ Measures ] . [ Internet Sales Amount ] /
     (   [ Measures ] . [ Internet Sales Amount ] , 
        Cousin( [ Date ] . [ Fiscal ] .CurrentMember,  [ Date ] . [ Fiscal ] .CurrentMember.Parent.PrevMember)
     )  -   1
  ), FORMAT_STRING  =   ' 0% '    
SELECT  { [ Measures ] . [ Internet Sales Amount ] , Measures. [ Change Amount Rate ] }  ON   0 ,
[ Date ] . [ Fiscal ] . [ Month ] .Members  ON   1
FROM   [ Adventure Works ]

输出结果如下：


总结
同比和环比是BI项目中经常要做得内容，采用MDX语句可以轻松的实现这个需求。