Advice on factless table use

View previous topic View next topic Go down

Advice on factless table use

Post  dcow on Thu Sep 03, 2009 8:40 pm

I have a situation where i have modeled a process relating to invoicing such that there is a fact table of invoice line items and some dimensions such as date and recipient of the item to name a couple. The situation i am encountering however is that the facts only represent what actually does get delivered to a recipient . The user however is likely to want to see in any report run these results plus zero quantities against the recipients of those who did not recieve any items at all. (IE. The information that is not in the fact table). So my question is , Is this a situation where a coverage (factless) table is required so that all the possible recipients on a date are mapped? My situation seems are little different to any examples if have seen with coverage tables in that the coverage table will bloat in size.


Hopefully this makes some sense .

dcow

Posts : 5
Join date : 2009-09-03

View user profile

Back to top Go down

Use an outer join view

Post  Colin Davies on Thu Sep 03, 2009 11:02 pm

If I understand your question correctly, what you need is to be able to report on all possible recipients, including those who have no receipts (i.e. are not in the fact table). I have encountered this many times. One approach that works is to create a view that is a left outer join of all possible recipients with the fact table, substituting a zero value where the right side of the join contains nulls (e.g. in Oracle use an NVL function on the column to substitute a zero for the nulls).

If you prefer to exchange runtime processing for disk space, do this left outer join in the ETL and populate the fact table with rows for all possible recipients, including zeroes where there are no receipts. This is the approach I prefer when I have the disk space available. Write once, read many is the typical DW scenario.

I hope that helps.

Colin Davies

Posts : 8
Join date : 2009-05-20

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum