Obiee – Period to period comparison with the analytical function Lag/Lead

During [[http://forums.oracle.com/forums/message.jspa?messageID=3403773#3403773|this thread]] on the Obiee forum, [[http://108obiee.blogspot.com/|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.

[[analytic:analytic_functions:analytic_function_lag|lag and lead]] are analytical functions that can be used to get the value of a column in a previous/next row. [[analytic:analytic_functions:analytic_function_lag|More ...]]

**Attention** : Your data must be [[analytic:dense_sparse|dense]] to have a correct result. See the paragraph [[#with_sparse_data|with sparse data]]. If you don’t have [[analytic:dense_sparse|dense]] data, you can always use the [[dat:obiee:presentation_service:obiee_period_to_period_ago_todate|Obiee Ago Function]]

===== Implementation =====
To implement this database function, we need to use the [[dat:obiee:bi_server:design:fact_table:obiee_embedded_database_function|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 :

{{:dat:obiee:presentation_service:obiee_period_to_period_comparison_lag.jpg|}}

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.

{{:dat:obiee:presentation_service:obiee_lag_log.jpg|}}

Much better than the [[dat:obiee:presentation_service:obiee_period_to_period_ago_todate|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 [[#with_sparse_data|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
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 [[analytic:dense_sparse]] where we would expect 22 rows of data (11 weeks each from 2 years) if the data were [[analytic:dense_sparse|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.

{{:dat:obiee:presentation_service:obiee_lag_with_sparse_data.jpg|}}

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 [[http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1014934|Data Densification for Reporting ...]]) and it’s not really realizable in OBIEE with the Business Model Layer, you have to deal with the [[dat:obiee:presentation_service:obiee_direct_database_request]]

 

View the original article here