Verklein je dataset en los problemen met performance op

Remo is consultant bij ilionx, werkt op dagelijkse basis met Power BI en ondersteunt klanten in het gebruik. In deze blog neemt hij je mee in het verkleinen van de opslaggrootte in Power BI. Power BI wordt binnen veel organisaties gebruikt als dé tool om dashboards en rapportages op te leveren. Maar hoe zorg je ervoor dat de prestatie van de dashboards niet tegenvalt? In deze blog gaat Remo dieper in op het verbeteren van Power BI performance door de opslaggrootte van de dataset aanzienlijk te verkleinen.

De opslaggrootte van je Power BI dataset

Over het algemeen geldt, ‘hoe groter de dataset, hoe langzamer de rapporten’. Denk maar aan een Excelbestand waarbij een bestand met veel rijen aan data snel vastloopt. Hoe meer rijen, hoe langzamer je filters reageren en hoe groter de kans dat Excel vastloopt. Maar welke zaken zorgen er nou voor dat mijn Power BI dataset in omvang toeneemt? 

Voorbeeldsituatie

Voor de uitleg in deze blog gaan we uit van de volgende situatie: 

• We beschikken over een datawarehouse met verkoopgegevens van een retailorganisatie. De organisatie heeft 100 winkels, die elk 5.000 klanten per dag hebben, die elk 10 producten kopen. 
• In het dataplatform wordt informatie op het allerlaagste, meest gedetailleerde niveau vastgelegd. Voorbeeld: elke kassabonregel (elk verkochte product) heeft een eigen rij met data in het dataplatform. 
• Power BI kent twee modi, Import en Direct Query (we gaan hier verder niet op in). We maken gebruik van de modus Import, waarbij de data wordt opgeslagen in Power BI. 

Tabellen

De data waar Power BI mee werkt wordt opgeslagen in tabellen. Een tabel bestaat uit rijen en kolommen. In een tabel van 10 rijen (observaties) en 10 kolommen (kenmerken) heeft 100 cellen. In elke cel staat een waarde, die een beschrijving geeft van het kenmerk voor die specifieke observatie. De opslagruimte die nodig is voor één tabel is afhankelijk van het aantal rijen, het aantal kolommen en de waardes in de cellen. 

Beperk het aantal rijen

Hoe meer rijen met data er moeten worden opgeslagen, hoe meer geheugen daarvoor nodig is. Maar hoe beperk je nou het aantal rijen in een Power BI dataset? 

Data aggregatie

Als de data in het dataplatform op het meest gedetailleerde niveau is opgeslagen, maar de businessvraag niet over dat detailniveau gaat, dan is het zonde de data op dat niveau op te slaan. Het is dan beter om op te tellen (aggregeren) naar een hoger niveau. Bijvoorbeeld: In plaats van de omzet op bonregel te importeren naar Power BI, kan dit op winkel-dagniveau.  

Als we alle bonregels uit het voorbeeld in Power BI zouden inladen, hebben we voor één jaar voor alle winkels 365*100*5000*10= 1.825.000.000 (1.8 miljard) rijen nodig.
Dezelfde som op winkel-dagniveau levert: 365*100= 36.500 rijen. Een verschil van factor 50.000.

Stel jezelf altijd de vraag of je het correcte aggregatieniveau te pakken hebt om de businessvragen te beantwoorden, zonder dat je onnodig veel rijen importeert.

Voorbeeld van aggregatie

Voorbeeld van het aggregeren van data. In dit voorbeeld wordt de tabel gereduceerd van 12 naar 3 rijen (factor 4). 

Beperk de datahistorie

Naast het kiezen van het juiste agreggatieniveau, is het belangrijk te bedenken hoeveel historische data je wil bewaren. Hoe meer historie je wilt behouden, hoe meer rijen je nodig hebt. Is alle historische data die nu is ingeladen écht nodig hebt voor het beantwoorden van de structurele businessvragen? Het halveren van het aantal jaren historische data kan de grootte van het model maarliefst halveren. Denk ook goed na over het type Slowly Changing Dimensions (SCD) dat je toepast. Ook dit heeft een grote impact op de performance van je datawarehouse.

Beperk het aantal kolommen

Tabellen worden in Power BI per kolom opgeslagen en de opslagruimte is afhankelijk van de inhoud van de kolom. Wat kun je doen om de opslagruimte van de kolommen in je dataset te beperken?

Verwijder ongebruikte kolommen

