[OBIEE 11g] Time Series functions

 

 

 

Time Series functions in OBIEE 11g

 

 

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?

Yes, Absolutely 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(<>, <>, <>) 

 
     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(<>, <>) 
    

 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: 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