本文来源于《MDX Solutions with Microsoft SQL.Server Analysis Services 2005 and Hyperion Essbase 2nd Edition》一书的内容,主要介绍了一些经常编写的MDX语句的写法。以下MDX语句可以在SSAS的示例库:Adventure Works中运行。
例子模型以下的MDX中用到的Hierarchy如下:
百分比1)某个子项占总体的百分比。比如:每种Product的销售额占所有Product销售额的百分比。
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Sale Amount Ratio ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1 FROM [ Adventure Works ]
2)某个子项占其父项的百分比。比如:每种Product的销售额占其所属的SubCategory销售额的百分比。
' [Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount], [Product].[Product Categories].CurrentMember.Parent) '
, FORMAT_STRING = ' 0.00% '
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Sale Amount Ratio ] } ON 0 ,
NON EMPTY CROSSJOIN( [ Product ] . [ Subcategory ] . [ Subcategory ] .Members, [ Product ] . [ Product Categories ] . [ Product Name ] .Members) ON 1
FROM [ Adventure Works ]
3)某个子项占其祖先的百分比。比如:每种Product的销售额占其所属的Category销售额的百分比。
' [Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount],
ANCESTOR([Product].[Product Categories].CurrentMember, [Product].[Product Categories].[Category])) '
, FORMAT_STRING = ' 0.00% '
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Sale Amount Ratio ] } ON 0 ,
NON EMPTY CROSSJOIN( [ Product ] . [ Category ] . [ Category ] .Members, [ Product ] . [ Product Categories ] . [ Product Name ] .Members) ON 1
FROM [ Adventure Works ]
分配、分摊数量1)根据一个Measure值来分配数量。比如:按照每种Product占总体的销售额多少来分摊成本。
' ([Measures].[Internet Total Product Cost], [Product].[Product Categories].[All])*
[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount], [Product].[Product Categories].[All]) '
, FORMAT_STRING = ' 0.00 '
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Product Cost ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Product Name ] .Members ON 1
FROM [ Adventure Works ]
2)根据一个Hierarchy来分配数量。比如:在Product Hierarchy中计算每种Category的成本的时候,可以根据每种Category下有多少个产品来进行分配。
' ([Measures].[Internet Total Product Cost], [Product].[Product Categories].[All])/
Count(
Descendants (
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].[Product Name],
SELF
),
INCLUDEEMPTY
) '
, FORMAT_STRING = ' 0.00 '
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , [ Measures ] . [ Product Cost ] } ON 0 ,
NON EMPTY [ Product ] . [ Product Categories ] . [ Category ] .Members ON 1
FROM [ Adventure Works ]
平均值1)简单平均值。比如:计算一个月中每天平均的销售额是多少。
[ Measures ] . [ Internet Sales Amount ] /
COUNT (Descendants( [ Ship Date ] . [ Fiscal ] .CurrentMember, [ Ship Date ] . [ Fiscal ] . [ Date ] ), INCLUDEEMPTY)
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , Measures. [ Avg Gross Profit Margin ] } ON COLUMNS,
[ Ship Date ] . [ Fiscal ] . [ month ] .Members ON ROWS
FROM [ Adventure Works ]
2)加权平均值。没有想到好的例子。
基于时间的计算1)同比和环比。比如:今年每月的销售额和去年同期相比的变化
参见前两天写的Blog : http://www.cnblogs.com/microsheen/archive/2006/10/28/542818.html,这里要补充的是,在同比MDX中,采用COUSIN或ParallelPeriod都可以,但是采用ParallelPeriod更好一些。
2)累计到当前的统计。比如:得到一年中每一个月的累计销售额。
SUM (
PeriodsToDate( [ Ship Date ] . [ Fiscal ] . [ Fiscal Year ] , [ Ship Date ] . [ Fiscal ] .CurrentMember),
[ Measures ] . [ Internet Sales Amount ]
)
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , Measures. [ Additive Internet Sales Amount ] } ON COLUMNS,
[ Ship Date ] . [ Fiscal ] . [ month ] .Members ON ROWS
FROM [ Adventure Works ]
3)移动平均值。比如:计算一种Category过去三个月的平均销售额合计。
AVG (LastPeriods( 3 , [ Date ] . [ Calendar ] .CurrentMember),
[ Measures ] . [ Internet Sales Amount ] )
SELECT
{ [ Measures ] . [ Internet Sales Amount ] , Measures. [ Average Internet Sales Amount ] } ON COLUMNS,
NON EMPTY ( [ Product ] . [ Product Categories ] . [ Category ] .Members,
DESCENDANTS( [ Date ] . [ Calendar ] . [ Calendar Year ] . & [ 2002 ] , [ Date ] . [ Calendar ] . [ Month ] , SELF)
) ON ROWS
FROM [ Adventure Works ]
总结MDX的表现力非常强大,以上的一些内容希望起到抛砖引玉的作用。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7600305/viewspace-891195/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7600305/viewspace-891195/