Wednesday, November 12, 2008

OBIEE Outerjoin workaround?

(Direct answer: No :-( )

Have a look at this table:

image

What I'm looking for is this:

image

But I don't want to use outerjoins, since obiee messes them up anyway when you try do filtering later on.

Here is a small Q&D trick

Add an extra fact column and multiply it with a random number:

image

Next hide the column:

image

What is happening?

If we look in the log we see that OBIEE isn't executing the RAND() function on the Database side but internally. For that it first gets only the dimension columns from the database:

select T26.D_YEAR as c2,
T26.D_YEAR_MONTH as c3
from
DIM_YEAR_MONTH_DAY T26
where ( T26.D_YEAR = '2013' )

Next it will get the facts:

select D2.c2 as c1,
D2.c3 as c2,
D2.c1 as c3
from
(select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select sum(T31.F_FACT_VAL) as c1,
T26.D_YEAR as c2,
T26.D_YEAR_MONTH as c3,
ROW_NUMBER() OVER (PARTITION BY T26.D_YEAR_MONTH ORDER BY T26.D_YEAR_MONTH ASC) as c4
from
DIM_YEAR_MONTH_DAY T26,
F_FACTS T31
where ( T26.D_YEAR = '2013' and T26.D_YEAR_MONTH_DAY = T31.D_DATE )
group by T26.D_YEAR, T26.D_YEAR_MONTH
) D1
where ( D1.c4 = 1 )
) D2
order by c2

After that it stitches the query's together.

Till Next Time

5 comments:

Srikanth Karedla said...

Hi,

If i add filters, the result is still changing.

Thanks,
Srikanth

Luz said...

Hi, if I add another column this doesnt work, how can I make this or left outer join for two columns. Thank you very much for your help

John Minkjan said...

You probalbly have to add an "or is null" to you filter

regards

Anonymous said...

This solution does not work !

Parameshwaran Venkataraman said...

How to control BI server generating 'where ( D1.c4 = 1 )' within RPD.
Any ideas?