Advice on factless table use

[dcow]

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 .

[Colin Davies]

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.