Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 2

I received some interesting replies through email as well as in the blog comments for the last Puzzle. I will blog about the solution for that and the solution for the 2nd puzzle together later this week. So far 3 people have answered and all are right which i will explain in my solution blog. Thanks to everyone who participated so far (Gerard Nico, Christian & Harish Gopalan). It is still open and you can still post your solution, if you haven’t already, until i blog the solutions later this week. On to the second Puzzle of the week today.

This is a very common but an interesting scenario which sometimes can act as the key in determining the performance of BI EE especially when you have a big repository with a lot of users accessing it simultaneously. As you all know BI EE avoids fan traps through the concept of conforming dimensions. Since conforming dimensions are used quite frequently, it is imperative that BI EE generates the right performing SQL or rather we model the repository in such a way that BI EE generates the correct SQL queries. Today’s puzzle is

“How do we push the conforming dimension join to the database and how do we push the conforming dimension join to the BI Server(how do we control this behavior)?”

For example, if you consider the repository below, it is a very simple model with Channels Dimension joining to 2 fact tables COSTS and SALES.

Now when someone creates a report containing both the measures and a dimension attribute, we want the conforming dimension join to be pushed into the database SQL itself as shown below

WITH
SAWITH0 AS (select sum(T1929.QUANTITY_SOLD) as c1,
     T1776.CHANNEL_DESC as c2
from
     CHANNELS T1776,
     SALES T1929
where  ( T1776.CHANNEL_ID = T1929.CHANNEL_ID )
group by T1776.CHANNEL_DESC),
SAWITH1 AS (select sum(T1784.UNIT_COST) as c1,
     T1776.CHANNEL_DESC as c2
from
     CHANNELS T1776,
     COSTS T1784
where  ( T1776.CHANNEL_ID = T1784.CHANNEL_ID )
group by T1776.CHANNEL_DESC)
select distinct case  when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c2 is not null then SAWITH0.c2 end  as c1,
     SAWITH0.c1 as c2,
     SAWITH1.c1 as c3
from
     SAWITH0 full outer join SAWITH1 On SAWITH0.c2 = SAWITH1.c2
order by c1

As you see in the above case, the query was completely fired back to the database. You can confirm that this is a conforming dimension join by looking at the full outer join clause and the entire SQL. Remember, it is not necessary in your case to generate the exact same SQL listed above. It is enough if you can demonstrate how the entire query can be pushed as a single query to the database i.e a modified form of the above SQL is acceptable as well.

The second part of the puzzle is how do we model the same repository above to achieve the conforming dimension join to happen in the BI Server memory as shown below

You can see that the conforming dimension join in this case happens in the BI Server layer. BI Server basically fires 2 queries separately and stitches them together in its own memory. In what ways can we control this behavior? There are more than one solution for this but again this is a little bit trickier than the Puzzle 1 i gave yesterday. Another important point to note is, in both the above cases, the output of the report should not change i.e both database based join and BI Server based join should produce the same results.

Some more interesting puzzles lined up for next week. Stay Tuned!!!

in