SQL met Power BI

4. Verbinding maken met SQL Server

Laten we een voorbeeld doornemen dat laat zien hoe je een SQL Server-database verbindt met Power BI Desktop en deze vervolgens gebruikt om de database te analyseren.

Voorbeeld-database

Voor demonstratiedoeleinden gebruiken we een openbaar toegankelijke SQL Server-instance op AWS en een database die is aangemaakt op basis van de dataset Superstore.

Deze dataset bevat informatie over producten, verkopen, winst, enz. en ons doel als data-analisten is om de data te analyseren en kritieke verbeterpunten binnen dit fictieve bedrijf te vinden. Deze SQL Server-instance is gehost door Ken Flerlage.

Toegang tot de voorbeeld-database

• Installeer de SQL Server Management Studio (SSMS). SSMS is een gratis en geïntegreerde omgeving voor het beheren van elke SQL-infrastructuur. Met SSMS kun je de data-tiercomponenten die door je applicaties worden gebruikt implementeren, monitoren en upgraden, evenals queries en scripts bouwen.
• Nadat je het hebt gedownload en op je systeem hebt geïnstalleerd, zie je het volgende scherm dat om specifieke inloggegevens vraagt.

Voer de volgende gegevens in:

Server Name: ec2-52-14-205-70.us-east-2.compute.amazonaws.com
Authentication: SQL Server Authentication
Login: SQL
Password: SQL
Mogelijk gemaakt door

Je krijgt nu ‘Read Only’-toegang tot de database ‘SuperStoreUS’.

In ons voorbeeld is ec2–52–14–205–70.us-east-2.compute.amazonaws.com de naam van de instance, SuperstoreUS en Test zijn de databases, en Orders, Customers enz. zijn de tabellen binnen de SuperstoreUS-database. Er kunnen dus meerdere instances zijn, en elke instance kan meerdere databases bevatten die op hun beurt ook meerdere tabellen kunnen hebben.

5. SQL-data importeren in Power BI

Power BI Desktop organiseert de data in queries. Dit betekent dat alle data in een tabelachtige structuur wordt weergegeven.

De verbinding instellen

Open Power BI Desktop en ga naar het startscherm. In het deelvenster Get Data zijn veel opties beschikbaar voor databronnen die je met Power BI Desktop kunt verbinden. We maken verbinding met de SQL Server.

Als je op de optie SQL Server klikt, opent een nieuw scherm waarin je de server invult waarmee je Power BI Desktop wilt verbinden. Vul de gegevens in en klaar.

We kunnen nu op de gewenste tabel klikken en de inhoud bekijken. Om een bepaalde tabel te laden, vink je simpelweg het selectievakje ernaast aan en laad je deze.

Modi voor dataconnectiviteit

Een SQL-database kan op twee manieren met Power BI Desktop worden verbonden; beide opties verschijnen op het hoofdscherm.

Import

Zoals de naam al aangeeft, ‘importeert’ de importmethode de geselecteerde tabellen in Power BI Desktop. Power BI gebruikt vervolgens deze geïmporteerde data voor het maken van visualisaties of het uitvoeren van bewerkingen. Om wijzigingen in de onderliggende data te zien, moeten we de data vernieuwen, wat de volledige dataset opnieuw importeert.

DirectQuery

Als DirectQuery wordt gebruikt, wordt er geen data geïmporteerd of gekopieerd in Power BI Desktop. Terwijl we visualisaties maken of ermee interacteren, bevraagt Power BI Desktop de onderliggende databron, wat betekent dat we altijd met de huidige data werken. Deze methode biedt echter beperkte mogelijkheden voor databewerking, in tegenstelling tot de importmethode.

Hier is een link die dieper ingaat op de DirectQuery-methode.

6. Query Editor

Alle geselecteerde tabellen worden in Power BI Desktop geladen en weergegeven als afzonderlijke datasets in de Data view.

Van hieruit kunnen we onze datasets aanpassen. Hiervoor gebruiken we de Query Editor. De Query Editor kan worden gebruikt om datasets te wijzigen, ongeacht hun databron. We kunnen bewerkingen uitvoeren zoals het hernoemen van een dataset, het verwijderen van één of meerdere kolommen, enz. in de Query Editor.

Het linkerdeelvenster toont het aantal actieve queries, terwijl het rechterdeelvenster het paneel Query Settings wordt genoemd en alle stappen toont die aan een query zijn gekoppeld.

De Advanced Editor

De Advanced Editor toont de code voor de query die wordt uitgevoerd op de databron. De syntaxis komt overeen met M, de Power Query-formuletaal. Je kunt ook je eigen code schrijven.

Je werk opslaan

Nadat je de nodige aanpassingen aan de data hebt gedaan via de Query Editor, kies je Close & Apply in het menu File van de Query Editor. Dit past de wijzigingen toe op de data in Power BI Desktop.

7. Datasets samenvoegen

Datasets samenvoegen is handig wanneer we één of meer datasets willen combineren tot één. Dit samenvoegen gebeurt ook via de Query Editor. Deze keer laden we drie tabellen uit de SuperstoreUS-database: Orders, Customers en Returns.

De kolom Fields is gevuld met de drie geselecteerde tabellen. Klik nu op de knop Edit Queries en ga naar de optie Combine, waar de vervolgkeuzelijst de optie Merge toont. We gebruiken dit om een nieuwe query te maken door bestaande queries samen te voegen.

Het dialoogvenster Merge opent (zoals hieronder weergegeven), en we selecteren de tabellen die we willen samenvoegen en het type join dat we willen. We moeten kolommen selecteren die in beide tabellen voorkomen.

Laten we een nieuwe query maken en deze Orders and customers. noemen. We combineren via deze query de tabellen Orders en Customers.

Deze nieuwe query bevat alle primaire kolommen uit de tabel orders en relatiekolommen uit de tabel customers. Verwijder alle relatiekolommen behalve de laatste, die de query Customers vertegenwoordigt. Selecteer vervolgens kolommen uit de query Customers om toe te voegen aan de nieuw samengevoegde query.

De volgende demo maakt het proces duidelijker.

Deze samengevoegde query bestaat uit één database met alle gewenste kolommen. We kunnen nu eenvoudig met deze ene database werken in plaats van met meerdere databronnen, wat tot verwarring kan leiden.

8. Een dashboard bouwen en publiceren

Als de dataset klaar is en alle bewerkingen zijn uitgevoerd, kunnen we verdergaan met het maken van het dashboard. Een Power BI-dashboard, ook wel canvas genoemd, bestaat uit meerdere visualisaties op één pagina die helpen om een verhaal te vertellen. Deze visualisaties, tiles genoemd, worden vanuit de rapporten aan het dashboard vastgemaakt.

Laten we nu proberen te begrijpen welke inzichten we kunnen halen uit de superstore-dataset (Bron: Power BI Dashboard):

• Winst per staat

• Omzet & winst per segment

• Omzet & winst per regio

• Omzet per subcategorie

• Winst per regio

• Hoeveelheid

Na het aanpassen van formaat, uiterlijk en kleur krijgen we een dashboard dat lijkt op het onderstaande.

Superstore-dashboard

Publiceren

Data is pas echt waardevol als die gedeeld kan worden binnen een team of organisatie. Het gegenereerde dashboard of de rapporten kunnen ook worden gedeeld door ze te publiceren naar de Power BI-service. We kunnen vervolgens de Power BI-apps gebruiken om de dashboards/rapporten te bekijken of ermee te interacteren.

9. Conclusie

SQL en Power BI samen gebruiken tilt data-analyse naar een hoger niveau. We kunnen SQL Server eenvoudig verbinden met Power BI en de data er direct in ophalen. Power BI stelt gebruikers in staat om met één klik tussen verbindingen te schakelen om in-memory queries toe te passen op een grotere dataset. SQL is een bijzonder nuttige tool en kan, gecombineerd met de expertise van Power BI, de analyse krachtiger en inzichtelijker maken.

Referenties

• What is Power BI Desktop?
• Power BI Dashboard – Creating Dashboard In Power BI From A Report (Door Vishal Padghan, edureka)