Zijn er kolommen met daarin informatie die je niet gebruikt voor het beantwoorden van de businessvragen? Dan is het advies deze niet te importeren. Als je dit wel doet, nemen ze onnodig opslagruimte in beslag en maken daarmee je dataset onnodig trager. 

Kardinaliteit en data dictionaries 

Soms is het opsplitsen van data van 1 kolom naar 2 kolommen juist wél een goed idee. Dit is een gevolg van de kardinaliteit. kardinaliteit in Power BI geeft aan hoeveel unieke waardes er zijn in een kolom. Als een kolom slechts twee mogelijke waardes heeft (bijvoorbeeld ‘Ja’ of ‘Nee’), dan is de kardinaliteit twee. Hoe hoger de kardinaliteit, hoe meer verschillende waarden opgeslagen moeten worden.

Data dictionary

Power BI vertaalt dit naar een zogeheten dictionary, waarin elke unieke waarde uit de kolom zo gecomprimeerd mogelijk wordt opgeslagen. Hoe meer unieke waarden, hoe groter de dictionary is (hoe meer woorden je taal bevat, hoe groter je woordenboek zal zijn).
Om deze reden is het soms juist wél goed extra kolommen op te nemen, om zo je ‘woordenboek’ te verkleinen. Denk bijvoorbeeld aan het opdelen van tijdstempels in een los datumveld en een tijdveld.

Voorbeeld van het opsplitsen van kolommen

Het beste voorbeeld hiervan is het opdelen van tijdstempels (bijvoorbeeld 22-04-2022 14h:30m:24s) in een los datumveld en een tijdveld. Voor het hele jaar 2022 heeft het datumveld een cardinaliteit van 365 (het aantal dagen per jaar). Het tijdveld heeft een cardinaliteit van 24u60m60s= 86.400.

Zou je deze twee combineren tot één kolom, dan zal die kolom maar liefst 86.400*365 = 31.536.000 mogelijke combinaties van datums en tijd hebben. Ongeacht of elke combinatie daadwerkelijk voorkomt: je woordenboek zal veel groter zijn dan twee woordenboeken van de datumveld en tijdvelden samen. Namelijk 31.536.000 versus 365 en 86.400.

Voorbeeld van kardinaliteit in Power BI

Dit voorbeeld van kardinaliteit in Power BI laat zien hoe de opslaggrootte van een kolom tot stand komt in Power BI. 

Beperk de grootte van de waarden

De waarden in een cel van een tabel kunnen op verschillende manieren worden opgeslagen. Wat kan je doen om de opslaggrootte van de waarden in de tabellen te minimaliseren?

Modelleer de data met het stermodel

In dit voorbeeld hebben we beschikking over een dataplatform, waar de data volgens een ster gemodelleerd is. Dit betekent dat er zo min mogelijk informatie dubbel wordt opgeslagen. Zo wordt bij elke bonregel vastgelegd bij welk winkelnummer de aankoop is gedaan. In een aparte tabel worden alle kenmerken van de winkel beschreven (zoals de locatie, de grootte, openingstijden, etc.). Door de data als ster te modelleren hoeft deze informatie maar één keer te worden opgeslagen en niet voor elke bonregel herhaald te worden. 

Voorbeeld van een stermodel

Een voorbeeld van een stermodel in Power BI. De kenmerken van de klant, winkel, product, datum en bon hoeven overal slechts éénmaal te worden vastgelegd en worden vervolgens gekoppeld aan de juiste bonregel.

Data types en aantal decimalen

Hoe je de data in een kolom opslaat is ook van belang voor de grootte van het model. Hierboven is al beschreven wat er gebeurt met de omvang van de dictionary bij een grotere kardinaliteit. Het is daarom niet handig om bijvoorbeeld getallen met 10 decimalen achter de komma op te slaan. Meestal beperken we ons tot vier decimalen, zodat er geen verschillen in de afronding op centen ontstaat. 

Bovenstaande voorbeelden laten zien waarom het belangrijk is vooraf na te denken over het reduceren van de grootte van de Power BI dataset. Zo werken de dashboards naar behoren en verloopt de adoptie binnen je organisatie vlekkeloos. Zo weet je zeker dat je collega’s het door jou gemaakte dashboard ook daadwerkelijk gebruiken. In veel gevallen is het af te raden om de BI-tool direct te koppelen aan de databronnen. Het gebruik van een datawarehouse als tussenlaag leidt dan tot een aanzienlijk betere performance.