SQL Server 2008 Analysis Services(OLAP) - 环比、同比

 

--- 同期比较 Cousin

 

登记日期不连续，存在空缺 ===>

            登记日期维度：每月/每天都存在，尽管在事实表中不存在的“天”

 

Case

        When IsEmpty

             (  

               ( 

                 Cousin([Dim Djrq].[YearMonthDay].CurrentMember, [Dim Djrq].[YearMonthDay].CurrentMember.Parent.PrevMember), 

                 [Measures].[Sfzh Distinct Count] 

               ) 

             ) 

        Then Null

 

        Else ( 

               ( [Dim Djrq].[YearMonthDay].CurrentMember,[Measures].[Sfzh Distinct Count]  ) 

               -

               ( Cousin([Dim Djrq].[YearMonthDay].CurrentMember, [Dim Djrq].[YearMonthDay].CurrentMember.Parent.PrevMember),[Measures].[Sfzh Distinct Count]  ) 

             ) 

             /

             ( Cousin([Dim Djrq].[YearMonthDay].CurrentMember, [Dim Djrq].[YearMonthDay].CurrentMember.Parent.PrevMember),[Measures].[Sfzh Distinct Count])

 

End

 

 

 

--- 同期比较 parallelperiod

 

 

--- 同期比较 Generate/DOWSet

 

With Member [Measures].[PrevWeekSales] as

([Measures].[Sales Amount],

Generate(

{[Date].[Date].CurrentMember,Exists([Date].[Date].[Date].Members,[Date].[Day of Week].CurrentMember) as DOWSet}.Item(0),

DOWSet.Item(Rank([Date].[Date].CurrentMember,DOWSet) - 2)).Item(0)

), FORMAT_STRING = "Currency"

select {[Measures].[Sales Amount], [Measures].[PrevWeekSales]} on 0,

{[Date].[Date].&[20010701]:[Date].[Date].&[20010714]} on 1

from [Adventure Works]

 

 

--- 环比

 

Case

 

        When [Dim Djrq].[YearMonthDay].CurrentMember.Level.Ordinal = 0

        Then Null

 

        When IsEmpty

             (  

               ( 

                 [Dim Djrq].[YearMonthDay].CurrentMember.PrevMember, 

                 [Measures].[Sfzh Distinct Count] 

               ) 

             ) 

        Then Null

 

        When IsEmpty

             (  

               ( 

                 [Dim Djrq].[YearMonthDay].CurrentMember, 

                 [Measures].[Sfzh Distinct Count] 

               ) 

             ) 

        Then Null

 

        Else ( 

               ( [Dim Djrq].[YearMonthDay].CurrentMember,[Measures].[Sfzh Distinct Count]  ) 

               -

               ( [Dim Djrq].[YearMonthDay].CurrentMember.PrevMember,[Measures].[Sfzh Distinct Count]  ) 

             ) 

             /

             ( [Dim Djrq].[YearMonthDay].CurrentMember.PrevMember,[Measures].[Sfzh Distinct Count])

 

    End