Supporto di query native nei connettori personalizzati di Power Query
Nota
Questo articolo illustra gli argomenti avanzati relativi all'implementazione del supporto di query native per i connettori personalizzati, oltre alla riduzione delle query. Questo articolo presuppone che tu abbia già una conoscenza funzionante di questi concetti.
Per altre informazioni sui connettori personalizzati di Power Query, vedere Panoramica di Power Query SDK.
In Power Query è possibile eseguire query native personalizzate sull'origine dati per recuperare i dati desiderati. È anche possibile abilitare la funzionalità per mantenere la riduzione delle query in questo processo e i processi di trasformazione successivi eseguiti all'interno di Power Query.
L'obiettivo di questo articolo è illustrare come implementare tale funzionalità per il connettore personalizzato.
Questo articolo usa come punto di partenza un esempio che usa il driver ODBC SQL per l'origine dati. L'implementazione della funzionalità di query nativa è attualmente supportata solo per i connettori ODBC conformi allo standard SQL-92.
Il connettore di esempio usa il driver SQL Server Native Client 11.0 . Assicurarsi di avere installato questo driver per seguire questa esercitazione.
È anche possibile visualizzare la versione completata del connettore di esempio dalla cartella Fine nel repository GitHub.
SqlCapabilities
Nel record del connettore di esempio è possibile trovare un campo di record con il nome Sql92Translation
e il valore PassThrough. Questo nuovo campo è necessario per passare la query nativa usando Power Query senza alcuna convalida.
SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
// Place custom overrides here
// The values below are required for the SQL Native Client ODBC driver, but might
// not be required for your data source.
SupportsTop = false,
SupportsDerivedTable = true,
Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
FractionalSecondsScale = 3,
Sql92Translation = "PassThrough"
]),
Assicurarsi che questo campo venga visualizzato nel connettore prima di procedere. In caso contrario, verranno visualizzati avvisi ed errori in un secondo momento quando si tratta di usare una funzionalità non supportata perché non è dichiarata dal connettore.
Compilare il file del connettore (come .mez o.pqx) e caricarlo in Power BI Desktop per il test manuale e definire la destinazione per la query nativa.
Nota
Per questo articolo si userà il database di esempio AdventureWorks2019. Tuttavia, è possibile seguire qualsiasi database di SQL Server a scelta e apportare le modifiche necessarie quando si tratta delle specifiche del database scelto.
Il modo in cui verrà implementato il supporto delle query native in questo articolo è che all'utente verrà richiesto di immettere tre valori:
- Nome server
- Nome database
- Query nativa a livello di database
Ora all'interno di Power BI Desktop passare all'esperienza Recupera dati e trovare il connettore con il nome SqlODBC Sample.
Per la finestra di dialogo del connettore immettere i parametri per il server e il nome del database. Selezionare OK.
Verrà visualizzata una nuova finestra dello strumento di navigazione. In Strumento di navigazione è possibile visualizzare il comportamento di spostamento nativo dal driver SQL che visualizza la visualizzazione gerarchica del server e dei database al suo interno. Fare clic con il pulsante destro del mouse sul database AdventureWorks2019 e quindi scegliere Trasforma dati.
Questa selezione consente di accedere all'editor di Power Query e a un'anteprima di ciò che è effettivamente la destinazione della query nativa, perché tutte le query native devono essere eseguite a livello di database. Esaminare la barra della formula dell'ultimo passaggio per comprendere meglio come il connettore deve passare alla destinazione delle query native prima di eseguirle. In questo caso la barra della formula visualizza le informazioni seguenti:
= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]
Source è il nome del passaggio precedente che, in questo caso, è semplicemente la funzione pubblicata del connettore con i parametri passati. L'elenco e il record all'interno di esso consentono di spostarsi in una tabella in una riga specifica. La riga è definita dai criteri del record in cui il campo Name deve essere uguale a AdventureWorks2019 e il campo Kind deve essere uguale a Database. Quando la riga si trova, l'esterno [Data]
dell'elenco {}
consente a Power Query di accedere al valore all'interno del campo Dati , che in questo caso è una tabella. È possibile tornare al passaggio precedente (Origine) per comprendere meglio questo spostamento.
Con la destinazione ora identificata, creare un passaggio personalizzato dopo il passaggio di spostamento selezionando l'icona fx nella barra della formula.
Sostituire la formula all'interno della barra della formula con la formula seguente e quindi selezionare INVIO.
= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
FROM [Person].[Address]")
Dopo aver applicato questa modifica, viene visualizzato un avviso sotto la barra della formula che richiede l'autorizzazione per eseguire la query nativa sull'origine dati.
Selezionare Modifica autorizzazione. Viene visualizzata una nuova finestra di dialogo Query database nativa che tenta di avvisare l'utente delle possibilità di esecuzione di query native. In questo caso, si sa che questa istruzione SQL è sicura, quindi selezionare Esegui per eseguire il comando.
Dopo aver eseguito la query, viene visualizzata un'anteprima della query nell'editor di Power Query. Questa anteprima convalida che il connettore sia in grado di eseguire query native.
Con le informazioni raccolte dalle sezioni precedenti, l'obiettivo è ora tradurre tali informazioni nel codice per il connettore.
Il modo in cui è possibile eseguire questa conversione consiste nell'aggiungere un nuovo campo record NativeQueryProperties al record Publish del connettore, che in questo caso è il SqlODBC.Publish
record. Il NativeQueryProperties
record svolge un ruolo fondamentale nella definizione del modo in cui il connettore interagirà con la Value.NativeQuery
funzione.
Il nuovo campo record è costituito da due campi:
- NavigationSteps: questo campo definisce il modo in cui lo spostamento deve essere eseguito o gestito dal connettore. Contiene un elenco di record che descrivono i passaggi per passare ai dati specifici su cui eseguire una query usando la
Value.NativeQuery
funzione . All'interno di ogni record, definisce quali parametri sono necessari o necessari affinché tale spostamento raggiunga la destinazione desiderata. - DefaultOptions: questo campo consente di identificare il modo in cui determinati parametri facoltativi devono essere inclusi o aggiunti al record delle
Value.NativeQuery
opzioni. Fornisce un set di opzioni predefinite che possono essere usate durante l'esecuzione di query sull'origine dati.
I passaggi di spostamento possono essere classificati in due gruppi. Il primo contiene i valori immessi dall'utente finale, ad esempio il nome del server o del database, in questo caso. Il secondo contiene i valori derivati dall'implementazione specifica del connettore, ad esempio il nome dei campi che non vengono visualizzati all'utente durante l'esperienza di recupero dei dati. Questi campi possono includere Name
, Kind
, Data
e altri a seconda dell'implementazione del connettore.
Per questo caso, era presente un solo passaggio di navigazione costituito da due campi:
- Nome: questo campo è il nome del database passato dall'utente finale. In questo caso, era
AdventureWorks2019
, ma questo campo deve essere sempre passato così com'è da quello immesso dall'utente finale durante l'esperienza di recupero dei dati. - Tipo: questo campo è informazioni che non sono visibili all'utente finale ed è specifico per l'implementazione del connettore o del driver. In questo caso, questo valore identifica il tipo di oggetto a cui accedere. Per questa implementazione, questo campo sarà un valore fisso costituito dalla stringa
Database
.
Tali informazioni verranno convertite nel codice seguente. Questo codice deve essere aggiunto come nuovo campo al SqlODBC.Publish
record.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
}
]
Importante
Il nome dei campi fa distinzione tra maiuscole e minuscole e deve essere usato come illustrato nell'esempio precedente. Tutte le informazioni passate ai campi, ConstantValue
, IndexName
o FieldDisplayName
devono essere derivate dal codice M del connettore.
Per i valori che verranno passati da quello immesso dall'utente, è possibile usare la coppia FieldDisplayName
e IndexName
. Per i valori fissi o predefiniti e non possono essere passati dall'utente finale, è possibile usare la coppia ConstantValue
e IndexName
. In questo senso, il record NavigationSteps è costituito da due campi:
- Indici: definisce i campi e i valori da usare per passare al record che contiene la destinazione per la
Value.NativeQuery
funzione. - FieldAccess: definisce il campo che contiene la destinazione, che in genere è una tabella.
Il DefaultOptions
campo consente di passare parametri facoltativi alla Value.NativeQuery
funzione quando si usa la funzionalità di query nativa per il connettore.
Per mantenere la riduzione delle query dopo una query nativa e presupponendo che il connettore abbia funzionalità di riduzione delle query, è possibile usare il codice di esempio seguente per EnableFolding = true
.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
},
DefaultOptions = [
EnableFolding = true
]
]
Con queste modifiche sul posto, compilare il connettore e caricarlo in Power BI Desktop per il test e la convalida.
In Power BI Desktop con il nuovo connettore personalizzato sul posto avviare il connettore dall'esperienza Recupera dati . Quando si avvia il connettore, si noterà che la finestra di dialogo include ora un campo di testo lungo con il nome Query nativa e, tra parentesi, contiene i campi necessari per il funzionamento. Immettere gli stessi valori per il server, il database e l'istruzione SQL immessa in precedenza durante il test del connettore.
Dopo aver selezionato OK, viene visualizzata un'anteprima della tabella della query nativa eseguita in una nuova finestra di dialogo.
Seleziona OK. Una nuova query verrà ora caricata all'interno dell'editor di Power Query in cui è possibile eseguire ulteriori test del connettore in base alle esigenze.
Nota
Se il connettore dispone di funzionalità di riduzione delle query e ha definito EnableFolding=true
in modo esplicito come parte del record facoltativo per Value.NativeQuery
, è possibile testare ulteriormente il connettore nell'editor di Power Query controllando se ulteriori trasformazioni ripiegano nell'origine o meno.