Best Praticte for inserting fact and linking to dimensions

View previous topic View next topic Go down

Best Praticte for inserting fact and linking to dimensions

Post  bmoraillon on 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

View user profile

Back to top Go down

Re: Best Praticte for inserting fact and linking to dimensions

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Best Praticte for inserting fact and linking to dimensions

Post  bmoraillon on 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

View user profile

Back to top Go down

Re: Best Praticte for inserting fact and linking to dimensions

Post  Jeff Smith on 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

View user profile

Back to top Go down

Re: Best Praticte for inserting fact and linking to dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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