OBIEE Playing with TopN part 3 the Rank Function

The TOPN function is basically the RANK function with a filter. Knowing this can help us to get around the following error message:

You can translate TOPN(<<column>>,10) to a Where RANK(<<column>>) <=10.

How BottmN(<<column>>,10)?.

Try this: COUNT(<<column>>) – RANK(<<column>>) <=9 {0 based}.

So the BottomN 10 customers become:

SELECT "D0 Time"."T05 Per Name Year" saw_0, "D1 Customer"."C0  Cust Key" saw_1, "D1 Customer"."C1  Cust Name" saw_2, "F1 Revenue"."1-01  Revenue  (Sum All)" saw_3, COUNT("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year")-RANK("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year") saw_4 FROM "Sample Sales" WHERE COUNT("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year")-RANK("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year") <= 9 ORDER BY saw_0, saw_4

Till Next Time

OBIEE Playing With TopN Part 2

In part 1 we ended with:

Let’s try to add the top2 product2 for each customer in the top 10. Start with adding the product column:

You will notice that the revenue for each customer has dropped. This means we first have to “lock” the revenue on a customer / year level:

Okay that fixed, let’s get the revenue by product:

Some how I don’t think this correct. Let’s get the details for one customer:

He has bought several products so let’s tweak this some more. We need to add the TOPN2 Filter for product by customer by year.

Just add as an extra column.

Let’s put it in a pivot to make it even more readable:

Till Next Time

OBIEE Playing With TopN Part 1

Basic question Give back the Top10 Customers:

Simple add a TopN filter to request:

Most manager won’t be satisfied with this report because there isn’t a time element present. Let’s add a year:

 

Okay what about the top10 for each year? Here is one solution:

First convert the filter to SQL:

Add a by to the TOPN Part:

TOPN("F1 Revenue"."1-01  Revenue  (Sum All)",10 by "D0 Time"."T05 Per Name Year" ) <= 10

What about showing the top 2 products for each top10 customer? I will discus that in part2.

Till Next Time

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

OBIEE Using the Top N Filter (repost)

Learn to use the Top N filter to filter results in Answers requests. Covers advanced usage of the Top N filter using the by clause, to show the Top N results within a particular grouping (i.e., Top N Customer Revenue amounts by Product):

Suppose you want to see the Top 5 results from an Answers report, such as the Top 5 overall customers by revenue:

This can easily be acheived by using a simple “in Top N” filter:

What if you want to see the Top 5 Customers by Revenue for each Product? Using a simple Top N filter won’t work in this case, as it will produce the following results:

To get OBIEE to show the correct results, you need to take the Top N filter into SQL mode. Starting with a normal Top N filter, click Advanced at the bottom of the filter window and select Convert this filter to SQL:

Next, edit the filter with the by clause enclosed in red - unfortunately you’ll have to type the column name in manually, so be sure to use quotes if the column or presentation table contains spaces. The column used here is the field you want to group by (Product, in this example):

Now, the Top N filter will give the desired results - The Top 5 Customers by Product:

The full request criteria (notice the sorting options used to produce the results above):


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

OBIEE TopN versus the rest

Getting a TopN in OBIEE is pretty simple using the rank function:

But how can we compare this to the rest?

Let’s add an extra column which gives us the rank number if it’s between 1 and 10, else 99.

case when RANK("F1 Revenue"."1-01  Revenue  (Sum All)") <= 10 then RANK("F1 Revenue"."1-01  Revenue  (Sum All)") else 99 end

Remove the original filter and check the results:

Now add the Customer name to the extra Column:

case when RANK("F1 Revenue"."1-01  Revenue  (Sum All)") <= 10 then Cast( RANK("F1 Revenue"."1-01  Revenue  (Sum All)") as char)|| ' - '||"D1 Customer"."C1  Cust Name" else '99 - The Rest' end

Check the results:

Now the big trick, switch to pivot table and arrange the columns like this:

Till Next Time