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