Obiee Bmm Layer Left Outer Join Problem

dilek

Hello everybody,

I have a problem with left outer joins. here is generated physical sql code on obiee; when I put a filter on my analysis from the right side table (e_donem as below), no result is returned. But the centered table (bidb.st_olcum_noktasi) has the data.

select distinct son.on_okuyan_kurum as c1,

     son.on_id as c2,

     end.e_donem as c3,

     end.e_id as c4,

     end.e_t0_ilk_endeks as c5

from bidb.st_olcum_noktasi son

left outer join  dgpys.endeks end on son.on_id = end.e_on_id

where  son.on_id = 7088

and end.e_donem = to_date('2017-07-01' , 'YYYY-MM-DD')

Why obiee outs the filter under where condition? Is it possible to say the query like;

select distinct son.on_okuyan_kurum as c1,

     son.on_id as c2,

     end.e_donem as c3,

     end.e_id as c4,

     end.e_t0_ilk_endeks as c5

from bidb.st_olcum_noktasi son

left outer join  dgpys.endeks end on son.on_id = end.e_on_id and end.e_donem = to_date('2017-07-01' , 'YYYY-MM-DD')

where  son.on_id = 7088

Can you help me please?

Regards,

Dilek

Gianni Ceresa

Hi Dilek,

What you saw it perfectly normal, OBIEE uses the joins in the physical layer to perform the outer join. Filters aren't join conditions, but definitely WHERE conditions.

Can't you try to workaround the issue by adding another piece of filter to generate a " OR end.e_donem IS NULL" in your analysis?

dilek

Hi Gianni,

Thank you for your answer.I tried , but nothing changed. Is there any other thing than I can? :S

Gianni Ceresa

Did you see the "is null" filter in the query or OBIEE thought to be smarter and removed it?

If you change the direction of the join (right outer or even full outer) it will work in this case, but this will also change the business meaning.

Do you have other usage of this outer join you need to keep or that kind of analysis is the one you want to perform?