Het sterschema: waarom je model feiten en dimensies scheidt
Een datamodel werkt pas als feiten en dimensies gescheiden zijn. Wat een sterschema is, hoe relaties lopen en welke fouten je rapport ongemerkt breken.
De meeste rapporten beginnen bij één platte export. Eén rij per verkoop, met de klantnaam, het product, de categorie en de vestiging allemaal in dezelfde tabel. Dat werkt, tot de tweede vraag.
In het artikel over het datamodel schreef ik dat het model het eigenlijke werk is. Dit artikel gaat over de vorm die dat model hoort te hebben. Voor bijna elk Power BI-model is dat dezelfde vorm: een sterschema.
Twee soorten tabellen
Een sterschema kent twee soorten tabellen, en het hele idee is dat je ze uit elkaar houdt.
Feitentabellen bevatten gebeurtenissen: wat er gebeurd is. Een factuurlijn, een betaling, een voorraadtelling. Veel rijen, vooral getallen en sleutels.
Dimensietabellen bevatten de dingen waardoor je kijkt: datum, product, klant, vestiging, kanaal. Weinig rijen, vooral beschrijvingen.
| Feitentabel | Dimensietabel | |
|---|---|---|
| Bevat | Gebeurtenissen | Beschrijvingen |
| Aantal rijen | Veel, groeit elke dag | Weinig, groeit traag |
| Kolommen | Sleutels en bedragen | Namen, categorieën, kenmerken |
| Voorbeeld | fct_factuurlijn | dim_product, dim_datum |
| Wat je ermee doet | Optellen | Filteren en groeperen |
De ster
In een model dat ik bouwde voor een retailer met meerdere kanalen, staan de factuurlijnen in het midden, in fct_factuurlijn. Daarrond staan de dimensies: datum, product, klant, kanaal, vestiging, rekening en dagboek.
Elke dimensie raakt de feitentabel precies één keer. Teken het uit en je krijgt een middelpunt met punten errond. Vandaar de naam.
Waarom niet één platte tabel
Herhaling. In een platte tabel staat de productcategorie op elke lijn waarop dat product verkocht werd. Hernoem je een categorie, dan veranderen er duizenden rijen. In een dimensie is het één rij.
Meerdere feiten, dezelfde dimensies. In datzelfde model staan vier feitentabellen: factuurlijnen, analytische lijnen, boekingen met een kasdatum, en een budget. Ze delen dezelfde datum- en kanaaldimensie. Eén slicer op kanaal filtert ze alle vier tegelijk. Dat kan alleen omdat de dimensie gedeeld is. Het budget is een mooi voorbeeld: dat leeft per maand en per kanaal, een heel ander detailniveau dan een factuurlijn, en het hangt toch aan dezelfde dimensies. Waar dat budget vandaan komt, staat in het artikel over budgetten.
DAX werkt zoals het bedoeld is. Filters lopen van de dimensie naar het feit. Bijna elke DAX-functie gaat uit van die vorm. Wie op een platte tabel rekent, vecht tegen de taal. Zie de les over DAX.
Grootte en snelheid. Power BI comprimeert per kolom. Een korte sleutel comprimeert goed, een lange tekst die een miljoen keer herhaald wordt niet.
De relaties
Een relatie in een sterschema is bijna altijd één-op-veel: één rij in de dimensie, veel in het feit. Eén product, veel factuurlijnen.
Twee regels maken dat werkend. De sleutel in de dimensie is uniek. En de filter loopt in één richting, van de dimensie naar het feit. Een relatie in twee richtingen is de uitzondering, niet de standaard.
De korrel
Voor je een feitentabel bouwt, beslis je wat één rij is. Eén factuurlijn, niet één factuur. Dat heet de korrel.
Korrels door elkaar halen is een van de meest voorkomende fouten. Zet het factuurtotaal op elke factuurlijn, en wie die kolom optelt, telt het totaal zo vaak als er lijnen zijn.
In dat model zijn factuurlijnen en boekingen daarom twee aparte feitentabellen. De lijnen hebben één rij per lijn, de boekingen één rij per document met de kasdatum. Twee korrels, twee tabellen, dezelfde dimensies.
De valkuilen
Een sleutel die niet uniek is. Staat een klant twee keer in je klantendimensie, dan weigert Power BI de relatie of maakt het er een veel-op-veel van, en tellen rijen dubbel. Het is hetzelfde probleem als de JOIN in het artikel over SQL.
Sleutels die ontbreken. Een feit met een productnummer dat niet in de productdimensie staat, belandt in je rapport onder een leeg label. Geef elke dimensie een rij "Onbekend", en zet er een controle-measure bij die telt hoeveel feiten daar terechtkomen. In dat model had precies één factuurlijn geen vestiging. Het bleek een testfactuur te zijn, en we vonden ze dankzij die controle-measure.
Feiten aan elkaar koppelen. Een relatie tussen twee feitentabellen loopt bijna altijd mis. Verbind ze via de dimensies die ze delen.
Een sneeuwvlok. Product, categorie en productgroep als drie aparte tabellen in een ketting. Het oogt netjes, maar het voegt relaties toe en maakt filters trager. Vouw ze samen tot één productdimensie.
Meerdere datums. Een boeking heeft een factuurdatum en een kasdatum. Er kan maar één relatie met de kalender actief zijn. De andere maak je inactief, en je zet ze in DAX aan waar je ze nodig hebt:
Ontvangen =
CALCULATE (
SUM ( fct_boeking[kasbedrag] ),
USERELATIONSHIP ( fct_boeking[kasdatum], dim_datum[datum] )
)Geen echte datumtabel. De kalender is een dimensie op zich, met elke dag en zonder gaten. Hoe dat toch misloopt, staat in het artikel over lege measures.
Hoe je begint
Schrijf de vragen op die je wil beantwoorden. Neem "omzet per vestiging per maand". De dingen waardoor je kijkt, vestiging en maand, zijn dimensies. Het getal dat je optelt, omzet, zit in een feit.
Begin met één feitentabel en drie dimensies. Een nieuw feit voeg je later toe, en het klikt meteen vast aan de dimensies die er al zijn.
De stelling
Een sterschema is geen techniek voor grote bedrijven. Het is de vorm waarin een rapport vragen kan beantwoorden die je vandaag nog niet kent.