Come utilizzare la funzionalità di query diretta di Power BI – techsyncer

Quando si creano report Power BI, gli utenti si aspettano che il report venga aggiornato periodicamente e che i dati siano costantemente aggiornati. Come? Aggiornare manualmente i report funziona, ma Power BI Direct Query offre una funzionalità molto migliore, l’aggiornamento pianificato.

In questo tutorial, imparerai come sfruttare la funzionalità di Power BI Direct Query collegando un set di dati memorizzato in un server ed eseguendo query sui tuoi dati.

Resta sintonizzato e aumenta la produttività risparmiando tempo nel processo!

Prerequisiti

Questo tutorial sarà una dimostrazione pratica. Per seguire, assicurati di avere quanto segue:

Power BI desktop – Questo tutorial utilizza la versione 2.109.1021.0 di Power BI desktop.

Un server SQL.

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

Gateway dati locali di Microsoft.

SQL Server Management Studio (SSMS) – Questo tutorial utilizza SQL Server Management Studio 18.12.1.

Creare un Database SQL da Gestire con Power BI Direct Query

Power Bi Direct Query consente di connettersi direttamente a un set di dati e offre al tuo progetto l’opzione di caricare dati in tempo reale nel tuo progetto. Ma prima di creare un database SQL e una connessione, è necessario conoscere il nome del server e il nome dell’origine dati per acquisire i dettagli corretti.

Per creare un database SQL, seguire questi passaggi:

1. Cerca ODBC nella barra di ricerca, cerca e fai clic su Amministratore origine dati ODBC (a 64 bit) per aprirlo.

2. Successivamente, passare alla scheda DSN di sistema nella finestra Amministratore origine dati ODBC e fare clic su Aggiungi per avviare l’aggiunta di una nuova origine dati.

3. Selezionare il driver SQL Server dall’elenco sottostante e fare clic su Fine per creare un’origine dati SQL.

DirectQuery non supporta tutte le origini dati. MySQL non è supportato, mentre SQL lo è.

4. Ora, assegna un nome alla tua fonte dati (MssqlDataSource), seleziona un’istanza SQL (POWERSERVER\POWERSERVER) installata sulla tua macchina e clicca su Fine.

Prendi nota del nome del server SQL e del nome della fonte dati, poiché li necessiterai per la stringa di connessione in Python.

5. Rivedi i dettagli della tua fonte dati e clicca su Testa fonte dati per verificare la connessione.

Se la connessione è valida, riceverai un messaggio TEST COMPLETATO CON SUCCESSO, come mostrato di seguito.

Verifying testing the data source completed

Connessione al server SQL

Ora che hai creato una fonte dati per il tuo server SQL, la utilizzerai per creare una stringa di connessione nel tuo codice Python.

