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

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

发表于: 2009-09-17   作者:dinguangx   来源:转载   浏览:
sql
摘要: /* .parent 获取当前成员的父成员 可以结合.currentMember和.parent来获取当前member占父成员的比重 */ with member [percent] as ([Measures].[store sales]) / ([Measures].[store sales],[product].[brand name].parent) ,for
/* .parent 获取当前成员的父成员
   可以结合.currentMember和.parent来获取当前member占父成员的比重
  */
with  member [percent] as
  ([Measures].[store sales]) / ([Measures].[store sales],[product].[brand name].parent)
  ,format_string='0.00%'
select {[Measures].[store sales] ,[Measures].[store cost],[percent]} on columns,
   non empty [product].[brand name].members on rows 
from [sales]

/* ancestor(member,level) 获取当前成员的祖先成员
   可以结合.currentMember和ancestor来获取当前member占祖先成员的比重
  */

with  member [Measures].[percent] as
  ([Measures].[store sales]) / ([Measures].[store sales],ancestor([product].[brand name].currentMember,[product].[product subcategory]))
  ,format_string="0.00%"
select {[Measures].[store sales] ,[Measures].[store cost],[Measures].[percent]} on columns,
   non empty [product].[brand name].members on rows 
from [sales]

/* avg(set[,measure_expr]) 计算集合在某一度量上的平均值
	sum(set[,measure_expr]) 计算集合在某一度量上的总和
	count(set)计算集合中的成员数量
	
  在AS中,不能用Level.members,否则会将all也给统计进来,但在mondrian中却不会这么做
  而且,在AS中,count()运算时,并没有考虑member所在的父级别,而在mondrian中会考虑层次关系:
  亦即,相同名称的member如果处理不同的父层次中,mondrian会将它们作为不同的member,而AS认为它们是
  同一member
*/
with member [avg] as
     avg({[Product].[Brand Name].children},[Measures].[Store Cost])
     member [sum] as
     sum({[Product].[Brand Name].children},[Measures].[Store Cost])
     member [count] as
     count({[Product].[Brand Name].children}) 
select {[avg],[sum],[count]} on columns 
from [sales]

/**
 * 时期相关的一些函数.
 * .prevMember  获得前一个周期成员  可用于进行同期比
 * .nextmember  获得当前成员的后一个成员
 * Parallelperiod(level,num_expr,member)
 */
with member [Measures].[sales increase] as
     [Measures].[Unit Sales] -
        ([Time By Day].[quarter].currentMember.prevMember,[Measures].[Unit Sales])
,format_string="0.00"
select {[Measures].[Unit Sales],[Measures].[sales increase]} on columns,
       {[Time By Day].[quarter].children} on rows 
from [Sales]
//拿当前月与上季度的月分进行比较(即同季度比)
with member [Measures].[sales increase] as
     [Measures].[Unit Sales] - 
          ([Measures].[Unit Sales],ParallelPeriod([Time By Day].[the_month],3,[Time By Day].currentMember))
,format_string="0.00"
select {[Measures].[Unit Sales],[Measures].[sales increase]} on columns,
       {[Time By Day].[the_month].children} on rows 
from [Sales]

/**
 * 下面的例子来说明本年到当前月的总额,如果要统计当前季的总和,就将参数由the_year修改为the_month
 * PeriodsToDate([level[,member]])  
 * 使用这个函数时,level所在的维度应该为时间维度,而且每个level的levelType都应该被正确定义
 * 另外,还可以使用另外一种简洁的表达方式来代替PeriodsToDate()函数.
 *  (1) ytd(member)来代替periodsToDate(<year level>,member)
 *  (2) qtd(member)来代替periodsToDate(<quarter level>,member)
 *  (3) mtd(member)来代替PeriodsToDate(<month level>,member)
 *  (4) wtd(member)来代替PeriodsToDate(<week level>,member)
 */
with member [Measures].[year total sales]
as  'Sum(ytd(),[Measures].[Store Sales])'
select {[Measures].[Store Sales],[Measures].[year total sales]} on columns,
      {[Time By Day].[the_month].members} on rows
from [Sales]

/**
 * <Set> LastPeriods(num_expr,member)选择基于目标成员的时间范围
 *  num_exp:如果为正,向给定的日期之前选择,为负向给定的日期之后选择
 * 示例为:查询April之后的连续5个月的销售量信息
 */
 select {LastPeriods(-5,[Time By Day].[the_month].[April])} on rows,
    [Measures].[Unit Sales] on columns
 from [Sales]
 
 /**
  * 直接获取期初和期末的成员
  * <member> OpeningPeriod([[<level>][,<member>]])
  * <member> ClosingPeriod([[<level>][,<member>]])
  */
//查询1997年的第1个季度和最后一个季度
select {OpeningPeriod([Time By Day].[quarter],[time by day].[the_year].[1997]),ClosingPeriod([Time By Day].[quarter],[time by day].[the_year].[1997])} on 0
from [Sales]
//查询quarter level下的第1个季度和最后一个季度,得到的是[1997].[Q1]和[1998].[Q4]
select {OpeningPeriod([Time By Day].[quarter]),ClosingPeriod([Time By Day].[quarter])} on 0
from [Sales]
//仍然查询quarter level下的第1个和最后一个季度
select {OpeningPeriod([time by day].[1997].[Q1]),ClosingPeriod([time by day].[1997].[Q2])} on 0
from [Sales]
//获取year level下的年份
select {OpeningPeriod(),ClosingPeriod()} on 0
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号