Time Dimension, NULLs and Time datatype

We need to design a Time dimension. Essentially, it would be:

TimeSK int
Time time
...


How do people handle the various types of unknowns. It's easy in other types of dimensions where the SK is set to negative values, and there's a varchar that can carry a descriptive label. Even datetimes can usually be filled with a dummy date that is obviously wrong. But, there's no "wrong" time. What are the common practices to employ here?

What do you mean by 'unknown'?  Dimensions have natural keys and surrogate keys.  During load you use the natural key to locate the surrogate key.

There are unknowns where you have no natural key (i.e. its null) and you have unknowns where you have a natural key but it can't be found in the table.

With nulls it is common practice to assign a specific negative surrogate key (with a corresponding row in the dimension table), although equally effective is to treat it as any other new natural key.  Standard practice with new unknown natural keys is to create an inferred row in the dimension.  That is, you create a row, assign a surrogate key and load the natural key and whatever other useful information from the source.  For other dimensions you usually get correct information later from the appropriate source (i.e. a later arriving dimension scenario), for dates you would need to decide if those rows get populated and with what.

Specifically, I am talking about what is described in "Handling Null Foreign Keys in Fact Tables" in http://www.kimballgroup.com/2010/10/06/design-tip-128-selecting-default-values-for-nulls/

I assign the usual 0, -1, -2, and -3 to the surrogate key, and the natural key, when a varchar, gets the "Missing Value", etc., values. But, what happens when the natural key is a time? What have people done here? I guess I'm asking for practical ideas to the theoretical concept with which I agree...

ngalemmo wrote:
Standard practice with new unknown natural keys is to create an inferred row in the dimension.  That is, you create a row, assign a surrogate key and load the natural key and whatever other useful information from the source.  For other dimensions you usually get correct information later from the appropriate source (i.e. a later arriving dimension scenario), for dates you would need to decide if those rows get populated and with what.

I am referring here to unknowns that are not null. Time, customer number… it doesn't matter.