Time Series Wizard in OBIEE

Time Series Wizard (Time Series Measure)

Oracle BI Server provides Ago and ToDate functions for time series comparisons.

There is no direst way in SQL to make time comparison so Oracle BI Server has provided these two functions for time series comparison. Time series measures ( Ago & ToDate ) are the best way to compare sales between current year and previous year and sales till date.

Both these functions operate on measures.

Ago Function: Calculates aggregated value as of some time period shifted from the current time.

Example : Ago can produce sales for every month of the current quarter and the corresponding quarter.

ToDate Function:

The ToDate function is used to aggregate a measure attribute from the beginning of a specified time period to the currently displaying time. For example, the ToDate function can calculate Year to Date sales for a given year.

Steps to Model Time Series Data

1. Identify time dimension and chronological keys

2 Create the Ago Measure

Create a new logical column and then use the expression builder to build Ago function.

1. 

Now In this Example, Amount positive is the measure , Month is time level and 1 is the time period.After placing all the values and it will look like this,

Here we can see that in measure we have selected amount positive , next we are going to select month as a time dimension. And in the number of periods we will put 1.

Screen will look like this,

3.Use existing columns to Create Additional Ago Measure

Use existing columns to Create Additional Ago Measure ,such as Change Ago, Percent Change Ago.

In this example Ago Month measure is used to calculate the change in Amount between the current month and the previous month .

4. Create ToDate Measure

Create a new logical column and then use the expression builder to build the ToDate function.

In this Example Amount positive is the measure and year is the time level.

5. Add New Measure to the Presentation Layer

Add new time series  meausues to the Presentation layer so that users can include them in query criteria.

6. Test the results in Answers.

Run a request and test all the measures we have created using time series measures.

Some Limitations 

• You may only enter Ago and ToDate functions in the Expression Builder Dialog Box in the Administration Tool.
• You can not use them in  coded SQL.
• These two functions are relational Data Source only.