上下文(Context)
上面这段MDX语句能够正常的计算出结果,但是,如果我们加上一些限定,比如:计算每种产品在2002年和2003年的平均每天的销售额。
上面的MDX语句的输出结果中会出现很多#error,其原因在于:在[Measures].[Average Internet Sales Amount]的当前上下文中,由于[Date].[Calendar]仅包含两个成员(即2002年和2003年),所以MDX解析器无法确定哪一个成员是CurrentMember。要想解决这个问题,其中的一个办法是使用Existing,详见下文:
上面的语句中EXISTING [Date].[Calendar].[Date]表示当前上下文中包含的[Date].[Calendar].[Date],所以能够得到正确的结果。
MDX的运行机制可以用一句话来描述——在Cube结构的基础上,按照一定顺序执行MDX中的不同部分,然后根据不同部分的上下文来计算,最后输出。这里有一个问题:何为MDX的上下文呢?上下文是指在所有维度的Hierarchy上都取一个或多个值构成的区域。
为了加深对上下文的认识,让我们来看一看下面的语句——计算每种产品的平均每天的销售额。
WITH
MEMBER
[
Measures
]
.
[
Average Internet Sales Amount
]
AS
AVG (DESCENDANTS( [ Date ] . [ Calendar ] .CurrentMember, [ Date ] . [ Calendar ] . [ Date ] ,SELF),
[ Measures ] . [ Internet Sales Amount ] )
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Average Internet Sales Amount ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1
FROM [ Adventure Works ]
AVG (DESCENDANTS( [ Date ] . [ Calendar ] .CurrentMember, [ Date ] . [ Calendar ] . [ Date ] ,SELF),
[ Measures ] . [ Internet Sales Amount ] )
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Average Internet Sales Amount ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1
FROM [ Adventure Works ]
上面这段MDX语句能够正常的计算出结果,但是,如果我们加上一些限定,比如:计算每种产品在2002年和2003年的平均每天的销售额。
WITH
MEMBER
[
Measures
]
.
[
Average Internet Sales Amount
]
AS
AVG (DESCENDANTS( [ Date ] . [ Calendar ] .CurrentMember, [ Date ] . [ Calendar ] . [ Date ] ,SELF),
[ Measures ] . [ Internet Sales Amount ] )
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Average Internet Sales Amount ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1
FROM [ Adventure Works ]
WHERE
{ [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2002 ] , [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2003 ] }
AVG (DESCENDANTS( [ Date ] . [ Calendar ] .CurrentMember, [ Date ] . [ Calendar ] . [ Date ] ,SELF),
[ Measures ] . [ Internet Sales Amount ] )
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Average Internet Sales Amount ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1
FROM [ Adventure Works ]
WHERE
{ [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2002 ] , [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2003 ] }
上面的MDX语句的输出结果中会出现很多#error,其原因在于:在[Measures].[Average Internet Sales Amount]的当前上下文中,由于[Date].[Calendar]仅包含两个成员(即2002年和2003年),所以MDX解析器无法确定哪一个成员是CurrentMember。要想解决这个问题,其中的一个办法是使用Existing,详见下文:
WITH
MEMBER
[
Measures
]
.
[
Average Internet Sales Amount
]
AS
AVG (EXISTING [ Date ] . [ Calendar ] . [ Date ] ,
[ Measures ] . [ Internet Sales Amount ] )
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Average Internet Sales Amount ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1
FROM [ Adventure Works ]
WHERE
{ [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2002 ] , [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2003 ] }
AVG (EXISTING [ Date ] . [ Calendar ] . [ Date ] ,
[ Measures ] . [ Internet Sales Amount ] )
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Average Internet Sales Amount ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1
FROM [ Adventure Works ]
WHERE
{ [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2002 ] , [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2003 ] }
上面的语句中EXISTING [Date].[Calendar].[Date]表示当前上下文中包含的[Date].[Calendar].[Date],所以能够得到正确的结果。