null values best practice digest
2 posters
Page 1 of 1
null values best practice digest
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
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
Re: null values best practice digest
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).
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).
Similar topics
» Null values in facts, yes or no?
» NULL Values in Fact Table
» High % of NULL's - yet they want it!
» Source Values and Conformed Values in the Dimension table
» Lab Result values in Fact Table has int and non int values
» NULL Values in Fact Table
» High % of NULL's - yet they want it!
» Source Values and Conformed Values in the Dimension table
» Lab Result values in Fact Table has int and non int values
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|