With all of the research I have done, I have come to the conclusion that the
proper way to represent a NULL value in my fact table would be to create an
entry in my dimension table that represents "no value" or NULL or something
like that and then reference the surrogate key in my fact table. I have been
trying to find some specific discussions on the topic so that I can better
argue my case as to why (or why not) to approach it this way, but I am
coming up short.

How have you approached this issue? Are there alternatives to this approach?
What are the pros and cons?

Thanks in advance for your response.



tim

I've usually seen it done using a -1. Every dimensional table we have has
a -1 member named "unknown".

Same idea...however I'd replace the NULL's with a -1 in your ETL process. If
you're building your warehouse directly off your transaction system I'd
build a view on top of your fact table and use an isnull. NULL's can do
weird things.

This is, of course, assuming the NULL's are a dimension.


"Tim Chapla" <tchapla (AT) nospam (DOT) com> wrote