Time Dimension Design Question

[Jimbo]

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?

[jefftyzzer]

On Dec 19, 12:08 pm, Jimbo <[e-mail]:

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

[Jimbo]

On Dec 19, 10:42 pm, jefftyzzer <[e-mail]:

On Dec 19, 12:08 pm, Jimbo <[e-mail]:

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

[Ed Murphy]

jefftyzzer wrote:

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').

Don't you have to CAST or CONVERT time_dim.date to CHAR or VARCHAR
first, lest it try and fail to convert 'Date not Available' to
DATETIME instead?

[dmcmunn]

On Dec 24 2007, 2:33 pm, Ed Murphy <[e-mail]:

jefftyzzer wrote:

I suppose that when you then join the fact to thetimedimension, you
could apply a COALESCE to the date column, e.g.,
COALESCE(time_dim.date, 'Date not Available').

Don't you have to CAST or CONVERT time_dim.date to CHAR or VARCHAR
first, lest it try and fail to convert 'Date not Available' to
DATETIME instead?

Please consider a web page I maintain dedicated to providing the
building blocks for kick starting a date dimension: http://www.ipcdesigns.com/dim_date/
All of the information is free including free downloads of scripts to
build a date dimension from scratch or download one that is pre-built.
If you find the downloads helpful, please leave a comment. You don't
even have to register, but I do appreciate feedback. There are also
other free tool kits for data profiling and one for ETL metadata
management should be ready by February 1, 2008. Best of luck, Don
McMunn