I wanted to write this as an introduction to combining data from multiple facts/subject areas into a single analytic. The post is aimed primarily at end-users as there are developer techniques we can use to circumvent some of the restrictions described below.
Let us first refresh ourselves with a subject area actually is. In its most basic form it is simply a fact with associated dimensions. Consider the following simplified example for a financial fact.
So we can easily report on financial transactions by any of the four dimensions listed.
Now let’s suppose we have a completely separate subject area based on some HR Salary information.
Again, we can use that star in isolation however we wish. However… what if our user decides that they would like to report on the monthly spend alongside the monthly salary cost?
Without considering any dimensions this works fine – we can simply include the measure from each fact. The difficulty comes when we want to include dimensions – the key rule being this…
You can only report on measures from multiple facts where all dimensions that are used in the analytic are shared.
So let’s look at those two facts together.
We can see that the date dimension is shared between the two facts. So we can easily produce an analytic such as:
| Date | Transaction Amount | Salary Amount |
|---|---|---|
| 01-04 | 1234 | 1243 |
However what if we wished to run that report for say the Planning department? We could not do that! The department dimension is not shared across both dimensions, so if you try this in OBIEE, whilst you won’t get an error, you’ll end up with something like this:
| Date | Transaction Amount | Salary Amount |
|---|---|---|
| 01-04 | 1243 |
The amount from the financials fact cannot be calculated. There is a conflict in the granularity of the two facts in the analytic. If you think about it, this makes perfect sense. How can OBIEE tell you the transaction amount for the Planning department when there is nothing in the transaction fact that splits down the rows in there to that level.
It’s surprising how often this requirement arises in the real world, and how often two facts are unfortunately incompatible. For example:
Show me how many people have been off sick with headache this month along with the headcount of the department
Immediately we’re in trouble – we can’t filter the analytic by the sickness reason because to say “what’s my headcount filtered by absence reason” is both meaningless and impossible. Well… not really impossible, as there are workarounds to this which I’ll go into on a future post. However if you’re trying to combine two subject areas and are seeing some unexpected results then this may well be why.