Condividi tramite

Supporto di query native nei connettori personalizzati di Power Query

Annotazioni

Questo articolo illustra gli argomenti avanzati relativi all'implementazione del supporto di query nativa per i connettori personalizzati, oltre a query folding. 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. Puoi anche abilitare la funzionalità per mantenere il folding delle query durante 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.

Prerequisiti

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.

Modificare le SQLCapabilities del tuo connettore

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 effettuare test manuali e definire la destinazione per la query nativa.

Testare manualmente le funzionalità di query native del connettore

Annotazioni

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 del server
  • Nome del 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.

Schermata del connettore disponibile all'interno dell'esperienza di ottenere dati di Power BI Desktop.

Per la finestra di dialogo del connettore immettere i parametri per il server e il nome del database. Quindi, seleziona OK.

Screenshot della finestra di dialogo del connettore con server e database come parametri.

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.

Screenshot dell'opzione Trasforma dati dal menu contestuale all'interno della finestra Navigatore.

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. Ispezionare la barra delle formule dell'ultimo passaggio per comprendere meglio come il connettore deve navigare 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 aiutano semplicemente a navigare una tabella verso 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. Una volta individuata la riga, il [Data] al di fuori dell'elenco {} permette 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.

Screenshot di una tabella che mostra i valori e i campi usati per il passaggio di navigazione.

Testare la query nativa

Con l'obiettivo ora identificato, creare un passaggio personalizzato dopo il passaggio di navigazione selezionando l'icona fx nella barra della formula.

Screenshot del pulsante fx all'interno della formula usata per creare un passaggio personalizzato.

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.

Screenshot del messaggio di avviso della necessità di autorizzazione per eseguire questa query del database nativo.

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.

Screenshot che mostra come approvare una finestra di dialogo per una query nativa del database.

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.

Screenshot della query nativa eseguita durante lo sviluppo e il test iniziale del connettore.

Implementare la logica di query nativa nel connettore

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 affinché tale navigazione 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, Datae 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 sempre essere passato esattamente come è stato immesso dall'utente finale durante il processo di acquisizione 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, IndexNameo 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.

DefaultOptions

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 applicate, compilare il connettore e caricarlo in Power BI Desktop per il test e la validazione.

Testare e convalidare il connettore

In Power BI Desktop, con il nuovo connettore personalizzato implementato, avvia 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.

Screenshot della finestra di dialogo del connettore con il campo lungo di testo della query nativa visualizzato.

Dopo aver selezionato OK, viene visualizzata un'anteprima della tabella della query nativa eseguita in una nuova finestra di dialogo.

Screenshot della finestra di dialogo con un'anteprima della tabella della query nativa eseguita.

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.

Annotazioni

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.

  • Last updated on