Last week I have been blogging about Multiple Fact Reporting on (Non-)Conforming dimensions. Thanks to Nicolae I was triggered to do some further investigation on this topic. He had a question; 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
- Siebel IT Toolbox
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.
I will try to ask some other sources what their solution is to this ‘problem’.
More to come.
DaAnalytics Blog 
