Een veel gebruikte databron in Power BI zijn SQL-server databases, zowel on-premise als in de Azure cloud. Denk bijvoorbeeld aan een datawarehouse in Azure of een rechtstreekse koppeling met de database van een bepaalde applicatie.
Wanneer je deze data importeert is het erg belangrijk om de juiste volgorde te kiezen van je stappen in Power Query. Voor de performance van je import kan dit een enorm verschil betekenen. In deze blog vertellen we je meer over ‘Query Folding’. Overigens is dit niet alleen van toepassing op SQL-server databases, maar op de meeste relationele databases.
SQL Query
Wanneer je in Power BI gegevens inleest vanuit een SQL-database dan wordt er op de achtergrond door Power BI een T-SQL commando uitgevoerd. Bijvoorbeeld SELECT * FROM VerkoopFacturen. Als resultaat geeft SQL-server alle regels uit de tabel VerkoopFacturen. Vaak wil je alleen de regels van een bepaalde periode, bijvoorbeeld het laatste jaar. De T-SQL query is dan SELECT * FROM VerkoopFacturen WHERE Jaar = 2019. De selectie wordt door SQL-server uitgevoerd en alleen de geselecteerde regels worden teruggegeven. Dat betekent dat er veel minder regels worden ingelezen, wat minder tijd kost en in een betere performance resulteert.
Native Query
In de Power Query Editor zie je echter aan de rechterkant alleen maar de stappen die je hebt uitgevoerd. Je hoeft niet na te denken over de T-SQL query, dat regelt Power Query voor je. Op de achtergrond vertaalt Power Query je stappen in een T-SQL commando. In dit voorbeeld heb ik de tabel VerkoopFacturen gefilterd. Als je met de rechtermuisknop klikt op de filter-stap en kiest voor View Native Query dan zie je het T-SQL commando dat Power Query gebruikt.
Dit zorgt ervoor dat SQL-server alleen de regels van 2019 terug geeft. Dat is veel efficiënter dan wanneer SQL-server eerst alle verkoopfacturen van alle jaren zou doorgeven en dat Power Query vervolgens alle jaren van voor 2019 zou verwijderen. Dit principe heet ‘Query Folding’.
Query Folding
Niet alle acties die je in Power Query kunt doen zijn om te zetten in een T-SQL commando. Power Query zal dit net zo lang proberen totdat er een stap komt die niet in T-SQL kan. Vanaf die stap stopt de query folding en de verdere stappen worden gewoon in Power Query uitgevoerd.
In dit voorbeeld hiernaast doe ik een ‘split column’ actie, dat wordt niet door T-SQL ondersteund. Daarna wordt het filter op het jaar 2019 toegepast. Als je nu met de rechtermuisknop op de filter-stap klikt, dan is de optie View Native Query grijs, dus niet beschikbaar. De vorige stap heeft query folding uitgeschakeld. Het gevolg hiervan is dat nu wél eerst alle verkoopfacturen van alle jaren worden ingelezen en dat Power Query vervolgens de oude jaren gaat verwijderen. Het eindresultaat is hetzelfde, maar het verschil in performance is enorm.
In dit voorbeeld kun je zien dat het van groot belang is om de volgorde van je toegepaste stappen zorgvuldig te kiezen. Door de ‘split column’ actie uit te voeren ná de filteractie, wordt de query wel efficiënt uitgevoerd. Je kunt eenvoudig checken tot welke stap query folding wordt toegepast, door op elke stap met de rechtermuisknop te klikken en te controleren of View Native Query nog beschikbaar is.
Meer weten?
We creëren oplossingen met behulp van Microsoft Power BI. Een toegankelijke tool waarmee je in korte tijd een helder inzicht krijgt in je data. Power BI kan gekoppeld worden met vrijwel alle (offline en online) software en ook met diverse externe open databronnen.
Nodig ons gerust uit voor een vrijblijvende kennismaking, zodat we de meerwaarde voor jouw onderneming kunnen laten zien.