Lab 9: Creating Time Series Measures
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 .
|
Click Insert selected item to add the Ago function to the Expression Builder.
 |
7 .
|
Click <<Measure>>in the expression.
 |
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.
 |
Create a Measure Using the TODATE Function
1 .
|
Right-click the F1 Revenue logical table and select New Object > Logical Column.
|
2 .
|
On the General tab, name the new logical column Year To Date 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 and double-click ToDate to insert the expression.
 |
6 .
|
Click <<Measure>> in the expression.
 |
7 .
|
Select Logical Tables > F1 Revenue and then double-click Revenue to add it to the expression.
 |
8 .
|
Click <<Level>> in the expression.
 |
9 .
|
Select Time Dimensions > H1 Time and then double-click Year to add it to the expression.
 |
10 .
|
Click OK to close the Expression Builder.
|
11 .
|
Check your work in the Logical Column dialog box:
 |
12 .
|
Click OK to close the Logical Column dialog box.
|
13 .
|
Drag the Year To Date Revenue logical column to the Base Facts presentation folder.
 |
Create a Measure Using the PERIODROLLING Function
1 .
|
Right-click the F1 Revenue logical table and select New Object > Logical Column.
|
2 .
|
On the General tab, name the new logical column Revenue 3-Period Rolling Sum.
 |
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 and double-click PeriodRolling to insert the expression.
 |
6 .
|
Click <<Measure>> in the expression.
 |
7 .
|
Select Logical Tables > F1 Revenue and then double-click Revenue to add it to the expression.
 |
8 .
|
Click <<Starting Period Offset>> in the expression.
 |
9 .
|
Enter -2. This identifies the first period in the rolling aggregation.
 |
10 .
|
Click <<Ending Period Offset>>.
 |
11 .
|
Enter 0. This identifies the last period in the rolling aggregation.
These integers are the relative number of periods from a displayed period. In this example, if the query grain is month, the 3 month rolling sum starts two months in the past (-2) and includes the current month (0).
|
12 .
|
Click OK to close the Expression Builder.
|
13 .
|
Check your work in the Logical Column dialog box:
 |
14 .
|
Click OK to close the Logical Column dialog box.
|
15 .
|
Drag the Revenue 3-Period Rolling Sum logical column to the Base Facts presentation folder.
 |
16 .
|
Save the repository and check consistency. Fix any errors or warnings before you proceed.
|
17 .
|
Close the repository. Leave the Administration Tool open.
|
Test Your Work
1 .
|
Return to Fusion Middleware Control Enterprise Manager and load the BISAMPLE repository. If you need help, click here to review steps from earlier in this tutorial.
|
2 .
|
Return to Oracle BI and sign in.
|
3 .
|
Create the following analysis to test AGO and TODATE functions:
Time.Per Name Month
Time.Per Name Year Base Facts.Revenue Base Facts.Month Ago Revenue Base Facts.Year to Date Revenue  |
4 .
|
Set the following filter for the analysis:
Per Name Year is equal to / is in 2008.
 |
5 .
|
For the Per Name Year column, select Column Properties > Column Format > Hide. This will prevent Per Name Year from displaying in the analysis results.
 |
6 .
|
Sort Per Name Month in ascending order.
 |
7 .
|
Click Results.
Month Ago Revenue displays revenue from the previous month. Year To Date Revenue calculates a running sum of revenue for the year on a monthly basis.
|
8 .
|
Create the following new analysis and filter to test the PERIODROLLING function at the month grain:
Time.Per Name Month
Time.Per Name Year Base Facts.Revenue Base Facts.Revenue 3-Period Rolling Sum
Per Name Year is equal to / is in 2008
 |
9 .
|
For the Per Name Year column, select Column Properties > Column Format > Hide. This will prevent Per Name Year from displaying in the analysis results.
 |
10 .
|
Sort Per Name Month in ascending order.
 |
11 .
|
Click Results.
Revenue 3-Period Rolling Sum is calculated based on the month grain.
|
12 .
|
Create the following new analysis and filter to test the PERIODROLLING function at the year grain:
Time.Per Name Year
Base Facts.Revenue Base Facts.Revenue 3-Period Rolling Sum  |
13 .
|
Sort Per Name Year in ascending order.
 |
14 .
|
Click Results.
Revenue 3-Period Rolling Sum is calculated based on the year grain. A measure with the PERIODROLLING function calculates results based on the query grain.
|
Summary
In this tutorial you learned how to build an Oracle BI metadata repository using the Oracle BI Administration Tool. You learned how to import metadata from a data source, simplify and reorganize the imported metadata into a business model, and then structure the business model for presentation to users who request business intelligence information via Oracle BI user interfaces.
In this tutorial, you have learned how to:
- Build the three layers of a Oracle BI repository
- Test and validate an Oracle BI repository
- Manage logical table sources
- Create simple measures and calculation measures
- Create logical dimensions with level-based hierarchies
- Create level-based measures
- Create logical dimensions with parent-child hierarchies
- Create logical dimensions with ragged and skipped-level hierarchies
- Use aggregates to improve query performance
- Use initialization blocks and variables
- Create time series measures
Resources
Credits
- Lead Curriculum Developer: Jim Sarokin
User Comments
Title: 
Post as havuphuong1987@yahoo.com 
Post anonymously
This feedback form is for tutorial corrections and suggestions. Because of the volume of suggestions, we cannot reply to every comment. In particular:
- If you are encountering a software problem and need to request support or consulting, file a service request on My Oracle Support.
(Comments are moderated and will not appear immediately.)
 |
