Power BI-model te groot? Zo los je geheugenproblemen slim op met TMSL

In de praktijk liepen we bij een klant tegen een groot probleem aan: het semantisch model was uitgegroeid tot meer dan 1,6 GB (bestandsgrootte). Wanneer we dit model binnen de service wilden verversen, kregen we te maken met “out of memory”-fouten.

Benodigd geheugen kan in potentie dubbel zo hoog zijn omdat Power BI het model tijdens het verversen beschikbaar houdt voor queries. We hebben met tools als Bravo en Measure Killer geprobeerd het model kleiner te maken. Helaas was het opsplitsen van het model in meerdere kleinere modellen was voor de business geen werkbare oplossing.

Methoden om je model binnen geheugenlimieten te verversen

Om ervoor te zorgen dat het semantisch model binnen de grenzen van het beschikbare geheugen kan worden ververst, zijn er verschillende praktische strategieën beschikbaar. Hieronder leg ik kort 10 methoden uit in willekeurige volgorde:

1. Minder data

Beperk de omvang van je dataset door alleen relevante periodes of dimensies te laden. Zo kun je bijvoorbeeld kiezen om enkel de data van de laatste 2 jaar te importeren, in plaats van alle historische gegevens. Een andere optie is een hoger aggregatieniveau te kiezen. Dit zorgt ervoor dat het model kleiner blijft en efficiënter gebruikmaakt van het geheugen.

2. Incrementele verversing toepassen

Maak gebruik van incrementele verversing, waarbij alleen nieuwe of gewijzigde data worden ingeladen. Op deze manier hoeft niet het volledige model bij elke verversing opnieuw verwerkt te worden, wat het geheugengebruik aanzienlijk verlaagt.

3. Berekeningen optimaliseren

Vereenvoudig berekende kolommen en measures. Complexe berekeningen kun je het beste zo veel mogelijk naar de bron verplaatsen of vooraf aggregeren voordat je ze importeert. Dit vermindert de belasting op het model en versnelt het verversingsproces.

4. Overschakelen naar DirectQuery-modus

Bij zeer grote datasets kun je in Power BI kiezen voor DirectQuery. Hierbij worden gegevens direct uit de databron opgehaald in plaats van opgeslagen in het model, waardoor het geheugengebruik flink daalt. Let wel: elke interactie veroorzaakt een query naar de bron, wat de laadtijd kan verhogen bij complexe rapporten.

Door aggregaties toe te passen kun je data samenvatten in het model. Detailinformatie wordt pas uit de bron gehaald wanneer nodig, zodat je snelheid en flexibiliteit combineert met een laag geheugengebruik.

5. Large Storage Format inschakelen

Wanneer je gebruikmaakt van Power BI Premium/Fabric, kun je het Large Storage Format activeren voor je dataset. Dit maakt paginering mogelijk en zorgt ervoor dat grotere modellen efficiënter worden verwerkt.

6. Verbeterde verversing met PartialBatch Commit Mode

Door enhanced refresh te combineren met partialBatch commit mode kun je het geheugengebruik tijdens verversingen verder beperken. Zo wordt het model stapsgewijs ververst, wat resulteert in minder piekbelasting op het geheugen. Deze optie is o.a. te configureren van de Fabric pipeline activiteit “Refresh semantic model”.

7. Ontwerp voor Query folding

Query folding zorgt dat Power BI alleen het noodzakelijke gedeelte van de data laadt en bewerkt, in plaats van alle ruwe gegevens naar de lokale engine te halen. Dit verkleint het geheugen- en opslaggebruik, vooral bij grote tabellen, en verbetert de prestaties van zowel datasetverversingen als incrementele updates. Hoe groter de originele dataset en hoe complexer de transformaties, hoe groter het effect van query folding op efficiënt gebruik van opslag.

8. Splits op in meerdere kleinere modellen

In overleg met de business kun je het Power BI-model opdelen in kleinere, themagerichte modellen. Dit vraagt goede communicatie over de voor- en nadelen, want minder flexibiliteit en gebruiksgemak kunnen opwegen tegen snellere prestaties en lagere beheer- en kosten. Kleinere modellen zijn sneller en makkelijker te beheren, terwijl wijzigingen minder impact hebben. Samen met de business bepaal je de juiste balans tussen flexibiliteit en efficiëntie.

9. Weet de geheugenrestricties van Fabric

Het is belangrijk om vooraf inzicht te hebben in de geheugenrestricties van Fabric. Door deze limieten te kennen, weet je precies wat je kunt verwachten en waar je mogelijk tegenaan loopt bij het werken met grote datasets of complexe modellen. Dit helpt bij het plannen van je modelontwerp en het voorkomen van onverwachte beperkingen tijdens gebruik. Alle relevante limieten en restricties zijn terug te vinden via de officiële documentatie: What is Power BI Premium? - Microsoft Fabric | Microsoft Learn

10. Gebruik TMSL

TMSL (Tabular Model Scripting Language) geeft je controle over welke tabellen of partities je parallel of sequentieel ververst. Hierdoor kun je het geheugengebruik tijdens een refresh beter sturen en voorkom je problemen bij grote modellen. Door TMSL in een Fabric notebook te gebruiken, heb je bovendien meer grip op logging en alternatieve flows bij specifieke gebeurtenissen.

 

Hoe hebben wij TMSL toegepast

Om TMSL te kunnen gebruiken heb je Fabric capaciteit nodig. Welke SKU is dan natuurlijk de vraag. Om dit te beantwoorden hebben wij een aparte werkplek ingericht met een toegewijde Fabric capaciteit. We zijn begonnen met F8. Daarna hebben wij TMSL geschreven in een Fabric notebook. Je dient gebruik te maken van de bibliotheek sempy.fabric.

Vervolgens hebben wij een functie geschreven welke een collectie van objecten accepteert. Vervolgens hebben wij zelf bepaalt welke set van tabellen dus ververst worden

 

 

Voorlopige conclusie

Wanneer we uitsluitend kijken naar het effect van programmatische verversing via TMSL (met sempy.fabric), zien we een duidelijk verschil in capaciteitsbehoefte. In onze tests bleek een F32 SKU voldoende om het model stabiel te verversen. Dezelfde workload vereiste via de standaard Power BI-service een F64 SKU.

Hoewel het optimalisatieproces nog niet volledig is afgerond en er dus nog ruimte is voor verdere verbetering, laten deze eerste resultaten al zien dat een eigen, configureerbare verversingsstrategie via TMSL aanzienlijke voordelen kan opleveren. Zelfs zonder diepgaande optimalisatie behaal je met deze aanpak al veel winst op het gebied van performance en resourcegebruik.

Een bijkomend voordeel is dat deze strategie gebaseerd is op code. Hierdoor kan het eenvoudig geïntegreerd worden in bestaande CI/CD-processen, wat het beheer en de doorontwikkeling van de oplossing vergemakkelijkt.