Oracle BI EE 11g - Filters & Selection Steps - Top N Products Case Study

One of the OLAP-Aware features of BI EE 11g in Answers is the introduction of Selection Steps for additional analysis. At a high level, Selection Steps provide additional analysis capabilities that are not available directly from the Criteria tab. This provides us with more control on the SQL generated.

Till 10g, to achieve a complex report, one had to rely on some complex formulas and understanding how BI Server will push those formulas in SQL. We did not have a control on whether we wanted a filter or a calculation to be done as part of the query itself or outside of the query. In 11g, we now have that ability through selection steps. At a high level, the diagram below shows how Selection Steps work

Lets try to understand this through an example. One of the common requirements while doing a retail product analysis is to find out the Top 10 products that have been sold in a day. The requirement is to show the Top 10 performing products and also group every other product which is not in the top 10 into a common category called Others. A very common requirement as shown in the screenshot below

This seems to be a very simple example as all we want is to group certain products based on their Rank. We cannot use TopN function for this as TopN applies a filter and hence cannot group the remaining records. So we start of, like in 10g, with using a simple case statement as shown below

CASE WHEN RANK("Sales"."AMOUNT_SOLD") < 11 THEN "Products"."PROD_NAME" ELSE 'Others' END

Lets try to use this in the report and see what happens.

As you see this has not worked for us as BI EE does not know that the Rank has to be done first and then CASE statement has to be applied. You will basically see the same behavior in 10g as well. So, how do we go about solving this requirement without making any change to the repository. This is where Selection Steps can come in very handy.

So we start off with including Product Name, Rank and the Sales column in the report as shown below

As a next step, we include the same case statement that was used above. This time it will work as we are at the Product grain.

Then we need to basically sum up the sales of all products that fall under the Others category. This is achieved by modifying the Measure to use the formula as shown below

SUM("Sales"."AMOUNT_SOLD" BY CASE WHEN RANK("Sales"."AMOUNT_SOLD") < 11 THEN "Products"."PROD_NAME" ELSE 'Others' END)

Till this step we can do the same thing in 10g as well. But if you look at the report, we now have to show only the first 11 rows as that is what is required by the end users. So what we want is a capability to apply a filter on top of what we have created in the report. To do that we start with all member in the selection step and then create a new Selection Step condition as shown below

In the condition specify a filter of the Rank column to be less than 12 so that only the first 11 records are chosen.

Now hide the first product column. This will provide us with the desired output.

If you look at the query generated, you will notice that the Selection Step filter is applied on top of the reporting query.

WITH
SAWITH0 AS (select sum(T44322.AMOUNT_SOLD) as c1,
     T44287.PROD_NAME as c2
from
     SH.PRODUCTS T44287,
     SH.SALES T44322
where  ( T44287.PROD_ID = T44322.PROD_ID )
group by T44287.PROD_NAME),
SAWITH1 AS (select 0 as c1,
     D1.c2 as c2,
     case  when Case when D1.c1 is not null then Rank() OVER
( ORDER BY D1.c1 DESC NULLS LAST ) end < 11
then D1.c2 else 'Others' end  as c3,
     Case when D1.c1 is not null then Rank()
OVER ( ORDER BY D1.c1 DESC NULLS LAST ) end as c4,
     D1.c1 as c6
from
     SAWITH0 D1),
SAWITH2 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     sum(D1.c6) as c5
from
     SAWITH1 D1
group by D1.c1, D1.c2, D1.c3, D1.c4),
SAWITH3 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c6,
     ROW_NUMBER() OVER (PARTITION BY D1.c3, D1.c2
ORDER BY D1.c3 DESC, D1.c2 DESC) as c7
from
     SAWITH2 D1),
SAWITH4 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     sum(case D1.c7 when 1 then D1.c6 else NULL end )
over (partition by D1.c3)  as c5
from
     SAWITH3 D1)
select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from
     SAWITH4 D1
where  ( D1.c2 in ('17" LCD w/built-in HDTV Tuner',
'18" Flat Panel Graphics Monitor', '5MP Telephoto Digital Camera',
'8.3 Minitower Speaker', 'Envoy 256MB - 40GB', 'Envoy Ambassador',
'Home Theatre Package with DVD-Audio/Video Play',
'Mini DV Camcorder with 3.5" Swivel LCD',
'SIMM- 16MB PCMCIAII card',
'SIMM- 8MB PCMCIAII card', 'Unix/Windows 1-user pack') )
order by c1, c4 NULLS FIRST, c2

Selection Steps provide more flexibility and at the same time providing opportunities to create more complex reports.