1. Avvia Visual Studio e premi CTRL+SHFT+` per aprire un nuovo terminale.

2. Successivamente, esegui il comando sqlcmd di seguito per accedere alla tua istanza del server SQL.

sqlcmd -S SQL_SERVER -E

3. Una volta connesso, esegui le seguenti query per creare un nuovo database (CREATE DATABASE) chiamato MSSQLDB.

CREATE DATABASE MSSQLDB;
GO

4. Crea un file Python DBconnect.py e aggiungi il codice qui sotto, che ti consente di connetterti al tuo database SQL utilizzando SQLAlchemy ORM per Python.

L’engine segna il punto di partenza della tua applicazione SQLAlchemy. L’engine descrive la pool di connessioni e il dialetto per il Python Database API Specification (DBAPI). Python DBAPI è una specifica all’interno di Python che definisce modelli di utilizzo comuni per tutti i pacchetti di connessione al database. Questa specifica comunica con il database specificato.

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# Creazione di una stringa di connessione, utilizzando urllib per formattare il testo.
conn = urllib.parse.quote_plus(
# Valori per la stringa di connessione prelevati dalla fonte di dati
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# Creazione di una connessione con il motore sqlalchemy
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. Crea un file Python chiamato CreateTable.py nella tua cartella principale e aggiungi il codice qui sotto, quindi eseguilo. Il codice qui sotto crea una tabella chiamata studenti nel tuo database SQL.

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

# Crea una costruzione Metadata che contiene definizioni di tabelle
  # e oggetti associati come indice, vista, trigger, ecc.
meta = MetaData()

# Rappresenta il CREATE TABLE nella sintassi SQL regolare per creare una tabella.
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# Utilizza l'oggetto motore per creare tutti gli oggetti tabella definiti
  # e memorizza le informazioni in metadata.
meta.create_all(coxn)

6. Successivamente, apri il tuo SSMS e controlla il database, la tabella e le colonne create.

7. Crea un file Python chiamato WriteToTable.py, aggiungi il codice qui sotto e eseguilo.

Questo codice contiene la logica per scrivere i valori nella tabella del database.

//WriteToTable.py
# Importa la tabella studenti dal file CreateTable.py.
from CreateTable import students
# Importa la stringa di connessione coxn dal file DBconnect.py.
from DBconnect import coxn
# Inserisce più valori nella tabella studenti
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

Preparazione del gateway dati in locale

Ora che hai creato una stringa di connessione e sei riuscito a connetterti al database, dovrai creare un gateway dati in locale. Questo gateway dati sarà responsabile della connessione del tuo database a Power BI.

1. Apri il gateway dati in locale e accedi.

2. Verifica lo stato del tuo gateway dati in locale e conferma che sia online, come mostrato di seguito.

Checking if the On-premises data gateway is online

3. Successivamente, apri Power BI, fai clic su Ottieni dati nella scheda Home e scegli SQL Server per iniziare a connettere la tua origine dati a Power BI.

4. Ora, connettiti a DirecQuery con quanto segue:

Inserisci il nome del Server e il nome del Database nei campi corrispondentiAssicurati che sia selezionata l’opzione DirectQuery.Fai clic su OK per connetterti a DirectQuery.

5. Seleziona una tabella (studenti) dal database e fai clic su Carica per caricare i dati.

6. Successivamente, fai clic sull’icona della tabella per utilizzare una visualizzazione tabella per visualizzare i dati.

La tabella qui sotto mostra i dati che hai caricato dalla tabella del database

7. Fai clic sul menu File per accedere alle azioni da eseguire su Power BI.

8. Ora, fai clic su Pubblica → Pubblica su Power BI per pubblicare il tuo report dei dati.

9. Fare clic su Apri <il tuo report> una volta che la pubblicazione è riuscita, reindirizzando il tuo browser all’elenco delle attività recenti che hai svolto in Power BI (passo 10).

10. Fare clic sul report per aprirlo, come mostrato di seguito.

Alla prima configurazione, potresti incontrare un problema di gateway come mostrato di seguito, poiché non hai ancora collegato la fonte dati del progetto al gateway dati di Power BI.

11. Fare clic sull’icona delle impostazioni sotto la colonna Azioni per avviare l’aggiunta di una nuova connessione al gateway. In questo modo, puoi risolvere l’errore di configurazione del gateway.

Initiating adding a new gateway connection

12. Configurare la nuova connessione al gateway e fare clic su Crea per creare una nuova connessione tra la tua fonte dati e il gateway.

13. Infine, una volta che vedi che il gateway è in esecuzione, selezionare il nome della fonte dati (MssqlDataSource) dal menu a discesa Associa e fare clic su Applica.

Visualizzazione e Gestione dei Report sui Dati

Dopo aver stabilito una connessione tra il gateway e Power BI, puoi visualizzare il tuo report e impostare un programma di aggiornamento automatico.

1. Navigare alla pagina principale dei report di Power BI.

Le prestazioni di Power Bi Direct Query dipendono dalla fonte dati sottostante. Il tempo impiegato dalla fonte dati sottostante per rispondere alle richieste determinerà quanto velocemente l’utente può visualizzare il report. O se riusciranno a vedere i report in primo luogo.

2. Successivamente, fare clic sul report, come mostrato di seguito, per aprirlo.

Dopo aver aperto il report, dovresti vedere i dati, come mostrato di seguito.

3. Tornare alla home del report e fare clic su Aggiorna → Aggiorna pianificato per impostare un aggiornamento pianificato del report.

Oltre a creare report in tempo reale, Power Bi Direct Query ti permette di impostare un aggiornamento pianificato, aggiornando automaticamente i tuoi report.

4. Ora, seleziona l’intervallo di aggiornamento desiderato dal menu a discesa e clicca su Applica per applicare le modifiche.

5. Apri il tuo script Python WriteToTable.py e aggiungi il codice qui sotto per inserire ulteriori voci nel tuo database con quanto segue e esegui lo script.

L’esecuzione di questo codice non fornisce output, ma verificherai i valori inseriti nella tabella successivamente nei passaggi seguenti.

Non c’è un editor di Power Query per fonti multidimensionali come SAP Business Warehouse, il che limita ciò che puoi realizzare con i tuoi dati.

//WriteToTable.py
# Importazione della tabella degli studenti dal file CreateTable.py.
from CreateTable import students
# Importazione della stringa di connessione coxn dal file DBconnect.py.
from DBconnect import coxn

# Inserimento di valori multipli nella tabella degli studenti
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'}
])
# Creazione del cursore di connessione.
conn = coxn.connect()
# Esecuzione del comando insert().
conn.execute(ins)

6. Successivamente, passa al tuo browser di Power BI e clicca sulla scheda Datasets + dataflows.

Se tutto va bene, vedrai l’ultimo orario di aggiornamento e il successivo aggiornamento del set di dati come nello screenshot qui sotto.

Nota che Power Bi Direct Query è sensibile a modifiche e formattazioni. Potresti ricevere un errore se il tuo editor di Power Query contiene passaggi complessi.

Verifying the last refreshed time and next refresh of the dataset

Come previsto, il ricaricamento avviene automaticamente dopo 15 minuti e il successivo aggiornamento è programmato per avvenire altri 15 minuti dopo.

Conclusione

In questa guida, hai imparato come utilizzare la funzionalità Power Bi Direct Query per connettere il tuo set di dati memorizzato sul server. Hai anche affrontato l’esecuzione delle query direttamente sui tuoi dati.

Creare report è un lungo processo di raccolta, pulizia, caricamento su una fonte dati, caricamento dei dati in Power BI e lavorare sulle visualizzazioni di Power BI. Questo processo menzionato è fornito. Tuttavia, successivamente, puoi aggiornare il set di dati manualmente o aggiornare i dati automaticamente secondo l’orario impostato.

Power BI Direct Query ha i suoi svantaggi nella gestione e manipolazione dei dati nel tuo set di dati. Ma il fatto che tu possa creare un report una volta e non lavorare mai più sul backend è un incentivo enorme quando si utilizza questa funzione.