在做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语句可以轻松的实现这个需求。
posted on 2006-10-28 16:09
microsheen 阅读(3144)
评论(10) 编辑 收藏 网摘 所属分类:
SQL ServerBI