Explain Time Series Functions: AGO, TODATE, PERIODROLLING in Oracle BI

25 / 55

Analyticsintermediate

Time Series Functions: AGO, TODATE, PERIODROLLING

Explain OBIEE's built-in time-series calculation functions and how they depend on a properly modeled Time hierarchy.

Year-over-year, month-to-date and rolling averages are the most requested calculations in any BI tool. OBIEE provides functions for them, and they all depend on a properly modelled Time hierarchy.

AGO is like looking up what you weighed exactly one year ago today. TODATE is like your running weight-loss total since January 1st this year. PERIODROLLING is like your trailing 3-month average weight, which slides forward every time a new month's reading comes in, rather than resetting each January.

Key Concepts

The three.

    AGO(measure, level, n)
      revenue from n periods ago, at that level

    TODATE(measure, level)
      running total from the start of that level to here

    PERIODROLLING(measure, start, end)
      a window relative to the current period

    AGO("Sales"."Revenue", "Time"."Year", 1)      last year's revenue
    TODATE("Sales"."Revenue", "Time"."Year")      year to date
    PERIODROLLING("Sales"."Revenue", -2, 0)       this and the two
                                                  prior periods

Year-over-year growth is then one column.

    (Revenue - AGO(Revenue, Year, 1)) / AGO(Revenue, Year, 1)

Without these, that means a self-join to the fact table on a shifted date, written into every report that needs it.

They require the Time dimension to be modelled properly. Not just present — modelled:

  - a dimension flagged as a Time dimension in the RPD
  - a complete hierarchy: Year, Quarter, Month, Day
  - correct level keys, and a chronological key on each level

The chronological key is the one people miss. It tells the server how to order periods, so it knows which month precedes which. Without it, AGO cannot determine "one period back" and returns nothing or something wrong.

AGO

Gaps in the time dimension break them. These functions navigate by position in the hierarchy, not by date arithmetic. A time table missing weekends, or missing a month with no transactions, means "one period ago" skips.

That is the argument for a complete, densely populated date dimension — every day, whether or not anything happened.

Where they can be used. In an analysis, and also in the RPD as a logical column. Defining year-over-year growth once in the model rather than in each report is the same reasoning as any other shared calculation.

The caution. These functions generate additional queries or larger scans, because the server must retrieve the comparison periods too. A report with four time-series columns over a wide date range is several times the work of the plain measure, which is worth knowing before adding them to a dashboard that loads for five hundred people.

What the interviewer is probing.1. "What do these three do?" Probing: the breadth. Stalls: "Dates." Moves up: AGO shifts a measure back by a period, TODATE accumulates within a period, PERIODROLLING sums a moving window.

2. "What is the prerequisite for them to work?" Probing: the key detail. Stalls: "Nothing." Moves up: a properly configured time dimension with chronological keys at each level — without it they return wrong values or errors.

3. "Why not just write the comparison with a FILTER?" Probing: the judgement. Stalls: "No difference." Moves up: time series functions let the server generate efficient SQL and respect the query grain, where hand-built comparisons often do not.

4. "What is the common defect?" Probing: experience. Stalls: "None." Moves up: a missing chronological key at one level, so year-ago works monthly but silently misbehaves at quarter level.