Blog - Obiee 10G – Period to period comparison with the analytical function Lag/Lead

Card Puncher Data Processing


During this thread on the Obiee forum, Goran point out a solution for a period to period comparison with the analytical function lag. It was time for me to drill down in this analytical function and to compare it with the obiee time function : ago.

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


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 and don’t care of the sparsity of the data.

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,
where ( T245.TIME_ID = T268.TIME_ID and T268.CALENDAR_YEAR = 2000 )
) 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 do a partitioned outer join with a dense set of time data in SQL. (To know more Data Densification for Reporting …) and it’s not really realizable in OBIEE with the Business Model Layer, you have to deal with the OBIEE 10G/11G - Direct Database Request

Discover More
Obiee Logical Sql Densification
Blog - Obiee 10G - Densification with the OBIEE logical Sql (Sparse to dense data)

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...
Obiee Scheduler Configuration Mail
Blog - Obiee 10G – How to configure the Mail server of Oracle BI 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 the...

Share this page:
Follow us:
Task Runner