Creating Time Series Measures & Create a Measure Using the AGO Function

In this topic you create time series calculation measures using Oracle BI time series functions.

Time series functions include AGO, TODATE, and PERIODROLLING. These functions let you use Expression Builder to call a logical function to perform time series calculations instead of creating aliases for 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.

To create time series measures, you perform the following steps: • Identify a Logical Dimension as a Time Dimension • Identify Level Keys as Chronological Keys • Create a Measure Using the AGO Function • Create a Measure Using the TODATE Function • Create a Measure Using the PERIODROLLING Function • Test Your Work

Identify a Logical Dimension as a Time Dimension

1 . Return to the Administration Tool and open the BISAMPLE repository in offline mode. 2 . In the BMM layer, double-click the H1 Time logical dimension to open the Logical Dimension dialog box.

3 . In the Structure section, select Time.

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. 4 . Click OK to close the Logical Dimension dialog box.

Identify Level Keys as Chronological Keys 1 . Expand the H1 Time logical dimension and double-click the Time Detail level to open the Logical Level dialog box. 2 . Click the Keys tab.

3 . Select the Chronological Key check box for Calendar Date.

4 . Click OK to close the Logical Level dialog box. 5 . Repeat and set chronological keys for the following levels:

It is best practice to designate a chronological key for every level of a time logical dimension.

 Create a Measure Using the AGO Function

1 . Right-click the F1 Revenue logical table and select New Object > Logical Column. 2 . On the General tab, name the column Month Ago Revenue.

3 . On the Column Source tab, select "Derived from existing columns using an expression."

4 . Open the Expression Builder. 5 . Select Functions > Time Series Functions > Ago.

6 . Double-click Ago or click Insert selected item to add the Ago function to the Expression Builder.

7 . Click <<Measure>>in the expression.

These core tutorials will help you to learn the fundamentals of OBIEE 11g. 
For an in-depth understanding and practical experience,  explore OBIEE 11g Training.

8 . Select Logical Tables > F1 Revenue and then double-click Revenue to add it to the expression.

9 . Click <<Level>> in the expression.

10 . Select Time Dimensions > H1 Time and then double-click Month to add it to the expression.

11 . Click <<Number of Periods>> and enter 1. The Ago function will calculate the Revenue value  one month before the current month.

12 . Click OK to close the Expression Builder. Check your work in the Logical Column dialog box:

13 . Click OK to close the Logical Column dialog box. The Month Ago Revenue time series measure is added to the F1 Revenue logical table.

14 . Drag the Month Ago Revenue logical column to the Base Facts presentation folder.