Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Advice on factless table use

2 posters

Go down

Advice on factless table use Empty Advice on factless table use

Post  dcow 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 .


Posts : 5
Join date : 2009-09-03

Back to top Go down

Advice on factless table use Empty Use an outer join view

Post  Colin Davies 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

Back to top Go down

Back to top

- Similar topics

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