De Tijd Dimensie
De tijd dimensie met voor elke dag een rij is de belangrijkste tabel in menig data warehouse. Veelal gelden speciale attributen zoals feestdagen, werkdagen maar ook alternatieve hierarchieen. Hierdoor is het volgens data warehouse guru Ralph Kimbal het verstandigst om deze dimensie in een middagje in Excel te bouwen.
(Tip 51: Latest Thinking On Time Dimension Tables)
Voor eenvoudige tijd dimensies en om snel een begin te hebben kan je onderstaande sql querie gebruiken.
De querie maakt gebruikt van common table expressions een feature dat vanaf versie 2005 in sql server beschikbaar is.
Daarnaast wordt gebruik gemaakt van de mogelijkheid om het isoweeknummer te bepalen dat vanaf versie 2008 beschikbaar is.
(DATEPART(ISOWK,date)
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[dimTime]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[dimTime]
-- set first day = 1 monday, default (us standard) = 7 sunday
SET DATEFIRST 1;
--Use recursion in this common table expression
WITH cteDates AS
(
SELECT CAST ('2005-01-01' AS DATETIME) Date --Start Date
UNION ALL
SELECT Date + 1
FROM cteDates
WHERE Date + 1 < = '2015-12-31' --End date
)
SELECT
[DateKey] = YEAR(date)*10000+MONTH(date)*100+DAY(date)
,[Date]
,[Year]= YEAR (date)
,[Quarter] = DATEPART ( qq, date)
,[YearQuarter]= CAST(YEAR(date) AS VARCHAR(4)) + '-Q' + CAST(DATEPART ( qq, date) AS VARCHAR(1)) --"2010-Q1"-
,[Month]= MONTH (date)
,[YearMonth]=YEAR(Date)*100+MONTH(Date)
,[MonthName] =DATENAME (mm, date)
,[ShortMonthName]=LEFT ( DATENAME (mm, date), 3)
--,[Week]=DATEPART (wk, Date)
,[Week]= DATEPART (ISOWK, Date) --use user function isoweek for sql 2005
,[DayInYear]=DATEPART (dy, date)
,[DayInMonth]=DAY (date)
,[DayName]=DATENAME (dw, date)
,[ShortDayName]=LEFT (DATENAME (dw, date), 3)
,[YYYYMMDD] =CONVERT(VARCHAR(8),date,112)
,[YYYY/MM/DD] =CONVERT(VARCHAR(10),date,111)
,LastDayInMonth=DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,date)+1,0))
INTO dimTime
FROM cteDates
OPTION (MAXRECURSION 0)
