Posted by Jimbo on 12/20/07 17:27

On Dec 19, 10:42 pm, jefftyzzer <jefftyz...@sbcglobal.net> wrote:
> On Dec 19, 12:08 pm, Jimbo <jamesfer...@hotmail.com> wrote:
>
> > Im trying to design my time dimension and need to add a field to
> > handle null dates in the fact. So if at the time of ETL the date isnt
> > known, referential integrity will be preserved. Kimball suggests
> > insterting a record in the time dimension to handle this with a
> > description of 'Date not available' or something like that. However
> > if users are doing inner joins on the dimension they will obvisously
> > be pulling the datetime field..what should be in the datetime field
> > for this particular record?
>
> James,
>
> If I'm reading right (DW Toolkit 2d Ed, pp 60, 261), I don't think
> Kimball is suggesting that you put a dummy date in the dimensional row
> (e.g. 1/1/1900 or 12/21/9999)--although I suppose you could do that if
> that's a convention your users would agree to--but rather that that
> value be NULL, just as it is in the source data. (This would mean,
> though, that your date column in the dimensional table would have to
> be NULLable.)
>
> I suppose that when you then join the fact to the time dimension, you
> could apply a COALESCE to the date column, e.g.,
> COALESCE(time_dim.date, 'Date not Available').
>
> --Jeff

Ah that makes sense...thanks, thats what I'll do.

-Jim

[Back to original message]