本文来源于《MDX Solutions with Microsoft SQL.Server Analysis Services 2005 and Hyperion Essbase 2nd Edition》一书的内容。在编写MDX中,各种各样的边界情况存在,比如:Member不存在,被0除,或则某个部分在cube和维度中并不存在等等,本文对这些情况下如何来处理做了一个小结。以下MDX语句可以在SSAS的示例库:Adventure Works中运行。
Member不存在的情况
在使用Member的一些函数(比如:Lag,Lead,ParalledPeriod等)的时候,我们经常需要得到当前的Member相对的一个Member(比如:前一个Member,父级Member,或去年同期的一个Member等等)。然而由于维度和Hierarchy中的Member是有限的,所以这个相对的Member有时并不存在。在这种情况下,我们可以用IIF判断这个Member是否为Null来解决这个问题(z)。比方说,计算过去6个月的平均销售额。
WITH MEMBER [Measures].[Last 6 Month Average Sales Amount] AS
IIF (
[Date].[Calendar].CurrentMember.Lag(5) IS NULL,
NULL,
Avg (
{[Date].[Calendar].CurrentMember.Lag(5): [Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount]
)
), FORMAT_STRING="Currency"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Last 6 Month Average Sales Amount]} ON 0,
{[Date].[Calendar].[Month].Members} ON 1
FROM [Adventure Works]
这里需要注意的是如果 Cell(也就是Turple)不存在也会发生和Member不存在的相同的问题,其实如果Member不存在,Cell是一定不存在的,所以“Member不存在的情况”也可以被看作是“Cell不存在的情况”。
Level错误的情况
有的时候,Axis中的Set包含了位于不同Level的Member,而且每个Level中对于Member的处理方式不同,这种情况下的处理也要小心。比方说,在计算每种产品和其所属SubCategory,Category的销售量的时候,对于产品我们需要显示其标准价格(List Price),我们可以象下面这样来写。
WITH MEMBER [Measures].[Product List Price] AS
IIF (
[Product].[Product Categories].CurrentMember.Level IS [Product].[Product Categories].[Product Name],
[Product].[Product Categories].CurrentMember.Properties("List Price"),
NULL
)
SELECT
{[Measures].[Internet Sales Amount],[Measures].[Product List Price]} ON 0,
{DESCENDANTS([Product].[Product Categories].[All], [Product].[Product Categories].[Product Name], SELF_AND_BEFORE )} ON 1
FROM [Adventure Works]
被0除的情况
表达式出现0为除数的情况有两种:
1)Member, Cell(也就是Turple)不存在
SSAS中,如果Member, Cell不存在,其返回的值是0。比如:统计每个月每种产品的销售额时,但是有可能在某个月份某个产品由于没有卖出一个。这种情况下,上文已有说明。
2)Cell统计的值为0
比如:统计每个月份占所处季度利润百分比的时候,有可能该季度的利润为0。
无论是那种情况,对于被0除你可以用判断表达式是否为0来处理,像下面这样计算同期比:
WITH MEMBER [Measures].[Same Period Ratio] AS
IIF (
(
[Measures].[Internet Sales Amount],
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
)
) = 0,
NULL,
[Measures].[Internet Sales Amount]/
(
[Measures].[Internet Sales Amount],
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
)
) - 1
), FORMAT_STRING="Percent"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Same Period Ratio]} ON 0,
NON EMPTY{DESCENDANTS([Date].[Calendar].[All Periods], [Date].[Calendar].[Month], SELF)} ON 1
FROM [Adventure Works]