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

Obiee Ceim


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 :


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 :

Obiee Period To Period Comparison Lag

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.

Obiee Lag Log

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
     (select sum(T245.AMOUNT_SOLD) as c1,
               T268.CALENDAR_MONTH_DESC as c2
               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.

Obiee Lag With Sparse Data

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

Discover More
Saw Object
OBIEE - BI Presentation Service (SAW/OBIPS)

AnswersInteractive DashboardsDelivers The Oracle BI Presentation Service Server: is a pure Web Environment visualize data from the Oracle BI Server through the creation of Interactive Dashboards...
Obiee Logical Sql Densification
OBIEE - Densification with logical Sql

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. And it's also the most problem that you have when you work...
Obiee Scheduler Configuration Mail
OBIEE 10G/11G - How to configure the Mail server of Scheduler/Delivers

This article talk the configuration of the smtp mail server of Oracle BI Scheduler. To set up the smtp server, you must enter at least this information as : the sender address the smtp server ...
Obiee 11g Aggregate At
OBIEE 10G/11G - Period to period comparison with the AGO Function

Ago is a time function that calculates the aggregated value from the current time back to a specified time period. This function is useful for comparisons, such as Dollars compared to Dollars a Quarter...

Share this page:
Follow us:
Task Runner