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.

Changing Colors

One of my clients wanted to change to color fromat of the grand-total columns in a Pivot-view. The format of values of the grand-total should match the format of the label of the grand-total. I used to go to one of John’s blogposts; OBIEE views.css color cheat sheet. One of my colleagues pointed me to the color picker functionality in Paint.Net, although there are probably more programs with such functionality.

How does it work? Picture the following pivot;

Make a screenshot of this pivot and open it in Paint.Net. Open de colors-window (F8)

Click on the color-picker in the tools-window.

Use the color-picker to select the color you want to use. In this case I had to click on the grand-total column. Now when you check the colors-window, you get exactly the color codes you have to use to change the format of the grand-total values.

Very easy to use, but yet very handy.