 | |
Time Dimension Design Question
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Time Dimension Design Question -
12-19-2007
, 02:08 PM
#2
| |||
| |||
|
|
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? |
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
#3
| |||
| |||
|
|
On Dec 19, 12:08 pm, Jimbo <jamesfer... (AT) hotmail (DOT) 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 |
-Jim
#4
| |||
| |||
|
|
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'). |
first, lest it try and fail to convert 'Date not Available' to
DATETIME instead?
#5
| |||
| |||
|
|
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? |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.