Model 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 doesn't 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.

This section contains the following topics:

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.

Time series functions include TODATE, and PERIODROLLING. These functions let you use Expression Builder to call a logical function to perform time series calculations instead of aliasing physical tables and modeling logically. The time series functions calculate AGO, TODATE, and PERIODROLLING functions based on the calendar tables in your data warehouse, not on standard SQL date manipulation functions.

The image shows a sample report that includes several measures derived using time series functions.

Description of the illustration timeseries.gif

Several different grains may be used in the time query, such as:

Query grain

The lowest time grain of the request.
Time Series grain
The time series grain indicates the aggregation or offset is requested, for the AGO and TODATE functions. In the report example shown in the image, the time series grain is Quarter. Time series queries are valid only if the time series grain is at the query grain or higher. The PERIODROLLING function doesn't have a time series grain; instead, you specify a start and end period in the function.
Storage grain

You can generate the report, shown in the example, from daily sales or monthly sales. The grain of the source is called the storage grain. A chronological key must be defined at this level for the query to work, but performance is generally much better if a chronological key is also defined at the query grain.

Queries against time series data must exactly match to access the query cache.

The following sections describe the time series conversion functions:

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. The value of Dollars Qago for month 2008/08 equals the value of Dollars for month 2008/05.

The image shows example values for the Dollars and Dollars Qago measures.

Description of the illustration timeseries_1.gif

In the example shown above, the Dollars Qago measure is derived from the Dollars measure.

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

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

<<Measure>> represents the logical measure column from which you want to derive. In this example, you would select the measure "Dollars" from your existing logical fact tables.

<<Level>> is the optional time series grain you want to use. In this example, you would select "Quarter" from your time dimension.

<<Number of Periods>> is the size of the offset, measured in the grain you provided in the <<Level>> argument. For example, if the <<Level>> is Quarter and the <<Number of Periods>> is 2, the function displays dollars from two quarters ago.

Using this function template, you can create an expression for a One Quarter Ago measure, as follows:

Ago("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" , 1)

The <<Level>> parameter is optional. If you don't want to specify a time series grain in the AGO function, the function uses the query grain as the time series grain.

For example, you could define Dollars_Ago as Ago(Dollars, 1). Then, you could perform the following logical query:

SELECT Month, Dollars, Dollars_Ago

The result is the same as if you defined Dollars_Ago as Ago(Dollars, Month, 1), or you could perform the following logical query:

SELECT Quarter, Dollars, Dollars_Ago

The result is the same as if you defined Dollars_Ago as Ago(Dollars, Quarter, 1).

See Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.

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.

For example, the image shows a report with the measure Dollars QTD, the Quarter To Date version of the Dollars measure.

Description of the illustration timeseries_2.gif

In the example, Dollars QTD for Month 2008/05 is the sum of Dollars for 2008/04 and 2008/05. Dollars QTD is the sum of the values for all the query grain periods (month) for the current time series grain period (quarter). The accumulation starts over for the next quarter.

In the example, the Dollars QTD measure is derived from the Dollars measure.

In Expression Builder, the TODATE function uses the following format:

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

<<Measure>> represents the logical measure column from which you want to derive. In this example, you select the measure Dollars from your existing logical fact tables.

<<Level>> is the time series grain you want to use. In this example, you select Quarter from your time dimension.

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

ToDate("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" )

The query grain is specified in the query itself at run time. For example, this measure can display Quarter To Date at the Day grain, and accumulates up to the end of the Quarter.

See Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.

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 such as a 13-week Rolling Average.

The PERIODROLLING function doesn't have a time series grain, the length of the rolling sequence is determined by the query grain. For example, the Dollars 3-Period Rolling Average calculates the mean of values from the last 3 months if the query grain is Month, but calculates the mean of the last 3 years if the query grain is Year.

The image shows a report with these two measures.

Description of the illustration timeseries_3.gif

In the example above , the Dollars 3-Period Rolling Sum and Dollars 3-Period Rolling Avg measures are derived from the Dollars measure.

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

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

<<Measure>> represents the logical measure column from which you want to derive. To create the measure Dollars 3-Period Rolling Sum, you select the measure, Dollars from your existing logical fact tables.

<<Starting Period Offset>> and <<Ending Period Offset>> identify the first period and last period used in the rolling aggregation. The integer is the relative number of periods from the displayed period. In this example, the query grain is month, and the 3-month rolling sum starts 2 periods in the past and includes the current period, that is, for month 2008/07, the rolling sum includes 2008/05, 2008/06 and 2008/07. To create the measure, Dollars 3-Period Rolling Sum, the integers to indicate these offsets are -2 and 0.

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

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

