|
|
Posted by jefftyzzer on 12/20/07 06:42
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
Navigation:
[Reply to this message]
|