Facts and Dimensions – a Perfect Couple

With Oracle BI EE (OBIEE) you are able to report against various different data models. There are several different interesting blogposts to prove that. Let me Google that for you; Transactional Schemas, Data Vault. In the end the Business Model and Mapping Layer (Logical Layer) ‘needs’ a Star Schema to construct the model we can report upon.

To simplify things, the Star Schema is one Fact-Table with one or more Dimension-Tables. So when you start creating your reports in eg. Oracle BI Answers, it’s just a matter of combining the Fact-Table with the Dimension-Table(s). Last week I was at a client who had a problem with one of the reports; Unexpected Results.

The issue was the following; 3 Dimension-Tables joined together. Dimension-Tables in a Star Schema do not have a direct relationship with each other. The 3 Dimension-Tables together only get a meaning when there is a Fact-Table included. Let me clarify that.

Let’s say we have 3 Dimension-Tables (Date – 27/10/2014, Product – iPhone, Region – Europe). If we put these 3 Dimension-Tables in a report, what does that tell us? Nothing! We can guess. There are iPhones sold in Europe on 27/10/2014? That might be. How much? We don’t know. There are iPhones stolen in Europe on 27/10/2014? That might be correct as well.

If we add a Fact-Table (Quantity Sold – 20,000) to the above, the whole query makes more sense. There are 20,000 iPhones sold in Europe on 27/10/2014.

When you construct a query in OBIEE with only Dimension-Tables OBIEE includes a Fact-Table in it’s query. Unless you have defined an Implicit Fact Column, you don’t necessarily know for sure which Fact-Table/Column is included in the Dimension-Only Query.

Facts and Dimensions are a Perfect Couple, please include both of them in your query.

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.

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;

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.