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.
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.
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 theAGOandTODATEfunctions. 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. ThePERIODROLLINGfunction does not 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.
Note:
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.
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 do not 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 AGO in the 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," which is the Quarter To Date version of the "Dollars" measure.
In the example shown in the image, Dollars QTD for Month 2008/05 is the sum of Dollars for 2008/04 and 2008/05. In other words, 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 shown in the image, the Dollars QTD measure is derived from the Dollars measure.
In Expression Builder, the TODATE function has the following template:
ToDate(<<Measure>>, <<Level>>)
<<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 time series grain you want to use. In this example, you would select "Quarter" from your time dimension.
Using this function template, you can create the following expression for the measure:
ToDate("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" )
Note that 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, but still accumulates up to the end of the Quarter.
See TODATE in the 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 does not 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 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
Do not 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 PERIODROLLING in the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.
Creating Logical Time Dimensions
Creating time dimensions requires selecting a Time option and designating a chronological key for every level of every dimension hierarchy.
Follow these additional guidelines when modeling time series data:
-
It only makes sense to use a time series function when the data source contains history. Usually, a relational database that contains history uses a star or snowflake schema with an explicit time dimension table. A normalized, historical database is much rarer, but would still include a time hierarchy with levels in a schema similar to a snowflake. A simple date field is not adequate.
-
Oracle Business Intelligence requires the time dimension physical table (or set of normalized tables) to be separate from the physical fact table to which it is related.
However, a somewhat common source schema pattern is a fully denormalized relational table or flat file, where the time dimension columns are in the same table as the facts and other dimensions. This cannot qualify as a time dimension, because the time dimension table is combined with the fact table. In this case, if you cannot change the model in the source, the best practice is to create an Opaque View of the physical table containing the time columns, which acts as the distinct physical time dimension table. This Opaque View time dimension must then be joined to the physical table that contains the facts.
-
In the Physical layer, the time dimension table or lowest-level table in the normalized/snowflake case must join directly to the fact table without intervening tables. This join must be a foreign key join.
-
The tables in the physical model containing the time dimension cannot join to other data sources, except at the most detailed level.
-
A member value, for example, a row in relational sources, must be physically present for every period at every hierarchy level. There cannot be any skips in the sequence. Note that it does not matter whether there is fact data for every period; only the dimension data must be complete.
-
Each unit of distance between members, such as "month," "half," or "year," must be modeled in a separate hierarchy level.
Selecting 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.
Setting 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" is not unique unless it is concatenated to a column containing year names.
The image shows how to designate a chronological key in the Logical Level dialog.
Creating 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 cannot derived 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 is 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, month-to-date, and so on. To do this, select Derived from existing columns using an expression and refer to the base measure in the expression.
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.
Creating the AGO Measure
This example show to create the derived AGO measures in the Sampleapp demonstration repository.