Combining Facts/Subject Areas in Oracle OBIEE – Part 2

Combining Facts/Subject Areas in Oracle OBIEE – Part 2

A while back I wrote a post about how to combine measures from different facts in an OBIEE analysis. If you missed that then you can find it here. One of the key issues I highlighted was that generally it is only possible to join up facts where the dimensions you are using are common across both facts. I.e.

Fact Diagram

There is however a technique that can be used to achieve this. Let’s assume we want to report on the number of sickness absence days by department in a BI Apps model. A simplified view of these two facts and the mandatory dimensions would be

Absence Workforce Diagram


 

As you can clearly see, we need the absence dimension to filter out only those absences related to sickness, however that dimension is not part of the workforce fact – it is of course meaningless to report on the workforce by absence type as not all of the workforce have been absent. The first time people attempt this I usually see something like this:

Analytic Demo

Which will result in this:

Analytic Demo Results

So how do we get some figures in there? And how do we get entries for where there were no absences (you may notice that they are omitted)? We use a filtering technique on the column itself and remove the analytic level filter. This applies the predicate to a separate query internally within OBIEE before the data is combined.

Column Formula

Look up the Filter documentation for yourself, however the most basic usage is along the lines of:

Filter("Absence Facts"."Absence Days" Using "Absence Type / Reason"."Absence Category" = 'Sickness') 

So now we have an analytic which looks like this:

Analytic Demo 2

That produces these results:

Analytic Demo Results 2

There are other ways of doing this, however this is the easiest whilst minimising customisation within the OBIEE repository and not using UNION analytics (which themselves can pose a whole host of problems).

Leave a Reply

Your email address will not be published. Required fields are marked *

19 + 13 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.