OBIEE - Time Series Conversion Functions : AGO and TODATE

Obiee Ceim

About

Time series functions in OBIEE.

The implementation of the time serie function is based on the time dimension. ( See set up time dimensions)

At query time, the Oracle BI Server then generates SQL that pushes the time offset processing down to the database whenever possible.

Before using these functions on a particular dimension, you have to set up the time dimension

Implementation

  • First, set up time dimensions based on the period table in your data warehouse.
  • Then, you can define measures with the time serie function:
    • AGO,
    • TODATE,
    • and PERIODROLLING from 11g

.

Use and grain

Limitation, use :

The Ago and ToDate functions allow you use Expression Builder to call a logical function to perform time series calculations instead of aliasing physical tables and modeling logically.

Exp Builder Ago Td

The time series functions calculate Period Ago and Period to Date functions based on user supplied calendar tables, not on standard SQL date manipulation functions.

The following list describes the important grains in navigating a time query, using the following query example:

Select quarter, YearAgoSales
  • Query grain. The grain of the request. In the query example, the query grain is Quarter.

The grain of a cube in the business model is the combination of the most detailed levels it supports (for instance Day x Product x Office x Sales Rep x Customer. At query time, measures can always be aggregated to higher levels, such as Year x Brand x Company, which is referred to as the “query grain.”

  • Time Series grain. The grain at which the aggregation is requested. In the query example, the Time Series grain is Year. Note that the PERIODROLLING function does not have a time series grain; instead, you specify a start and end period in the function.

Time series query is valid only if the time series grain is at the query grain or higher

  • Storage grain. The query in the example can be computed from daily sales or from monthly sales, or from quarterly sales. The grain of the aggregate source is called aggregation grain.

The chronological key has to be defined at this level

Functions

Ago

OBIEE 10G/11G - Period to period comparison with the AGO Function

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.

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,
  • the last 3 years if the query grain is Year.

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

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

Using this function template, you can create the following expression for the measure:

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

The expression for the 3-month rolling average is:

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

It is usually a mistake to use the AVG function to create a rolling average. AVG computes the average of the database rows accessed at the storage grain, but you need an average where the denominator is the number of rolling periods at the query grain.

Note that the PERIODROLLING function includes a fourth optional hierarchy argument that lets you specify 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.

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.

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

For example, this function can calculate Year to Date sales. A sort of rolling total sum into the level (Year, Quarter, Month) by time column.

Obiee Todate

Implementation:

To calculate the TODATE measure, it seems that OBIEE basically performs the following steps:

  • create an additional query on the TODATE measure with the grain of the report and the grain of the TODATE function.
  • get the data and sums the measure up in a rolling fashion for the grain level of the TODATE function
  • perform a internal join on the baseline column

Example of SQL:

SELECT
        SUM( C_SALES.SALES )       AS c1
      , D_TIME.FISCAL_QUARTER_NAME AS c2
      , D_TIME.FISCAL_YEAR_NAME    AS c3
    FROM
        D_TIME
      , C_SALES
    WHERE
        (
            D_TIME.FISCAL_YEAR_NAME       = '2016'
            AND D_TIME.TIME_DIMENSION_KEY = C_SALES.D_TIME
        )
    GROUP BY
        D_TIME.FISCAL_YEAR_NAME
      , D_TIME.FISCAL_QUARTER_NAME;

Documentation / Reference





Discover More
Bi Server Architecture With Client
OBIEE - Dimension (Logical Table)

A dimension logical table is typically joined to a fact logical table that will contain measures. Every logical dimension table should have a dimensional hierarchy associated with it. This rule holds...
Obiee Fragmentation Content One Column
OBIEE - Grain - Level of (summarization|aggregation)

Grain definition in the context of OBIEE. The following list describes the different grains in navigating a query: Query grain. The grain of the request. Aggregation grain. The grain of the aggregate...
Obiee Multiple Dimension Hierarchie
OBIEE 10G/11G - Dimension Hierarchy (logical dimensions)

(Dimension hierarchies|Logical Dimensions|Hierarchy) introduce formal hierarchies into a business model. They are created in the Business Model and Mapping layer. Before 11g, end users can't see them...
Obiee11g Time Dimension Property
OBIEE 10G/11G - How to set up the time dimension (for time series functions Ago, Todate, ) ?

Oracle BI Server ships Time Series Conversion Functions : AGO and TODATE. They are supported for all relational database and for use with Essbase. They are not support for flat files. To use them on...
Obiee Dimension Drill Down Parameters
OBIEE 10G/11G - Level-based Hierarchy

Level-based hierarchy is the first type of hierarchy in OBIEE. Dimension hierarchy levels allow : to perform aggregate navigation, to configure level-based measure calculations, users from Dashboard...
Obiee 11g Aggregate At
OBIEE 10G/11G - Period to period comparison with the AGO Function

Ago is a time function that calculates the aggregated value from the current time back to a specified time period. This function is useful for comparisons, such as Dollars compared to Dollars a Quarter...



Share this page:
Follow us:
Task Runner