MULTIPLE FACT TABLES
My current blog looks at the logical or business model. In particular how to connect fact tables so that you can get numbers from more than one fact in the same report – and actually show results.
This aspect of OBIEE seems to cause a fair amount of confusion and I have seen some slightly odd ways to try and achieve it – mostly through complex logical modelling with two or more logical tables sources for a fact.
But when you look at this in the right way it’s not that hard. If you try to combine aggregated numbers from two fact tables in SQL how do you do it? Probable something like this:
select d.some_date, sum(f1.transactions), sum(f2.hours)
from dim_date d, fact1 f1, fact2 f2
group by d.some_date
The key part here is that you are using a common dimension (DIM_DATE) that is joined to both fact tables. That makes it simple to group by a common column (SOME_DATE) and give related figures from both fact tables.
If you didn’t have that common dimension you would have to do something like combining two in-line views which could then be joined together, probably very badly.
select f1.trans, f2.hours
from
(select sum(transactions) as trans from fact1) f1,
(select sum(hours) as hours from fact2) f2
etc…
So in OBIEE you just have to keep this principle in mind. Any reports that uses columns from two fact tables should include common dimensions. Of course to make that happen you have to build it correctly in the Business or Logical Model layer
In the BMM include your two facts (‘Flight’ and ‘Duration’) and make sure that they are linked by at least one common dimension.
Obviously, the more dimensions you have in common the more capable the model will be for giving combined results.
Equally obviously, there will be dimensions that do not join to both fact tables that still need to be in the model. For example:
In this case if the user pulled columns from ‘Crew List’ into the report any results from the ‘Duration’ fact would disappear. If you look at the SQL in the Session Manager you’d see something like ‘CAST (NULL as DOUBLE PRECISION)’ for the ‘Duration’ column.
One way around this is to use an in-column filter on the column from the Flight fact table. Something like FILTER(Flight.Flight Count USING Crew List.Position = ‘Captain’).
This allows the data in Flight to be filtered with a non-common dimension without affecting the data from the Duration fact table.
No Comments