Bad modeling, I know - but can't be helped. :D Customer wants to create queries from 2 fact tables at the same time. Each fact table has their own subject area and they do have 3 common dimensions (2 time, 1 code) and about 20+ non-common. Both tables also have id columns that are used in joins in db sql queries. I have created a join between the facts in physical layer and inner join between them in BMM - which of course causes the other fact to behave like a dimension table in BMM (which is incorrect). When I create the analysis, I take what I need from each subject area but:
1: measures don't work in these combined queries, they return empty values
2: join doesn't work correctly in most cases, getting an error (or no results even if my sql in db gets me the correct data) and when I look at the sql, it doesn't join the facts even if the join exists
How do I create the joins between the facts so that I can combine the 2 subject areas however I want? Worst thing is that there is the third subject area with third fact and that should be probably linked to the two others. Sql in db to join the facts is actually really simple, like
select table1.ID
, table1.CREATED
, table1.DESC
, table2.TEXT
from table1
, table2
where table2.T1_ID = table1.ID
and table1.code1 = 'xyz'
and table2.code4 = '123';