Power BI tutorial week 2: verbinden van verschillende databronnen
Companies that use data effectively are 5x more likely to make better decisions and 6x more likely to be profitable.
— McKinsey & Company
Inleiding
Vandaag de dag speelt data een cruciale rol in het behalen van bedrijfsdoelen. Zo becijferde McKinsey & Company in 2016 in het rapport “The Age of Analytics: Competing in a Data-Driven World” dat bedrijven die hun data effectief benutten vijf keer zo waarschijnlijk betere beslissingen namen en zes keer meer kans hadden op winstgevendheid. Dashboards vormen een essentiële schakel in dit proces.
Het doel van deze tutorial is om je stap voor stap door een praktijkvoorbeeld te begeleiden waarin Business Intelligence wordt ingezet. Hiervoor maken we gebruik van Power BI en een dataset van een fictief snoepgoedbedrijf (afkomstig van Kaggle). We kiezen voor Power BI vanwege de volgende redenen:
- Microsoft Power BI wordt door Gartner als leider erkend in Business Intelligence;
- Power BI Desktop is gratis beschikbaar in de Microsoft Store;*
- Heeyoo heeft uitgebreide ervaring met het bouwen van op maat gemaakte dashboards in Power BI.
*Disclaimer: Deze tutorial is specifiek ontworpen voor Power BI Desktop op Windows-systemen, aangezien Power BI Desktop momenteel alleen beschikbaar is voor Windows-gebruikers. Als je een Mac-gebruiker bent, kun je de tutorial niet rechtstreeks uitvoeren. Als je Power BI Desktop wilt gebruiken op een Mac, kun je een virtuele Windows-machine draaien via bijvoorbeeld Parallels of Boot Camp. Wel is het mogelijk Power BI Service via je webbrowser te gebruiken om rapporten en dashboards te bekijken en te delen. Houd er rekening mee dat de functionaliteit van de webversie beperkter is dan die van Power BI Desktop.
Door deze tutorial te volgen, krijg je niet alleen inzicht in de mogelijkheden van Power BI, maar ook praktische handvatten om data effectief in te zetten binnen jouw organisatie.
Nadat we vorige week Power BI hebben geïnstalleerd, enkele instellingen hebben aangepast, kennis hebben gemaakt met de interface en onze eerste dataset hebben ingeladen, is het deze week tijd om extra datasets toe te voegen.
Tweede databron verbinden
- Open het .pbix bestand van week 1:
- Na het openen van het bestand belanden we in de front-end van Power BI. Vanuit Home navigeren we wederom naar Get data en kiezen voor Tekst/CSV:
- Selecteer in de map met bronbestanden voor deze tutorial Candy_Customer.csv:
- Net als vorige week kiezen we voor de Transform-optie, zodat we in de Power Query terechtkomen. Deze week duiken we dieper in de mogelijkheden van Power Query. Het doel van de Power Query is drieledig: in deze back-end van Power BI kun je data namelijk aanpassen (bijvoorbeeld filteren of sorteren), transformeren (zoals kolommen herstructureren) en vervolgens laden in je analyses of datamodellen.
- Allereerst hernoemen we de tabel ‘Customer’ (1). Let op dat er nu twee ‘queries’ in de linkerbalk staan (2): ‘Sales’ en ‘Customer’. Het is eenvoudig om tussen de twee queries te navigeren:
- Nu we de interface van Power Query beter kunnen bekijken, valt het op dat je via de werkbalk bovenaan naar verschillende tabs kunt navigeren. Hieronder volgt een overzicht van de belangrijkste tabs:
- Home: Algemene instellingen en transformatiehulpmiddelen (bijvoorbeeld het behouden of verwijderen van bepaalde kolommen, rijen verwijderen en het aanpassen van datatypes).
- Transform: Hulpmiddelen om bestaande kolommen te transformeren (bijvoorbeeld: splitsen, groeperen en tekst extraheren).
- Add column: Opties om nieuwe kolommen aan te maken (bijvoorbeeld op basis van bestaande kolommen).
- View: Hier heb je diverse opties om componenten te laten zien/verbergen. Een voorbeeld: door op Query settings te klikken, kan het gelijknamige paneel verborgen/getoond worden. Dit paneel bestaat uit een ‘Properties’ en een ‘Applied Steps’–gedeelte:
- We zoomen nu in op Applied Steps. deze stappen geven aan welke transformaties er in de Power Query Editor zijn toegepast. De drie stappen die we hier zien (‘Source’, ‘Promoted Headers’ en ‘Changed Type’), zijn automatisch door Power BI gegenereerd. Later in de tutorial voegen we ook zelf stappen toe en bewerken we ze.
- Door op een eerdere stap te klikken, kun je de transformatiegeschiedenis terugkijken en zien hoe de stappen op de geselecteerde query zijn toegepast. In dit geval heeft Power BI de eerste rij in de dataset als ‘headers’ geselecteerd, wat een van de standaard transformaties is bij het importeren van data:
- Door op een eerdere stap te klikken, kun je de transformatiegeschiedenis terugkijken en zien hoe de stappen op de geselecteerde query zijn toegepast. In dit geval heeft Power BI de eerste rij in de dataset als ‘headers’ geselecteerd, wat een van de standaard transformaties is bij het importeren van data:
Naast het aanpassen van de query-naam en het inspecteren van de Applied Steps, is het controleren van de data types een belangrijke stap in dit stadium. Elk datatype wordt aangegeven door een icoon dat naast de kolomkop staat. Zo kunnen we aan het ‘123’-icoon zien we dat de kolom ‘Customer ID’ het datatype ‘Whole number’ heeft, terwijl bijvoorbeeld de kolom ‘City’ het datatype ‘Text’ heeft, wat te zien is aan het ‘ABC’-icoon. Door op het icoon te klikken, kun je eventueel het datatype aanpassen. In ons geval zijn alle datatypes correct, maar kijk even door het lijstje om bekend te worden met de verschillende datatypes.
Naast het aanpassen van de query-naam en het inspecteren van de Applied Steps, is het controleren van de data types een belangrijke stap in dit stadium. Elk datatype wordt aangegeven door een icoon dat naast de kolomkop staat. Zo kunnen we aan het ‘123’-icoon zien we dat de kolom ‘Customer ID’ het datatype ‘Whole number’ heeft, terwijl bijvoorbeeld de kolom ‘City’ het datatype ‘Text’ heeft, wat te zien is aan het ‘ABC’-icoon. Door op het icoon te klikken, kun je eventueel het datatype aanpassen. In ons geval zijn alle datatypes correct, maar kijk even door het lijstje om bekend te worden met de verschillende datatypes.Verbinden met een SQL-database (demo)
In deze tutorial maken we uitsluitend gebruik van csv-bestanden. Het aantal connectors in Power BI is echter erg groot. Daarom zullen we in deze sectie laten zien hoe Power BI direct in verbinding met een SQL-database kan worden gebracht. Dit volgende stukje is puur demonstratief, dus je kunt dit rustig doornemen of overslaan als je liever direct verder gaat met de tutorial. Ga in het laatste geval verder vanaf ‘Verbinden overige databronnen’.
- Om verbinding te maken met een database, klikken we vanuit de Power Query op New Source en kies je voor SQL Server (dit is vergelijkbaar met de ‘Get Data’ optie vanuit het ‘Home’-menu):
- In de pop-up die verschijnt vullen we vervolgens de naam van de server en database in:
Opmerking: Let op dat we hierboven kunnen kiezen uit twee verschillende Data Connectivity modes: Import en DirectQuery. Dit is een belangrijk onderdeel van Power BI:
Import laadt de data volledig in Power BI en slaat deze op in een intern datamodel. Dit zorgt voor snellere prestaties en en maakt offline gebruik van de data mogelijk. De data kan op geplande tijden of handmatig worden ververst.
DirectQuery maakt een directe verbinding met de databron en stuurt bij elke interactie een query naar de bron. Dit zorgt ervoor dat je altijd de meest actuele data ziet, zonder dat je deze eerst in Power BI hoeft te laden. Dit is handig voor real-time data, maar kan invloed hebben op de prestaties. Afhankelijk van de snelheid van de databron en de complexiteit van de queries.
- De volgende stap is meestal het authenticeren van je account, zodat je verbinding kunt maken met de server en de database. Dit houdt in dat je je moet aanmelden met je gebruikersnaam en wachtwoord of via andere methoden zoals OAuth, Windows Authentication of andere verificatiemethoden, afhankelijk van de geselecteerde databron. Deze stap wordt hier niet getoond, maar is wel essentieel.
- Vervolgens zien we alle tabellen die in de database aanwezig zijn. In dit geval kiezen we Dim_Customer, die overeenkomt met de reeds geladen ‘Customers’ tabel en klikken we op ‘OK’ om deze in de Power Query te laden:
- De tabel uit de database is nu ingeladen en we kunnen deze hernoemen naar ‘Customer (Database)’:
- Dezelfde stappen kun je herhalen voor de andere tabellen in de database, zodat je alleen database-tabellen als databron hebt. Omdat we in deze demo verder werken met csv-bestanden, verwijderen we deze databron nu en ronden we de demo af:
Verbinden overige databronnen
- Vanuit de Power Query kunnen we direct een volgende databron toevoegen door op New Source te klikken en weer te kiezen voor Tekst/CSV:
- We importeren onze volgende databron: klik op Candy_Factory.csv en druk daarna op ‘OK’:
- Zoals gebruikelijk inspecteren we de datatypes en hernoemen de query, in dit geval naar ‘Factory’:
- Dezelfde stappen (New Source > Tekst/CSV) volgen we voor het bestand Candy_Product.csv, waarna we de query hernoemen naar ‘Product’:
- Ten slotte volgen we wederom dezelfde stappen (New Source > Tekst/CSV) voor het bestand Candy_Date.csv, waarop we de query hernoemen naar ‘Date’ en vaststellen dat de data inderdaad van het type ‘Date’ is:
Opmerking: Deze ‘Date’-tabel bevat een enkele kolom met een datum per rij, van 1 januari 2021 t/m 31 december 2030. Deze periode is gebaseerd op de jaren waarvoor data in de dataset aanwezig is, zodat we een volledige tijdsperiode kunnen gebruiken voor onze analyses. Hoewel je de datumtabel ook dynamisch kunt genereren met DAX, valt dat buiten het kader van deze tutorial. De datatabel is cruciaal voor het effectief werken met tijdsgebonden data in Power BI. Later in deze tutorial gaan we verder in op het belang van deze tabel.
- Nu alle tabellen zijn ingeladen, klikken we op Close & Apply om terug te keren naar de front-end:
- Eenmaal terug in de front-end zien we dat onze tabellen goed zijn ingeladen. Onder Table View kunnen we de inhoud van de tabellen inspecteren, terwijl we ze in de Model View schematisch kunnen terugvinden. Let op dat we de tabellen kunnen slepen en neerzetten. Waarom dit handig is, komt later in deze tutorial aan bod wanneer we data gaan modelleren.
Verversen van data
In het vorige gedeelte hebben we data ingeladen in Power BI. Maar wat als er iets wijzigt? Bijvoorbeeld: er wordt een transactie toegevoegd of de gegevens van een klant wijzigen? De data verversen in Power BI Desktop gaat simpel door op de Refresh knop te drukken. Die vind je onder Home in de bovenste werkbalk: 
Voor geautomatiseerde data-updates, zoals dagelijkse of wekelijks, kun je Power BI Service gebruiken. In Power BI Service kun je instellen dat de data op vaste momenten wordt vernieuwd. Dat is ideaal voor gedeelde dashboards en rapporten die altijd up-to-date moeten zijn.
Ten slotte slaan we ons werk op en sluiten we Power BI Desktop af. In deze tweede week heb je gezien hoe je Power BI kunt koppelen met een SQL-database, extra datasets importeert en hoe de ‘Refresh’-knop werkt. Volgende week gaan we aan de slag met het transformeren van data, tot dan!
Werk je met complexe databronnen en wil je dit schaalbaar aanpakken? Onze consultants helpen organisaties hier dagelijks bij. Neem gerust contact op, we vertellen je graag over de mogelijkheden.
