I got one pretty-interesting question the other day with regard to rolling filters and inter-row calculations. Lets understand the question first with a simple example. Lets consider a simple table containing Date, and the daily temperatures as shown below.
As you see, the above table records the daily temperatures. Now, the question is to create a report on this table in BI EE such that the report gives only those days and their corresponding temperatures where the temperatures are greater than 50 and also for 3 consecutive days. So, basically in the above table the report should have the following list of days and its corresponding temperatures.
In order to do this, there are 2 approaches. One is to create a view in the database layer and the other approach is to create a database function and then call that fuction from Answers using EVALUATE. Lets look at both the approaches. In the first approach, lets create a view with the following script.
CREATE VIEW DAILY_TEMP_VW AS select DAILY_DATE, TEMPERATURE,
CASE WHEN
(TEMPERATURE >= 50 AND LEAD(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50 AND LEAD(TEMPERATURE,2,0) over (order by DAILY_DATE) >= 50)
OR ( TEMPERATURE >= 50 AND LAG(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50 AND LEAD(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50)
OR (TEMPERATURE >= 50 AND LAG(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50 AND LAG(TEMPERATURE,2,0) over (order by DAILY_DATE) >= 50) THEN 1 ELSE 0 END AS CASE_STAT
FROM DAILY_TEMP
So, what this basically does is, it creates another column in the same table. This column will have a value of 1 if its corresponding temperature is greater than 50 and is part of 3 consecutive days. If you do a select * from this view you will be getting the below result
So, the next step is to import this table into the repository and then design the BM and Presentation Layer accordingly. Now, when you create the report in BI EE, add a filter of CASE_STAT = 1 which will give you all days with temperature > 50 for 3 consecutive days.
Now lets look at another approach where in, instead of using the above view, lets create a database function which would accept the daily date as an input.
CREATE OR REPLACE FUNCTION FUN_CASESTAT(P_DATE DATE) RETURN NUMBER IS
V_casestat number;
beginSELECT case_stat into v_casestat from (
select daily_date, temperature,
CASE WHEN
(temperature >= 50 and lead(temperature,1,0) over (order by daily_date) >= 50 and lead(temperature,2,0) over (order by daily_date) >= 50)
OR ( temperature >= 50 and lag(temperature,1,0) over (order by daily_date) >= 50 and lead(temperature,1,0) over (order by daily_date) >= 50)
OR (temperature >= 50 and lag(temperature,1,0) over (order by daily_date) >= 50 and lag(temperature,2,0) over (order by daily_date) >= 50) THEN 1 ELSE 0 END AS CASE_STAT
from daily_temp) where daily_date = P_DATE;RETURN v_casestat;END;
Now, call this function from Answers using EVALUATE.
EVALUATE(‘FUN_CASESTAT(%1)’,CAST(DAILY_TEMP_VW.DAILY_DATE AS CHAR))
Now, if you notice this report would produce the same result as the view that we created in the approach one. Now, just create a filter on the 3rd column to restrict all rows where this column = 1. This will produce the desired result.
I know, both the above methods involve changes in the backend database. Feel free to share any other ideas that you might have to solve this in the comments section.
Posts
