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

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:

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.

Author: Daan Bakboord

I am an Oracle Business Analytics Consultant with great interest in anything closely related to the Oracle Business Analytics (OBIEE, BICS, Big Data, Data Integration, Data Visualization) .

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s