Here is a very simple TSQL script that will flesh out a time dimension, for use with SQL Server Analysis Services (SSAS) cube, and can easily be molded to work with other vendor implementations.
AdventureWorks DW provides a nice reference implementation for a time dimension. Unfortunately provides no guidance around the actual population of the dimension. This script will provide a repeatable, configurable way of building out a similar implementation.
IF ( OBJECT_ID ( 'DW.DimTimeDays' ) > 0 )
DROP TABLE DW . DimTimeDays ;
CREATE TABLE DW . DimTimeDays (
[ TimeKey ] [ INT ] PRIMARY KEY IDENTITY ( 1 , 1 ) NOT NULL ,
[ FullDateAlternateKey ] [ DATETIME ] NULL ,
[ DayNumberOfWeek ] [ tinyINT ] NULL ,
[ DayNumberOfMonth ] [ tinyINT ] NULL ,
[ DayNumberOfYear ] [ smallINT ] NULL ,
[ WeekNumberOfYear ] [ tinyINT ] NULL ,
[ EnglishDayNameOfWeek ] [ nvarchar ]( 10 ) NULL ,
[ EnglishMonthName ] [ nvarchar ]( 10 ) NULL ,
[ MonthNumberOfYear ] [ tinyINT ] NULL ,
[ CalENDarQuarter ] [ tinyINT ] NULL ,
[ CalENDarYear ] [ char ]( 4 ) NULL ,
[ FiscalQuarter ] [ tinyINT ] NULL ,
[ FiscalYear ] [ char ]( 4 ) NULL )
@ StartDate DATETIME ,
@ EndDate DATETIME ,
@ DayCount DATETIME ,
@ diff INT ,
@ Count INT
@ StartDate = '2008-01-01' ,
@ EndDate = '2012-07-01' ,
@ Count = 0
@ diff = DATEDIFF ( dd , @ StartDate , @ EndDate )
WHILE @ Count <= @ diff
SELECT @ DayCount = DATEADD ( dd , @ Count , @ StartDate )
INSERT INTO DW . DimTimeDays (
@ DayCount ,
DATEPART ( dw , @ DayCount ),
CASE DATEPART ( dw , @ DayCount )
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
DATEPART ( day , @ DayCount ),
DATEPART ( dy , @ DayCount ),
DATEPART ( wk , @ DayCount ),
CASE DATEPART ( mm , @ DayCount )
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
DATEPART ( mm , @ DayCount ),
DATEPART ( qq , @ DayCount ),
DATEPART ( yy , @ DayCount ),
CASE ( DATEPART ( qq , @ DayCount ))
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 1
WHEN 4 THEN 2
CASE ( DATEPART ( mm , @ DayCount ))
WHEN 1 THEN DATEPART ( yy , @ DayCount )
WHEN 2 THEN DATEPART ( yy , @ DayCount )
WHEN 3 THEN DATEPART ( yy , @ DayCount )
WHEN 4 THEN DATEPART ( yy , @ DayCount )
WHEN 5 THEN DATEPART ( yy , @ DayCount )
WHEN 6 THEN DATEPART ( yy , @ DayCount )
else DATEPART ( yy , @ DayCount ) + 1
SET @ Count = @ Count + 1
I really just wanted to log this here for future reference. There are many varying implementations out there already, but the simplicity of this one was really appropriate for what I needed. Kudos to
Azaz Rasool for posting his implementation up.