Home / T-SQL / Centraliseren van KPI’s in je Datawarehouse

Centraliseren van KPI’s in je Datawarehouse

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!

About Ronald Kraijesteijn

Microsoft SQL Server and Business Intelligence consultant, MCITP Certified. If you need a BI specialist for a freelance job, please contact me!

Check Also

Split postal code range into single rows

It’s been a while since my last article. I’ve been busy working on a lot …

3 comments

  1. Ik zou graag de code van je sql functie willen zien waarin alle de Kpi’s formulas staat.

    • Ronald Kraijesteijn

      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

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *