SQLBlog Nederland Business Intelligence & Datawarehousing
Deze blog behandelt de periode dimensie. Deze lijkt sterk op de datum dimensie die eerder is besproken (en onmisbaar is in ieder datawarehouse) maar zijn functie is net iets anders. De kracht van het gebruik van deze tabel wordt beschreven met wat voorbeelden van AdventureWorks.
Wat is een periode dimensie?
Bij stermodellen is het gebruikelijk dat de mate van detaillering, ook wel bekend als “Grain”, binnen een feit/fact is vastgelegd op datum, het laagste level binnen de tijd dat opgeslagen wordt (tijd kan ook nog maar is nu niet relevant). Stel dat een fact een grain heeft op weekniveau. Je zult dan gaan proberen een koppeling te krijgen met de datumdimensie. Dit kun je doen door het weeknummer om te zetten naar een DatumID (SELECT 201001 * 100 + 01). Dit werkt veruit het eenvoudigst. Bij het rapporteren van managementinformatie werkt dit prima voor transactiefeiten die aggregeerbaar zijn. De som van de omzet van alles bestellingen over de dagen binnen een maand is immers de omzet van een maand. Maar wat te doen als je feiten moet rapporteren die je niet makkelijk kunt optellen? Met andere woorden, de totalen van maand 1 + 2 + 3 zijn niet de totalen van kwartaal 1. Denk hierbij aan “Aantal werkende mensen per week”. Dit is een feit dat niet op te tellen is. Toch wil je deze informatie in rapportages kunnen tonen op kwartaal, maand, jaar, week, etc. en bij voorkeur ook nog in een trendrapportage. De oplossing is een periode dimensie maken in combinatie met een snapshotfeit. Dit artikel geeft de voordelen, de werking en een AdventureWorks voorbeeld. Als bijlage van dit artikel zijn de scripts te vinden om een Periode-dimensie te bouwen.
Opbouw Periode dimensie
Een Periode-dimensie bevat voor iedere periode een record en kun je toepassen in je bijvoorbeeld je Kimball datawarehouse-model. Ieder periode maakt deel uit van een periode-grootheid. Een periodegrootheid kan een maand of een week zijn. De periode-dimensie bevat in mijn geval 102 records voor het jaar 2010. Voor weken zijn er 52 records (of 53), voor jaar een record, voor maand 12 records, etc.
Verder zijn de volgende kolommen belangrijk:
PeriodeID: unieke sleutel
Jaar: het jaar waar de periode in valt
PeriodeNummer: jaar + periodenummer
BeginDatumID: eerste dag van de periode
EindDatumID: laatste dag van de periode
PeriodeGrootheidID: grootheid van de periode
VorigJaar1PeriodeID, PeriodeID van de periode een jaar terug
VorigePeriode01PeriodeID: PeriodeID van de vorige periode
VorigePeriode02PeriodeID: PeriodeID van twee periodes terug
VorigePeriode03PeriodeID: PeriodeID van drie periodes terug
VorigePeriode04PeriodeID: PeriodeID van vier periodes terug
VorigePeriode05PeriodeID: PeriodeID van vijf periodes terug
VorigePeriode06PeriodeID: PeriodeID van zes periodes terug
PeriodeNummer is handig om later het resultaat van een periode-feit aan een ander feit te koppelen die bijvoorbeeld op week is geaggregeerd omdat PeriodeNummer hetzelfde is als WeekNummer. De Vorige ID’s zijn uitermate handig om later vergelijkingen te doen ten opzichte van de vorige periode. Dit kun je uitbreiden met meer velden (functie bijgesloten) met bijvoorbeeld deze SQL:
UPDATE DimPeriode SET VorigePeriode01PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -1) , VorigePeriode02PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -2) , VorigePeriode03PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -3) , VorigePeriode04PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -4) , VorigePeriode05PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -5) , VorigePeriode06PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -6); |
Sleutelopbouw periode dimensie
De primaire sleutel van de tabel is PeriodeID. Deze is uniek. De sleutel is opgebouwd met bepaalde logica: Jaar + PeriodeGrootheid + PeriodeNummer.
Voorbeeld Week 12-2010: 2010112
Voorbeeld Maand 8-2010: 2010208
Voor iedere periodegrootheid en iedere periode is er een record aanwezig. De periodegrootheden die onderkent zijn:
- Week
- Maand obv. Kalender
- Kwartaal obv. Kalender
- Periode obv. 444 Weekverdeling
- Maand obv. 445 Weekverdeling
- Kwartaal obv. 445 Weekverdeling
- Jaar obv. Kalender
- Jaar obv. Weekverdeling
- Dag
Praktijkvoorbeeld periode dimensie
De periode-dimensie kun je combineren met een organisatiestructuur. Zo krijg je combinatie van alle periodes en alle organisatie-eenheden. Vervolgens kun je aan deze combinaties waarden hangen, bijvoorbeeld KPI’s.
Business-vraagstukken die je kunt beantwoorden:
- Aantal medewerkers die bij ons bedrijf werken. De gebruiker moet met behulp van een parameter kunnen aanpassen hoe hij de data wilt bekijken. Hij moet dit per week, per maand, per kwartaal en per jaar kunnen bekijken. Je zult nu de meetwaarde “aantal medewerkers” moeten gaan bepalen op iedere periodegrootheid die men wenst.
- Ranking; hoe verhoud mijn afdeling zicht ten opzichte van andere afdelingen (omzet kpi delen door aantal fte)
- Omzetgegevens afzetten tegen een periode een jaar terug: je hoeft geen grote tabellen meer door te worstelen en SUM functies te gebruiken, je slaat de omzetgegevens in alle mogelijke combinaties op in een KPI tabel die gebruik maakt van DimPeriode
- Trendlijnen laten zien: trendlijnen performen vaak slecht in combinatie met SQL (zonder OLAP). Verschillende Group By en SUM functies verstoren de performance. Met een KPI tabel in combinatie met DimPeriode heb je dit probleem minder.
Voorwaarde voor een juiste werking van dit mechanisme is dat je redelijk complexe SQL nodig hebt om de nieuwe feiten te gaan vullen. Dit hoeft niet altijd zo te zijn. Om de werking en de kracht van de tabel te demonstreren geef ik enkele voorbeelden van SQL op AdventureWorksDWH.
Vraagstuk: aantal klanten die een internetsale hebben geplaatst, opgesplitst in weken, maanden en jaar.
SELECT DP.PeriodeID , SUM(FIS.IsOrder) AS AantalBestellingen FROM ( SELECT CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) + 60000 AS OrderDatumID , 1 AS IsOrder FROM FACTINTERNETSALES AS FIS INNER JOIN DIMTIME AS DT ON FIS.ORDERDATEKEY = DT.TIMEKEY WHERE CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) > 20040000 ) AS FIS --Hier is de truuk, bepaal in welke periodes een bestelling valt! INNER JOIN DimPeriode AS DP ON FIS.OrderDatumID BETWEEN DP.BeginDatumID AND DP.EindDatumID INNER JOIN DimPeriodeGrootheid AS DPG ON DP.PeriodeGrootheidID = DPG.PeriodeGrootheidID GROUP BY DP.PeriodeID ORDER BY PeriodeID; |
Wat je in de afbeeldingen ziet zijn alle periodes in 2010 met het aantal klanten die een internetsale hebben geplaatst. Zo heb ik er twee uitgepikt om toe te lichten hoe het werkt.
De uitkomst van deze SQL kun je in een nieuwe tabel stoppen, bijvoorbeeld FactPeriodeKPI. Een Measure binnen FactPeriodeKPI wordt dan AantalInternetBestellingen. Op deze manier kun je diverse KPI’s / Measures opslaan.
Vraagstuk: aantal unieke klanten per sales territory, afgezet over de tijd
Dit is een leuke een. Dit is een typische KPI die niet aggregeerbaar is. Het aantal klanten binnen een week kun je niet optellen om ze op maand te zien. Je moet deze waarden dus per periode uitrekenen. De truuk is om te kijken welke klanten een bestelling hebben per datum. Als een klant op een dag twee bestellingen heeft is dit niet relevant voor de telling, het gaat immers om het aantal unieke klanten per dag. Vervolgens ga je dit koppelen aan de periodes door te bepalen of een datum in een periode valt. Daar overheen doe je een COUNT DISTINCT om het aantal unieke klanten te bepalen. Het wordt helemaal een feest als een klantstructuur ook hierarchieen bevat. Een klant is alleen een klant op het hoogste niveau (bijv concern), alle onderliggende nummers moeten niet als uniek worden geteld. Dit laat ik even buiten de scope. Op naar de SQL:
SELECT DP.PeriodeID , DPG.Naam , DP.PeriodeNummer , COUNT(DISTINCT FIS.CUSTOMERKEY) AS AantalUniekeKlanten FROM ( -- Aantal Unieke klanten per orderdatum SELECT DISTINCT CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) + 60000 AS OrderDatumID , DC.CUSTOMERKEY FROM FACTINTERNETSALES AS FIS INNER JOIN DIMTIME AS DT ON FIS.ORDERDATEKEY = DT.TIMEKEY INNER JOIN DIMCUSTOMER AS DC ON FIS.CUSTOMERKEY = DC.CUSTOMERKEY WHERE CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) > 20040000 ) AS FIS --Hier is de truuk, bepaal in welke periodes een bestelling valt! INNER JOIN DimPeriode AS DP ON FIS.OrderDatumID BETWEEN DP.BeginDatumID AND DP.EindDatumID INNER JOIN DimPeriodeGrootheid AS DPG ON DP.PeriodeGrootheidID = DPG.PeriodeGrootheidID GROUP BY DP.PeriodeID , DP.PeriodeNummer , DPG.Naam ORDER BY PeriodeID; |
Bovenstaand resultaat laat de kracht van DimPeriode zien. Zoals je ziet zijn er in de eerste drie maanden een aantal unieke klanten. Daaronder zie je het kwartaalresultaat. Het kwartaalresultaat zijn het aantal unieke klanten in het eerste kwartaal. Dit is geen som van de eerste drie waarden want dan zou je op 5392 klanten uitkomen.
De laatste die ik behandel is het aantal verloren klanten T.O.V. Periode -1, -2, -4, -4. Dit doe ik binnenkort in dit artikel als ik meer tijd heb.
