MDX Cookbook 02 - 除数为零的问题

先直接看一个例子 -

WITH
MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Bad]
AS
[Date].[Calendar Year].[Calendar Year].&[2006] /[Date].[Calendar Year].[Calendar Year].&[2005],
FORMAT_STRING = 'Percent'
SELECT
{
[Date].[Calendar Year].[Calendar Year].&[2005],
[Date].[Calendar Year].[Calendar Year].&[2006],
[Date].[Calendar Year].[CY 2006 vs 2005 Bad]
} *
[Measures].[Reseller Sales Amount] ON 0,
{ [Sales Territory].[Sales Territory].[Country].MEMBERS}
ON 1
FROM [Adventure Works]

首先在 Data 维度 [Calendar Year] 层次结构中定义一个计算成员 [CY 2006 vs 2005 Bad]，因为默认的度量值是 [Reseller Sales Amount]，因此表达式中的 [Date].[Calendar Year].[Calendar Year].&[2006] /[Date].[Calendar Year].[Calendar Year].&[2005] 表示的意思就是 2006 年零售额与 2005年零售额的比。

然后按 Country 分别来显示 2005年，2006年的销售额以及 2006年与2005年的销售额比。

为了在 SSRS 中显示，必须对这个代码做出一些改造 -

WITH
-- CY 2005 Reseller Sales Amount
MEMBER [Measures].[CY 2005 Reseller Sales Amount]
AS
([Date].[Calendar Year].[Calendar Year].&[2005],[Measures].[Reseller Sales Amount])

-- CY 2006 Reseller Sales Amount
MEMBER [Measures].[CY 2006 Reseller Sales Amount]
AS
([Date].[Calendar Year].[Calendar Year].&[2006],[Measures].[Reseller Sales Amount])

-- CY 2006 VS CY 2005
MEMBER [Measures].[CY 2006 vs 2005 Bad]
AS
[Measures].[CY 2006 Reseller Sales Amount] /[Measures].[CY 2005 Reseller Sales Amount],
FORMAT_STRING = 'Percent'

SELECT {
[Measures].[CY 2005 Reseller Sales Amount],
[Measures].[CY 2006 Reseller Sales Amount],
[Measures].[CY 2006 vs 2005 Bad]
   } ON COLUMNS,
   { [Sales Territory].[Sales Territory].[Country].MEMBERS} ON ROWS
FROM [Adventure Works];

从上面两个例子中查询结果来看，因为 CY 2005 中有一些 Reseller Sales Amount 是没有记录的，因此造成显示结果出现了 1.#INF 或者是 Infinity，这就是在除法运算的过程中分母为零的问题。

为了解决这样的问题，应该在除法元算的过程中添加条件判断。

MEMBER [Measures].[CY 2006 vs 2005 Bad]
AS
     IIF([Measures].[CY 2005 Reseller Sales Amount] = 0 , NULL,          [Measures].[CY 2006 Reseller Sales Amount] /[Measures].[CY 2005 Reseller Sales Amount]),
FORMAT_STRING = 'Percent'

为 NULL 的结果在 SSRS Report 上不会显示 -

 

---

本文涉及到的其它 MDX 知识点：

1. 计算成员和动态表达式: MDX Step by Step 读书笔记(五) - Working with Expressions (MDX 表达式)
2. 关于 IIF 性能和 MDX 中关于对 IIF 执行计划的有关文章： http://tinyurl.com/PerfGuide2008
3. 有关 SSAS 性能的文档 Analysis Services Performance Guide: http://www.microsoft.com/en-us/download/details.aspx?id=17303

其它 BI 笔记请参照 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)