微软Olap服务MDX函数应用举例.docx

微软OLAP服务MDX函数应用举例 本文目的对丰富的函数集可以提供的强大功能有一个直观的认识看看Microsoft是怎样应用函数，也许可以参考借鉴；应用举例 成员百分比分析 函数：CurrentMember、Parent等；分析各城市的销售所占全部城市的总销售额百分比。WITH MEMBER Measures.Unit Sales Percent AS (Store.CURRENTMEMBER, Measures.Unit Sales) / (Store.CURRENTMEMBER.PARENT, Measures.Unit Sales) , FORMAT_STRING = PercentSELECT Measures.Unit Sales, Measures.Unit Sales Percent ON COLUMNS,ORDER(DESCENDANTS(Store.USA.CA, Store.Store City, SELF),Measures.Unit Sales, ASC) ON ROWSFROM 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 rowsfrom 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 rowsfrom Warehouse and Sales 四则运算函数：四则运算函数；在成员上及指标上均可以进行四则运算，动态派生出新的成员及指标。WITH MEMBER MEASURES.ProfitPercent AS (Measures.Store Sales-Measures.Store Cost)/(Measures.Store Cost),FORMAT_STRING = #.00%MEMBER Time.First Half 97 AS Time.1997.Q1 + Time.1997.Q2MEMBER Time.Second Half 97 AS Time.1997.Q3 + Time.1997.Q4SELECT Time.First Half 97, Time.Second Half 97, Time.1997.CHILDREN ON COLUMNS,Store.Store Country.USA.CHILDREN ON ROWSFROM SalesWHERE (MEASURES.ProfitPercent) 逻辑判断函数：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 ROWSFROM Sales 成员属性函数：Properties、Dimension Properties成员属性是与成员绑定的，其对应关系导致很难选择合适的使用方式。以下是使用成员属性的例子，它对应每个商店成员列出了商店类型属性，相应的，商店经理、商店规模、商店地址等属性也可以被列出。该用法稍加灵活应用就可以解决过去遇到的企业名称企业代码对应展示问题。WITH MEMBER Measures.StoreType AS Store.CurrentMember.Properties(Store Type),MEMBER Measures.ProfitPct AS (Measures.Store Sales - Measures.Store Cost) / Measures.Store Sales, FORMAT_STRING = #.00%SELECT Descendants(Store.USA, Store.Store Name) ON COLUMNS,Measures.Store Sales, Measures.Store Cost, Measures.StoreType, Measures.ProfitPct ON ROWS FROM Sales另外一种用法：SELECT Measures.Units Shipped, Measures.Units Ordered ON COLUMNS,Store.Store Name.MEMBERS DIMENSION PROPERTIES Store.Store Name.Store Type ON ROWSFROM Warehouse 多步计算实现复杂逻辑函数：其实可以是任意函数合乎逻辑的组合求出从来没有买过乳制品的顾客，求解过程是先求出每位顾客在过去购买的乳制品的数量累计，然后找出累计值为0的顾客。同样，过去遇到的求税额大于平均税额的海关的问题可以类似求出。with member Measures.Dairy ever as sum(Time.members, (Measures.Unit Sales,Product.Food.Dairy)set Customers who never bought dairy as filter(Customers.members, Measures.Dairy ever = 0)select Measures.Unit Sales, Measures.Dairy ever on columns,Customers who never bought dairy on rowsfrom Sales 同期、前期函数：PrevMember、ParellelPeriod求解各产品销售额的去年同期值，年增长率。with member Measures.Store Sales Last Period as (Measures.Store Sales, Time.PrevMember), format=#,#.00member Measures.Yearly Increase Rate as (Measures.Store Sales - Measures.Store Sales Last Period)/ Measures.Store Sales Last Period, FORMAT_STRING = Percentselect Measures.Store Sales, Measures.Store Sales Last Period on columns, Product.members on rowsfrom Saleswhere (Time.1998)另一个例子，使用ParellelPeriod函数。WITH MEMBER Measures.YTD Unit Sales AS COALESCEEMPTY(SUM(YTD(), Measures.Unit Sales), 0) MEMBER Measures.Previous YTD Unit Sales AS (Measures.YTD Unit Sales, PARALLELPERIOD(Time.Year) MEMBER Measures.YTD Growth AS Measures.YTD Unit Sales - (Measures.Previous YTD Unit Sales)SELECT Time.1998 ON COLUMNS,Measures.YTD Unit Sales, Measures.Previous YTD Unit Sales, Measures.YTD Growth ON ROWSFROM Sales; Top N分析函数：TopCount求解1998年总购买量处于前5名的顾客；select Measures.Store Sales on columns,TopCount(Customers.Customer Name.members,5, Measures.Store Sales) on rowsfrom Saleswhere (time.1998) 成员过滤函数：Filter、Except求解1998年所有顾客中购买总额得到1万元以上的顾客，列出满足条件的顾客的名字、年购买数量、年购买金额。Select measures.Store Sales,measures.unit sales on columns,FILTER(Customers.Name.Members,Measures.Store Sales 10000) on rowsFrom salesWhare (time.1998)另外一种成员过滤（从所有的媒体类型中剔除No Media类型），确切的说应该是集合运算。select Measures.Unit Sales on columns,except(Promotion Media.Media Type.members,Promotion Media.Media Type.No Media) on rowsfrom Sa