Multiple Fact Reporting on (Non-)Conforming dimensions

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;

• DIM_TABLE_CONF_1
• DIM_TABLE_CONF_2
• FACT_TABLE_1
• FACT_TABLE_2 

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.