MDX初步知识-2

成员之间的关系

 

}         Members(AllMembers)

 

}       CurrentMember

}       PrevMember

}       NextMember

}       Parent

}       Children

 

AllMembers=Children+"default all"

 

创建计算成员

 

}         相当于T-SQL中的标量函数，针对度量的一种计算

 

}       典型范例（求毛利率） 注意：CALCULATE的作用

}       是一系列元组或者成员的集合

}       例如：

 

◦         某几个城市的组合

动态构造成员

 

WITH MEMBER [Measures].[Test] AS

[Measures].[Order Quantity]*1.25,

FORMAT_STRING = "Currency",

VISIBLE = 1

SELECT [Measures].[Test]  ON 0,

[Dim Customer].[State Province Name - Dim Geography].[State Province Name] ON 1

FROM [Adventure Works DW]

WHERE ([Dim Product].[Class].&[H])

MDX 函数和模板

 

成员百分比分析
函数：CurrentMember、Parent等；
分析各城市的销售所占全部城市的总销售额百分比。
WITH MEMBER Measures.[Unit Sales Percent] AS '((Store.CURRENTMEMBER, Measures.[Unit Sales]) / (Store.CURRENTMEMBER.PARENT, Measures.[Unit Sales])) ', FORMAT_STRING = 'Percent'
 
SELECT {Measures.[Unit Sales], Measures.[Unit Sales Percent]} ON COLUMNS,
 ORDER(DESCENDANTS(Store.[USA].[CA], Store.[Store City], SELF),[Measures].[Unit Sales], ASC) ON ROWS
 
FROM Sales

 

}       重要顾客分布分析

函数：Count、Sum、Filter、Descendants等；
分析各个省份中重要顾客的数量及他们的总购买量，"重要顾客"的定义是一个顾客的购买金额或者购买数目达到或超过一定的数值。
 
WITH MEMBER [Measures].[Qualified Count] AS               

        ‘ COUNT(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10))'               
 
 
MEMBER [Measures].[Qualified Sales] AS 'SUM(FILTER(DESCENDANTS(Customers.CURRENTMEMBER, [Customers].[Name]), ([Measures].[Store Sales]) > 10000 OR ([Measures].[Unit Sales]) > 10), ([Measures].[Store Sales]))'
 
 
SELECT {[Measures].[Qualified Count], [Measures].[Qualified Sales]} ON COLUMNS,
 
DESCENDANTS([Customers].[All Customers], [State Province], SELF_AND_BEFORE) ON ROWS
 
FROM Sales

 

排序
函数：Order
对各个产品类别按照Store Sales指标降序排列，排序分为维内排序/整体排序。
select {[Measures].[Unit Sales], [Measures].[Store Sales]} on columns,
Order([Product].[Product Department].members, [Measures].[Store Sales], DESC) on rows
 
from Sales

 

 

历史相关的累计值
函数：YTD、Sum、Descendants
求销售额的本年累计值YTD()，类似还可以求解历史累计YTD()、本月累计MTD()、本周累计WTD()等, 以及更通用的函数PeriodToDate()。
with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])'
 
select {[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns, {Descendants([Time].[1997],[Time].[Month])} on rows
from [Warehouse and Sales]

 

 

逻辑判断
函数：IIf
逻辑判断可以根据不同的条件产生不同的结果。下例判断各商店是否是啤酒及白酒的大卖家。
 
WITH MEMBER [Product].[BigSeller] AS 'IIf([Product].[Drink].[Alcoholic Beverages].[Beer and Wine] > 100, "Yes","No")'
SELECT {[Product].[BigSeller],[Product].children} ON COLUMNS,
 
{[Store].[All Stores].[USA].[CA].children} ON ROWS
 
FROM Sales
  带参数的查询

同期、前期
 
函数：PrevMember、ParellelPeriod
 
 
求解各产品销售额的去年同期值，年增长率。
 
with member [Measures].[Store Sales Last Period] as '([Measures].[Store Sales], Time.PrevMember)', format='#,###.00'
 
member [Measures].[Yearly Increase Rate] as ‘([Measures].[Store Sales] - [Measures].[Store Sales Last Period])/ [Measures].[Store Sales Last Period]', FORMAT_STRING = 'Percent'
 
 
select {[Measures].[Store Sales], [Measures].[Store Sales Last Period]} on columns,
 
{ [Product].members} on rows
 
from Sales
 
where ([Time].[1998])

 

◦        环比

◦        同比

 

 

如何在应用程序中查寻MDX

 

◦         创建Connection

◦         创建Command

◦         执行Command

◦         处理结果

◦        

}                         使用AdomdClient的访问接口

Top N分析
 
函数：TopCount
 
 
求解1998年总购买量处于前5名的顾客；
 
select {[Measures].[Store Sales]} on columns,
 
{TopCount([Customers].[Customer Name].members,5, [Measures].[Store Sales])} on rows
 
from Sales
 
where ([time].[1998])

 

 

成员过滤
 
函数：Filter、Except
 
 
求解1998年所有顾客中购买总额得到1万元以上的顾客，列出满足条件的顾客的名字、年购买数量、年购买金额。
 
Select {[measures].[Store Sales],[measures].[unit sales]} on columns,
 
FILTER(Customers.[Name].Members,[Measures].[Store Sales] > 10000) on rows
 
From sales
 
Whare ([time].[1998])
 
 
另外一种成员过滤（从所有的媒体类型中剔除No Media类型），确切的说应该是集合运算。
 
select {[Measures].[Unit Sales]} on columns,
 
except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}) on rows
 
from Sales
 

时间段

 

函数：sum、"："运算符

求美国的商店在指定时间段内的销售额。

 

WITH  MEMBER [Time].[1997].[Six Month] AS 'SUM([Time].[1]:[Time].[6])'

 

MEMBER [Time].[1997].[Nine Month] AS 'SUM([Time].[1]:[Time].[9])'

 

SELECT {[Time].[1997].[Six Month],[Time].[1997].[Nine Month]} ON COLUMNS,

 

{[measures].[store salse]} ON ROWS

 

FROM Sales

 

Where ([Store].[USA])

 

 

}       使用商业智能向导构造常见的计算成员

([Measures].[Internet Sales-Sales Amount] - [Measures].[Internet Sales-Total Product Cost]) / [Measures].[Internet Sales-Sales Amount]

}      

◦         绝大多数的时候，都需要保留这一句在最前面