OBIEE - Period to period comparison (YAGO, MAGO) with the analytical function Lag/Lead

About

lag and lead are analytical functions that can be used to get the value of a column in a previous/next row. More …

Your data must be dense to have a correct result. See the paragraph with sparse data. If you don't have dense data, you can always :

use the Obiee Ago Function
or densify your data

Articles Related

Implementation

To implement this database function, we need to use the evaluate functions.

Create a presentation column in an answer (you can also create a logical column in the repository) and enter in the formula screen this code :

EVALUATE('LAG(%1,1) over (order by %2)' as double, "Sales Facts"."Amount Sold", Calendar."Calendar Month Desc")

And if we select also form the SH schema, the calendar month desc and the amout sold, you obtain :

You can remark that you have an extra column “Moving sum”. This column contain this formula :

MSUM ("Sales Facts"."Amount Sold", 2) -  "Sales Facts"."Amount Sold"

And can be use to perform a period comparison but of course only for one interval. (2000-01 to 2000-02 but not 2000-01 to 2000-03)

Log / Performance

The total time to run this query is 10 second.

Much better than the obiee ago function which perform the same statement in more than 1min36.

Why ? Because principally, Obiee send to the database only one query.

select distinct D1.c2 as c1,
     D1.c1 as c2,
     LAG(D1.c1,1) over (order by D1.c2) as c3
from 
     (select sum(T245.AMOUNT_SOLD) as c1,
               T268.CALENDAR_MONTH_DESC as c2
          from 
               SH.TIMES T268,
               SH.SALES T245
          where  ( T245.TIME_ID = T268.TIME_ID and T268.CALENDAR_YEAR = 2000 ) 
          group by T268.CALENDAR_MONTH_DESC
     ) D1
order by c1

With Sparse Data

I use the example of sparse data in this article Dimensional Data Modeling - What means Data is dense/sparse ? where we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001.

And you can see that the lag column don't give the good value for the previous period because the lag function don't care about the time scale but the relative positions of year and week.

For information, the lag formula column :

EVALUATE('LAG(%1,1) over (order by %2, %3)' as double, "Sales Facts"."Amount Sold", 
Calendar."Calendar Year", Fiscal."Fiscal Week Number")

To fill the gap, you have to take the sparse data and densify them : OBIEE - Densification / Dimensions Preservation Possibilities