Hur man genererar en datumdimension i SQL server med ett rekursivt CTE uttryck
Datumdimensionen
Kanske är datumdimensionen den vanligast förekommande dimensionen i en stjärnmodell. Till skillnad från de flesta övriga dimensioner härstammar inte dimensionsmedlemmarna från ett källsystem. Medlemmarna är givna utifrån den gregorianska tideräkning som används i vår del av världen. Följaktligen är dimensionens utseende snarlikt mellan olika stjärnmodeller. Många BI-utvecklare anser därför att det smidigaste sättet att skapa datumdimensionstabellen är att kopiera den från en annan, redan existerande, stjärnmodell. Nedan tittar vi på ett alternativ till kopierametoden, en metod där datumdimensionen genereras i T-SQL kod.
CTE
Införandet av CTE´s i 2005 versionen av SQL server innebar att vi slapp ofoget med subselects och att vi fick bättre strukturerad och mer lättläst kod. Med CTE´s kom också möjligheten att skriva rekursiva uttryck. Med rekursiva CTE uttryck är det möjligt att generera rader utan att referera till en tabell.
Följande enkla kodsnutt använder ett rekursivt CTE uttryck för att skapa en datumdimensionstabell.
DECLARE @tDateDay AS DATE = > ’1990-01-01′;
WITH dimDate_CTE AS (
SELECT dateDay = @tDateDay
UNION ALL
SELECT dateDay = DATEADD(d,1,dateDay)
FROM dimDate_CTE
WHERE dateDay<= ’2019-12-31′)
SELECT
dimDateKey = ROW_NUMBER() OVER(ORDER BY dateDay),
dateID =CONVERT(INT,CONVERT(NVARCHAR(8),dateDay,112)),
dateDay = dateDay,
dateDayOfWeek = CONVERT(SMALLDATETIME,DATEPART(DW,dateDay)),
dateWeekNo = CONVERT(SMALLINT,DATEPART(WK,dateDay)),
dateMonthNo = CONVERT(SMALLINT,DATEPART(M,dateDay)),
dateQuarter = CONVERT(SMALLINT,DATEPART(Q,dateDay)),
dateYear = CONVERT(INT,DATEPART(YY,dateday))
INTO dimDate
FROM dimDate_CTE
OPTION (MAXRECURSION 11000)
Vi vill börja generera medlemmar från 1990-01-01. Första raden kod tilldelar detta värde till en lokal variabel. Notera att första raden kod endast fungerar i 2008 versionen eller senare av SQL server. I tidigare versioner existerar inte DATE datatypen. Det går inte heller, i tidigare versioner, att tilldela en lokal variabel ett värde på samma rad som den deklareras.
CTE uttrycket görs rekursivt genom att den andra SELECT frågan i en UNION ALL refererar till den första. Med andra ord, CTE uttrycket anropar sig själv. För varje anrop adderar vi en dag till startdatumet.
WHERE satsen i den andra SELECT frågan är vårt terminerings villkor. Vi vill fortsätta att addera en dag till startdatumet till det når 2019-12-31.
I den nedre SELECT frågan, som refererar till CTE uttrycket, skapar vi de dimensions attribut som vi finner relevanta. Alla attributen baseras på datum. Genererade rader lagras i tabellen dimDate.
Ett rekursivt CTE uttryck får som default endast anropa sig själv 100 gånger, detta för att man inte av misstag skriver uttryck som anropar sig själv i oändlighet. Sista raden kod är en query hint som tillåter rekursiva CTE uttryck, tillhörande denna SELECT sats, att anropa sig själva som mest 11000 gånger. Eftersom vi vet att det är 10958 dagar mellan vårt startdatum och vårt slutdatum känns 11000 som en bra siffra.
