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

3 posters

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

- Similar topics

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