Hoofdstuk 3 Data ophalen met Power BI Desktop
Gegevens importeren is de eerste stap van de data-ijsberg.
In het eenvoudige voorbeeld van het hoofdstuk ‘Eenvoudige toepassing in Power BI Desktop’ staan alle gegevens die we nodig hebben voor onze analyse in één bestand. Dat is vaak niet het geval.
Met Power BI Desktop kun je gegevens ophalen uit de zich almaar uitbreidende wereld van gegevens. En die lijst groeit nog altijd aan. Power BI verdeelt de gegevensbronnen in zeven categorieën.
1 File (Bestand): Excel-bestanden, CSV-bestanden, Tekstbestanden (TXT), JSON-bestanden (JavaScript Object Notation), mappen (met Power BI kun je volledige mappen ophalen), Sharepoint-mappen enzovoort. (Klik in het linkerdeelvenster op File om de volledige lijst te zien.)
2 Database: Access database, SQL Server database enzovoort. (Klik in het linkerdeelvenster op Database om de volledige lijst te zien.)
3 Microsoft Fabric (Preview): Power BI datasets, Dataflows, Datamarts (preview), Warehouses (preview), Lakehouses (preview), KQL Database (preview).
4 Power Platform: Power BI dataflows (Legacy), Common Data Service (Legacy), Dataverse, Dataflows.
5 Azure: een verzameling cloudservices van Microsoft.
6 Online services: Dynamics 365, Facebook, Google Analytics enzovoort. (Klik in het linkerdeelvenster op Online services om de volledige lijst te zien).
7 Other: web, Microsoft Exchange online enzovoort. (Klik in het linkerdeelvenster op Online services om de volledige lijst te zien.)
In de volgende voorbeelden maken we verbinding met een aantal veelgebruikte gegevensbronnen en laden we de gegevens in het gegevensmodel.
Notitie
Met Power BI Desktop maakt je verbinding met de brongegevens. Het bestand wordt dus eigenlijk niet echt geïmporteerd in Power BI. Wat er wel gebeurt, is dat er een nieuwe gegevensset wordt gemaakt in Power BI en dat de gegevens en het gegevensmodel (indien van toepassing) in de gegevensset worden geladen.
1 Gegevens ophalen uit bestanden
1.1 Een Excel-bestand ophalen
Situatieschets
Het bedrijf Tex-Mex bv gebruikt een Excel-werkmap om de gegevens van klanten en personeelsleden te bewaren (Database_Tex-Mex_Excel). De werkmap bevat meerdere werkbladen. We laden alle gegevens in Power BI Desktop.
1 Open Power BI Desktop. Klik in het lint op Home > Excel workbook (groep Data).
Figuur 2: verbinding maken met een Excel bestand
2 Navigeer naar het opgavebestand Database_Tex-Mex_Excel.xlsx en klik op Openen
3 Vink in het deelvenster Navigator alle tabellen aan:
Figuur 3: deelvenster Navigator
4 Klik op Load om de data naar het gegevensmodel te laden. Zodra de gegevens geladen zijn, kun je de tabel en de velden zien in het deelvenster Data.
Figuur 4: deelvenster Data
5 Klik op de weergave Data om de gegevens te zien:
Figuur 5: weergave data
6 Bewaar het bestand als H3_Gegevensanalyse_Tex-Mex.pbix.
Notitie
Het is mogelijk dat je een foutmelding krijgt als je probeert om verbinding te maken met een Excel-bestand met het oude bestandsformaat (.xls-bestand). In Power BI Desktop maken zowel Access-databases als vroege versies van Excel-werkmappen (.XLS-bestanden van het type Excel 97-2003) gebruik van de Access Database Engine. Verschillende situaties kunnen ertoe leiden dat de Access Database Engine niet goed werkt. Je kunt de Access Database Engine installeren (zie FAQ, Een Access-bestand kan niet geladen worden) of het Excel-bestand converteren naar het nieuwe bestandsformaat (.xlsx).
1.2 Een CSV-bestand ophalen
Notitie
Een CSV-bestand is een kommagescheiden bestand (Comma Separated Values). Dat is een bestand met een oude database-indeling. Het bestand bestaat alleen uit tekst, waardoor de gegevens gemakkelijk geïmplementeerd (lezen/schrijven) kunnen worden.
Situatieschets
Het bedrijf Tex-Mex bv heeft eigen software ontwikkeld om de klachten te registreren. Het bestand Klachten.csv is een bestand geëxporteerd uit dit systeem.
1 Klik in het lint op Home > Get Data > Text/CSV
Figuur 7: Get Data > Text/CSV
2 Navigeer naar het bestand Klachten.csv en klik op Openen.
3 Je krijgt onmiddellijk de inhoud van het bestand te zien.
4 Klik op Load om de data te laden naar het gegevensmodel. Zodra de gegevens geladen zijn, kun je de tabel en velden zien in het deelvenster Data.
5 Bewaar met CTRL+S.
1.3 Een volledige map met bestanden ophalen
Met Power BI kun je alle bestanden in een bepaalde map eenvoudig combineren in één tabel, zolang ze hetzelfde bestandstype en dezelfde structuur hebben.
Er zijn verschillende methoden om bestanden in een map te combineren. De meest gebruikte methode is Combine&Load (zie hieronder). Deze methode is eenvoudig toe te passen, maar werkt enkel wanneer de Excel-bestanden exact dezelfde structuur hebben. Deze methode werkt niet als bijvoorbeeld:
• het werkblad van een van de Excel-bestanden een andere naam heeft dan de naam waarnaar verwezen wordt bij het combineren van de bestanden;
• er een andere veldnaam gebruikt wordt in een van de Excel-bestanden;
• de kolommen in een andere volgorde gesorteerd zijn in een van de Excel-bestanden;
• enzovoort.
Het is dan ook aan te raden om een alternatieve methode te gebruiken om bestanden in een map te combineren. Deze methode wordt toegelicht in het hoofdstuk 'Gegevens opschonen en transformeren met Power Query Editor'.
Situatieschets
Maandelijks ontvangen we een Excel-bestand met de orders. In plaats van elk bestand afzonderlijk te laden, voegen we alle bestanden toe aan de map Orders_Excel. Met Power BI kunnen we de bestanden in één keer laden. Nieuwe bestanden worden automatisch toegevoegd zodra ze toegevoegd zijn aan de map Orders_Excel.
1 Klik in het startscherm op Get Data of in het lint op Home > Get Data > More…
2 Selecteer Folder en klik op Connect
Figuur 9: map ophalen
3 Navigeer naar de map Orders_Excel en klik op OK
4 Er wordt een lijst weergegeven met de bestanden in de map.
5 Klik op Combine > Combine & Load.
6 Standaard wordt het eerste bestand (First file) als model gebruikt. Selecteer het werkblad dat moet gecombineerd worden, het werkblad Orders
Notitie
Met deze methode wordt de werkbladnaam hard gecodeerd in de Power Query-functie, waardoor je problemen krijgt als je een werkblad met een andere naam wilt combineren. Dat kan achteraf wel gewijzigd worden in de Power Query Editor (als je kennis hebt van functies in Power Query Editor). Of je kunt een alternatieve/betere methode gebruiken; deze methode wordt toegelicht in het hoofdstuk 'Gegevens opschonen en transformeren met Power Query Editor'.
7 Klik op OK.
8 De bestanden worden samengevoegd tot één tabel en geladen in het gegevensmodel. Er wordt een extra kolom toegevoegd (Source.Name) met de naam van het bronbestand per record.
Figuur 12: Source.Name
Nieuwe gegevens toevoegen kan eenvoudig door het bestand toe te voegen aan de map Orders.
9 Verplaats het opgavebestand Orders_0618.xlsx naar de map Orders.
10 Klik in het lint Home op Refresh
Figuur 13: refresh
11 Controleer of de gegevens toegevoegd zijn. Ga naar de weergave Data. Filter de kolom Source.Name op 0618.
2 Gegevens ophalen van een webpagina
Het is mogelijk om gegevens op te halen van om het even welke webpagina. In ons voorbeeld maken we verbinding met een Wikipedia-pagina: nl.wikipedia.org/wiki/ISO_3166-1. Op die pagina staan de ISO-landcodes.
Notitie
De ISO-landcodes kun je ook vinden als Excel-bestand in de opgavebestanden (ISO-landcodes.xlsx)
We werken verder in het bestand H3_Gegevensanalyse_Tex_Mex.pbix.
1 Klik in het startscherm op Get data of in het lint op Home > Get Data > Web.
2 Kopieer en plak de URL in het dialoogvenster From Web.
Figuur 15: dialoogvenster From Web
3 Klik op OK.
4 In het linkerdeelvenster van de Navigator kun je alle tabellen aanvinken die op de webpagina te vinden zijn. Vink de tabel ISO-landcodes aan.
Figuur 16: ISO-landcodes
5 Klik op Load om de data naar het gegevensmodel te laden. Zodra de gegevens geladen zijn, kun je de tabel en velden zien in het deelvenster Data
6 Bewaar met CTRL+S.
3 Gegevens ophalen uit databases
3.1 Gegevens ophalen uit MS Access
MS Access is een voorbeeld van een relationele databaseapplicatie. (Zie ook het hoofdstuk ‘Gegevensmodel maken in Power BI Desktop’.)
Zo’n database is opgebouwd uit twee of meer tabellen die in onderlinge relatie met elkaar staan. Dat is meteen het verschil met de eendimensionale tabel waarmee we doorgaans werken in Excel.
Het bedrijf Tex-Mex bv heeft een relationele Access-database met de gegevens van de personeelsleden en de klanten
Open Power BI Desktop of klik op File > New als je nog aan het werken bent in een Power BI-bestand.
1 Klik in het startscherm op Get Data of in het lint op Home > Get Data > More…
2 Selecteer Database > Access database en klik op Connect
Figuur 17: Access-database ophalen
3 Navigeer naar het bestand Database_Tex-Mex.accdb en klik op Openen.
Notitie
Het is mogelijk dat je een foutmelding krijgt als je probeert om verbinding te maken met de Access-database. In Power BI Desktop maken zowel Access-databases als vroege versies van Excel-werkmappen (.XLSbestanden van het type Excel 97-2003) gebruik van de Access Database Engine. Verschillende situaties kunnen ertoe leiden dat de Access Database Engine niet goed werkt. Meer informatie hierover vind je bij de FAQ (Access-bestand kan niet geladen worden).
4 In het linkerdeelvenster zie je de lijst met alle tabellen die in de database zitten. Vink de tabellen tblPersoneelsleden en tblKlanten aan.
5 Klik op Select Related Tables. Automatisch worden de gerelateerde tabellen geselecteerd.
Notitie
De optie Select Related Tables is handig als je tabellen ophaalt uit een relationele database. Zonder deze optie zou je handmatig alle tabellen moeten selecteren die je wilt ophalen, wat vaak een goed begrip van de onderliggende databasestructuur vereist.
Met deze optie zal Power BI Desktop automatisch alle tabellen selecteren die een directe relatie hebben met een of meer van de al geselecteerde tabellen.
7 Zodra de gegevens geladen zijn, kun je de tabel en velden zien in het deelvenster Data.
8 Bewaar met CTRL+S.
3.2 Verbinding maken met een SQL-server
Microsoft SQL-server is een relationeel databasebeheerssysteem (RDBMS), ontwikkeld door Microsoft en veel gebruikt in het bedrijfsleven. SQL (uitgesproken als ‘S-Q-L’ of ‘Sequel’) is de afkorting van Structured Query Language. Het is wereldwijd de populairste (vraag)taal om relationele databases mee aan te sturen.
Notitie
Om verbinding te maken met een SQL-server moet je toegang hebben tot een SQL-server (van het bedrijf of zelf geïnstalleerd).
1 Klik in het lint op Home > SQL-server.
2 Geef in het vak Server de naam van de SQL-server op waarmee je verbinding wilt maken. Je kunt ook een databasenaam opgeven (als die bekend is).
Figuur 22: dialoogvenster SQL Server database
3 Klik op OK
4 Je krijgt een overzicht van de beschikbare tabellen in de Navigator. Vink de tabellen aan die je wilt laden in het gegevensmodel.
5 Klik op OK in het bericht om verbinding te maken via een niet-versleutelde verbinding. (Misschien is de werkwijze in jouw organisatie anders.)
Notitie
Dit voorbeeld wordt niet verder uitgewerkt omdat je toegang moet hebben tot een SQL-server (van het bedrijf of zelf geïnstalleerd).
4 Conclusie
Met Power BI kun je gegevens ophalen vanuit heel wat verschillende gegevensbronnen. De werkwijze om de data op te halen is gelijkaardig voor alle gegevensbronnen.
Als je verbinding maakt met een gegevensbron in Power BI, kun je kiezen hoe je verbinding maakt, via:
• Import data of scheduled refresh
• Direct query
• Live connection
4.1 Import data of scheduled refresh
Deze methode heeft twee namen, Import Data of Scheduled Refresh. Beide namen verklaren het gedrag van deze methode. Met deze methode worden gegevens van de bron in Power BI geladen. Laden in Power BI betekent dat je geheugen en schijfruimte verbruikt.
Als je 1 miljoen rijen in een brontabel hebt, en je laadt het in Power BI zonder te filteren, dan heb je uiteindelijk dezelfde hoeveelheid datarijen in Power BI. Als je echter een database hebt met 1000 tabellen, en je laadt maar 10 van die tabellen in Power BI, dan krijg je alleen voor die 10 tabellen geheugengebruik. Het komt erop neer dat je net zoveel geheugen en schijfruimte gebruikt als dat je gegevens in Power BI laadt.
Het is echter niet zo dat wanneer je een database importeert van 100 GB dat ze ook 100 GB bestandsgrootte inneemt in Power BI.
Power BI maakt gebruik van de compressiemotor van xVelocity en werkt op een column-store inmemory-technologie. Die comprimeert gegevens en slaat ze op in een gecomprimeerd formaat. Soms heb je een Excel-bestand van 1 GB, en wanneer je het importeert in Power BI, eindigt je Power BIbestand met slechts 10 MB. Dat komt vooral door de compressiemotor van Power BI. De compressiegraad is echter niet altijd zo hoog. Het hangt van veel dingen af: het aantal unieke waarden in de kolom, gegevenstypes en vele andere situaties.
Samengevat: Power BI zal gecomprimeerde data opslaan. De grootte van de data in Power BI kan veel kleiner zijn dan de grootte in de databron.
Belangrijke pro’s en contra’s voor deze methode
• Pro: geen beperking in functionaliteit
Met deze methode zijn alle functionaliteiten van Power BI beschikbaar. Je kunt Power Query gebruiken om gegevens uit meerdere bronnen te combineren of DAX voor geavanceerde tijdsintelligentie functies enzovoort.
• Contra: beperking van de bestandsgrootte
Met deze methode heb je een beperking op de grootte van het model. Je Power BI-model (of laten we zeggen het bestand) mag niet groter zijn dan 1 GB. Vergeet echter niet dat 1 GB in het Power BI-bestand niet gelijk is aan 1 GB gegevens in de bron (zoals vermeld in het gedeelte over de compression engine).
Er is een uitzondering hierop; Power BI Premium maakt het mogelijk om tot 50 GB grootte van het model te laden in de Power BI-website.
• Pro: dit is de snelste methode
Deze verbindingsmethode is de snelst mogelijke optie. Er is geen vertraging of traagheid met deze methode (zolang je het Power BI-model maar zonder prestatieproblemen hebt ontworpen).
4.2 Direct Query
Met Direct Query maak je een directe verbinding met de gegevensbron. De gegevens worden niét opgeslagen in het gegevensmodel. Power BI is een soort ‘visualisatielaag’ die telkens opnieuw de data uit de gegevensbron opvraagt. Power BI zal alleen metadata van tabellen (tabelnamen, kolomnamen, relaties...) opslaan, maar niet de data.
Belangrijke pro’s en contra’s voor deze methode
• Contra: beperking in gegevensbronnen
Direct Query is enkel mogelijk voor bepaalde gegevensbronnen. Een lijst kun je vinden via: https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources.
• Pro: schaalbaarheid (belangrijkste voordeel)
Met Direct Query zal de Power BI-bestandsgrootte veel kleiner zijn, en hoogstwaarschijnlijk raak je nooit de beperking van de grootte omdat er geen data in het model is opgeslagen.
• Contra: beperkte functionaliteit
Deze methode heeft niet de volledige functionaliteit van Power BI. Met deze methode zul je slechts twee weergaves hebben in Power BI Desktop (Report en Model).
Met Direct Query worden ook niet alle DAX-functies ondersteund.
• Contra: deze verbinding is langzamer dan andere verbindingen
Een groot nadeel van deze methode is dat de connectie trager is dan bij andere verbindingen. Iedere visualisatie stuurt een query naar de databron die de gegevens terugstuurt.
4.3 Live connection
Live Connection is zeer vergelijkbaar met Direct Query wat betreft de manier waarop het werkt met de gegevensbron. Met Live connection sla je geen data op in Power BI, de gegevens worden ook nu telkens opgevraagd (query). Er zijn toch een aantal verschillen met DirectQuery.
Live Connection wordt alleen ondersteund voor deze databronnen: https://docs.microsoft.com/ en-us/power-bi/connect-data/power-bi-data-sources.
Omdat de databronnen zelf modelleermachines zijn, sluit Power BI alleen hierop aan en haalt alle modelmetadata (metingen, attribuutnamen, relaties...) op. Met deze methode moet je al je modelleringsbehoeften in de gegevensbron behandelen en Power BI brengt die gegevens gewoon aan de oppervlakte via visualisaties.
Belangrijke pro’s en contra’s voor deze methode
• Pro: groot formaat model met OLAP of Tabular engine
Het grote voordeel van dit model is dat je een heel groot datamodel kunt hebben (niet beperkt tot 1 GB), en dat je ook de modelleerlaag van SSAS kunt gebruiken. SSAS Tabular geeft je DAX, en Multi-Dimensional geeft je MDX. Met een van deze twee talen kun je al je berekeningen en modelleringsbehoeften afdekken.
Deze methode heeft een betere modelleringsfunctie dan Direct Query, want in DirectQuery is er geen DAX of MDX als analytische taal om te helpen. Alle berekeningen moeten worden gedaan aan de databasekant.
• Con: geen Power Query (belangrijkste nadeel)
Met deze methode heb je enkel de weergave Report. Je kunt zelfs geen eenvoudige query toepassen.
Notitie
Het is ook mogelijk om twee methoden te combineren in één model, in dit geval spreekt men van een composite model.
Figuur 24: Composite model
Conclusie
• Welke methode presteert het best en het snelst?
Het importeren van gegevens is de snelst mogelijke optie. De data wordt in het geheugen van de server geladen en alle queries worden direct uitgevoerd.
Live Connection is de volgende optie in deze lijst, vooral als SSAS Tabular of Power BI Service wordt gebruikt omdat ze allebei in-memory-technologieën zijn en sneller presteren dan multidimensionaal. Direct Query is het traagste type verbinding.
De winnaar voor dit onderdeel is dus Import Data
• Welke methode is flexibeler?
Met Import Data krijg je de volledige functionaliteit van Power BI. Full Power Query transformaties, en DAX metingen en visualisaties. Direct Query en Live Connection zijn beide de volgende in deze lijst omdat elk van hen je iets oplevert.
De winnaar voor dit onderdeel is dus weer Import Data.
• Welke methode is meer schaalbaar?
De Import Data-methode heeft de grootte beperking van 1 GB per model. Zonder gebruik te maken van Power BI Premium is deze methode niet zozeer schaalbaar.
Met Direct Query en Live Connection krijg je een betere schaalbaarheid.
De winnaars voor dit onderdeel zijn dus Live Connection en DirectQuery.
5 Gegevens vernieuwen
Het type gegevensbron (onlineservice, database in de cloud, bestand op een lokale computer enzovoort) bepaalt hoe de gegevens worden vernieuwd.
In sommige gevallen hoef je helemaal niets te doen. Als je bijvoorbeeld rechtstreeks verbinding maakt met de gegevensbron (Direct Query) in plaats van de gegevens te laden in Power BI Desktop, worden de gegevens automatisch vernieuwd.
Maar in andere gevallen (bijvoorbeeld een verbinding met een werkmap op een lokale computer of met webpagina’s), moet je handmatig vernieuwen:
Klik in het lint op Home > Refresh.
Figuur 25: gegevens vernieuwen
De gegevens worden opnieuw geladen.
Notitie
Je kunt ook één gegevensbron vernieuwen. Klik in het deelvenster Fields met de rechtermuisknop op de tabel en kies voor de optie Refresh