Condividi tramite

Piano di query per Power Query (anteprima)

  • Articolo

Il piano di query per Power Query è una funzionalità che offre una visualizzazione migliore della valutazione della query. È utile determinare il motivo per cui una determinata query potrebbe non essere piegata in un determinato passaggio.

Tramite un esempio pratico, questo articolo illustra il caso d'uso principale e i potenziali vantaggi derivanti dall'uso della funzionalità del piano di query per esaminare i passaggi della query. Gli esempi usati in questo articolo sono stati creati usando il database di esempio AdventureWorksLT per Azure SQL Server, che è possibile scaricare dai database di esempio AdventureWorks.

Nota

La funzionalità del piano di query per Power Query è disponibile solo in Power Query Online.

Processo suggerito per usare la funzionalità del piano di query in Power Query esaminando gli indicatori di riduzione delle query, quindi esaminare il piano di query per un passaggio selezionato e infine implementare eventuali modifiche derivate dalla revisione del piano di query.

Questo articolo è stato suddiviso in una serie di passaggi consigliati per interpretare il piano di query. Questi passaggi sono i seguenti:

  1. Esaminare gli indicatori di riduzione delle query.
  2. Selezionare il passaggio di query per esaminare il piano di query.
  3. Implementare le modifiche apportate alla query.

Usare la procedura seguente per creare la query nel proprio ambiente Power Query Online.

  1. Da Power Query - Scegliere l'origine dati, selezionare Query vuota.

  2. Sostituire lo script della query vuota con la query seguente.

    let
  Source = Sql.Database("servername", "database"),
  Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
  #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
  #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
  #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
in
  #"Kept bottom rows"

  3. Modificare servername e database con i nomi corretti per il proprio ambiente.

  4. (Facoltativo) Se si sta provando a connettersi a un server e a un database per un ambiente locale, assicurarsi di configurare un gateway per tale ambiente.

  5. Selezionare Avanti.

  6. Nella editor di Power Query selezionare Configura connessione e specificare le credenziali per l'origine dati.

Nota

Per altre informazioni sulla connessione a un'istanza di SQL Server, vedere Database di SQL Server.

Dopo aver seguito questi passaggi, la query sarà simile a quella nell'immagine seguente.

Query di esempio con indicatori di riduzione delle query abilitati. Questa query si connette alla tabella SalesOrderHeader e seleziona alcune colonne degli ultimi cinque ordini con un valore TotalDue superiore a 1000.

Nota

Questo articolo usa un esempio semplificato per presentare questa funzionalità, ma i concetti descritti in questo articolo si applicano a tutte le query. È consigliabile avere una buona conoscenza della riduzione delle query prima di leggere il piano di query. Per altre informazioni sulla riduzione delle query, vedere Nozioni di base sulla riduzione delle query.

1. Esaminare gli indicatori di riduzione delle query

Nota

Prima di leggere questa sezione, è consigliabile esaminare l'articolo sugli indicatori di riduzione delle query.

Il primo passaggio di questo processo consiste nel esaminare la query e prestare particolare attenzione agli indicatori di riduzione della query. L'obiettivo è esaminare i passaggi contrassegnati come non piegati. È quindi possibile verificare se apportare modifiche alla query complessiva potrebbe rendere completamente le trasformazioni.

Indicatori di riduzione delle query per la query di esempio all'interno del riquadro Passaggi applicati.

Per questo esempio, l'unico passaggio che non può essere piegato è Mantieni le righe inferiori, che è facile da identificare tramite l'indicatore di passaggio non piegato . Questo passaggio è anche l'ultimo passaggio della query.

L'obiettivo è ora esaminare questo passaggio e comprendere cosa viene ripiegato all'origine dati e cosa non può essere piegato.

2. Selezionare il passaggio della query per esaminare il piano di query

Il passaggio Mantieni le righe inferiori è stato identificato come passaggio di interesse perché non viene ridotto all'origine dati. Fare clic con il pulsante destro del mouse sul passaggio e selezionare l'opzione Visualizza piano di query. Questa azione visualizza una nuova finestra di dialogo contenente un diagramma per il piano di query del passaggio selezionato.

Finestra di dialogo Piano di query che mostra una vista diagramma per il piano di query con nodi connessi da righe. Power Query tenta di ottimizzare la query sfruttando la valutazione differita e la riduzione delle query, come indicato in Nozioni di base sulla riduzione delle query. Questo piano di query rappresenta la conversione ottimizzata della query M nella query nativa inviata all'origine dati. Include anche tutte le trasformazioni eseguite dal motore di Power Query. L'ordine in cui vengono visualizzati i nodi segue l'ordine della query a partire dall'ultimo passaggio o output della query, rappresentato all'estrema sinistra del diagramma e in questo caso è il nodo Table.LastN che rappresenta il passaggio Righe inferiori mantenute.

Nella parte inferiore della finestra di dialogo è disponibile una barra con icone che consentono di ingrandire o ridurre la visualizzazione del piano di query e altri pulsanti per gestire la visualizzazione. Per l'immagine precedente, l'opzione Adatta per visualizzare da questa barra è stata usata per apprezzare meglio i nodi.

Finestra di dialogo Piano di query con i nodi ingranditi per una visualizzazione migliore.

Nota

Il piano di query rappresenta il piano ottimizzato. Quando il motore sta valutando una query, tenta di piegare tutti gli operatori in un'origine dati. In alcuni casi, potrebbe anche eseguire un riordinamento interno dei passaggi per massimizzare la riduzione. Tenendo presente questo aspetto, i nodi/operatori rimasti in questo piano di query ottimizzato in genere contengono la query dell'origine dati "piegata" e gli operatori che non possono essere piegati e valutati localmente.

Identificare i nodi piegati da altri nodi

È possibile identificare i nodi in questo diagramma come due gruppi:

  • Nodi piegati: questo nodo può essere o Value.NativeQuery "origine dati", ad esempio Sql.Database. Questi valori possono essere identificati anche con l'etichetta remota sotto il nome della funzione.
  • Nodi non piegati: altri operatori di tabella, ad esempio Table.SelectRows, Table.SelectColumnse altre funzioni che non possono essere piegate. Questi valori possono essere identificati anche con le etichette Analisi completa e Streaming.

L'immagine seguente mostra i nodi piegati all'interno del rettangolo rosso. Il resto dei nodi non può essere ripiegato nell'origine dati. Sarà necessario esaminare il resto dei nodi perché l'obiettivo è tentare di ripieghere tali nodi nell'origine dati.

Controlli visualizzazione piano di query nella parte inferiore della finestra di dialogo con l'opzione adatta alla visualizzazione selezionata.È possibile selezionare Visualizza dettagli nella parte inferiore di alcuni nodi per visualizzare informazioni estese. Ad esempio, i dettagli del Value.NativeQuery nodo mostrano la query nativa (in SQL) che verrà inviata all'origine dati.

Visualizzazione dei dettagli per il nodo Value.NativeQuery nel piano di query. La query illustrata qui potrebbe non essere esattamente la stessa query inviata all'origine dati, ma si tratta di un'approssimazione ottimale. Per questo caso, indica esattamente quali colonne verranno sottoposte a query dalla tabella SalesOrderHeader e quindi come filtra la tabella usando il campo TotalDue per ottenere solo le righe in cui il valore per tale campo è maggiore di 1000. Il nodo accanto a esso, Table.LastN, viene calcolato localmente dal motore di Power Query, perché non può essere piegato.

Nota

Gli operatori potrebbero non corrispondere esattamente alle funzioni usate nello script della query.

Esaminare i nodi non piegati e prendere in considerazione le azioni per rendere la trasformazione piegata

A questo punto sono stati determinati i nodi che non è stato possibile piegare e verranno valutati in locale. Questo caso ha solo il Table.LastN nodo, ma in altri scenari potrebbe avere molti altri.

L'obiettivo è applicare modifiche alla query in modo che il passaggio possa essere piegato. Alcune delle modifiche che è possibile implementare potrebbero variare dalla riorganizzazione dei passaggi all'applicazione di una logica alternativa alla query più esplicita all'origine dati. Ciò non significa che tutte le query e tutte le operazioni siano piegabili applicando alcune modifiche. È tuttavia consigliabile determinare la prova e l'errore se la query potrebbe essere ripiegata.

Poiché l'origine dati è un database di SQL Server, se l'obiettivo è recuperare gli ultimi cinque ordini dalla tabella, è consigliabile usare le clausole TOP e ORDER BY in SQL. Poiché non esiste alcuna clausola BOTTOM in SQL, la Table.LastN trasformazione in PowerQuery non può essere convertita in SQL. È possibile rimuovere il Table.LastN passaggio e sostituirlo con:

  • Un ordinamento decrescente in base alla colonna SalesOrderID nella tabella, poiché questa colonna determina quale ordine passa per primo e quale è stato immesso per ultimo.
  • Selezionare le prime cinque righe dopo l'ordinamento della tabella, questa trasformazione esegue lo stesso risultato di se fosse una riga inferiore mantenuta (Table.LastN).

Questa alternativa equivale alla query originale. Anche se questa alternativa in teoria sembra valida, è necessario apportare le modifiche per verificare se questa alternativa restituirà completamente questo nodo all'origine dati.

3. Implementare le modifiche alla query

Implementare l'alternativa illustrata nella sezione precedente:

  1. Chiudere la finestra di dialogo piano di query e tornare al editor di Power Query.

  2. Rimuovere il passaggio Mantieni le righe inferiori.

  3. Ordinare la colonna SalesOrderID in ordine decrescente.

    Ordinamento della colonna SalesOrderID in ordine decrescente usando il menu filtro automatico.

  4. Selezionare l'icona della tabella nell'angolo superiore sinistro della visualizzazione anteprima dati e selezionare l'opzione Mantieni le prime righe. Nella finestra di dialogo passare il numero cinque come argomento e premere OK.

    Usando il menu di scelta rapida della tabella per selezionare la trasformazione Mantieni le prime righe per mantenere solo le prime cinque righe.

Dopo aver implementato le modifiche, controllare di nuovo gli indicatori di riduzione della query e verificare se fornisce un indicatore piegato.

Tutti gli indicatori di riduzione delle query sono verdi e mostrano che possono essere piegati. La tabella finale fornisce le stesse righe ma in un ordine diverso. A questo punto è possibile esaminare il piano di query dell'ultimo passaggio, che è ora Mantieni le prime righe. Ora sono presenti solo nodi piegati. Selezionare Visualizza dettagli in Value.NativeQuery per verificare la query inviata al database.

Nuovo piano di query dopo aver apportato le modifiche alla query, che ora presenta solo nodi piegati, con Value.NativeQuery che mostra l'istruzione SQL completa che valuta la query.

Anche se questo articolo suggerisce l'alternativa da applicare, l'obiettivo principale è imparare a usare il piano di query per analizzare la riduzione delle query. Questo articolo fornisce anche la visibilità degli elementi inviati all'origine dati e delle trasformazioni che verranno eseguite in locale.

È possibile modificare il codice per visualizzare l'impatto che ha nella query. Usando gli indicatori di riduzione delle query, si avrà anche un'idea migliore dei passaggi che impediscono la riduzione della query.