NULL columns sometime give unexpected query results to users in user point of view. It happens in filtering (i.e. condition [COLUMN1 = 0 or COLUMN1 != 0] doesn't return all records, variance from the records with COLUMN1 is NULL). We should avoid those unexpected outcome by avoiding NULL in all dimension columns and dimension foreign key.
On the other hand, it is ok to use null in the measure columns in fact tables since we don't filter by measures.
To handle unknown dimension, we always recommend adding at least one dummy record in each dimension table to represent unknown/not applicable (e.g. with surrogate key -1). That's how we avoid null dimension foreign key in fact tables.
To summarize, all columns in dimension and fact tables are not nullable, except the measure columns.
No comments:
Post a Comment