Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 3 – Negative Modeling

Now that i have blogged about the solution for Puzzles 1 and 2, its time for the next puzzle. This one requires you to actually build a very simple repository and show me how you can do negative modeling i.e a model that will produce the longest single SQL(basically a pretty big SQL), all on a single table. To expand further, lets assume that you have a single table in the physical database called CHANNELS(with 3 attributes). Now, you need to build a repository that will basically expose 2 columns in the presentation layer. When we create a report using these 2 columns, the SQL produced should be extremely long. I know this is not something that you would encounter anywhere but this will basically help you understand what can produce large SQLs in the database. There are 3 pre-conditions to the puzzle

1. No physical aliases can be used. Only one physical table(No select SQL) should exist in the physical layer. Else this will become very easy to solve.
2. Your entire SQL should be driven out of your Business Model in the repository.
3. Only 2 columns can be exposed in the presentation layer (i can be a bit flexible here. If you need to expose more columns, feel free to expose them if you think that will make the SQL to be long enough but ideally i would like only 2 to be exposed)

There is no business case/requirement that the RPD has to solve. All it has to do is to produce the longest SQL that you think is possible. Of course again, there are multiple possible solutions but all i need is a generic idea of how you would be implementing this.

A sample SQL (which is not that long but longer than the normal simple select) is given below of a simple report containing 2 columns

WITH
SAWITH0 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from
     (select T1776.CHANNEL_CLASS as c1,
               T1776.CHANNEL_CLASS_ID as c2,
               T1776.CHANNEL_TOTAL_ID as c3,
               ROW_NUMBER() OVER (PARTITION BY T1776.CHANNEL_CLASS_ID ORDER BY T1776.CHANNEL_CLASS_ID ASC) as c4
          from
               CHANNELS T1776
     ) D1
where  ( D1.c4 = 1 ) ),
SAWITH1 AS (select sum(1) as c1,
     T1776.CHANNEL_TOTAL_ID as c2
from
     CHANNELS T1776
group by T1776.CHANNEL_TOTAL_ID),
SAWITH2 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4
from
     (select SAWITH0.c1 as c1,
               SAWITH1.c1 as c2,
               case  when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c3 is not null then SAWITH0.c3 end  as c3,
               SAWITH0.c2 as c4,
               ROW_NUMBER() OVER (PARTITION BY SAWITH0.c1, SAWITH0.c2,
               case  when SAWITH1.c2 is not null then SAWITH1.c2
               when SAWITH0.c3 is not null then SAWITH0.c3 end
               ORDER BY SAWITH0.c1 ASC, SAWITH0.c2 ASC,
               case  when SAWITH1.c2 is not null
               then SAWITH1.c2 when SAWITH0.c3 is not null
               then SAWITH0.c3 end  ASC) as c5
          from
               SAWITH0 full outer join SAWITH1 On SAWITH0.c3 = SAWITH1.c2
     ) D1
where  ( D1.c5 = 1 ) )
select SAWITH2.c1 as c1,
     SAWITH2.c2 as c2,
     SAWITH2.c3 as c3,
     SAWITH2.c4 as c4
from
     SAWITH2
order by c1

Puzzle 4 to follow next which again will be in the lines of negative modeling.