当前位置:首页 > 开发 > 开源软件 > 正文

MDX练习(一) ------兼顾MSAS与mondrian

发表于: 2009-09-17   作者:dinguangx   来源:转载   浏览:
sql
摘要: /* .members 查询级别下的所有成员.但是, 如果维度中包含多个层次,就必须明确指定level.member,而不能通过[Dimension].members来进行查询 */ select [Time By Day].[the_month].members on rows, {[Measures].[Store Cost]} on columns from [Sales
/* .members 查询级别下的所有成员.但是,
如果维度中包含多个层次,就必须明确指定level.member,而不能通过[Dimension].members来进行查询 */
select [Time By Day].[the_month].members on rows,
      {[Measures].[Store Cost]} on columns
from [Sales]
     
/* .children 查询成员下的成员,不包括all
* 在AS中,可以将children直接作用于level,而mondrian中只能将children作用于member
*/
select [Time By Day].[the_month].children on rows,
      {[Measures].[Store Cost]} on columns
from [Sales]  
     
/* Descendants(member[,[level],[flag] ]) 查询成员的后代成员 */
select Descendants([Time By Day].[the_month].[April]) on rows,
      {[Measures].[Store Cost]} on columns
from [Sales]  
select Descendants([Time By Day].[the_year].[1997],[Time By Day].[the_month],self) on rows,
      {[Measures].[Store Cost]} on columns
from [Sales]
     
/* non empty{} 去除集合中的空切片 */
select non empty {Descendants([Time By Day].[the_month].[April])} on rows,
      {[Measures].[Store Cost]} on columns
from [Sales]           
     
/* 使用元组来进行更高级的查询 -- 元组中的每一个成员都来自于不同的维度,而且不能由元组来构成元组*/
select {([Time By Day].[the_month].[April],[Measures].[store sales]),([Time By Day].[the_month].[June],[Measures].[store cost])} on 0,
      non empty{[Product].[Product].children} on 1
from [Sales]
where ([Customer].[State Province])
     
/* CrossJoin(set,set) 返回一个交叉集合 */
select crossjoin({[Time By Day].[the_month].[April],[Time By Day].[the_month].[June]},{[Measures].[store sales],[Measures].[store cost]}) on 0,
      {[Product].[Product].children} on 1
from [Sales]
where ([Customer].[State Province])
     
/* filter(set,boolean) 返回一个过滤之后的集合 */
select crossjoin({[Time By Day].[the_month].[April],[Time By Day].[the_month].[June]},{[Measures].[store sales],[Measures].[store cost]}) on 0,
      filter({[Product].[Product].children},([Time By Day].[the_month].[April],[Measures].[Store Sales]) > 20.0) on 1
from [Sales]
where ([Customer].[State Province])
     
/* order(set,tuple,flag) 返回一个有序集合
     BDESC 会忽略层次结构
     DESC   将元组的层次结构和维度组成考虑在内,生成一个更复杂的有序集*/
select crossjoin({[Time By Day].[the_month].[April],[Time By Day].[the_month].[June]},{[Measures].[store sales],[Measures].[store cost]}) on 0,
      order(
   filter({[Product].[Product].children},([Time By Day].[the_month].[April],[Measures].[Store Sales]) > 20.0)
   ,([Time By Day].[the_month].[April],[Measures].[Store Sales])
   BDESC) on 1
from [Sales]
where ([Customer].[State Province])   
     
/* 计算成员:通过{with member 成员标识符 AS '成员计算公式' [,properties]} 来定义
        (1)如果计算成员作为另一个成员的子成员出现,那么父成员的名称必须也出现,作为成员标识符的一部分.
        (2)不允许计算成员成为另一个计算成员的子成员
    */
with
member [Measures].[profit] as   [Measures].[Store Sales] - [Measures].[Store Cost]
       ,format_string = "$#.00"

member [Measures].[percent profit] as [Measures].[profit] / [Measures].[Store Sales]
       ,format_string = "#.00%"
select {[Measures].[Store Cost],[Measures].[Store Sales],[Measures].[profit],[Measures].[percent profit]} on columns,
      filter({[Product].Product.children},[Measures].[percent profit] > 0.6) on rows
from [sales]

/* 计算成员的优先级问题:如果两个计算成员分别位于行和列上,需要使用solve_order属性来解决优先计算的问题 */
with
member [Measures].[profit] as   [Measures].[Store Sales] - [Measures].[Store Cost]
       ,format_string = "$#.00"
       ,solve_order = 0

member [Time By Day].[quarter].[growth] as ([Time By Day].[quarter].[Q2] - [Time By Day].[quarter].[Q1]) / [Time By Day].[quarter].[Q1]
       ,format_string = "#.00%"
       ,solve_order = 1
select {[Measures].[Store Cost],[Measures].[Store Sales],[Measures].[profit]} on columns,
      {[Time By Day].[quarter].[Q1],[Time By Day].[quarter].[Q2],[Time By Day].[quarter].[growth]} on rows
from [sales]

MDX练习(一) ------兼顾MSAS与mondrian

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号