This discussion is archived
5 Replies Latest reply: Nov 7, 2013 9:16 AM by nianzhang RSS

Cross join of dimensions

nianzhang Newbie
Currently Being Moderated

Hi,

 

For example:  two dimension tables and one fact table.

dim1: branch

branch1

branch2

 

dim2: time

day1

day2

 

fact: sales

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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points