21 February 2012

Modeling Time Series Function in OBIEE

Modeling Time Series Data

Time series functions provide the ability to compare business performance with previous time periods, allowing you to analyze data that spans multiple time periods. For example, time series functions enable comparisons between current sales and sales a year ago, a month ago, and so on.

Because SQL does not provide a direct way to make time comparisons, you must model time series data in the Oracle BI repository. First, set up time dimensions based on the period table in your data warehouse. Then, you can define measures that take advantage of this time dimension to use the AGO, TODATE, and PERIODROLLING functions. At query time, the Oracle BI Server then generates highly optimized SQL that pushes the time offset processing down to the database whenever possible, resulting in the best performance and functionality.

About Time Series Functions

Time series functions operate on time-oriented dimensions. To use these functions on a particular dimension, you must designate the dimension as a Time dimension and set one or more keys at one or more levels as chronological keys. These keys identify the chronological order of the members within a dimension level.

                                      Fig-1 Table : Time Series Function Data Comparision

In Expression Builder, Ago function has the following template:

Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)

In Expression Builder, the TODATE function has the following template:

ToDate(<<Measure>>, <<Level>>)

In Expression Builder, the PERIODROLLING function has the following template:

PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)

About the AGO Function
The AGO function offsets the time dimension to display data from a past period. This

function is useful for comparisons, such as Dollars compared to Dollars a Quarter Ago.

Have the Look to the Ago Function Data in Table Above (Fig-1)

Ago Expression:
Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)
Ago("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" , 1)

About the TODATE Function
The TODATE function accumulates the measure from the beginning of the time series

grain period to the current displayed query grain period.

Have the Look to the ToDate Function Data in Table Above (Fig-1)

ToDate Expression:
ToDate(<<Measure>>, <<Level>>)
ToDate("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" )

About the PERIODROLLING 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,

Note that because this function has no time series grain, the length of the rolling

sequence is determined by the query grain. For example, "Dollars 3-Period Rolling

Average" averages the last 3 months if the query grain is Month, but averages the last 3

years if the query grain is Year.

This section describes how to build two measures using the PERIODROLLING

function: "Dollars 3-Period Rolling Sum," and "Dollars 3-Period Rolling Average."

Table above  shows a report with these two measures. (Fig-1)


PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)
PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0) /3

Reference :