SQLBlog Nederland Business Intelligence & Datawarehousing
Je maakt schitterende rapportages met de mooiste cijfers, omzetten, productiviteit en KPI’s. Door de tijd komen er steeds meer rapportages bij binnen de organisatie. Tot iemand ineens opmerkt dat de waarden van KPI’s niet gelijk zijn tussen de verschillende rapportages. Oops! In dit artikel bespreek ik een manier om te probleem het hoofd te bieden. Het is een best-practice die is ontstaan door veel ervaring met definities en KPI’s.
Veel mensen programmeren hun formules binnen hun rapportage. Een groot nadeel hiervan is, buiten performance om, dat de formules niet centraal zijn opgeslagen. Stel dat de definitie van een bepaalde formule door de tijd wijzigt (en dit gebeurt veel) dan moet je alle rapportages langs om de formules aan te passen! Een hels karwei. Een methode om dit te voorkomen is door de formules te centraliseren. Hoe dat moet leg ik hier uit.
Ik heb een Datwarehouse met historie (een soort Data Vault) en daarachter een Datamart/stermodel waarin alle business logica is geprogrammeerd. In de laatste zijn de verschillende feiten en dimensies te vinden. Wat ik wil is een centrale plek voor mijn KPI definities. Wat ik graag doe is werken met een SQL-functie waarin ik alle KPI formules programmeer. Iedere KPI krijgt een eigen nummer en een definitie. Vervolgens kan ik de functie met een begin- en einddatum en een KPI-ID. De functie retourneert dan het gewenste resultaat. Voorwaarde is wel dat de grain van de kpi’s gelijk is.
Schematisch ziet deze manier er zo uit:
Links vind je het Datawarehouse en rechts de datamart. Binnen de datamart vind je verschillende feiten. De functie is opgeslagen in de datamart en berekent zijn resultaten op de feiten. Het is dus belangrijk dat alle feiten en dimensie gevuld zijn voordat de functie werkt. Achter de functie vind je de FactKPI. Hierin slaan we het resultaat op van de functie.
Het idee is dat je in de ETL eerst het DWH bijwerkt, dan de Datamart en als laatste stap van het bijwerken van de datamart de KPI tabel.
Handig? Jazeker. Alle formules staan in de functie. Als er een kpi formule wijzigt pas je de functie aan. Dit is veel minder werk dan alle rapporten langslopen. Alle rapporten maken namelijk gebruik van de KPI tabel en niet meer van de “normale” facttables (indien mogelijk).
Voor het vullen van de KPI tabel roep je meerdere keren de functie aan, per KPI, en merge dit resultaat aan elkaar met een UNION.
De grain van de KPI tabel is in mijn geval: DatumID, MedewerkerID, KostenPlaatsID. Daarna volgen er allemaal kolommen met de berekende KPI’s.
Ik moet zeggen, dit werkt erg goed in de praktijk. Het voorkomt veel gedoe en tijd. Zorg ervoor de er in de functie die je gebruikt genoeg commentaar staat zodat helder is wat de definitie is van de KPI’s.
Mocht je ook een goede methode hebben om hiermee om te gaan, deel je ervaring!
Ik zou graag de code van je sql functie willen zien waarin alle de Kpi’s formulas staat.
Wat je moet doen is een functie maken die mbv een startdate en enddate en kpiid een resultaat teruggeeft.
Als je vervolgens een KPI tabel wilt vullen kun je meerdere keren een SELECT uitvoeren op deze functie waarbij je het resultaat aan elkaar koppelt met een UNION. Het KpiID wat je meegeeft is dan elke keer anders.
Bijv.
SELECT DatumID, 1 AS KpiID, SUM(KpiWaarde) AS KpiWaarde
FROM dbo.KpiFunctie (20120101, 20121231, 1)
UNION ALL
SELECT DatumID, 200 AS KpiID, SUM(KpiWaarde) AS KpiWaarde
FROM dbo.KpiFunctie (20120101, 20121231, 200)
De inhoud van de functie kan er zo uitzien (verkorte weergave en 1 stukje ervan);
IF @KpiID = 10820
INSERT @MyResult
SELECT KostenPlaatsID, WerknemerID, CONVERT(VARCHAR,CASE WHEN DD.DagNummerVanWeek = 6 THEN DD.DatumWaarde – 1
WHEN DD.DagNummerVanWeek = 7 THEN DD.DatumWaarde – 2
ELSE DD.DatumWaarde END,112) AS DatumID,
10820 AS KpiID, SUM(AbsentieUren) AS KpiValue
FROM ps.FactAbsentie AS FA
INNER JOIN dbo.DimDatum AS DD ON FA.DatumID = DD.DatumId
INNER JOIN ps.DimAbsentieReden AS AR ON FA.AbsentieRedenID = AR.AbsentieRedenID
INNER JOIN ps.DimAbsentieStatus AS DAS ON FA.AbsentieStatusID = DAS.AbsentieStatusID
WHERE AR.AbsentieRedenCode = ’25’
AND DAS.AbsentieStatusCode = ‘1’
AND FA.DatumID BETWEEN @StartDatumID AND @EindDatumID
GROUP BY KostenPlaatsID, WerknemerID, DD.DagNummerVanWeek, DD.DatumWaarde
Erg bedankt! En ik ga het properen.