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

null values best practice digest

2 posters

Go down

null values best practice digest Empty null values best practice digest

Post  bmoraillon Mon Aug 30, 2010 11:17 am

Hi all,
i read all the post and design tips about null values management.
Can you tell m if the following statements are right or wrong for you :

Dimension attributes null values management :

For string dimension attributes, use :
"Not available" : if they're are not in the production system
"Not applicable" : if there is no sense

For date dimension attributes, use : null

Fact foreign keys null values management :

link to one of this 3 special rows in dimension : -3 not applicable, -2 not available or -1 unknown

Fact attributes null values management :

null for string and integer attributes

Fact degenerated dimension null values management :

"Not applicable" or "Not available"

what about zip code ? => do i va to extend the field length from 5 to 13 in order to be able to put a "Not available" ?

Thanks you very much for your comment.
Best Regards


Posts : 12
Join date : 2010-06-06

Back to top Go down

null values best practice digest Empty Re: null values best practice digest

Post  ngalemmo Mon Aug 30, 2010 12:17 pm

As far as attributes go, I do not think there is any clear 'rule' as to what to do. It all depends on what the business wants to see. Most of the time, just leaving it null is fine.

As for foreign keys, if I am loading facts and the fact data provides a natural key that does not exist in the dimension, I will insert a new dimension row using that natural key and flag the row as inferred. I would not assign an arbitrary -1 (unknown) in such cases.

Inferring a row has a number of advantages. First, it gives you a mechanism of reporting such situations (along with related facts) so the business is aware of the issue. Second, it reserves a place in the dimension for attributes of that natural key should the key actually be valid (which is usually the case).

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

Back to top Go down

Back to top

- Similar topics

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