【MDX学习笔记之一】MDX中一些常见的计算

2023-09-16 13:59:49 终极管理员知识笔记 113阅读

本文来自《MDX Solutions with Microsoft SQL.Server Analysis Services 2005 and Hyperion Essbase 2nd Edition》的内容，主要介绍一些常用的编写MDX的语句。以下MDX语句可以在SSAS的示例库中运行：Adventure Works。例子模型以下的MDX中使用的层次结构如下：百分比1)儿童占总数的百分比。比如每个产品的销售额占所有产品销售额的百分比。使用成员[度量]。【salesaumutrio】作【度量】。[网上销售额]/([措施]).【网络销售】，【产品】。【产品类别】。[All])' FORMAT _ STRING=' 0.00% ' SELECT {[Measures]。[InternetSalesAmount]、[Measures]。[saleamountrio]} on 0，非空[产品]。[产品类别]。[product name]. members 1 from[

="color: rgba(255, 0, 0, 1)">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 1
FROM [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 1
FROM [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 1
FROM [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 1
FROM [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 ROWS
FROM [Adventure Works]

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

基于时间的计算
1）同比和环比。比如：今年每月的销售额和去年同期相比的变化
参见前两天写的Blog : ，这里要补充的是，在同比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 ROWS
FROM [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 ROWS
FROM [Adventure Works]

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

标签：