Monday, September 28, 2009

OBIEE When the Top N Filter fails… (repost)

If you’ve read my previous post about Using the Top N Filter, this will be a follow up post that covers a trick I discovered when the Top N filter didn’t do what I wanted it to do.
My requirement is to only show data for the past 6 weeks.  I have a base table that has massive amounts of weekly data, designated by a column week_ending.  At first, I thought I could simply add a Top N filter for "Week Ending" in Top 6.  This didn’t work however, since I have multiple rows containing the same date - it only returned data for the latest date in the table (which makes sense, because that date would be the same for each of the Top 6 since it occurs many times).  I also tried using the DISTINCT keyword in various places in the filter formula, as well as in the column definition - still no luck.
What I ended up doing was the following:
  1. I decided to turn to SQL to give me the results I need.  I started by building a query that gave me the Top 6 dates in my base table:
    SELECT * FROM (SELECT DISTINCT week_ending
    FROM base_table
    ORDER BY week_ending DESC)
    WHERE rownum <= 6;
    
    WEEK_ENDING
    ------------------------- 
    13-JUL-08
    06-JUL-08
    29-JUN-08
    22-JUN-08
    15-JUN-08
    08-JUN-08                 
    
    6 rows selected





  2. Next, I used the query above in the WHERE clause for a view:

    CREATE OR REPLACE VIEW top_six_weeks_vw AS
    SELECT *
    FROM base_table bt
    WHERE bt.week_ending IN (SELECT * FROM (SELECT DISTINCT week_ending                                          FROM base_table
    ORDER BY week_ending DESC)
    WHERE rownum <= 6);
    
    





  3. Now, simply model this view in the OBIEE Repository, and your Answers reports will only display the latest 6 weeks worth of data.


This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html









Till Next Time

No comments: