In mijn vijfde blog over Power BI in de praktijk van Kasparov BI wil ik het hebben over data transformatie (Power Query). Opnieuw voer voor vakgenoten, voel je vrij om feedback te geven of vragen te stellen. Graag zelfs! In het eerste artikel van deze serie, ‘een introductie van Power BI’, wordt Power Query genoemd als stap twee (2). Het doel van Power Query is de data ‘Power BI waardig’ te maken. Het wordt altijd aangeraden om data zoveel mogelijk upstream te transformeren, maar het zou heel goed kunnen dat niet elke actie volledig upstream kan worden getransformeerd. In Power Query kun je bijvoorbeeld gegevensbronnen combineren, logica toevoegen en noodzakelijke functionele wijzigingen aanbrengen.
Omdat de transformaties in Power Query invloed kunnen hebben op de vernieuwing en de prestaties van het semantic model, is het zaak om een paar basisregels op te stellen voor het gebruik van Power Query.
Power Query werkt op basis van stappen. Dit betekent dat tijdens het transformeren van een tabel elke transformatie als een aparte stap wordt gezien. Het voordeel hiervan is dat de ontwikkelaar altijd kan teruglezen welke transformaties zijn uitgevoerd en die dus ook makkelijk kan aanpassen. Het nadeel is dat een groot aantal transformaties voor performance- en vernieuwingsproblemen kunnen zorgen. Dit komt doordat Power BI bij een vernieuwing van een semantic importmodel elke stap vanaf het begin gaat uitvoeren. Veel transformaties / stappen betekent dus een langere vernieuwingstijd.
Om dit te ondervangen, is het belangrijk om de regel toe te passen: ‘Data dient zo ver mogelijk upstream, maar zo ver downstream als nodig, te worden getransformeerd’. Dit betekent dat transformaties en/of stappen die ook in de gegevensbron kunnen worden aangepast de voorkeur genieten ten opzichten van Power Query.
Power BI werkt op basis van rij-context. Het loopt in een kolom naar beneden ( per rij ) en herhaalt dit vervolgens voor de volgende kolom et cetera. Vanuit een prestatie-aspect betekent dit dat het beperken van het aantal kolomen prestatie winst zal opleveren. Beperk daarom het aantal kolommen tot hetgeen je echt nodig hebt.
Query folding is een functionaliteit binnen de Power Query Editor waarmee het ophalen van en het transformeren van brondata worden samengevoegd tot één query statement. Als een transformatie wordt meegenomen in query folding, betekent dit dat de native query wordt herschreven; dit is de query die wordt teruggestuurd naar het bronsysteem.
De Power Query Engine probeert query folding uit zichzelf al zoveel mogelijk toe te passen; het is echter aan de gebruiker om dit te monitoren en te ondersteunen. Een concreet voorbeeld is de volgorde van de stappen Changed Type en Filtered Rows; in de zojuist benoemde volgorde zou de filterstap niet worden meegenomen in de native query, aangezien query folding breekt door de stap Changed Type. Het omdraaien van deze stappen herstelt query folding en dit maakt de query efficiënter.
In Power Query kan worden verwezen naar een tabel door een ‘Referentie’, of de tabel en de toegepaste stappen kunnen worden gekopieerd door ‘Dupliceren’. Op basis van de benaming wordt vaak onterecht de aanname gedaan dat de ‘Referentie’ de tabel waarnaar wordt verwezen pakt als uitgangspunt, en dat deze query dus maar één keer wordt uitgevoerd. Dit is echter niet hoe Power Query werkt. De ‘Referentie’ dient om te voorkomen dat dezelfde stappen in meerdere Query’s moeten worden toegepast. Het is eigenlijk een hulpmiddel om transformaties te centraliseren om onderhoud en leesbaarheid te vergroten. Hieronder een voorbeeld om dit concept te illustreren.
Deze twee query’s zijn nagenoeg identiek, maar de ene tabel behoudt middels de laatste stap alleen rijen waarin [Product Color] gelijk is aan “Red” en de ander rijen waarin [Product Color] gelijk is aan “Black”. Als de behoefte van de eindgebruiker wijzigt en zij graag ook nog de kolom [Weight] wil toevoegen aan deze dimensietabellen, dient dit nu in twee query’s te worden aangepast. In de stap #”Removed Other Columns” wordt bepaald welke kolommen behouden worden, en ondanks dat de twee tabellen dezelfde kolommen behouden is de logica niet gecentraliseerd.
2. Referenties
Beide query’s verwijzen naar dezelfde bronquery; de d_product_base query. Dit is de plek waar alle logica voorafgaand aan het filteren op [Product Color] is gecentraliseerd:
Als de behoefte van de eindgebruiker wijzigt, hoeft dit slechts op één plek aangepast te worden. Om tot deze twee tabellen te komen, wordt de d_product_base query nog steeds twee keer uitgevoerd, maar voor de leesbaarheid, het onderhoud en de consistentie is deze opzet efficiënter.
Wees je goed bewust van deze werking, aangezien dus ook die referentietabellen een impact hebben op de snelheid van de data refresh.
Een parameter is een opgeslagen waarde die losstaat van een query. Hierdoor kan hij gemakkelijk worden aangepast en/of hergebruikt in meerdere query’s. Een parameter kan een ‘single value’ zijn, een lijst of een query. Voorbeeld: Bij het gebruik van een OTAP-methode ( test, acceptatie, productie omgeving ) kan het handig zijn om snel te kunnen wisselen van omgeving. Een parameter is hier uitermate geschikt voor. In plaats van in elke query te verwijzen naar de juiste omgeving, kan er ook worden verwezen naar de alleenstaande parameter. Bij het wijzigen van omgeving hoeft dan alleen de parameterwaarde te worden aangepast.
Merge Query’s
Met een Merge Query’s worden twee bestaande tabellen samengevoegd op basis van overeenkomende waarden uit een of meerdere kolommen. Dit is erg handig als twee tabellen moeten worden samengevoegd in de breedte, bijvoorbeeld om bepaalde transformaties en/of berekeningen te kunnen uitvoeren.
Verschillende samenvoegingsopties zijn:
Met een Append Query’s worden twee bestaande tabellen samengevoegd op basis van kolomnaam. In tegenstelling tot Merge Query’s waarbij twee bestaande tabellen worden samengevoegd in de breedte, wordt bij Append Query’s de tweede tabel als extra rijen toegevoegd aan de eerste tabel ( In de hoogte ). Append Query’s is bijvoorbeeld erg handig als men zijn datasources heeft opgesplits per jaar. Met Append Query’s kan deze worden samengevoegd tot één tabel.
Transform
Power Query zelf brengt geen vooraf gedefinieerd risico met zich mee. Het is echter van belang om na te denken over de handeling die zal worden doorgevoerd. Veel of enkele handelingen, zoals het combineren van gegevens, kunnen de ( vernieuwings) prestaties van het rapport beïnvloeden. Het wordt geadviseerd om alleen essentiële handelingen door te voeren. Als het mogelijk is om de wijziging in de gegevensbron door te voeren, wordt dat ten zeerste aanbevolen.
Met het publiceren van mijn laatste blog ‘Power Query’ is mijn serie over de introductie van Power BI afgerond. Als je mij al een tijdje volgt, heb ik je hopelijk nieuwe inzichten kunnen geven of op weg kunnen helpen met Power BI. Ik haal ontzettend veel plezier uit het schrijven van blogs voor Kasparov BI en wil dit dan ook graag voortzetten met een nieuwe serie ‘Power BI – het vervolg’.
Siebe van Boxtel werkt nu een half jaar bij ons, maar heeft al ruime ervaring als BI Consultant. Hij koos voor Kasparov BI om zijn carrière én die van collega’s een boost te geven. Want: “Ook al werk ik met data, ik ben op de eerste plaats mensgericht. En dat past hier perfect.” Siebe vertelt over zijn werk en drijfveren.
Bij softwarebedrijf Planon heeft een team van Kasparov BI een corporate reporting framework ontwikkeld. Kim Bergmans-Keijzer, Global Information Management Director bij Planon: “Drie miljoen mensen gebruiken onze software. Onze uitdaging: één waarheid over data om een constructief gesprek te voeren en data driven beslissingen te nemen” Planon wilde betrouwbare, kwalitatieve data in een duidelijke structuur, storytelling om het héle verhaal te vertellen en een goede gebruikerservaring. In een korte case movie vertellen betrokkenen over de reis die ze samen aflegden, onderweg naar een succesvol framework.
Zijn ruim twaalf jaar commerciële ervaring in de arbeidsbemiddeling, en een voorliefde voor data (“passie is zo’n truttig woord”) zijn de ideale combinatie in de rol van Commercial Director BI & ESG. Vanaf 1 juli gaat Gerard aan de slag in deze sleutelrol met een duidelijke focus om de groeiambities bij Kasparov BI & ESG te realiseren. Hij heeft er zin in, dat is duidelijk. “Ik zie uitgelezen kansen om samen met het team deze mooie organisatie naar the next level van volwaardig partner in BI & ESG te brengen”