Time Series Functions OBIEE 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

Sunday 15 January 2012

Time Series Functions OBIEE 11g


    Time Series Functions

    Time series functions operate on time-oriented dimensions. The time series functions calculate AGOTODATE, and PERIODROLLING functions based on user supplied calendar tables, not on standard SQL date manipulation functions.
    Time series functions operate on time-oriented dimensions. To use time series functions on a particular dimension, you have to designate the dimension as a Time dimension and set one or more keys at one or more levels as chronological keys. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information.
    Functions include:

    AGO

    This function is a time series aggregation function that calculates the aggregated value from the current time back to a specified time period. For example, AGOcan produce sales for every month of the current quarter and the corresponding quarter-ago sales.
    Multiple AGO functions can be nested if all the AGO functions have the same level argument. You can nest exactly one TODATE and multiple AGO functions if they each have the same level argument.
    Syntax 
    AGO(expr, [time_level], offset)
    
    Where:
    expr is an expression that references at least one measure column.
    time_level is an optional argument that specifies the type of time period, such as quarter, month, or year.
    offset is an integer literal that represents the time shift amount.
    Example 
    The following example returns last year's sales:
    AGO(sales, year, 1)
    
    Determining the Level Used by the AGO Function
    The unit of time (offset) used in the AGO function is called the level of the function. This value is determined by the measure level of the measures in its first argument, the AGO level (optionally specified within the function), and the query level of the query to which the function belongs.
    • The measure level for the measure can be set in the Administration Tool. If a measure level has been set for the measure used in the function, the measure level is used as the level of the function. The measure level is also called the storage grain of the function.
    • The AGO level can be optionally specified as the second argument of the function. If a measure level has not been set in the Administration Tool, but anAGO level has been specified, the AGO level is used as the level of the function. The AGO level is also called the time series grain of the function.
    • If a measure level has not been set in the Administration Tool, and if no AGO level has been set explicitly in the function, the query level is used as the level of the function. The query level is also called the query grain of the function.

    PERIODROLLING

    This function computes the aggregate of a measure over the period starting x units of time and ending y units of time from the current time. For example, you can use PERIODROLLING to compute sales for a period that starts at a certain quarter before and ends at a certain quarter after the current quarter.
    You cannot nest AGO and TODATE functions within a PERIODROLLING function. Also, you cannot nest PERIODROLLINGFIRST, and LAST functions.
    If you embed other aggregate functions (like RANKTOPNPERCENTILEFILTER, or RSUM) inside PERIODROLLING, the PERIODROLLING function is pushed inward. For example, PERIODROLLING(TOPN(measure)) is executed as TOPN(PERIODROLLING(measure)).
    Syntax 
    PERIODROLLING(measure, x ,y [,hierarchy])
    
    Where:
    measure is the name of a measure column.
    x is an integer that specifies the offset from the current time. Precede the integer with a minus sign (-) to indicate an offset into the past.
    y specifies the number of time units over which the function will compute. To specify the current time, enter 0.
    hierarchy is an optional argument that specifies the name of a hierarchy in a time dimension, such as yr, mon, day, that you want to use to compute the time window. This option is useful when there are multiple hierarchies in a time dimension, or when you want to distinguish between multiple time dimensions.
    If you want to roll back or forward the maximum possible amount, use the keyword UNBOUND. For example, the function PERIODROLLING (measure, -UNBOUND, 0)sums over the period starting from the beginning of time until now.
    You can combine PERIODROLLING and AGGREGATE AT functions to specify the level of the PERIODROLLING function explicitly. For example, if the query level is day but you want to find the sum of the previous and current months, use the following:
    SELECT year, month, day, PERIODROLLING(AGGREGATE(sales AT month), -1)
    
    Examples 
    PERIODROLLING(monthly_sales, -1, 1)
    
    PERIODROLLING(monthly_sales, -UNBOUND, 2)
    
    PERIODROLLING(monthly_sales, -UNBOUND, UNBOUND)
    
    Determining the Level Used by the PERIODROLLING Function
    The unit of time (offset) used in the PERIODROLLING function is called the level of the function. This value is determined by the measure level of the measures in its first argument and the query level of the query to which the function belongs. The measure level for the measure can be set in the Administration Tool. If a measure level has been set for the measure used in the function, the measure level is used as the level of the function. The measure level is also called thestorage grain of the function.
    If a measure level has not been set in the Administration Tool, then the query level is used. The query level is also called the query grain of the function. In the following example, the query level is month, and the PERIODROLLING function computes the sum of the last, current, and next month for each city for the months of March and April:
    SELECT year, month, country, city, PERIODROLLING(sales, -1, 1)
    WHERE month in ('Mar', 'Apr') AND city = 'New York' 
    
    When there are multiple hierarchies in the time dimension, you must specify the hierarchy argument in the PERIODROLLING function. For example:
    SELECT year, fiscal_year, month, PERIODROLLING(sales, -1, 1, "fiscal_time_hierarchy")
    
    In this example, the level of the PERIODROLLING function is fiscal_year.

    TODATE

    This function is a time series aggregation function that aggregates a measure from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.
    TODATE function may not be nested within another TODATE function. You can nest exactly one TODATE and multiple AGO functions if they each have the same level argument.
    TODATE is different from the TO_DATE SQL function supported by some databases. Do not use TO_DATE to change to a DATE data type. Instead, use the CASTfunction. See "CAST" for more information.
    Syntax 
    TODATE(expr, time_level)
    
    Where:
    expr is an expression that references at least one measure column.
    time_level is the type of time period, such as quarter, month, or year.
    Example 
    The following example returns the year-to-date sales:
    TODATE(sales, year)

    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

    No comments:

    Post a Comment

    Latest Govt Jobs Vacancy 2023