Model Time Series Data in a Semantic Model

This topic provides information to help you understand and use functions to model time series data in a semantic model.

Topics:

About Time Series Functions for Semantic Models

Time series functions operate on time-oriented dimensions in a semantic model. 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.

Description of the illustration timeseries.gif

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 and TODATE 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. The PERIODROLLING 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 Time Series Function

In a semantic model, you can use the AGO function to calculate the value of a measure a set number of periods ago.

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.

Description of the illustration timeseries_1.gif

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 Time Series Function

In a semantic model, you can use the TODATE function to calculate the period to date value for a measure.

This example 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 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 Time Series Function

In a semantic model, 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 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 in a Semantic Model

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

See Create the Logical Time Dimension in a Semantic Model.

About Setting Chronological Keys in a Semantic Model

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 in a Semantic Model

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.

See About Creating Logical Time Dimensions in a Semantic Model and About Setting Chronological Keys in a Semantic Model.

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 in a Semantic Model

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 Time Series Function, About the TODATE Time Series Function, and About the PERIODROLLING Time Series 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.