OBIEE -Time Series in OBIEE

Time Series in obiee

Time series functions present the capability to evaluate and comparing business performance by current period of time to previous time periods, allowing you to analyze data for multiple time periods.

Comparison between 10g and 11g

·         Only two series functions like AGO, TO DATE

·         In answer side we don’t have the option to implement time series functions

·         Only in RPD level have the function of time series in 10g

·         Three time series functions like AGO, TO DATE, PERIOD ROLLING

·         Here in answer side we have the function to implement time series functions

·         Both RPD and Answer side have the functions of time series in 11g

Procedures where same for AGO and TODATE function in 10g as well as 11g.

1. Ago:

Ago time series function defines the sale or revenue of business statistics according to the period over months or year. It calculates the last year amount on the same date of present year.

Syntax:

Ago (<<Measure>>, <<Level>>, <<Number of Periods>>)

·         Measure: A measure column which you to make use in this function

·         Level: On which level you want to calculate this measure. This level is selected from the Time Dimension hierarchy

·         Number of Period: A numerical value which will go for how many levels you want to go before from current time.

Description:

Ago is the function to calculate the revenue over the period, it requires the measure column, level of hierarchy, and how many periods of revenue you need.

Measure should be summed up sum function in the logical column.

Hierarchy is used here in ago function are time dimension hierarchy only.

For example: time dimension hierarchy created like

Year Quarter Month  Day

So according to the year if we need the sales or revenue means just assign the ago function like this Ago (“Revenue”, “year”, 1) this denotes the last year revenue value.

For Example Look Below screen shots:

Look the vales for the year 2005

Look the values for the year 2006

Using ago function in logical columns, we have to show the fact value for 2005 in the year of 2006 with new column.

You can use this level of hierarchy as well as year also. Here I am going to calculate for the sale before 12 months from the present month.

In the levels tab you must have the select the any column key as “Chronological Key “.

Compile and save the rpd. Login to answer side and clear cache and manage session and reload the server metadata.

And see the report. We can see the 2005 year values in 2006 year according to the month.

The same thing I'm doing in OBIEE 11g RPD side (Admin):

OBIEE 11g Answer side:

2.TO DATE:

TODATE function is used to calculate the revenue for 2 months or 3 months from the beginning time of the year. A time series aggregation function that aggregates a measure attribute from the beginning of a specified time period to the current time. For example, this function can calculate Year to Date sales.

If we need to see the revenue up to now (Current Month) @ Year. For example we are in the month of august means it calculates the revenue from starting of the year January to august.

Syntax:

ToDate (<Measure>, <Level>)

·    

        Measure: A measure column which you to make use in this function

·    

     Level: On which level you want to calculate this measure. This level is selected from the Time Dimension hierarchy

Description:

ToDate ("SH"."SALES"."AMOUNT_SOLD”, "SH"."Time Dimension"."Year”)

    With the above example 'Amount Sold' Column is the measure and 'Year' is the level which will calculate the measure from the beginning of the year to a specified time.

Example: Create a logical column and use the ToDate function to calculate the revenue according to the month wise.

Just look and compare in answer side with actual value and To date calculated value.

the above picture you can see 2 columns one is Actual sum and actual _YTD.

For January the Actual sum is -1162298.2 and actual_YTD is-1162298.2

For February the Actual sum is -1325146.4 and actual_YTD is calculated by Jan+Feb = -3572256.2 and 

same for all the other months. So it calculated by month + month.

Period rolling a new feature present in OBIEE 11g:

Period rolling is the time series function which is used to calculate the revenue over the period for months and year from starting year or month and ending period or month. Interval of calculation of the measure.

This function allows us to create a aggregated measure across a specified set of query grain period, rather than within a fixed time series grain. The common use of this function is to create a Rolling Average such '10-Week Rolling Average'

Syntax:

Period Rolling (<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)

     Measure: represents the logical measure column from which you want to derive

    Starting Period Offset: identify the first period used in the rolling aggregation

    Ending Period Offset: Identify the last period used in the rolling aggregation

     For e.g.: Period Rolling ( "SH"."SALES"."AMOUNT_SOLD", -2, 0)

    With the above example I'm creating three months rolling sum of column 'Amount Sold. It includes past two months with the current month. The numerical value '-2' in the offset indicates the month Jan-12, Feb-12 if our current month is 'Mar-12'.The numerical value '0' in the offset indicates the Current Month

Note:

Period Rolling( ) function which will not the calculate the Average sum of three months for the measure. Average (AVEG ( ) function in OBIEE) computes the average of the database rows accessed at the storage grain. So If you want to calculate the 3-Month sales average for the above explained Period Rolling 

example 

we have to write the syntax like below:

3-Month Sales Average =

(Period Rolling (“SH"."SALES"."AMOUNT_SOLD", -2, 0))/3

Example:

We have the measure columns Actual _sum, using this column I used period rolling for 2 months before from the current month. So it should calculate the current month by summing of previous 2 months. For reference I created the period rolling function in answer side and calculated see the below pictures.

Period rolling (“ADSS”.”Actual_sum”,-2, 0)

See in the result tab:

Check here, the period rolling values should add the 2 months values + current month.

January – Actual sum -1162298.2      period rolling -1162298.2

February – Actual sum -1084811.6    period rolling (Added Jan+Feb)=-2247109.8

March – Actual sum -1325146.4        period rolling (summed Jan+Feb+March)=-3572256.2

April – Actual sum -1203334.7          Period rolling (Summed Feb+March+April)=-3613292.