For example: two dimension tables and one fact table.
branch1, day2, 100
what I want in answers when using branch and time and sales without any filters:
Branch1, day1, 0
branch1, day 2, 100
Branch 2, day1, 0
Branch 2, day 2, 0
so basically I want to keep all dimensions combinations and related facts or 0 if there is no related facts. How to do it?
I know how to do it for just one dimension. That is to set up the relationship between the dimension and the fact as outer join. But how about two or more dimensions?
In SQL it will be something like dimension 1 cross join dimension 2 then left outer join the fact. How to do it in OBIEE 10 G?
The SQL issued in Advanced tab is something like this (I used different table and column names. So I changed them for better understanding). RMIS is the presentation layer name.
SELECT time.day saw_0, branch.branch saw_1, sales.branch saw_2, sales.day saw_3, sales.sales saw_4 FROM RMIS ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4