Time Series functions in OBIEE 10g and 11g - OBIEE GURU

govtjobonline-Latest Government Jobs 2023 Recruitment Notifications

Latest Govt Jobs Whatsapp Group Links-Telegram

For Latest Govt Jobs 2023 or for Freejobalerts or Join

Latest Government Jobs 2023

Thursday 7 February 2013

Time Series functions in OBIEE 10g and 11g

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

    Whatsapp group

    Govt Jobs Whatsapp Group Links 2023

    Telegram group

    Govt Jobs Telegram Group

    Facebook group

    Govt Job Online Facebook Group

    Follow on Facebook group

    Govt Jobs Online Facebook Page

    Subscribe to Youtube Channel

    Govtjobsonline Youtube

    Join Latest Govt Jobs Telegram Group here

    Join Government Jobs Whatsapp Group here (State Wise)

    Join State Wise Sarkari Results Telegram here

    1. ITI Jobs Notifications
    2. ITI Apprenticeship Program
    3. Diploma Jobs Notifications
    4. Engineering Jobs Notifications
    5. UPSC Jobs Notifications
    6. Court Jobs Notifications
    7. Staff Nurse Jobs Notifications
    8. NHM Jobs Notifications
    9. Exam Syllabus and Pattern
    10. SSC Recruitment

    2 comments:

    1. Hi,
      It is really nice piece of information shared and so wanted to share something that if anybody is interested to learn OBIEE 10g and 11g online can visit http://www.wiziq.com/course/22309 because it is a really good site to learn online.

      ReplyDelete
    2. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again,
      Regards, obiee training in hyderabad

      ReplyDelete

    Latest Govt Jobs Vacancy 2023