Date Dimension Using Computed Columns

Picked up this nice script from a client last week.  Use this script below to create a Date Dimension for a data warehouse that is based off of computed columns. The only column that needs to be populated is the CalendarDate column.

CREATE TABLE [DimDate](
[DateKey] [int] IDENTITY(1,1) NOTNULL,
[CalendarDate] [datetime] NULL,
[CalendarYearNumber] AS (datepart(year,[CalendarDate])),
[CalendarYearName] AS (CONVERT([varchar](4),datepart(year,[CalendarDate]),(0)))PERSISTED,
[CalendarQuarterNumber] AS (datepart(quarter,[CalendarDate])),
[CalendarQuarterName] AS (‘QTR’+CONVERT([varchar](1),datepart(quarter,[CalendarDate]),(0))),
[CalendarMonthNumber] AS (datepart(month,[CalendarDate])),
[CalendarMonthName] AS (datename(month,[CalendarDate])),
[CalendarMonthNameShort] AS (left(datename(month,[CalendarDate]),(3))),
[DayNumberOfWeek] AS (datepart(weekday,[CalendarDate])),
[DayNameOfWeek] AS (datename(weekday,[CalendarDate])),
[CalendarDayNumberOfMonth] AS (datepart(day,[CalendarDate])),
CONSTRAINT [PKdimDate] PRIMARYKEYCLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]
)ON [PRIMARY]
GO
SETANSI_PADDINGOFF
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s