• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Create a Measure Using the PERIODROLLING Function in OBIEE

Time series functions provide the ability to compare business performance with previous time periods, allowing you to analyze data that spans multiple time periods.

In OBIEE 11 we can use the Time Series Function in analysis and not only in Administration.

Functions of OBIEE 11g

-AGO

-TODATE

-PERIODROLLING

AGO

AGO function is a time series aggregation function that calculates the aggregated value from the current time back to a specified time period.

Time series functions operate on members of time dimensions which are at or below the level of the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query. Alternatively, you can apply a filter to the query that specifies a single member at or below the given level.

If unsupported metrics are requested, NULL values are returned and a warning entry is written to the nqquery.log file when the logging level equals three or above.

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(<measure_expression>, <level>, <number of period>)

TODATE

The TODATE function accumulates the measure from the beginning of the time series grain period to the current displayed query grain period.

ToDate aggregates a measure attribute from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.

If unsupported metrics are requested, NULL values will be returned and a warning entry will be written to the NQQuery.log file when the logging level equals three or above.

A 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.

Syntax:

TODATE(<measure_expression>, <level>)

PERIODROLLING

The PERIODROLLING function does not have a time series grain; instead, you specify a start and end period in the function.

The PERIODROLLING function lets you perform an aggregation across a specified set of query grain periods, rather than within a fixed time series grain. The most common use is to create rolling averages.

Syntax 

PERIODROLLING(measure, x ,y [,hierarchy])

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

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.

Create a Measure Using the PERIODROLLING Function in OBIEE

-Right-click the F1 Revenue logical table and select New Object > Logical Column.

-On the General tab, name the new logical column Revenue 3-Period Rolling Sum.

OBIEE 11g Tutorials

-On the Column Source tab, select “Derived from existing columns using an expression.”

OBIEE 11g Tutorials

-Open the Expression Builder.

At TekSlate, we offer resources that help you in learning various IT courses. 
We avail both written material and demo video tutorials. 
To gain in-depth knowledge and be on par with  practical experience, then explore OBIEE 11g Training Online.

-Select Functions > Time Series Functions and double-click PeriodRolling to insert the expression.

OBIEE 11g Tutorials

-Click <<Measure>> in the expression.

OBIEE 11g Tutorials

-Select Logical Tables > F1 Revenue and then double-click Revenue to add it to the expression.

OBIEE 11g Tutorials

-Click <<Starting Period Offset>> in the expression.

OBIEE 11g Tutorials

-Enter -2. This identifies the first period in the rolling aggregation.

OBIEE 11g Tutorials

-Click <<Ending Period Offset>>.

OBIEE 11g Tutorials

-Enter 0. This identifies the last period in the rolling aggregation.

OBIEE 11g Tutorials

These integers are the relative number of periods from a displayed period. In this example, if the query grain is month, the 3 month rolling sum starts two months in the past (-2) and includes the current month (0).

-Click OK to close the Expression Builder.

-Check your work in the Logical Column dialog box:

OBIEE 11g Tutorials

-Click OK to close the Logical Column dialog box.

-Drag the Revenue 3-Period Rolling Sum logical column to the Base Facts presentation folder.

OBIEE 11g Tutorials

-Save the repository and check consistency. Fix any errors or warnings before you proceed.

-Close the repository. Leave the Administration Tool open.

For indepth understanding of OBIEE 11g click on

Summary
Review Date
Reviewed Item
Create a Measure Using the PERIODROLLING Function in OBIEE
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Create a Measure Using the PERIODROLLING Function in OBIEE"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.