Being a Consultant, I take pride in adding value to my client at every opportunity possible. And it’s the thumb rule of the game, that you keep yourself updated all the time so that you perform on these opportunities.
Ok, What did you mess up??
|Yes, It’s just NULL!!! And so, clean it up!!|
Ok, fortunately I did not mess up anything, but I lately figured out that in our fact a foreign key was NULL!!
Don’t tell me, you do not know what to do then??
First thing I jumped out of my chair and said “Fill it up with the key of the Unknown Member”.
It’s Correct!! So, what’s the problem?
NOPE!! It’s much more than that. The main objective of any BI solution is to help business gain insight. So here, a much more informative key would have added value. Just think of the following case
I joined a pool club, by purchasing a membership from the club’s vendor during a summer campaign in the downtown. Given that I got the membership number, I book myself a game the very next day, as I had promised my friends and show off my new club. However, the club’s vendor submits the details only after 2 days after some verification, which gets processed only after a week.
So, if somebody’s been developing reports on the number of games played on a daily basis, there is a 100% chance that my record is not found in their member dimension, but there is a fact record!!! Hence, a 100% chance that the foreign key is null.
So, in this case, it would had been just great if the foreign key told us that my member information had not been processed yet, instead of just throwing my record into the “Unknown” bucket. So, as the data warehouse gurus suggest, we got to make the unknown foreign key more meaningful.
For your reference, you could read more from the gurus themselves at http://www.kimballgroup.com/2010/10/06/design-tip-128-selecting-default-values-for-nulls/
Till we catch up, Adios….