Nulls vs Blanks in dimension tables

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?

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.

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

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.

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?

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.