De liefdesverklaring van Power Pivot: relaties

Een serie blogs over Excel en Power BI.

BLOG – In deze series van blogs bespreek ik het Business Intelligence vijflagen model: delen, visualiseren, analyseren, voorberieden en verbinden. Vandaag gaan we verder met de derde laag: het analyseren. Het grootste verschil tussen Excel en Power Pivot of Power BI ligt in de relaties. De klassieke Excel versie kent namelijk geen relaties. Relaties staan niet alleen complexere modellen toe, maar bepalen ook de context van aggregaat berekeningen. 

Door Henk Vlootman, adviseur, trainer, spreker en auteur, en topspecialist op het gebied van Microsoft Power BI en Power Query.

Relaties tussen verschillende tabellen bepalen de context waarin een berekening leeft en daarmee het resultaat van die berekening. En dat maakt van Power Pivot of Power BI een filter programma. Zoals gezegd, Excel kent geen relaties en is daarmee een berekeningsprogramma. Wat betekent dit concreet? Het resultaat van een berekening in Excel is altijd hetzelfde. In Power Pivot is het resultaat van een berekening ook altijd hetzelfde, maar het gebruik van de verschillende tabellen, en daarmee de relaties, bepalen de context binnen visualisaties en dus wat je ziet. Het kan best zo zijn dat in een visualisatie het getal netjes over de filters wordt verdeeld, maar in een andere visualisatie – en daarmee een andere context – het resultaat leeg laat zien. De context vereist daarmee een geheel andere en nieuwe kwaliteit van de ontwikkelaar / bouwer.

Relaties in Power Pivot of Power BI werken hetzelfde: in het relatiescherm selecteer je de kolom waarmee je de relatie wilt leggen, je sleept deze naar de kolom in de tabel waarmee je de relatie wil maken en laat de muisknop los en hups, daar is de gewenste relatie (zie afbeelding 1). Nu zijn er wel een paar voorwaarden. Maar laten we eerst eens kijken wat een relatie toevoegt aan jouw model.

afb. 1.

Relaties zijn het smeermiddel tussen tabellen. Excel werkt met één enkele tabel (in klassiek Excel 'bereik' genoemd). Alle informatie staat hierin. Door de aard van de tabelopbouw bevat deze heel veel herhalingen. Kijk maar eens naar de Excel tabel in afbeelding 2. Het onderhoud van dit soort tabellen is altijd lastig. In Power Pivot daarentegen zorgen relaties er onder andere voor dat jouw tabel geen herhalingen bevat.

afb. 2.

Voor relaties heb je twee verschillende soorten tabellen nodig: feiten- en dimensietabellen (ook wel filtertabellen genoemd). Feitentabellen zijn de grote jongens in de data wereld. Hier staan alle (historische) transacties in. Dat kan zomaar oplopen tot miljoenen rijen. Dit soort tabellen zijn vaak lastig te lezen, omdat alleen getallen worden gebruikt. Kijk je naar de dimensietabellen, dan zijn dat de zelfstandige naamwoorden van je organisatie (of model). Alle soorten omschrijvingen, die van toepassing zijn, staan in de tabel. Relaties zorgen voor een soepele, gesmeerde werking tussen beide tabellen.

Beide tabellen bekijk je in het licht van rijcontexten. Kijk je naar afbeelding 3, dan zie je een rijcontext in een feitentabel én een bijbehorende dimensietabel. In de afbeelding is ook de relatie tussen de kolommen aangegeven. Heb je een relatie tussen de beide tabellen, op basis van de sleutelkolommen, dan lees je het volgende: op 31 maart (feitentabel) heeft klant 1, de heer Jansen (dimensietabel) € 5,00 (feitentabel) betaald. Kijk je naar de tweede regel dan zie je dat dezelfde heer Jansen op 5 april € 7,50 heeft betaald. Relaties zorgen er op de achtergrond voor dat de rijcontext van een tabel virtueel vergroot wordt met de inhoud van de andere tabel. Een soort van VLOOKUP, maar dan oneindig veel sneller en efficiënter. Voorwaarde is dat klant Jansen maar eenmaal in de dimensietabel mag voorkomen. 

afb. 3.

Natuurlijk hoeft dat niet persé de naam zijn, want ik kan best meerdere meneren Jansen als klant hebben, maar de het nummer in de relatiekolom (in ons voorbeeld dus 1) moet wel uniek zijn. Wat is nu de – echt ongelofelijke – kracht van relaties? Kijk maar eens naar afbeelding 4. Hier zie je dat in het model ook een feitentabel van de verkoop is opgenomen. Daar staat ook het nummer van meneer Jansen in. Je stelt in het model vast, dat mijnheer Jansen naast de betaling ook op 1 maart een bestelling van € 12,50 gedaan heeft. 

afb. 4.

Doordat de dimensietabellen de feitentabellen met elkaar ‘verbindt’ krijg je geheel andere inzichten dan in Excel met maar één tabel. En zoals je in afbeelding 5 ziet kan een model heel veel relaties bevatten. Het is best complex om dit soort modellen in Power Pivot of Power BI te managen, maar je kunt het wel. In Excel echter wordt zo het bereik onmogelijk groot.

afb. 5.

Relaties in Power Pivot of Power BI zijn niet uniek: Access en SQL zijn relationele databases, waarin ook relaties worden gedefinieerd. Maar die relaties werken anders, ze worden onder de motorkap berekend. Relaties in Power Pivot of Power BI zijn, onder hun motorkap, een Microsoft ‘trade secret’. Ik weet echt niet hoe ze werken, maar ze werken wel een heel erg stuk sneller. Dat maakt het werken in Power Pivot echt professioneel, maar ook compleet anders dan je gewend bent met Excel.

En daar raak ik het zwakke punt van Power Pivot of Power BI. Relaties tussen de tabellen maakt de leercurve behoorlijk steil. Je moet namelijk niet alleen leren hoe Power Pivot of Power BI werkt, maar je moet je ook de nieuwe, bijbehorende concepten eigen maken. Maar ik heb nòg een waarschuwing voor je. Als je die drempel namelijk gepasseerd bent, gaat er een geheel nieuwe wereld voor je open. Echt: “business will never be the same!” Ik verzeker je, hoeveel je ook van Excel houdt, je wil dan nooit meer terug. Zo groeide ook bij mij de liefde voor relaties. Oh, bijna vergeten: ik sprak over voorwaarden van relaties. Er is in de praktijk eigenlijk maar één simpele controle, die Power Pivot of Power BI uitvoert. Die controle is of een van de kolommen in de relatie uniek is. 

Er valt nog veel meer te ontdekken over relaties, maar dat is voor een ander moment. Volgende keer gaat het over de vierde laag: visualiseren. Daarmee stappen we de laag binnen, waar de waarde voor een bedrijf wordt gecreëerd. In die zin is die laag, vanuit de positie van de business, de meest interessante in het vijflagen model. We houden ons daarom dan niet direct bezig met het maken van visualisaties, maar meer wat laag 4 (en ook laag 5) betekenen voor de business.

Blogs in deze serie:

• Excel: Tabellen for sale. Maar welke moet ik kiezen?
• Excel: De kunst van rij in kolom berekeningen
• Explosieve kracht in een cel: aggregaat berekeningen
• De liefdesverklaring van Power Pivot: relaties
• De echte waarde van Power BI
• Schema first of data first? Maar waar is je data?
• Visualiseren, de kunst van het verleiden (1)
• Visualiseren, de kunst van het verleiden (2)
• Low-code of no-code, de weg naar de toekomst (1)
• Low-code of no-code, de weg naar de toekomst (2)
• Controles, levensbloed voor je model (1)