Nulls vs Blanks in dimension tables

View previous topic View next topic Go down

Nulls vs Blanks in dimension tables

Post  Jeff Smith on Thu Sep 29, 2011 11:26 am

What's the preference in a dimension table - a null or a blank? My preference above all else is consistency.

In certain instances I use "NA", "Not Available", or "Not Applicable" to fill in missing data. But sometimes this is inappropriate. There are times, like with Addresses, that "Not Available" would be the wrong value. Or when the Name fields are seperate and include Prefix, First Name, Last Name, Suffix, etc. Blanks don't feel right, but a null make concatenating the data pretty complex.

Thoughts?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Nulls vs Blanks in dimension tables

Post  ngalemmo on Fri Sep 30, 2011 2:29 am

For things like names and addresses, blank or empty strings are fine. You certainly don't want to put some default value in there and mess up the name.

As far as concatenating, have a 'full name' column and do it as part of the load rather than doing it in queries.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Nulls vs Blanks in dimension tables

Post  BoxesAndLines on Fri Sep 30, 2011 8:44 am

I normally convert blank to nulls. I normally don't replace null or blank with any other text value.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Nulls vs Blanks in dimension tables

Post  hang on Fri Sep 30, 2011 6:40 pm

I agree with B&L on this, even though it might be against the guideline advised by many dimensional modelling experts. I still think null value has a place in dimensional modelling and cannot be replaced by any other things. Sometimes I also intentionally convert blank or zero into null, as I can leverage COALESCE instead of using CASE. Especially in fact table, null can return proper aggregate value than zero, null can also handle the nasty "divided by zero" gracefully.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Nulls vs Blanks in dimension tables

Post  ngalemmo on Sat Oct 01, 2011 2:37 am

In certain instances I use "NA", "Not Available", or "Not Applicable" to fill in missing data. But sometimes this is inappropriate. There are times, like with Addresses, that "Not Available" would be the wrong value. Or when the Name fields are seperate and include Prefix, First Name, Last Name, Suffix, etc. Blanks don't feel right, but a null make concatenating the data pretty complex.

The question is about names and addresses. Unless you are running Oracle (where blank and null are the same thing), why would you use null? In a normal database the correct definition of 'null' is: the value has not been set. In other words, if you are inserting a row and a column was not specified in the list of columns being inserted, the value for that column would be null (assuming a default value was not specified). To make a column null, you need to remove it from the insert statement. Doing it conditionally requires ugly code or using dynamic SQL, which is inefficient and difficult to do when using an ETL tool. Why bother, when all you need to do is set the column value to ''.

In the original example, if the name suffix was blank, what new information would you gain by setting it to null?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Nulls vs Blanks in dimension tables

Post  Jeff Smith on Wed Oct 05, 2011 2:05 pm

I think a good reason for not using Nulls concerns filtering. If the filter is a "NOT EQUAL", then you will not get the correct answer if the column has nulls.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Nulls vs Blanks in dimension tables

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