RSS Feed!

About me

I am a business intelligence developer working at Bizware since August 2015. In this blog I will present code samples, primarilly in tsql but also findings in all other parts of the Microsoft BI-stack.


Disclaimer: All code should be considered as "as-is", i.e. badly tested and not working until extensive testing have been made by anyone reading the code. :)


Happy coding!

Skapa en datumdimension för ett data warehouse (inkluderar skript)

Hej!

Grunden för all analys inom business intelligence är tid. Du vill se försäljning, lagervärden eller marknadsandelar under en vecka, ett år eller en månad. För att göra detta behöver du en tidsdimension till det fakta du vill analysera. Först skapar du en tabell som i mitt exempel heter dimDatum:


CREATE TABLE dbo.dimDatum (
datumNyckel INT NOT NULL,
dag DATETIME NOT NULL,
vecka INT NOT NULL,
manad INT NOT NULL,
kvartal INT NOT NULL,
ar INT NOT NULL,
dagimanad INT NOT NULL,
dagivecka INT NOT NULL,
arvecka INT NOT NULL,
manadmednoll NVARCHAR(2) NOT NULL,
dagnamn NVARCHAR(10) NOT NULL,
manadsnamn NVARCHAR(20) NOT NULL

CONSTRAINT PK_DimDatum_datumNyckel
PRIMARY KEY (datumNyckel)
)

I denna tabell finner du bland annat månad, år, dag och dagnamn, men också kolumnen manadmednoll, en kolumn som kan vara bra att ha när du ska “sätta ihop” datum åt olika håll.

För att populera denna tabell behöver vi loopa framåt eller bakåt. Jag har valt att skapa ett skript som går bakåt. Du kan ändra skriptet enkelt att gå framåt genom att sätta minusvärde på parametern @nrofdaysback.


DECLARE @CurrentDate AS DATETIME
DECLARE @NrOfDaysBack AS INT
DECLARE @CurrentDaysBack AS INT

SET @NrOfDaysBack = 2500
SET @CurrentDaysBack = 0

WHILE @CurrentDaysBack < @NrOfDaysBack BEGIN -- Ändra på minustecknet för att få värden för framtiden SET @CurrentDate = (SELECT DATEADD(D, -@CurrentDaysBack , GETDATE())) INSERT INTO dbo.dimDatum SELECT CAST(CONVERT(nvarchar(10), @CurrentDate, 112) as int) as datumNyckel, CAST(CONVERT(nvarchar(10), @CurrentDate, 112) as DATETIME) as Dag, DATEPART(WEEK,@CurrentDate) as vecka, MONTH(@CurrentDate) as manad, DATEPART(Q,@CurrentDate) as kvartal, YEAR(@CurrentDate) as ar, DATEPART(DD,@CurrentDate) as dagimanad, DATEPART(WEEKDAY,@CurrentDate) -1 as dagivecka, CASE WHEN DATEPART(WEEK,@CurrentDate) < 10 THEN CAST(YEAR(@CurrentDate) AS NVARCHAR(4)) + '0' + CAST(DATEPART(WEEK,@CurrentDate) AS NVARCHAR(2)) ELSE CAST(YEAR(@CurrentDate) AS NVARCHAR(4)) + CAST(DATEPART(WEEK,@CurrentDate) AS NVARCHAR(2)) END as arvecka, CASE WHEN MONTH(@CurrentDate) < 10 THEN '0' + CAST(MONTH(@CurrentDate) AS NVARCHAR(2)) ELSE CAST(MONTH(@CurrentDate) AS NVARCHAR(2)) END as manadmednoll, CASE WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 1 THEN 'Måndag' WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 2 THEN 'Tisdag' WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 3 THEN 'Onsdag' WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 4 THEN 'Torsdag' WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 5 THEN 'Fredag' WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 6 THEN 'Lördag' WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 0 THEN 'Söndag' END as dagnamn, CASE WHEN MONTH(@CurrentDate) = 1 THEN 'Januari' WHEN MONTH(@CurrentDate) = 2 THEN 'Februari' WHEN MONTH(@CurrentDate) = 3 THEN 'Mars' WHEN MONTH(@CurrentDate) = 4 THEN 'April' WHEN MONTH(@CurrentDate)= 5 THEN 'Maj' WHEN MONTH(@CurrentDate)= 6 THEN 'Juni' WHEN MONTH(@CurrentDate)= 7 THEN 'Juli' WHEN MONTH(@CurrentDate)= 8 THEN 'Augusti' WHEN MONTH(@CurrentDate)= 9 THEN 'September' WHEN MONTH(@CurrentDate)= 10 THEN 'Oktober' WHEN MONTH(@CurrentDate)= 11 THEN 'November' WHEN MONTH(@CurrentDate)= 12 THEN 'December' END as manadsnamn SET @CurrentDaysBack += 1 END

Och där har du din tidsdimension, redo att användas för att sortera fakta.

/R

Tags: ,

By | 9. May 2011 | Business Intelligence, Skripts | 6 Comments »

6 Comments

  1. perka says:

    Snyggt! Hur gör du i Date Dimensionen för att få en vecka som är “två veckor”? Tex. V.5 2011 börjar sista januari och slutar första veckan i februari.

    Jag tänker då främst på när man bygger en hierarki för datum.

  2. admin says:

    Jag tror det inte blir något problem, eftersom granulariteten (den minsta nivån) ligger på dag, så sköter sig det där självt. Du kan nog inte skapa en dimension i en kub där du har År-Månad-Vecka-Dag, då kommer det nog att bli lite knas. Var det så du mena? :)

  3. perka says:

    Du menade att man inte kan skapa en hierarki med den konstelationen va?

    Har två hierarkier, en år-månad-vecka-dag och en år-vecka-dag men blir samma beteende med dubbla veckor i bland…

  4. Hejsan

    Jag tog mig friheten att lägga upp ett eget script baserat på ditt. Några ändringar är att jag har satt svenska veckonummer och översatt objektnamnen till engelska..

  5. admin says:

    Hej Taher!

    Det gör du helt rätt i! Kul att det kommer till användning! :)

    /Robin

Leave a Reply