Time Dimension Design Question

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?

Re: Time Dimension Design Question

On Dec 19, 12:08 pm, Jimbo 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

Re: Time Dimension Design Question

On Dec 19, 10:42 pm, jefftyzzer wrote:
> On Dec 19, 12:08 pm, Jimbo 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

Re: Time Dimension Design Question

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?

Re: Time Dimension Design Question

On Dec 24 2007, 2:33 pm, Ed Murphy wrote:
> 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