Multiple Fact Reporting on (Non-)Conforming dimensions


Update 31 August 2017

Although published about 7 years ago, this subject still seems a source of questions. To understand the concepts of Oracle BI (OBIEE) better, it is good to check out the following blogposts as well. They will link to other blogs “Don’t forget the Logical Layer” & “Going to the core of Oracle Analytics” and will help you better understand the basis of OBIEE. This basis is an absolute must to get the most out of OBIEE.


There are some questions, which are popping up at the Oracle BI EE Forums regularly. One of those questions is;

*** How to model multiple facts against (non-) conforming dimensions?

I will try to work things out. Click on the images to see more detail.

Note: I am aware of the extra white space between the images. That’s not intended functionality, but lack of knowledge of  WordPress.

Picture the following:

There are two fact tables and three dimension tables. FACT_TABLE_1 has two conformed dimension tables; DIM_TABLE_CONF_1 and DIM_TABLE_CONF_2 and one non-conformed dimension table DIM_TABLE_NON_CONF_1.

FACT_TABLE_2 has two conformed dimension tables; DIM_TABLE_CONF_1 and DIM_TABLE_CONF_2.

The Physical Model would have the following structure:

Physical Diagram

Based on the Physical Model we could construct the following Logical Model:

Logical Diagram

I have created one fact table which contains Logical Table Sources (LTS) for FACT_TABLE_1 and FACT_TABLE_2

Logical Model

As you can see I have created Dimensions (Hierarchy’s) for each Dimension Table.

FACT_TABLE_2 has no physical relationship with DIM_TABLE_NON_CONF_1. Therefore you should set the logical levels for FACT_TABLE_2 to the ‘Grand Total’-level of DIM_TABLE_NON_CONF_1. This way the Oracle BI Server won’t look for a join between DIM_TABLE_NON_CONF_1 and FACT_TABLE_2.

If you want to avoid nulls, set the detail levels for the facts. Set the ‘Grand Total’-levels for the metrics as well.

Logical Table Source – Fact I
Logical Table Source – Fact II
Logical Column – Fact II

If we take a look at Oracle BI Answers, we can create a report which contains data from the following tables;

Oracle BI Answers – Conformed Dimension

Now we can bring data from DIM_TABLE_NON_CONF_1 into this report. It is impossible to devide data from FACT_TABLE_2 over this dimension. Therefore the data will be the same for every value of this dimension.

Oracle BI Answers – (Non-) Conformed Dimension

*** Summary:

 It’s possible to report on facts and dimensions which not have a physical relationship to each other. Just make sure you create dimensions (hierarchy’s) for every dimension table. Next to that you should set the logical levels for your logical tables.

Author: Daan Bakboord

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

4 thoughts on “Multiple Fact Reporting on (Non-)Conforming dimensions”

  1. Thanks for the detailed description.
    Just a note for OBIEE 12C, you have to check the “Show Total value for all measures on unrelated dimensions.” option in the advanced page of analysis; otherwise will show blank.


  2. Hi,

    thanks for shareing this…
    one question: What happen when you want to filter o nonconforming dimension? You will see that your facts, that has that dimension as conforming, will be null if the condition from filter is true. How to get rid of them?
    Set for that fact another filter is not null…
    Is there another way?



Leave a Reply

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

You are commenting using your 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