Hoe de functionaliteit voor Directe Query van Power BI te gebruiken – techsyncer

Wanneer u Power BI-rapporten maakt, verwachten de kijkers dat het rapport periodiek wordt vernieuwd en dat de gegevens voortdurend worden bijgewerkt. Hoe? Handmatig bijwerken van de rapporten werkt, maar Power BI Direct Query biedt een veel betere functie, geplande vernieuwing.

In deze tutorial leert u hoe u kunt profiteren van de functionaliteit van Power BI Direct Query door een dataset die is opgeslagen in een server te verbinden en query’s uit te voeren tegen uw gegevens.

Blijf afgestemd en verhoog de productiviteit terwijl u tijd bespaart in het proces!

Vereisten

Deze tutorial zal een praktische demonstratie zijn. Zorg ervoor dat u het volgende hebt:

Power BI desktop – Deze tutorial maakt gebruik van Power BI desktopversie 2.109.1021.0.

Een SQL Server.

A code editor – This tutorial uses VS code version 1.71.

Microsoft On-premises Data Gateway.

SQL Server Management Studio (SSMS) – Deze handleiding maakt gebruik van SQL Server Management Studio 18.12.1.

Een SQL-database maken om te beheren met Power BI Direct Query

Power BI Direct Query maakt het mogelijk om rechtstreeks verbinding te maken met een dataset en biedt uw project de mogelijkheid om live gegevens naar uw project te uploaden. Maar voordat u een SQL-database en verbinding maakt, moet u de servernaam en de gegevensbronnaam weten om de juiste details te verkrijgen.

Volg deze stappen om een SQL-database te maken:

1. Zoek naar ODBC in uw zoekbalk, zoek en klik op ODBC Data Source Administrator (64-bit) om deze te openen.

2. Ga vervolgens naar het tabblad Systeem-DSN in het venster ODBC Data Source Administrator en klik op Toevoegen om een nieuwe gegevensbron toe te voegen.

3. Selecteer de SQL Server-driver uit de onderstaande lijst en klik op Voltooien om een SQL-gegevensbron te maken.

DirectQuery ondersteunt niet elke gegevensbron. MySQL wordt niet ondersteund, terwijl SQL dat wel is.

4. Noem nu je gegevensbron (MssqlDataSource), selecteer een SQL-instantie (POWERSERVER\POWERSERVER) geïnstalleerd op je machine, en klik op Voltooien.

Houd de naam van de SQL Server en de gegevensbron in de gaten, want je hebt ze nodig voor je Python-verbindingsreeks.

5. Bekijk de details van je gegevensbron en klik op Test Gegevensbron om je gegevensbron te testen.

Als de verbinding goed is, krijg je een TEST VOLTOOID SUCCESVOL bericht, zoals hieronder weergegeven.

Verifying testing the data source completed

Verbinding maken met de SQL Server

Nu je een gegevensbron hebt gemaakt voor je SQL-server, zul je die gegevensbron gebruiken om een verbindingsreeks te maken voor je Python-code.

1. Start Visual Studio op en druk op CTRL+SHFT+` om een nieuwe terminal te openen.

2. Voer vervolgens de onderstaande sqlcmd-opdracht uit om in te loggen op je SQL-serverinstantie.

sqlcmd -S SQL_SERVER -E

3. Als je bent ingelogd, voer dan de volgende query’s uit om een nieuwe database (CREATE DATABASE) genaamd MSSQLDB te maken.

CREATE DATABASE MSSQLDB;
GO

4. Maak een Python-bestand DBconnect.py en voeg de onderstaande code toe, waarmee je verbinding kunt maken met je SQL-database met behulp van de SQLAlchemy ORM voor Python.

De engine markeert het startpunt van je SQLAlchemy-toepassing. De engine beschrijft de verbindingspool en het dialect voor de Python Database API Specificatie (DBAPI). Python DBAPI is een specificatie binnen Python om gemeenschappelijke gebruikspatronen voor alle databaseverbindingen te definiëren. Deze specificatie communiceert met de opgegeven database.

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# Een verbindingsreeks maken, waarbij urllib wordt gebruikt om de tekst te formatteren.
conn = urllib.parse.quote_plus(
# Waarden voor de verbindingsreeks worden overgenomen uit de gegevensbron
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# Verbinding maken met sqlalchemy-engine 
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. Maak een Python-bestand genaamd CreateTable.py in je hoofdmap en voeg onderstaande code toe, en voer het uit. De onderstaande code maakt een tabel genaamd students in je SQL-database.

//CreateTable.py
from DBconnect import coxn
from sqlalchemy import Table, Column, Integer, String, MetaData

# Maakt een Metadata-constructie die definities van tabellen bevat 
  # en bijbehorende objecten zoals index, view, triggers, etc.
meta = MetaData()

# Vertegenwoordigt de CREATE TABLE in reguliere SQL-syntaxis om een tabel te maken.
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# Gebruikt het engine-object om alle gedefinieerde tabelobjecten te maken 
  # en slaat de informatie op in metadata.
meta.create_all(coxn)

6. Open vervolgens je SSMS en controleer de database, tabel en kolommen die zijn aangemaakt.

7. Maak een Python-bestand genaamd WriteToTable.py, voeg de onderstaande code toe en voer het uit.

Deze code bevat de logica voor het schrijven van waarden naar de databasetabel.

//WriteToTable.py
# Importeren van de tabel students uit het bestand CreateTable.py.
from CreateTable import students
# Importeren van de verbindingsreeks coxn uit het bestand DBconnect.py.
from DBconnect import coxn
# Invoegen van meerdere waarden in de tabel students
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

Voorbereiden van On-premises Data Gateway

Nu je een verbindingsreeks hebt gemaakt en erin geslaagd bent verbinding te maken met de database, moet je een Data Gateway maken. Deze Data Gateway is verantwoordelijk voor het verbinden van je database met Power BI.

1. Open de On-premises data gateway en meld je aan.

2. Controleer de status van je On-premises data gateway en bevestig dat deze online is, zoals hieronder weergegeven.

Checking if the On-premises data gateway is online

3. Open vervolgens Power BI, klik op Gegevens ophalen onder het lint Start en kies SQL Server om de verbinding van je gegevensbron met Power BI te initiëren.

4. Maak nu verbinding met DirecQuery met het volgende:

Voer je Server-naam en Database-naam in de velden inZorg ervoor dat de optie DirectQuery is geselecteerd.Klik op OK om verbinding te maken met DirectQuery.

5. Selecteer een tabel (studenten) uit de database en klik op Laden om de gegevens te laden.

6. Klik vervolgens op het tabelpictogram om een tabelvisualisatie te gebruiken om de gegevens te bekijken.

De tabel hieronder toont de gegevens die je hebt geladen uit de databasetabel

7. Klik op het menu Bestand om acties uit te voeren in Power BI.

8. Klik nu op Publiceren → Publiceren naar Power BI om je datarapport te publiceren.

9. Klik op Openen <jouw rapport> zodra je publicatie succesvol is, waardoor je browser wordt omgeleid naar de lijst met recente activiteiten die je hebt uitgevoerd in Power BI (stap 10).

10. Klik op het rapport om het te openen, zoals hieronder wordt weergegeven.

Bij de eerste configuratie kun je een gateway probleem tegenkomen zoals hieronder weergegeven, aangezien je de gegevensbron van het project nog niet hebt gekoppeld aan de gegevensgateway van Power BI.

11. Klik op het instellingenpictogram onder de kolom Acties om een nieuwe gatewayverbinding toe te voegen. Hiermee kun je het configuratiefout van de gateway oplossen.

Initiating adding a new gateway connection

12. Configureer de nieuwe gatewayverbinding en klik op Maken om een nieuwe verbinding te maken tussen je gegevensbron en de gateway.

13. Ten slotte, zodra je ziet dat de gateway actief is, selecteer je de naam van de gegevensbron (MssqlDataSource) uit het vervolgkeuzemenu Kaarten naar en klik je op Toepassen.

Gegevensrapporten bekijken en beheren

Nadat je een verbinding hebt tot stand gebracht tussen de gateway en Power BI, kun je je rapport bekijken en een vernieuwingsschema instellen.

1. Ga naar de startpagina van je Power BI-rapporten.

De prestaties van Power Bi Direct Query zijn afhankelijk van de onderliggende datasetbron. De tijd die de onderliggende dataset nodig heeft om te reageren op verzoeken zal bepalen hoe snel de gebruiker het rapport kan bekijken. Of dat ze überhaupt de rapporten succesvol kunnen bekijken.

2. Klik vervolgens op het rapport, zoals hieronder wordt weergegeven, om het te openen.

Nadat je het rapport hebt geopend, zou je de gegevens moeten zien, zoals hieronder wordt weergegeven.

3. Ga terug naar de rapport startpagina en klik op Vernieuwen → Schema vernieuwen om een geplande vernieuwing van het rapport in te stellen.

Naast het maken van live rapporten, stelt Power Bi Direct Query je in staat om een geplande vernieuwing in te stellen, waarbij je rapporten automatisch worden bijgewerkt.

4. Selecteer nu het vernieuwingsinterval van jouw keuze uit het vervolgkeuzemenu en klik op Toepassen om de wijzigingen toe te passen.

5. Open je Python-script WriteToTable.py en voeg de onderstaande code toe om meer vermeldingen aan je database toe te voegen, en voer het script uit.

Het uitvoeren van deze code geeft geen uitvoer, maar je zult de ingevoegde waarden later in de tabel verifiëren in de volgende stappen.

Er is geen Power Query Editor voor multi-dimensionale bronnen zoals SAP Business Warehouse, wat beperkt wat je kunt bereiken met je gegevens.

//WriteToTable.py
# Importeer de tabel met studenten uit het bestand CreateTable.py.
from CreateTable import students
# Importeer de coxn-verbindingssnaren uit het bestand DBconnect.py.
from DBconnect import coxn

# Invoegen van meerdere waarden in de tabel met studenten.
ins = students.insert().values([
{'name':'Damian','lastname':'Marley'},
{'name':'Rita','lastname':'Marley'},
{'name':'Ziggy','lastname':'Marley'},
{'name':'Sam','lastname':'Dylan'},
{'name':'Jakob','lastname':'Dylan'},
{'name':'Maria','lastname':'Dylan'}
])
# Aanmaken van een verbindingscursor.
conn = coxn.connect()
# Uitvoeren van het insert() commando.
conn.execute(ins)

6. Schakel vervolgens over naar je Power BI-browser en klik op het tabblad Datasets + dataflows.

Als alles goed gaat, zie je de laatst vernieuwde tijd en de volgende vernieuwing van de dataset zoals in de onderstaande schermafbeelding.

Merk op dat Power Bi Direct Query gevoelig is voor wijzigingen en opmaak. Je kunt een foutmelding krijgen als je Power Query Editor complexe stappen bevat.

Verifying the last refreshed time and next refresh of the dataset

Zoals verwacht gebeurt de herladen automatisch na 15 minuten, en de volgende vernieuwing is ingesteld om over nog eens 15 minuten plaats te vinden.

Conclusie

Doorheen deze tutorial heb je geleerd hoe je de Power Bi Direct Query-functionaliteit kunt gebruiken om verbinding te maken met je dataset die is opgeslagen op je server. Je hebt ook kennis gemaakt met het direct uitvoeren van queries tegen je gegevens.

Het maken van rapporten is een langdurig proces van verzamelen, schoonmaken, uploaden naar een gegevensbron, gegevens laden naar Power BI, en werken aan de Power BI-visuele elementen. Dit eerder genoemde proces is gegeven. Maar daarna kun je ofwel de dataset handmatig vernieuwen of de gegevens automatisch vernieuwen volgens het ingestelde schema.

Power BI Direct Query heeft zijn nadelen bij het verwerken en manipuleren van gegevens in je dataset. Maar het feit dat je een rapport slechts één keer hoeft te maken en nooit meer aan de backend hoeft te werken, is een enorme stimulans bij het gebruik van deze functie.