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

Best Praticte for inserting fact and linking to dimensions

Go down

Best Praticte for inserting fact and linking to dimensions Empty Best Praticte for inserting fact and linking to dimensions

Post  bmoraillon Tue Aug 24, 2010 11:26 am

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.

bmoraillon

Posts : 12
Join date : 2010-06-06

Back to top Go down

Best Praticte for inserting fact and linking to dimensions Empty Re: Best Praticte for inserting fact and linking to dimensions

Post  ngalemmo Tue Aug 24, 2010 12:02 pm

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Best Praticte for inserting fact and linking to dimensions Empty Re: Best Praticte for inserting fact and linking to dimensions

Post  bmoraillon Tue Aug 24, 2010 3:28 pm

ngalemmo wrote: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 !

bmoraillon

Posts : 12
Join date : 2010-06-06

Back to top Go down

Best Praticte for inserting fact and linking to dimensions Empty Re: Best Praticte for inserting fact and linking to dimensions

Post  Jeff Smith Wed Sep 08, 2010 1:38 pm

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).

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Best Praticte for inserting fact and linking to dimensions Empty Re: Best Praticte for inserting fact and linking to dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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