[OBIEE 10g] What To Do When OBIEE Fact Tables Do Not Join To All Dimension Tables ?

 

What to do when OBIEE fact tables do not join to all dimension tables?

Here using the Sh Schema to Explain the above Scenario

 

The Step By Step Process:

Import Products,costs,sales,customers Tables From SH Schema….

Physical Diagram Joins between Facts and Dimensions

In The Above We don’t have join between customers and costs….when we try to get the data,

From this in Answers it will be like this

In one way this result is correct, because there is no join between customer and costs…

So the BI server has fired the Database with the following Query 

select distinct D1.c1 as c1,

     D1.c2 as c2,

     cast(NULL as  DOUBLE PRECISION  ) as c3

from

     (select distinct T78.CUST_FIRST_NAME as c1,

               T102.PROD_CATEGORY as c2

          from

               CUSTOMERS T78,

               PRODUCTS T102,

               SALES T125

          where  ( T78.CUST_ID = T125.CUST_ID and T102.PROD_ID = T125.PROD_ID )

     ) D1

order by c1, c2

Though we Have not selected any column from sales the bi server has

Directed to aggregate based on sales….

In order to Fetch the Data using customers and costs we need to set Physically complex join or we can set content level to Total

In physical layer, by making the condition always true…

Physical Diagram Will be Like this

In Bmm Layer Make Sure to Create Hierarchies for the dimensions,Don’t set any Level Based Measures

 Drag and drop in Presentation Layer

Check for global consistency and save it.  Run the Bi Server and Presentation services And check the result..

Now, This works….Here The Important Point to remember is the output that has got is an Cartesian product….Generally Don’t go for this because it will cause some performance issues…..