Best Praticte for inserting fact and linking to dimensions

[bmoraillon]

Hi all,
is it allowed to have null values in fact table for some dimensions foreign keys ?

Exemple :

DateDimFk DimFk1 DimFk2 50 (coming from business system 1)
DateDimFk null DimFk2 50 (coming from business system 2)

or do i have to create a special dimension data row (for instance "?") to link these facts ?

Many thanks in advance.
Best Regards.

[ngalemmo]

Null foreign keys in fact tables is very bad practice. Always populate the key and reference a 'not applicable' row. Queries that have joins where an FK may be null will exclude those rows from the query, even if you are not filtering on a value in that dimension.

[bmoraillon]

Null foreign keys in fact tables is very bad practice. Always populate the key and reference a 'not applicable' row. Queries that have joins where an FK may be null will exclude those rows from the query, even if you are not filtering on a value in that dimension.

Thank you very much !

[Jeff Smith]

It's common to use -1 as the default value for a dimension key value in the fact table. Add a row to each dimension table with -1 as it's surrogate key value. I would populate the columns for this "null" row with "NA", Not Applicable", "missing", etc., whatever is appropriate (Nulls in a column that is being filtered are a drag).