Aggregatietabel (aggregation table)

Wat is een aggregatietabel?

Een aggregatietabel is een samenvattingstabel die data op een grovere korrel bewaart dan de detailtabel erachter. In plaats van één rij per transactie houdt ze één rij per groep bij, bijvoorbeeld de totale omzet per productcategorie per maand. De totalen worden één keer uitgerekend en opgeslagen, zodat een query die alleen de samenvatting nodig heeft een handvol rijen leest in plaats van elke transactie te doorlopen.

Het gaat helemaal om snelheid. Ralph Kimball omschreef aggregaties als eenvoudige numerieke optellingen van atomaire data, puur gebouwd om queries sneller te maken, en merkte op dat ze sommige queries honderd of zelfs duizend keer sneller kunnen maken. Als een feitentabel twee miljard rijen heeft en een rapport alleen maandtotalen per regio vraagt, beantwoordt een aggregatietabel met een paar duizend rijen dat vrijwel meteen.

Zie het als de subtotalen onderaan een rekenblad. Je kan elke lijn opnieuw optellen telkens iemand het totaal vraagt, of je houdt dat totaal in een cel en leest het er zo af.

De korrel is het hele ontwerp

Een aggregatietabel wordt bepaald door haar korrel: wat één rij voorstelt. Je bouwt er een door het detail op te rollen, ofwel door een dimensie helemaal weg te laten, ofwel door een dimensie naar een hoger niveau te brengen, zoals dag naar maand of product naar categorie.

De keerzijde is dat een samenvatting alleen vragen op of boven haar eigen korrel kan beantwoorden. Een tabel samengevat tot maand kan je niet vertellen wat er op een dinsdag gebeurde, en een tabel samengevat tot categorie kan geen enkel product apart tonen. Heeft een rapport fijnere detail nodig dan het aggregaat bevat, dan moet de query terugvallen op de detailfeitentabel.

Daarom kan één opzet meerdere aggregaties op verschillende korrels dragen, elk gericht op een courant querypatroon. Kimball raadt aan om elke korrel in zijn eigen fysieke tabel te zetten in plaats van ze door elkaar te mengen.

Aggregatietabellen in Power BI

Power BI heeft dit patroon ingebouwd. In een groot model kan je de detailfeitentabel in DirectQuery-modus tegen de bron houden en er een kleinere aggregatietabel in import-modus naast zetten, die in het geheugen wordt gecached. Wanneer een visual alleen kolommen en een korrel nodig heeft die het aggregaat dekt, antwoordt de engine vanuit de snelle samenvatting in het geheugen. Heeft de visual fijnere detail nodig, dan valt Power BI stilletjes terug op de DirectQuery-feitentabel.

Een verkoopfeit van een miljard rijen kan een import-aggregaat hebben met maandtotalen per productcategorie en regio, dat maar tienduizenden rijen bevat. Een dashboard met de maandomzet per regio raakt die samenvatting; een gebruiker die doorklikt naar één dag en één product zakt door naar het detail. De omschakeling gebeurt vanzelf, dus wie het rapport bouwt en wie het bekijkt hoeft de tabel nooit met de hand te kiezen.

Aggregatietabel versus gematerialiseerde view

Een aggregatietabel en een gematerialiseerde view lossen hetzelfde probleem op, namelijk een vooraf berekend resultaat bewaren zodat lezen goedkoop is. Het verschil zit in wie het actueel houdt.

Een gematerialiseerde view wordt beheerd door de database: je definieert de query één keer, en de engine beheert het opgeslagen resultaat en de verversing, soms automatisch. Een zelfgebouwde aggregatietabel is van jou. Je schrijft de optelling in een gewone tabel in je datapijplijn, wat je volledige controle geeft over wanneer en hoe ze ververst, en je de taak geeft ze in de pas te houden met het detail. Een stille fout waardoor de samenvatting verouderd raakt, is dan ook aan jou om te vangen.

Waar moet je op letten bij het gebruik van een aggregatietabel

Veroudering is de echte kost. Een samenvatting is maar zo actueel als haar laatste herberekening. Ververst het detail doorheen de dag maar het aggregaat 's nachts, dan spreken de twee elkaar tegen, en gebruikers merken het wanneer een doorklik niet klopt met het cijfer erboven.

De totalen moeten sluiten. Een aggregatie is alleen betrouwbaar als het detail optellen hetzelfde antwoord geeft. Gemiddelden, unieke tellingen en andere niet-optelbare measures rollen niet op door gewoon op te tellen, dus een naïeve optelling ervan levert stil verkeerde cijfers op.

Meer aggregaten is niet altijd beter. Elke extra samenvatting is weer een tabel om te bouwen, te verversen en consistent te houden. Richt aggregaties op de querypatronen die echt pijn doen, in plaats van alles vooraf samen te vatten.

Stem de korrel af op echte rapporten. Een aggregaat op de verkeerde korrel wordt overgeslagen, dus je betaalt om het te onderhouden en raakt toch de detailtabel. Baseer de korrel op hoe mensen de data echt opdelen.