null values best practice digest

View previous topic View next topic Go down

null values best practice digest

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

bmoraillon

Posts : 12
Join date : 2010-06-06

View user profile

Back to top Go down

Re: null values best practice digest

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

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

View user profile http://aginity.com

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