Pico bello Power BI-datamodel

Om Power BI optimaal te benutten moet je datamodel pico bello zijn. Zo voorkom je performance problemen, complexe DAX-queries en “hoe zit dat ook alweer?"-vragen. In deze post leer je de fundamentele stappen voor een goed datamodel

1. Kies een onderwerp

Of dat je een rapportage maakt of datamodel, hou je vast aan een onderwerp.

Denk aan financiële boekingen, voorraadmutaties, enquêtegegevens of verkooporders. Ieder onderwerp is al complex genoeg.

Breid stap voor stap het datamodel uit. Zo blijft het datamodel overzichtelijk, krijg je geen problemen met relaties en is het datamodel goed te documenteren.

2. Data analyseren

Wat zijn jouw feitentabellen en dimensies?

Stel je voor, jullie boekhoudpakket is makkelijk te benaderen via Power BI en binnen no-time heb je data. De tabel met financiële gegevens is helaas vrij complex.

Iedere boeking is weer gekoppeld aan een kostenplaats, een debiteur, een crediteur en een grootboekrekening. Deze opsomming bevatten de dimensies, deze dimensies helpen de gebruiker van het datamodel om de feiten te begrijpen.

3. Sterstructuur

In een sterstructuur staat de feitentabel centraal. Bijna alle berekeningen werken met de waarden uit de feitentabel. De dimensietabellen zijn allemaal slechts een stap verwijderd van de feitentabel. Waardoor je deze gemakkelijk kan gebruiken om de data uit de feitentabel te filteren, data te categoriseren of data te specificeren.

De sleutel term hierbij is relaties. Een relatie geeft de samenhang tussen tabellen aan. Relaties zijn in tegenstelling tot het echte leven vrij gemakkelijk. Ze komen in drie varianten: een-op-een-, een-op-veel- en veel-op-veel-relatie.

Stel je voor, we tekenen op een mentaal whiteboard aan de linkerkant tabel A en aan de rechterkant tabel B.

Bij een een-op-een-relatie mag een rij aan de linkerkant met slechts relateren met een rij aan de rechterkant, en andersom.

Bij een veel-op-veel-relatie kunnen er aan de linkerkant meerdere rijen relateren met meerdere rijen aan de rechterkant, en andersom.

Nu komt de lastige variant maar meest belangrijke variant: een-op-veel. De feitentabel in een sterstructuur is altijd aan de veel kant van een een-op-veel-relatie. Er zijn namelijk veel rijen die naar een of dezelfde rij in een dimensie tabel verwijzen. Mocht je hier vastlopen laat dan zeer zeker een reactie achter.

Als we het boekhoudpakket als voorbeeld nemen, dan hebben wij deze feitentabellen en dimensietabellen:

• Feitentabel: Financiële mutaties
• Dimensies:
  • Relaties: Voor zowel het vinden van de debiteuren als de crediteuren. Probeer ze in een tabel te houden zodat je makkelijk kan filteren, met DAX kun je eventuele relatieproblemen oplossen;
  • Kostenplaatsen.;
  • Rubrieken: Mochten deze in een aparte tabel staan voeg ze dan toe aan de feitentabel via het grootboekrekeningnummer. Anders krijg je een sneeuwvlokstructuur. Dit kan het maken van DAX-formules erg complex maken;
  • Grootboekrekeningen.

4. Uitzonderingen op de sterstructuur

In sommige situaties moet je uitwijken van de sterstructuur. Hieronder staan mijn persoonlijke lijst aan uitzonderingen.

Koppeltabellen

Soms is een dimensietabel alleen te gebruiken via twee of meerdere relaties die samen veel-op-veel zijn. In zo’n situatie kun je gebruik maken van een koppeltabel. Dit is een tabel die tussen twee tabellen in zit.

Een goed voorbeeld is een feitentabel met politici, waarvan per periode geregistreerd staat welke onderwerpen zij vertegenwoordigen. De periodes en de onderwerpen zet je mooi in een aparte dimensies. Zo ontstaat een ster. Alleen kan de onderwerpen tabel nog niet gekoppeld worden.

Hiervoor maak je een koppeltabel. De koppeltabel bevat twee kolommen: een kolom die verwijst naar een regel in de onderwerpentabel en een andere kolom die verwijst naar een regel in de feitentabel. Een regel in de feitentabel kan dus meerdere onderwerpen hebben. Die tweede kolom verwijst dan zowel naar een periode als naar een politic. Hierbij maken wij gebruik van een samengevoegde primaire sleutel, Want we willen dat dimensie tabellen naar elkaar verwijzen. Want anders krijgen we een circulaire relatie. En cirulaire relaties moeten we in Power BI vermijden.

Rapportageschema’s

Vooral in de wereld van financiën is dit wel een bekend begrip. Een rapportageschema, is een tabel die, wordt veelal gebruik om waarden vanuit een feitentabel anders weer te geven.

Een rapportageschema heeft altijd met een kolom die verwijst naar een ander dimensietabel. Dit hoeft niet per se een-op-een-relatie te zijn, wanneer je een-op-veel-relatie gebruikt, kun je gegevens samenvatten.

Een rapportageschema zou bijvoorbeeld een afdeling kunnen zijn. Medewerkers registreren hun uren, die urenregistraties heb jij in je dataset. Nu vraagt de grote baas: “Hoeveel uren heeft afdeling x besteed?”. Dan kun je met het rapportageschema gemakkelijk deze gegevens eruit filteren.

Let wel op: rapportageschema’s zijn niet ideaal voor structuren die veel veranderen. Een kolommenbalans staat misschien wel jaren lang vast maar de medewerkers van een afdeling niet. Heb je te maken met structuren die snel veranderen, probeer er dan een moment-opname van te maken.

Voeg dan bijvoorbeeld zodra je de urenregistraties in handen krijgt, een kolom met de afdelingscode van de medewerker toe. Sla deze data dan op een vaste plek op en werk deze data dan incrementeel.

Mocht de medewerker dan van afdeling veranderen dan blijven de uren toch nog historisch gezien op de andere afdeling staan. Helaas is dit niet te doen in Power BI, omdat Power Bi niet incrementeel met data om kan gaan. In deze situatie kun je bijvoorbeeld Azure Data Factory gebruiken.

5. Etiquette

Lees hier hoe je het datamodel overzichtelijk en overdraagbaar houdt. Zowel naar je medeontwerpers als de eindgebruikers.

Tabelnamen

Hou ze kort, omschrijvend, typerend en alles in dezelfde taal.

Voordat je begint, kies een taal die overdraagbaar is met collega’s en mogelijke derden die aan de rapportage werken. Vaak gebruik ik het Engels omdat dit overdraagbaar is naar de supporthelpdesk van Microsoft en omdat ik vaak veel minder woorden nodig heb.

Maak de naam typerend door altijd te beginnen met een voorvoegsel bijvoorbeeld:

• “F_”: Voor feitentabellen,
• “D_”: Voor dimensietabellen,
• “R_”: Voor rapportageschema’s,
• “K_”: Voor koppeltabellen.

Beschrijf de inhoud van de tabel. Maar ga niet te specifiek en probeer alles in dezelfde taal te houden. Dus niet “urenregistraties vanaf 2011”. Nee, “Urenregistraties”. Enkele voorbeelden:

• “F_Urenregistraties”,
• “K_Politici-periode & onderwerp” (zie hoofdstuk “Koppeltabellen”, deze tabel via een feitentabel met samengevoegde primaire sleutel verwijst naar tabel met onderwerpen),
• “R_Kolommenbalans”.

Tabelomschrijvingen

Via de diagramweergave in Power BI kun je zodra je op een tabel klikt in het rechterpaneel een omschrijving toevoegen. Beschrijf hier afkomst van de data, verversingsnelheid, inhoud en belangrijke aannames.

Bijvoorbeeld: Enquêtegegevens van enquête “Huisvesting in verzorgingstehuizen”, wordt jaarlijks bijgewerkt, vertegenwoordigd niet de jongvolwassenen (leeftijdsgroep 18 t/m 32).

Wanneer een gebruiker van het datamodel de cursor op de tabelnaam plaatst, krijgt deze een duidelijke omschrijving te zien.

Kolomnamen

Met kolomnamen zijn wij niet zo vrij als met tabelnamen. DAX-formules schrijven met spaties en speciale tekens is erg lastig en foutgevoelig. De wereld van de computerprogrammeurs worstelt al jaren om hun variabelen goede namen te geven. Het debat “camelCase” VS “snake_case” is al decennia gaande. Kies daarom de stijl die je zelf fijn vind. Het belangrijkste is dat je bij een stijl blijft.

“camelCase”

1. Woorden zitten aan elkaar,
2. Behalve het eerst woord beginnen woorden altijd met een hoofdletter

Voorbeelden:

• employeeCode,
• departmentKey.

“snake_case”

1. Woorden zitten aan elkaar,
2. Behalve het eerst woord beginnen woorden altijd met een hoofdletter

Voorbeelden:

• employee_code,
• department_key.

Namen van berekeningen

DAX-berekeningen kunnen gekoppeld worden aan een tabel, maar dienen binnen het datamodel een unieke naam te hebben. Laat daarom DAX-berekeningen altijd beginnen met een voorvoegsel waaruit je kan herleiden bij welke tabel ze horen.

Bijvoorbeeld “F_FM_” voor de feitentabel met financiële mutaties.

Veel berekeningen lijken op elkaar bijvoorbeeld, een berekening die een bepaalde waarde sommeert en een andere berekening die dezelfde waarde sommeert op dezelfde wijze maar ook nog eens filtert. Als je de berekeningen bijvoorbeeld de volgende namen geeft, dan kun je ze veel makkelijker terugvinden:

• “F_FM_Transactiewaarde”
• “F_FM_Transactiewaarde; Alleen intercompany”

Omschrijvingen voor kolommen en berekeningen

Net als bij tabellen kun je een omschrijvingen toevoegen aan berekeningen en kolommen. Sommige kolommen of berekeningen zijn misschien via een hele complexe formule gevormd, dit is de perfecte plek om die formule te plaatsen.

Bij het gebruik van een tabularmodel via SSAS (SQL Server Analysis Services, hiermee maak je een centraal datamodel dat door meerdere Power BI-rapportages kan worden gebruikt) is dit ideaal omdat je dan in Power BI niet kan zien welke formule er is gebruikt. Je kunt dan de formule letterlijk in de omschrijving plaatsen. Zodat eindgebruikers de formule ook kunnen lezen.

Conclusie

Het maken en onderhouden van een datamodel vergt een goede etiquette. Zodra je deze samen met je collega’s hanteert, kosten de rapportages minder tijd om te onderhouden, zijn ze overdraagbaarder en kan Power BI sneller berekeningen uitvoeren.

Al deze tips zijn samengesteld uit mijn eigen ervaringen, problematieken en de technieken van Ralph Kimball (The Data Warehouse Toolkit). Vergeet niet deze pagina toe te voegen aan je bladwijzers, want ik ga gegarandeerd nog tips toevoegen.