[MDX学习笔记之一]MDX中一些常见的计算

例子模型
以下的MDX中用到的Hierarchy如下：

百分比
1）某个子项占总体的百分比。比如：每种Product的销售额占所有Product销售额的百分比。

WITHMEMBER[Measures].[SaleAmountRatio]AS
'[Measures].[InternetSalesAmount]/([Measures].[InternetSalesAmount],[Product].[ProductCategories].[All])',FORMAT_STRING='0.00%'
SELECT
{[Measures].[InternetSalesAmount],[Measures].[SaleAmountRatio]}ON0,
NONEMPTY[Product].[ProductCategories].[ProductName].MembersON1
FROM[AdventureWorks]

2）某个子项占其父项的百分比。比如：每种Product的销售额占其所属的SubCategory销售额的百分比。

WITHMEMBER[Measures].[SaleAmountRatio]AS
'[Measures].[InternetSalesAmount]/
([Measures].[InternetSalesAmount],[Product].[ProductCategories].CurrentMember.Parent)'
,FORMAT_STRING='0.00%'
SELECT
{[Measures].[InternetSalesAmount],[Measures].[SaleAmountRatio]}ON0,
NONEMPTYCROSSJOIN([Product].[Subcategory].[Subcategory].Members,
[Product].[ProductCategories].[ProductName].Members)ON1
FROM[AdventureWorks]

3）某个子项占其祖先的百分比。比如：每种Product的销售额占其所属的Category销售额的百分比。

WITHMEMBER[Measures].[SaleAmountRatio]AS
'[Measures].[InternetSalesAmount]/
([Measures].[InternetSalesAmount],
ANCESTOR([Product].[ProductCategories].CurrentMember,[Product].[ProductCategories].[Category]))'
,FORMAT_STRING='0.00%'
SELECT
{[Measures].[InternetSalesAmount],[Measures].[SaleAmountRatio]}ON0,
NONEMPTYCROSSJOIN([Product].[Category].[Category].Members,[Product].[ProductCategories].[ProductName].Members)ON1
FROM[AdventureWorks]

分配、分摊数量
1）根据一个Measure值来分配数量。比如：按照每种Product占总体的销售额多少来分摊成本。

WITHMEMBER[Measures].[ProductCost]AS
'([Measures].[InternetTotalProductCost],[Product].[ProductCategories].[All])*
[Measures].[InternetSalesAmount]/
([Measures].[InternetSalesAmount],[Product].[ProductCategories].[All])'
,FORMAT_STRING='0.00'
SELECT
{[Measures].[InternetSalesAmount],[Measures].[ProductCost]}ON0,
NONEMPTY[Product].[ProductCategories].[ProductName].MembersON1
FROM[AdventureWorks]

2）根据一个Hierarchy来分配数量。比如：在Product Hierarchy中计算每种Category的成本的时候，可以根据每种Category下有多少个产品来进行分配。

WITHMEMBER[Measures].[ProductCost]AS
'([Measures].[InternetTotalProductCost],[Product].[ProductCategories].[All])/
Count(
Descendants(
[Product].[ProductCategories].CurrentMember,
[Product].[ProductCategories].[ProductName],
SELF
),
INCLUDEEMPTY
)'
,FORMAT_STRING='0.00'
SELECT
{[Measures].[InternetSalesAmount],[Measures].[ProductCost]}ON0,
NONEMPTY[Product].[ProductCategories].[Category].MembersON1
FROM[AdventureWorks]

平均值
1）简单平均值。比如：计算一个月中每天平均的销售额是多少。

WITHMEMBERMeasures.[AvgGrossProfitMargin]AS
[Measures].[InternetSalesAmount]/
COUNT(Descendants([ShipDate].[Fiscal].CurrentMember,[ShipDate].[Fiscal].[Date]),INCLUDEEMPTY)

SELECT
{[Measures].[InternetSalesAmount],Measures.[AvgGrossProfitMargin]}ONCOLUMNS,
[ShipDate].[Fiscal].[month].MembersONROWS
FROM[AdventureWorks]

2）加权平均值。没有想到好的例子。

基于时间的计算
1）同比和环比。比如：今年每月的销售额和去年同期相比的变化
参见前两天写的Blog :http://www.cnblogs.com/microsheen/archive/2006/10/28/542818.html，这里要补充的是，在同比MDX中，采用COUSIN或ParallelPeriod都可以，但是采用ParallelPeriod更好一些。

2）累计到当前的统计。比如：得到一年中每一个月的累计销售额。

WITHMEMBERMeasures.[AdditiveInternetSalesAmount]AS
SUM(
PeriodsToDate([ShipDate].[Fiscal].[FiscalYear],[ShipDate].[Fiscal].CurrentMember),
[Measures].[InternetSalesAmount]
)
SELECT
{[Measures].[InternetSalesAmount],Measures.[AdditiveInternetSalesAmount]}ONCOLUMNS,
[ShipDate].[Fiscal].[month].MembersONROWS
FROM[AdventureWorks]

3）移动平均值。比如：计算一种Category过去三个月的平均销售额合计。

WITHMEMBERMeasures.[AverageInternetSalesAmount]AS
AVG(LastPeriods(3,[Date].[Calendar].CurrentMember),
[Measures].[InternetSalesAmount])
SELECT
{[Measures].[InternetSalesAmount],Measures.[AverageInternetSalesAmount]}ONCOLUMNS,
NONEMPTY([Product].[ProductCategories].[Category].Members,
DESCENDANTS([Date].[Calendar].[CalendarYear].&[2002],[Date].[Calendar].[Month],SELF)
)ONROWS
FROM[AdventureWorks]

总结
MDX的表现力非常强大，以上的一些内容希望起到抛砖引玉的作用。