Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

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)NOT
NULL,
[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
SET
ANSI_PADDING
OFF
GO

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.