Implementing "future" date in date dimension. 1

[alexjones]

I am implementing my first dimensional model for a first-time data warehouse effort. We're using SQL Server 2005. Here is my initial attempt at defining a date dimension table with all the "usual" attributes, i.e.,

CREATE TABLE [dbo].[DimDate] (
	[DateKey] [int] IDENTITY (1, 1),
	[Date] [datetime] ,
	[FullDate] [varchar] (30) ,
	[DayOfWeek] [varchar] (20) ,
	[DayInMonth] [tinyint] ,
	[DayInYear] [smallint] ,
	[IsLastDayOfWeek] [bit] ,
	[IsLastDayOfMonth] [bit] ,
	[CalendarWeekEndDate] [datetime] ,
	[CalendarWeekInYear] [tinyint] ,
	[CalendarMonthName] [varchar] (30) ,
	[CalendarMonthInYear] [tinyint] ,
	[CalendarMonthYear] [varchar] (35) ,
	[CalendarYearMonth] [char] (7) ,
	[CalendarQuarter] [char] (2) ,
	[CalendarYearQuarter] [char] (7) ,
	[CalendarYear] [char] (4) ,
	[WeekdayIndicator] [varchar] (30) ,
	[HolidayIndicator] [varchar] (30) ,
	CONSTRAINT [PK_DimDate] PRIMARY KEY  NONCLUSTERED 
	(
		[DateKey]
	) 
) GO

I have read that I could first turn the identity off and insert "pseudo-date" records with, perhaps, negative key values to represent situations such as future, n/a, unknown, etc.

I assume it's a good idea to have non-null attributes in dimension tables.

I could probably enter "Unknown" or "N/A", etc. in the FullDate field (which usually holds, e.g., July 4, 2006). and come up with reasonable defaults for most of the others.

My concern is the actual date field (the datetime one). Do I create bogus dates for this field? Like '12/31/2999' for "future"?

Any and all suggestions, opinions will be most welcome.

 

[johnherman]

Why not create the future dates? If these were order dates or contract expiration dates, then future dates are valid for those.

As far as nulls, there are usually at least two 'bogus' dates, one for a very early date and one for a very future date.

For instance, date of death for someone who has not died should be the date for the very future date. This is the "Unknown" Early Date.

The very early date might be for the inception date for processes which began before the computer system, or the begin date for a price. This is the "Unknown" Future Date.

A third bogus date could refer to "Not Applicable" which would be for the date of pregnancy for a male or date of last prostate exam for a female.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[johnherman]

And I forgot to answer your question. It is up to you if you want that field to have a dummy value or stay null. We currently use 1/1/4000 for the UNK Future Date, 1/1/1799 for the UNK Early Date, and 1/1/2500 for N/A.

Sometimes I wish we had left them NULL, sometimes I am glad they have values.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[alexjones]

Thank you so much for your response. Your discussion of when such values might be appropriate was particularly helpful. Have a star

 

[johnherman]

Thanks for the star.

Everyone - For the record, in my first response, I incorrectly named the situations for Unknown Early Date and Unknown Future Date. My mind has been wandering a bit today. Sorry.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright