Het Power BI-datamodel: het hart van je rapportage

Wat is een semantisch model?

Een semantisch model is de laag tussen je bronnen en je rapporten. Er staan drie dingen in: de tabellen uit je bronnen, de relaties ertussen en de berekeningen, zoals omzet, brutomarge en resultaat ten opzichte van de begroting. In Power BI heette het tot eind 2023 een dataset.

Het woord semantisch zegt waar het om gaat: betekenis. Het model legt vast wat een getal betekent. Omzet is bij Noordkade het gefactureerde bedrag op de omzetrekeningen, zonder btw. Dat staat één keer in het model, en elk rapport gebruikt dezelfde berekening.

Daarom is het model het hart van een rapportage die klopt. Je kunt tien rapporten bouwen op één model, en ze tonen allemaal hetzelfde getal. Bouw je tien rapporten met elk hun eigen berekening, dan heb je tien kansen op een verschil.

Het sterschema: feiten en dimensies

Een goed model heeft een vaste vorm: het sterschema. In het midden staan de feitentabellen, met wat er gebeurde, zoals boekingen, uren of verkopen. Eromheen staan de dimensies, met de manier waarop je ernaar kijkt, zoals datum, kostenplaats, rekening of klant.

Zo ziet het model van de Noordkade-demo eruit.

Het voordeel zie je bij filteren. Kies je in een rapport kostenplaats utiliteit, dan filtert de dimensie Kostenplaats tegelijk het grootboek, de begroting en de fte. Realisatie, begroting en bezetting staan zo vanzelf naast elkaar, zonder dat iemand ze aan elkaar hoeft te knopen.

Twee regels houden het schema gezond. Feitentabellen zijn lang en smal: veel regels, weinig kolommen. Dimensies zijn kort en breed: één regel per kostenplaats of rekening, met alle beschrijvende kolommen. En relaties lopen van de dimensie naar de feiten, in één richting.

Measures: elke KPI één keer

De berekeningen in een model heten measures, en je schrijft ze in DAX. Een measure rekent altijd in de context van wat je in het rapport hebt gekozen: een maand, een kostenplaats, een project. Zo schrijf je de brutomarge één keer, en werkt hij overal.

Omzet = - CALCULATE ( [Saldo], Rubriek[Rubriek] = "Omzet" )
Kostprijs = CALCULATE ( [Saldo], Rubriek[Rubriek] = "Kostprijs" )
Brutomarge = [Omzet] - [Kostprijs]
Brutomarge % = DIVIDE ( [Brutomarge], [Omzet] )

Dit zijn measures uit de Noordkade-demo. Omzet heeft een min, omdat omzet in de boekhouding aan de creditkant staat. Dat soort afspraken leg je één keer vast, en niemand hoeft er meer over na te denken. DIVIDE voorkomt een foutmelding in een maand zonder omzet.

Zet alle measures in één aparte tabel, bij Noordkade heet die Kengetallen. Dan vindt iedereen ze op dezelfde plek. Het demomodel heeft er 27, van omzet tot een controle op dubbel ingeboekte facturen.

Een datumtabel voor vorig jaar en cumulatief

Bijna elke vraag in een rapportage gaat over tijd. Wat is het cumulatief tot nu toe? Hoe verhoudt dit zich tot vorig jaar? Daarvoor heeft het model een eigen datumtabel nodig, met één regel per dag, gemarkeerd als datumtabel.

Omzet cumulatief = TOTALYTD ( [Omzet], Datum[Date] )
Omzet vorig jaar = CALCULATE ( [Omzet], SAMEPERIODLASTYEAR ( Datum[Date] ) )

Met een datumtabel werken zulke functies voor elke measure. De begroting koppel je aan dezelfde datumtabel, ook al staat die per maand. Dan staan realisatie, begroting en vorig jaar vanzelf naast elkaar.

Het model controleert ook de data

Een model kan ook controleren. Het demomodel heeft measures die bij elke verversing laten zien waar de data niet deugt.

• Regels zonder kostenplaats. Boekingen die buiten elk afdelingsoverzicht vallen. Bij Noordkade gaat het om 3.130 euro.
• Mogelijk dubbel ingeboekt. Facturen van dezelfde leverancier met dezelfde omschrijving onder meer dan één boekstuk.
• Debet min credit. Moet altijd nul zijn. Is het dat niet, dan is de export onvolledig.

