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:
Based on the Physical Model we could construct the following Logical Model:
I have created one fact table which contains Logical Table Sources (LTS) for FACT_TABLE_1 and FACT_TABLE_2
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.
If we take a look at Oracle BI Answers, we can create a report which contains data from the following tables;
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.
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.