Model Time Series Data
This topic provides information to help you understand and use functions to model time series data.
About Time Series Functions
Time series functions operate on time-oriented dimensions. You use them to compare business performance with previous time periods, allowing you to analyze data that spans multiple time periods.
For example, time series functions enable you to compare current sales to sales from one year ago or one month ago.
Because SQL doesn't provide a direct way to make time comparisons, you must
model time series data in the semantic model. 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 Analytics query engine generates highly optimized SQL that pushes the time offset processing to
the database whenever possible, resulting in the best performance and functionality.
To use time series 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.
Use Expression Editor 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.
This example shows a sample report that includes several measures derived using time series functions.
You can use several different grains, 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
andTODATE
functions. In the above example, the time series grain is Quarter. Time series queries are valid only if the time series grain is at the query grain or higher. ThePERIODROLLING
function doesn't have a time series grain, so instead you specify a start and end period in the function. -
Storage grain - You can generate the report shown in the above 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 define a chronological key at the query grain.
Queries against time series data must exactly match to access the query cache.
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.
This example shows values for the Dollars and Dollars Qago measures.
In the above example, 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 that
you want to derive from. In this example, you 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 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.
Use this function template to 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.
This example shows a report with the measure Dollars QTD, the Quarter To Date version of the Dollars measure.
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 that you want to derive from. 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.
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 Editor, 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.
About Creating Logical Time Dimensions
Creating time dimensions requires selecting a Time hierarchy type 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 data source 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 table 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 a SELECT statement of the physical table containing the time columns to act as the distinct physical time dimension table. You must join the SELECT statement 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 any intervening tables.
-
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 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.
About Setting Chronological Keys
The chronological keys you set identify the member order within the time dimension level.
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-2022, Jan-4-2022, Jan-5-2022 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-2022, Jan-3-2022, Jan-5-2022, which is an incorrect chronological
order.
The Oracle Analytics query engine 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 hierarchy 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.
Create the Logical Time Dimension
To enable the time series functions on the dimension, select the Time hierarchy type in the logical dimension table and then designate a chronological key for every level of each dimension hierarchy.
- On the Home page, click Navigator and then click Semantic Models.
- In the Semantic Models page, click a semantic model to open it.
- In the Logical layer, double-click the logical table where you want to enable time series functions and in the logical table's tabs click Hierarchy.
- Click the Hierarchy Type field and select Time.
- Click a hierarchy level and in its details click Chronological Key and choose a key.
- For each hierarchy level that you need to set a key for, go the level's details, click the Chronological Key, and choose a key.
- Click Save.
Create AGO, TODATE, and PERIODROLLING Measures
You can build time series measures by creating derived expressions from base measures.
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.
For information about how to use time series functions in expressions, see About the AGO Function, About the TODATE Function, and About the PERIODROLLING Function.
- On the Home page, click Navigator and then click Semantic Models.
- In the Semantic Models page, click a semantic model to open it.
- In the Logical layer, double-click the logical table containing the logical column that you want to add a time series function to. Click Columns.
- Locate and click the column and click Detail View. Scroll to Sources and click Logical Expression.
- Click Open Expression Editor.
- In Expression Builder, go to the Function panel and scroll to Time Series Calculations and use these functions to build the expression.
- Click Save.