I'm struggling with a few of Ralph Kimball's concepts in Chapter 5 of
The Data Warehouse Toolkit, 2nd edition.

#1) Page 129 talks about using an "Unknown" or "To be determined"
label for date values in the time pipeline that have not yet been
established. So this then means two things: 1) the database column
that the user constrains on is NOT a date value data type (you cannot
stick "Unknown" into an Oracle date field), and 2) when the user uses a
between operator the values in this character field must by in the
yyyymmdd or yyyy/mm/dd format, which can be less than optimal. I'm
inclined to of course keep the surrogate key concept but go ahead and
leave the column that the user constrains on as a date field and simply
leave the "Unknown" values as null -- after all, tools like Business
Objects have a built in "is null" condition for the user to pick, and I
don't think it is that tough of a concept to get across to end users,
and in fact would be easier than users having to remember to type in
"Unknown" using the correct case/spelling/verbage (e.g. was it
"Unknown" or " n/a"?).

#2) For each date role-playing dimension I plan to put the surrogate
key in the fact table along with the actual date value -- the former to
offer an array of standard calendar based queries (e.g. how many orders
in September 2005?) and the latter to speed up queries when all the
user wants to see is the date value and is constraining on some other
attributes (show me the pipeliness for all the orders by ACME in 2005).
This particular acculating snapshot fact table can have 61 different
stages / role playing time dimensions.

--Bob Harford
Data Warehouse Architect
AlphaInsight Corporation