Hi Experts,
I have a very simple set up of Star Schema with two fact tables and 1 dimension. Both fact tables joined to the dimension at the same level.
When i pull a column from both fact tables and the dimension table in OBIEE, it has to create simple SQL like below:
select FACT1.column1,
Fact2.Column1,
Dim.Column1
from FACT1, FACT2, DIM
where FACT1.ID = DIM.ID and FACT2.ID = DIM.ID
but instead it creating a query in a very complex way:
select case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end as c2,
D1.c1 as c3,
D2.c1 as c4
from
(select FACT1.Column1 as c1,
DIM.Column1 as c2
from
DIM T1287863,
FACT1 T1287945
where (DIM.ID = FACT1.ID)
) D1 full outer join (
select FACT2.Column1 as c1,
DIM.Column1 as c2
from
DIM,
FACT2
where ( DIM.ID = FACT2.ID)
) D2 On isnull(D1.c2 , '1') = isnull(D2.c2 , '1') and isnull(D1.c2 , '2') = isnull(D2.c2 , '2')
I even tried setting the levels for both the fact tables and it still creates the query in avove way. Any thoughts on this will be vary helpful.