working on applications and technology

There is one scenario that will be talked about eventually no matter what kind of BI tools you might use.

It is the value comparison of the time dimension. Which we call the 'Time Series Data' in OBIEE,

Unlike essbase where we could use calculation script to control every cell value inside the cube, OBIEE comes with 'Ready to use' functions that will help us do this kind of calculation.

Example Scenario

Suppose that we have a fact table that store all the sales data for year 2008 and 2009, we want to calculate the 'sales change' and 'change percent' for these two years.

It is easy to do this calculation inside Oracle database using PL/SQL in the following steps:

1. Select sum(sales) into :var_2008_sales from sales_fact where sales_year=2008;
2. Select sum(sales) into :var_2009_sales from sales_fact where sales_year=2009;
3. Sales Change = :var_2009_sales - :var_2008_sales
4. Change percent = (:var_2009_sales - :var_2008_sales ) * 100/ :var_2008_sales

I don't know if OBIEE is using a similiar approach for time comparison calculation and it is not our business to find out cause all we concern about is that we could do time comparison calculation by applying the function 'AGO' to the measures.

AGO function in OBIEE

The AGO measure in OBIEE use the following format:

AGO(<measure>, <time level unit> , <number to shift>)

Parameter explaination:

<measure> - the measure we want to use for calculation, in our example, 'Sales' is the measure we will use
<time level unit> - the dimension level of the time dimension, It should be an existing dimension level, It might be 'year', 'quarter','month' or other level defined in the time dimension.
<number to shift> - the unit you want to shift along the time level, e.g., one month , three year etc.

If you 're familiar with Essbase , you might have the feeling that you have read this before , Yes, the AGO function in OBIEE is similiar with the Essbase function 'PRIOR' . So if you 're using essbase as the OBIEE data source, you could have to choose which function you want to use to do the time comparison : use the 'PRIOR' function inside Essbase? or use AGO function inside OBIEE .

Time comparison implementation in OBIEE

Let's say that the measure 'Sales' is the sales measure from the normal fact table . we will have to define addtional measure for time comparison purpose:

Define the '2008Sales' logical column in the Sales fact table using 'AGO' function

Enter the AGO function expression in the Expression builder

Now we could see both the 'Sales' measure and '2008Sales' columns in BI Answers, Just drag these columns and the 'Sales change' value will appear in no time!

Using this technique, we could not only compare the 'yearly' change of the sales data, we could also calculate 'monthly change' , 'daily change' or other 'change'  of time dimension level we want to use .