The example also shows a 3-month rolling average. To compute this measure, you can divide the rolling sum that you previously created by 3 to get a 3-period rolling average. The assumption to divide the rolling sum by 3 results from the <<Starting Period Offset>> and <<Ending Period Offset>> fields for the rolling sum that are -2 and 0.

The expression for the 3-month rolling average is:

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

Don't use the AVG function to create a rolling average. The AVG function computes the average of the database rows accessed at the storage grain. To perform the rolling average, you need an average where the denominator is the number of rolling periods at the query grain.

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. See Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.

Create Logical Time Dimensions

Creating time dimensions requires selecting a Time option and designating a chronological key for every level of every dimension hierarchy.

Use these guidelines when modeling time series data:

Use a time series function when the data source contains history. A relational database that contains history might use a star or snowflake schema with an explicit time dimension table. A normalized, historical database might include a time hierarchy with levels in a schema similar to a snowflake. A simple date field isn't adequate for use with a time series function.
Oracle Analytics Server requires the time dimension physical table or set of normalized tables that are separate from its related physical fact table.

A common source schema pattern is a fully denormalized relational table or flat file that has time dimension columns are in the same table as facts and other dimensions. This common source schema pattern can't qualify as a time dimension, because the time dimension table is combined with the fact table. Because you can't change the source model, you can create an Opaque View of the physical table containing the time columns to act as the distinct physical time dimension table. You must join the Opaque View time dimension to the physical table that contains the facts.
In the Physical layer, the time dimension table or lowest-level table in the normalized/snowflake must join directly to the fact table without an intervening tables. Create the join as a foreign key join.
The tables in the physical model containing the time dimension can't join to other data sources, except at the most detailed level.
A member value, a row in relational sources, must be physically present for every period at every hierarchy level. They must not contain rows that are skipped in the sequence. You don't need a fact data for every period. Only the dimension data must be complete.
You must model each unit of distance between members such as month, half, or year, in a separate hierarchy level.

Select the Time Option in the Logical Dimension Dialog

Select the Time option in the General tab of the Logical Dimension dialog to enable time series functions on this dimension.

You can only use logical dimensions with the Time option selected as the time dimension for the time series functions AGO, TODATE, and PERIODROLLING.

The image shows the Time option in the Logical Dimension dialog.

Description of the illustration time_dim1.gif

Set Chronological Keys for Each Level

Designate a chronological key for every level of each dimension hierarchy.

The chronological keys must be comparable with the standard SQL ORDER BY clause. The ORDER BY clause on the chronological key must reflect the real world chronological order of the time dimension members represented by the key. For example, if the time dimension members are: Jan-3-2013, Jan-4-2013, Jan-5-2013 then the following chronological keys can be assigned to them in the same order: 1, 5, 9. However, assigning chronological keys such as 2,1,3 would result in Jan-4-2013, Jan-3-2013, Jan-5-2013, which is an incorrect chronological order.

The Oracle BI Server uses the chronological key to create mathematically correct time series predictions, such as Jan + 2 months = Mar. You should set a chronological key for every level, except for the Grand Total level, so that you can perform time series operations on all levels with good performance. This enables you to use an AGO, TODATE, or PERIODROLLING function for any level of any time dimension hierarchy, such as fiscal month ago, calendar year ago, and day ago.

Theoretically, time series functions operate correctly if only the bottom level key in the Logical Dimension is chronological. In practice, however, this causes performance problems because it forces the physical query to use the lowest grain, causing joins of orders of magnitude more rows, for example, 365 times more rows for a "year ago" joining at the "day" grain.

As with any level key, be sure the key is unique at its level. For example, a column containing simple month names such as "January" isn't unique unless it's concatenated to a column containing year names.

The image shows how to designate a chronological key in the Logical Level dialog.

Description of the illustration time_dim2.gif

Create AGO, TODATE, and PERIODROLLING Measures

You can build time series measures by creating derived expressions from base measures.

To create a derived expression, you must create a new logical column and select Derived from existing columns using an expression, then open Expression Builder to build the appropriate time series function.

Follow these guidelines when modeling time series functions:

You can't derive time series functions from measures that use the fragmentation form of federation. This rule prevents some complex boundary conditions and cross-source assumptions in the query generation and result merging, such as the need to join some time dimension rows from one source to some of the fact rows in a different source. To reduce maintenance and increase accuracy, it's best to create a single base measure, and then derive a family of time series measures from it. For example, start with a base measure, then define variations for month-ago, year-ago, and month-to-date.
You must define the unit as a level of the time dimension, so that it can take advantage of the chronological keys built in the time dimension.

The following example shows how to build the AGO measure. See the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition for detailed syntax for the other time series functions, TODATE and PERIODROLLING.