Oracle Business Intelligence

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.

I’m sure there are many other ways these results could be accomplished, but this was the first thing that came to mind.

September 12th, 2008 | Category: OBIEE, SQL | Subscribe to comments | Leave a comment | Trackback URL