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.