Een stermodel bouwen uit een platte export, in drie handelingen
Je krijgt van IT een export van 1.200 orderregels uit het ordersysteem. Je eerste reflex is om meteen een measure te schrijven. Op zich begrijp ik dat, alleen officieel moet dat pas je derde reflex zijn.
Op elke regel staan klantnaam, plaats, regio, productnaam en categorie opnieuw, elf kolommen in totaal. Niet eens allemaal nodig. Je manager wilde voor het kwartaaloverleg omzet per regio en categorie, toch? De export staat open en er is nog niets gebouwd.
De drie handelingen, in deze volgorde
Van de platte tabel maak je een stermodel, de orderregels in het midden en de rest eromheen.
Handeling één is de korrel vastleggen. De korrel van een tabel is wat één rij voorstelt. In deze export is dat één product op één order. Daar begin ik altijd mee, want elke latere keuze hangt eraan.
Handeling twee doe je in Power Query, de laag waar je data ophaalt en klaarzet. Alles wat over de klant gaat krijgt een eigen tabel zonder dubbele rijen, en alles wat over het product gaat ook. Klantnummer en productcode blijven in de orderregels staan, want daarlangs lopen de koppelingen terug naar de orderregels. Dat scheelt een hoop data en dus opslag. Microsoft schrijft dat een export of data-extract meestal plat is en dat Power Query de plek is om zo’n bron in meerdere tabellen te splitsen.
Handeling drie is de measure, de berekening die pas rekent op het moment dat iemand naar het rapport kijkt. Dan eindelijk.
Wat er na handeling twee overblijft
Klanten heeft nu 85 rijen, één per klant, met naam, plaats en regio. Producten heeft 60 rijen in 6 categorieën. Orderregels houdt 1.200 rijen, met nog zes van de elf kolommen: ordernummer, orderdatum, klantnummer, productcode, aantal en bedrag.
De measure is één regel:
Omzet = SUM ( Orderregels[Bedrag] )
SUM telt de bedragen op binnen de filters van dat moment. Zet je regio in een matrix, de kruistabel in je rapport, dan loopt het filter via de relatie op klantnummer naar de orderregels. Je measure noemt regio nergens. Dat werkt omdat de klanttabel filtert en groepeert, en omdat de orderregels optellen. Dezelfde measure geeft ook omzet per categorie, zonder dat je hem aanpast.
Dan komen de verzendkosten erbij
Door een update aan je ERP staat er € 6,95 verzendkosten per order, en in de export komt dat bedrag op elke regel van de order terug. Je manager wil nu het kwartaaltotaal weten, met verzendkosten. Met een measure meteen op de export telt SUM 1.200 keer € 6,95, dus € 8.340. Maar het waren 400 orders. Een order van drie regels telt drie keer mee.
De korrelvraag uit handeling één had dit gevangen. Dit bedrag hoort bij een hele order, en de korrel hier is een orderregel. Je weet nu dus dat je handeling twee nog een keer moet doen, met een eigen tabel van één rij per order. Die tabel heeft 400 rijen, en dezelfde SUM over verzendkosten telt daar mooi op € 2.780.
Omzet per regio klopte op de platte tabel trouwens ook. Het verschil zit in kolommen die over iets anders gaan dan één rij. Dat zie je pas als je de korrel hebt benoemd.
De vraag voor je eerste measure
Wat is één rij in deze export, en welke kolommen gaan over iets anders dan die rij? Past een kolom niet op de korrel, dan krijgt hij in Power Query (of met SQL in je data warehouse) een eigen tabel.
Wat je morgen doet
Sorteer je eigen export op ordernummer en zoek een bedrag dat per order op elke regel terugkomt. Dat is jouw kolom verzendkosten. Welke was dat bij jou? Schrijf in één zin op wat een rij in jouw export voorstelt en kijk welke kolommen daar niet bij horen. Wil je je eigen export samen met mij langs deze drie handelingen leggen? Ik denk graag met je mee, zie trainerbjorn.nl.