Riduzione delle query nelle query native

In Power Query è possibile definire una query nativa ed eseguirla sull'origine dati. L'articolo Importare dati da un database usando una query di database nativa illustra come eseguire questo processo con più origini dati. Tuttavia, usando il processo descritto in questo articolo, la query non sfrutta la riduzione delle query dai passaggi successivi della query.

Questo articolo illustra un metodo alternativo per creare query native sull'origine dati usando la funzione Value.NativeQuery e mantenere attivo il meccanismo di riduzione delle query per i passaggi successivi della query.

Nota

È consigliabile leggere la documentazione sulla riduzione delle query e sugli indicatori di riduzione delle query per comprendere meglio i concetti usati in questo articolo.

Connettori dati supportati

Il metodo descritto nelle sezioni successive si applica ai connettori dati seguenti:

• Amazon Redshift
• Dataverse (quando si usa un calcolo avanzato)
• Google BigQuery
• PostgreSQL
• SAP HANA
• Snowflake
• SQL Server

Connettersi alla destinazione dall'origine dati

Nota

Per illustrare questo processo, questo articolo usa il connettore SQL Server e il database di esempio AdventureWorks2019. L'esperienza può variare dal connettore al connettore, ma questo articolo illustra i concetti fondamentali su come abilitare le funzionalità di riduzione delle query sulle query native per i connettori supportati.

Quando ci si connette all'origine dati, è importante connettersi al nodo o al livello in cui si vuole eseguire la query nativa. Per l'esempio riportato in questo articolo, tale nodo è il livello di database all'interno del server.

Dopo aver definito le impostazioni di connessione e aver specificato le credenziali per la connessione, viene visualizzata la finestra di dialogo di spostamento per l'origine dati. La finestra di dialogo di spostamento contiene tutti gli oggetti disponibili a cui è possibile connettersi.

Da questo elenco è necessario selezionare l'oggetto in cui viene eseguita la query nativa (nota anche come destinazione). Per questo esempio, l'oggetto è a livello di database.

Nella finestra dello strumento di navigazione in Power Query selezionare e tenere premuto (o fare clic con il pulsante destro del mouse) sul nodo del database nella finestra dello strumento di navigazione e selezionare l'opzione Trasforma dati . Se si seleziona questa opzione, viene creata una nuova query della visualizzazione complessiva del database, ovvero la destinazione necessaria per eseguire la query nativa.

Una volta completata la query nell'editor di Power Query, nel riquadro Passaggi applicati verrà visualizzato solo il passaggio Origine . Questo passaggio contiene una tabella con tutti gli oggetti disponibili nel database, in modo analogo a come sono stati visualizzati nella finestra Strumento di navigazione.

Usare la funzione Value.NativeQuery

L'obiettivo di questo processo è eseguire il codice SQL seguente e applicare più trasformazioni con Power Query che possono essere ripiegate all'origine.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

Il primo passaggio consiste nel definire la destinazione corretta, che in questo caso è il database in cui viene eseguito il codice SQL. Una volta che un passaggio ha la destinazione corretta, è possibile selezionare tale passaggio, in questo caso Source in Applied Steps, e quindi selezionare il pulsante fx nella barra della formula per aggiungere un passaggio personalizzato. In questo esempio sostituire la Source formula con la formula seguente:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

Il componente più importante di questa formula è l'uso del record facoltativo per il quarto parametro della funzione con il campo del record EnableFolding impostato su true.

Nota

Per altre informazioni sulla funzione Value.NativeQuery, vedere l'articolo della documentazione ufficiale.

Dopo aver immesso la formula, viene visualizzato un avviso che richiede l'esecuzione di query native per il passaggio specifico. Selezionare Continua per la valutazione di questo passaggio.

Questa istruzione SQL restituisce una tabella con solo tre righe e due colonne.

Riduzione delle query di test

Per testare la riduzione della query della query, è possibile provare ad applicare un filtro a una delle colonne e verificare se l'indicatore di riduzione della query nella sezione passaggi applicati mostra il passaggio come piegato. In questo caso, è possibile filtrare la colonna DepartmentID in modo che abbia valori non uguali a due.

Dopo aver aggiunto questo filtro, è possibile verificare che gli indicatori di riduzione della query visualizzino ancora la riduzione della query in questo nuovo passaggio.

Per convalidare ulteriormente la query inviata all'origine dati, è possibile selezionare e tenere premuto (o fare clic con il pulsante destro del mouse) sul passaggio Righe filtrate e selezionare l'opzione Visualizza piano di query per controllare il piano di query per tale passaggio.

Nella visualizzazione piano di query è possibile vedere che un nodo con il nome Value.NativeQuery con un collegamento ipertestuale Visualizza dettagli . È possibile selezionare questo collegamento ipertestuale per visualizzare la query esatta inviata al database di SQL Server.

La query nativa viene sottoposta a wrapping intorno a un'altra istruzione SELECT per creare una sottoquery dell'originale. Power Query consente di creare la query più ottimale in base alle trasformazioni usate e alla query nativa fornita.

Suggerimento

Per gli scenari in cui si verificano errori perché la riduzione delle query non era possibile, è consigliabile provare a convalidare i passaggi come sottoquery della query nativa originale per verificare se potrebbero verificarsi conflitti di sintassi o contesto.