Van draaitabel naar datamodel: denken in tabellen

Wie draaitabellen kent, kent de helft van een datamodel. Een draaitabel is al een klein model: je kiest velden, en Excel rekent. Het verschil is dat een draaitabel op één tabel werkt, en een model op meerdere tabellen met relaties ertussen. Wie die stap maakt, hoeft geen VERT.ZOEKEN meer te gebruiken, en krijgt berekeningen die overal hetzelfde werken.

In dit artikel lees je hoe je van één groot tabblad naar losse tabellen gaat, wat relaties en measures zijn, en hoe je in Excel al kunt oefenen. Waar dit past in de overstap, staat in de gids van Excel naar Power BI.

Wat je al kent

Een draaitabel doet drie dingen die een model ook doet. Je kiest wat je wilt zien, zoals omzet per maand. Je kiest hoe je het wilt opsplitsen, zoals per kostenplaats. En je filtert met slicers. Excel rekent de rest.

Dat is precies hoe een rapport in Power BI werkt. Je sleept velden naar een visual, en het model rekent. Wie een draaitabel kan bouwen, kan een rapport bouwen. De nieuwe stap zit in wat eronder ligt.

Eén groot tabblad

Voor een draaitabel heb je één tabel nodig. Daarom zetten we in Excel alles in één tabblad. De boekingen uit het grootboek, met daarnaast een kolom met de rubriek, opgehaald met VERT.ZOEKEN uit het rekeningschema. En een kolom met de naam van de kostenplaats, opgehaald uit een ander tabblad. En de begroting, die er eigenlijk niet in past omdat die per maand is en niet per boeking.

Dat werkt, tot het groot wordt. Twintig maanden grootboek met elk een paar duizend regels en drie opzoekkolommen maakt het bestand traag. En elke opzoekformule is een plek waar het mis kan gaan.

Losse tabellen met relaties

In een model zet je elke soort gegevens in een eigen tabel. De boekingen in de tabel Grootboek. Het rekeningschema in de tabel Rekening. De kostenplaatsen in de tabel Kostenplaats. Daartussen leg je relaties: het rekeningnummer in het grootboek hoort bij het rekeningnummer in het rekeningschema.

Door die relatie hoef je de rubriek niet meer op te halen. Kies je in een rapport de rubriek Omzet, dan filtert het model het grootboek op alle rekeningen met die rubriek. De koppeling doet wat het VERT.ZOEKEN deed, maar dan één keer, voor alle rapporten tegelijk.

En er komt iets bij wat met één tabel niet kon. De begroting staat per maand en per kostenplaats, het grootboek per boeking. Als allebei een relatie hebben met dezelfde tabel Datum en dezelfde tabel Kostenplaats, kun je ze naast elkaar zetten. Kies je kostenplaats utiliteit, dan filtert die keuze het grootboek én de begroting.

Zo ziet dat er bij Noordkade uit

Het model van Noordkade Techniek, het fictieve installatiebedrijf uit de demo, heeft precies die opbouw.

In het midden staan de feitentabellen, met wat er gebeurde: het grootboek, de begroting en de fte. Eromheen staan de dimensies, met de manier waarop je kijkt: datum, kostenplaats, rekening en rubriek. Deze vorm heet een sterschema. Meer daarover in de gids over het Power BI-datamodel.

Een slicer die over tabellen heen werkt

In Excel werkt een slicer op de draaitabellen die op dezelfde gegevens zijn gebouwd. Wil je de begroting en de realisatie met één slicer filteren, dan moeten ze in dezelfde tabel staan, of moet je twee slicers met de hand gelijk zetten.

In een model is dat vanzelf geregeld. De slicer zit op de tabel Kostenplaats. Omdat zowel het grootboek als de begroting een relatie met die tabel hebben, filtert één klik op utiliteit beide tegelijk. Hetzelfde geldt voor de maand: één slicer op de datumtabel filtert realisatie, begroting en fte. Dat is het moment waarop de meeste Excel-specialisten zien waarom het model de moeite waard is.

Van berekend veld naar measure

In een draaitabel maak je soms een berekend veld, bijvoorbeeld de brutomarge als omzet min kostprijs. Dat berekende veld hoort bij die ene draaitabel. Maak je een tweede draaitabel, dan maak je het opnieuw, en misschien net anders.

In een model heet zo’n berekening een measure. Je schrijft hem één keer, in de taal DAX:

Brutomarge % = DIVIDE ( [Brutomarge], [Omzet] )

Daarna werkt hij in elk rapport, in elke visual, per maand, per kostenplaats en per project. Iedereen rekent met dezelfde formule. Dat is het grootste verschil met Excel, en de reden dat cijfers in een model overal kloppen.

Oefenen in Excel

Je hoeft niet meteen naar Power BI om dit te leren. Excel heeft een eigen datamodel, ook wel Power Pivot genoemd. Zet je tabellen als opgemaakte tabel in Excel, voeg ze toe aan het datamodel en leg de relaties. Daarna bouw je een draaitabel op het datamodel in plaats van op één tabel. Je ziet dan velden uit meerdere tabellen in dezelfde lijst.

Het is dezelfde techniek als in Power BI. Wie het in Excel onder de knie heeft, herkent alles. Het verschil zit in delen en verversen. Een Excel-bestand met een datamodel blijft een bestand. In Power BI publiceer je het model één keer, en lezen alle rapporten eruit.

Drie dingen om af te leren

• Alles in één tabel willen. Het voelt veilig omdat je alles ziet. In een model is het juist veiliger om elke tabel apart te houden.
• VERT.ZOEKEN. Elke opzoekformule wordt een relatie. Als je merkt dat je iets wilt opzoeken, is dat het signaal voor een relatie.
• Rekenen in de rij. In Excel maak je vaak een extra kolom met een berekening per regel. In een model reken je meestal in een measure, op het moment dat je het nodig hebt.

Wat ik vaak zie misgaan

• Het grote tabblad overzetten naar Power BI. Dan heb je een model met één tabel, en alle beperkingen van Excel in een ander programma.
• Relaties in beide richtingen. Het lijkt handig, maar het maakt filters onvoorspelbaar. Laat relaties lopen van de dimensie naar de feiten, in één richting.
• Berekende kolommen voor alles. Wie elke berekening als kolom maakt, krijgt een groot en traag model. Gebruik measures voor alles wat je optelt of deelt.

Wat je nu kunt doen

1. Pak je grootste Excel-tabblad. Tel de kolommen die met VERT.ZOEKEN worden opgehaald. Elke kolom is een kandidaat voor een eigen tabel.
2. Bouw in Excel een datamodel met twee tabellen en één relatie, en maak er een draaitabel op.
3. Kijk hoe het in Power BI werkt. In de demo filter je op kostenplaats en zie je grootboek en begroting tegelijk meebewegen.