Multiple Fact Reporting on (Non-)Conforming dimensions – Part II

What happens when you want to filter on a non-conforming dimension?

When you filter an a non-conforming dimension, you could get a null value for the fact which has all the dimensions as conforming.

 

 

Is this not exactly how the Oracle BI-server works? We have created one logical fact table and two different logical table sources (LTS). The Oracle BI-server creates seperate queries for each LTS in a Oracle BI Answers query.
Query I:

Query I connects fact table 1 to all the dimensions, including a filter on the non-conforming dimension.
Query II:

Query II connects fact table 2 one to the conforming dimensions only. Because there is no physical relationship between fact table 2 and the non-conforming dimension, it is not possible to filter query II on  dtnc1.value3 = ’3CCC3′ as well.
The results of both queries:

Lucky enough for me Nicolas did some investigating himself to:

• Oracle BI EE Forum
  • Filter on a dimension with a fact that don’t depend on that dimension
  • filter and non-conforming dimensions
• Siebel IT Toolbox
  • Two fact tables and non-conforming dimensions
    

It looks like the most easy solution is to filter on the fact table which has all the dimensions as conforming. In this case, that would be; Fct1 Value1.

Solution you could see in Multiple Fact Reporting on (Non-)Conforming dimensions – Part III