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.
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
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:
Which will result in this:
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.
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:
That produces these results:
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).