商务合作:179001057@qq.com

常用MDX计算查询(

技术2022-05-11  0


某平台价值19860元的编程课程资料免费领取【点我领取】


常用MDX计算查询 (2009-04-03 16:59:17) <script></script> 标签:杂谈  分类:技术资料

本文来源于《MDX Solutions with Microsoft SQL.Server Analysis Services 2005 and Hyperion Essbase 2nd Edition》一书的内容,主要介绍了一些经常编写的MDX语句的写法。以下MDX语句可以在SSAS的示例库:Adventure Works中运行。例子模型以下的MDX中用到的Hierarchy如下:

 

 

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

WITH MEMBER [Measures].[Sale Amount Ratio] AS'[Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount], [Product].[Product Categories].[All])' , FORMAT_STRING = '0.00%'SELECT{[Measures].[Internet Sales Amount], [Measures].[Sale Amount Ratio]} ON 0,NON EMPTY [Product].[Product Categories].[Product Name].Members ON 1FROM [Adventure Works]

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

WITH MEMBER [Measures].[Sale Amount Ratio] AS'[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 1FROM [Adventure Works]3)某个子项占其祖先的百分比。比如:每种Product的销售额占其所属的Category销售额的百分比。

WITH MEMBER [Measures].[Sale Amount Ratio] AS'[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 1FROM [Adventure Works]分配、分摊数量1)根据一个Measure值来分配数量。比如:按照每种Product占总体的销售额多少来分摊成本。

WITH MEMBER [Measures].[Product Cost] AS'([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 1FROM [Adventure Works]2)根据一个Hierarchy来分配数量。比如:在Product Hierarchy中计算每种Category的成本的时候,可以根据每种Category下有多少个产品来进行分配。

WITH MEMBER [Measures].[Product Cost] AS'([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 1FROM [Adventure Works]平均值1)简单平均值。比如:计算一个月中每天平均的销售额是多少。

WITH MEMBER Measures.[Avg Gross Profit Margin] AS   [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 ROWSFROM [Adventure Works]2)加权平均值。没有想到好的例子。

基于时间的计算1)同比和环比。比如:今年每月的销售额和去年同期相比的变化参见: http://blog.sina.com.cn/s/blog_4cd36be30100c7ga.html,这里要补充的是,在同比MDX中,采用COUSIN或ParallelPeriod都可以,但是采用ParallelPeriod更好一些。

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

WITH MEMBER Measures.[Additive Internet Sales Amount] AS   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 ROWSFROM [Adventure Works]3)移动平均值。比如:计算一种Category过去三个月的平均销售额合计。

WITH MEMBER Measures.[Average Internet Sales Amount] AS   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 ROWSFROM [Adventure Works]

 

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


最新回复(0)