[OBIEE 11g] Time Series Functions

Time Series functions in OBIEE11g  

In Sales business, The sales manager want to see the sales data for one month ago, one year ago or from some amount of time to current date or starting from a period and ending in another period they want to calculate the sales amount. How will you do that?

Time series functions providing the way to make the comparison between the time to calculate a measure because SQL is not providing any direct way to make time comparisons. So by using Time series function we can calculate a measure for the above explained requirements. 

Note: Before going to use the time series functions we have to create Time Dimension hierarchy first.

We are creating a measure using time series functions in BMM Layer of Administration tool.
In OBIEE 10g we are having two time series functions

1. Ago( )
2. ToDate( )

1. Ago( )
    This function calculates aggregated value for a measure as of some time period ( a month  ago, or a year ago) from the current time. This function is passing three parameters.
        Syntax: Ago(<<Measure>>, <<Level>>, <<Number of Periods>>) 
     i. Measure: A measure column which you to make use in this function
    ii. Level : On which level you want to calculate this measure. This level is selected from
        the Time Dimension hierarchy
   iii. Number of Period: A numerical value which will go for how many level
        you want to go before from current time.
        E.g.: Ago("SH"."SALES"."AMOUNT_SOLD" ,  "SH"."Time Dimension"."Month" , 1) 
        In the above example I have created a Column called 'Sales Month Ago' using Ago()
        function.

2. ToDate(  )

     This function aggregates a measure from a beginning of a specified time  period to the  currently displayed time. We can create a calculated column using this function by  following the same procedure how we have created a column using 'Ago' function.
     Syntax: ToDate(<<Measure>>, <<Level>>) 
     For e.g: ToDate("SH"."SALES"."AMOUNT_SOLD" ,  "SH"."Time Dimension"."Year" ) 
     With the above example 'Amount Sold' Column is the measure and 'Year' is the level  Which will calculated the measure  from the beginning of the year to a specified time.

In OBIEE11g with the above function there is one more function has been added


3. PeriodRolling( )

 
    This function allow us to create a aggregated measure across a specified set of query
    grain period, rather than within a fixed time series grain. The common use of this
    function is to create a Rolling Average such '10-Week Rolling Average'

Syntax: PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)
     Measure: represents the logical measure column from which you want to derive.
     Starting Period Offset: identify the first period used in the rolling aggregation.
     Ending Period Offset: Identify the last period used in the rolling aggregation.
     For e.g: PeriodRolling( "SH"."SALES"."AMOUNT_SOLD", -2, 0)
    With the above example I'm creating three months rolling sum of  column 'Amount Sold'. It  includes past two months with the current month.
    The numerical value '-2' in the offset indicates the month Jan-12, Feb-12 if our current  month is 'Mar-12' The numerical value '0' in the offset indicates the Current Month

Note:

PeriodRolling( ) function which will not the calculate the Average sum of three months for the measure. Average (AVG( ) function in OBIEE) computes the average of the database rows accessed at the storage grain. So If you want to calculate the 3-Month sales average for the above explained Period Rolling example we have to write the syntax like below:

3-Month Sales Average = 
(PeriodRolling( "SH"."SALES"."AMOUNT_SOLD", -2, 0) )/3