NULL values & Fact tables

Last Post 27 Aug 2004 09:20 AM by dhaney40. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
JackOfSpeed
New Member
New Member

--
17 Aug 2004 04:19 AM

I'm doing a little research into some issues we are having with a front-end BI tool and currently seeking some community feedback on NULL values in fact tables. Of course, outside the obvious of removing nulls from fact keys, I'm wondering:

Do you allow them in numeric fields? Or do you replace nulls from the source in ETL with zeros? I can imagine that each DW implementation is unique, but again, wanted some overall feedback on the advantages/disadv of NULLS in fact rows.

Thanks.
dhaney40
New Member
New Member

--
27 Aug 2004 09:20 AM
If there is a null value in a fact row, then why do you need to bring that record into the fact table at all? If you need it in the fact table to represent data for a specific slice of dimensions, then what is value of having a null? If the later is your situation, we have always replaced null with 0's or for character fields, 'N/A'. Good Luck.
alex0603
New Member
New Member

--
27 Aug 2004 11:36 AM
In my experince there is more than one fact in a fact column. For instance standard hours worked and over time hours worked, with a calculated fact of total hours worked. In these cases you could easily see a null value in a fact and still want the record.

Dimension keys should never have nulls (they can be defaulted to a standard unknown key)

Facts need to be looked at individually. In the above example I would probably default the the facts to zero. A null in any of the facts will not affect an aggregation such as a sum of "standard hours" or "overtime hours" but it would affect calculation the "total hours" (# + null = null).


But in another example -- tracking the number of customers affected(fact) per incident, lets say a power outage.

You still want the record even if the "customers affected" is null because it represents an event. Factless fact example. It will still be used in counts and % calculations. But you may not want to default this to 0 because 0 in this case is significant, and probably can not be assumed to be 0.


You are not authorized to post a reply.

Acceptable Use Policy