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.

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.