Zet die controles op een eigen pagina in het rapport. Dan ziet de controller na elke verversing in één oogopslag of de cijfers te vertrouwen zijn, voordat de directie ze ziet.

Vernieuwen zonder zorgen

Een model ververst volgens een schema, bijvoorbeeld elke nacht. Met een Pro-licentie kan dat tot acht keer per dag, op een Premium- of Fabric-capaciteit vaker. Voor een maandrapportage is één keer per nacht ruim genoeg.

Staan je bronnen in de cloud, zoals SharePoint of een boekhoudpakket met een koppeling, dan ververst het model zonder extra stappen. Staan ze op een server op kantoor of een netwerkschijf, dan heb je een gateway nodig: een klein programma dat de verbinding legt.

Mislukt een verversing, dan ligt het bijna altijd aan een van drie dingen: een bestand is verplaatst of hernoemd, een kolom in de bron heeft een andere naam gekregen, of de inloggegevens van de bron zijn verlopen. Zet daarom een melding aan bij een mislukte verversing, en spreek af wie hem oppakt.

Wie ziet wat

In een model leg je ook vast wie welke cijfers ziet. Met beveiliging op rijniveau ziet een afdelingshoofd alleen zijn eigen kostenplaats, en de directie alles. Uit hetzelfde model en hetzelfde rapport. Dat scheelt een rapport per afdeling, en dus een kopie per afdeling.

Bij gevoelige gegevens, zoals personeelsdata, kijk je ook naar wat je überhaupt inleest. Haal alleen op wat de rapportage nodig heeft, en waar het kan op het niveau van een team in plaats van per medewerker.

Licenties en kosten

Power BI kent drie soorten licenties. Welke je nodig hebt, hangt af van hoeveel mensen de rapporten bekijken.

Pro zit al in sommige Microsoft 365-pakketten, zoals E5. Kijk voor de actuele prijzen op de site van Microsoft, want die veranderen. De grootste kostenpost is meestal niet de licentie, maar het bouwen en onderhouden van het model.

Copilot en AI op je model

Copilot in Power BI beantwoordt vragen in gewone taal, op basis van je eigen semantische model en binnen je eigen Microsoft-omgeving. "Waarom is de marge in juni gedaald?" wordt dan een antwoord met een grafiek. Daarvoor is een betaalde Fabric-capaciteit nodig.

De kwaliteit van die antwoorden hangt bijna helemaal af van het model. Copilot leest de namen van tabellen, kolommen en measures, en de beschrijvingen die je erbij zet. Heet een measure "Measure 12", dan kan Copilot er weinig mee. Heet hij "Brutomarge %", met een beschrijving van wat er in de kostprijs zit, dan wel. Een goed model is dus ook de voorbereiding op AI.

Zelf gebruik ik AI bij het ontwerpen, schrijven van code en documenteren van modellen. De data blijft in de eigen omgeving van de klant, en wat AI maakt, controleer ik zelf, met afspraken die we vooraf vastleggen.

Wat ik vaak zie misgaan

• Eén platte tabel. Alles in één grote tabel, zoals in Excel. Het werkt tot er een tweede bron bijkomt, en dan rekenen de totalen verkeerd.
• Berekeningen in het rapport. Elke visual met zijn eigen formule. Dan heb je geen model, maar een verzameling losse berekeningen.
• Geen datumtabel. Vorig jaar en cumulatief worden dan per rapport opnieuw uitgevonden, elke keer net anders.
• Geen beschrijvingen. Na een jaar weet niemand meer wat "Realisatie aangepast 2" betekent. Copilot ook niet.

Wat je nu kunt doen

1. Open het model van je belangrijkste rapport. Is het een sterschema, of één platte tabel?
2. Tel de berekeningen die niet als measure in het model staan. Dat zijn de plekken waar verschillen ontstaan.
3. Geef je vijf belangrijkste measures een beschrijving. Het kost een kwartier, en je collega's en Copilot hebben er allebei wat aan. Wil je weten hoe je model ervoor staat? In een Blauwdruk kijk ik er in twee dagen naar, en ontwerpen we samen het model. Over de stap vanuit Excel lees je meer in de gids van Excel naar Power BI.