__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>>)**

Eg.

**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>>)**

Eg.

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

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

Eg.

**PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0) /3**

Reference :

otn.oracle.com