Parametri di query M dinamici in Power BI Desktop

Articolo
11/10/2023

Questo articolo descrive come creare e usare parametri di query M dinamici in Power BI Desktop. Con i parametri di query M dinamici, gli autori di modelli possono configurare i valori di filtro o filtro dei dati che i visualizzatori del report possono usare per un parametro di query M. I parametri di query M dinamici offrono agli autori di modelli un maggiore controllo sulle selezioni dei filtri da incorporare nelle query di origine DirectQuery.

Gli autori di modelli comprendono la semantica desiderata dei filtri e spesso sanno come scrivere query efficienti sull'origine dati. Con i parametri di query M dinamici, gli autori di modelli possono garantire che le selezioni dei filtri incorporano nelle query di origine al punto giusto per ottenere i risultati previsti con prestazioni ottimali. I parametri di query M dinamici possono essere particolarmente utili per l'ottimizzazione delle prestazioni delle query.

Guardare Sujata spiegare e usare i parametri di query M dinamici nel video seguente e quindi provarli manualmente.

Nota

Questo video potrebbe usare versioni precedenti di Power BI Desktop o il servizio Power BI.

Prerequisiti

Per eseguire queste procedure, è necessario disporre di una query M valida che usa una o più tabelle DirectQuery.

Creare e usare parametri dinamici

Nell'esempio seguente viene passato un singolo valore a un parametro in modo dinamico.

Aggiungere parametri

In Power BI Desktop selezionare >Trasforma dati Trasforma dati> home per aprire il editor di Power Query.
Nella editor di Power Query selezionare Nuovi parametri in Gestisci parametri nella barra multifunzione.
Nella finestra Gestisci parametri compilare le informazioni sul parametro . Per altre informazioni, vedere Creare un parametro.
Selezionare Nuovo per aggiungere altri parametri.
Al termine dell'aggiunta di parametri, selezionare OK.

Fare riferimento ai parametri nella query M

Dopo aver creato i parametri, è possibile farvi riferimento nella query M. Per modificare la query M, mentre è selezionata la query, aprire l'editor avanzato.
Fare riferimento ai parametri nella query M, come evidenziato in giallo nell'immagine seguente:
Al termine della modifica della query, selezionare Fine.

Creare tabelle di valori

Creare una tabella per ogni parametro con una colonna che fornisce i possibili valori disponibili per essere impostati dinamicamente in base alla selezione del filtro. In questo esempio si desidera che i StartTime parametri e EndTime siano dinamici. Poiché questi parametri richiedono un Date/Time parametro, si generano gli input possibili per impostare dinamicamente la data per il parametro .

Nella barra multifunzione di Power BI Desktop, in Modellazione, selezionare Nuova tabella.
Creare una tabella per i valori del StartTime parametro, ad esempio:

StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))
Creare una seconda tabella per i valori del EndTime parametro, ad esempio:

EndDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

Nota

Usare un nome di colonna che non si trova in una tabella effettiva. Se si usa lo stesso nome di una colonna di tabella effettiva, il valore selezionato viene applicato come filtro nella query.

Associare i campi ai parametri

Dopo aver creato le tabelle con i Date campi, è possibile associare ogni campo a un parametro. L'associazione di un campo a un parametro indica che quando il valore del campo selezionato cambia, il valore passa al parametro e aggiorna la query che fa riferimento al parametro.

Per associare un campo, nella visualizzazione Modello di Power BI Desktop selezionare il campo appena creato e nel riquadro Proprietà selezionare Avanzate.

Nota

Il tipo di dati della colonna deve corrispondere al tipo di dati del parametro M.
Selezionare l'elenco a discesa in Associa al parametro e selezionare il parametro da associare al campo:

Poiché questo esempio prevede l'impostazione del parametro su un singolo valore, mantenere Multi-select impostato su No, ovvero l'impostazione predefinita:

Se si imposta la colonna mappata su No per Selezione multipla, è necessario usare una singola modalità di selezione nel filtro dei dati o richiedere una selezione singola nella scheda filtro.

Se i casi d'uso richiedono il passaggio di più valori a un singolo parametro, impostare il controllo su Sì e assicurarsi che la query M sia configurata per accettare più valori. Di seguito è riportato un esempio di RepoNameParameter, che consente più valori:
Ripetere questi passaggi se sono presenti altri campi da associare ad altri parametri.

È ora possibile fare riferimento a questo campo in un filtro dei dati o come filtro:

Screenshot that shows referencing the fields.

Abilita Seleziona tutto

In questo esempio il modello di Power BI Desktop ha un campo denominato Country, che è un elenco di paesi/aree associate a un parametro M denominato countryNameMParameter. Questo parametro è abilitato per La selezione multipla, ma non è abilitato per Seleziona tutto. Per poter usare l'opzione Seleziona tutto in un filtro dei dati o in una scheda filtro dei dati, seguire questa procedura:

Screenshot that shows an example of a multiselect M parameter.

Per abilitare Seleziona tutto per Paese:

Nelle proprietà Avanzate per Paese abilitare l'interruttore Seleziona tutto, che abilita l'input Seleziona tutto valore. Modificare il valore Select all o prendere nota del valore predefinito.

Il valore Select all passa al parametro come elenco contenente il valore definito. Pertanto, quando si definisce questo valore o si usa il valore predefinito, assicurarsi che il valore sia univoco e non esista nel campo associato al parametro.
Avviare il editor di Power Query, selezionare la query e quindi selezionare Editor avanzato. Modificare la query M per usare l'opzione Seleziona tutto per fare riferimento all'opzione Seleziona tutto .
Nella Editor avanzato aggiungere un'espressione booleana che restituisce true se il parametro è abilitato per La selezione multipla e contiene il valore Select all e in caso contrario restituisce false:
Incorporare il risultato dell'espressione Seleziona tutto booleano nella query di origine. Nell'esempio è presente un parametro di query booleano nella query di origine denominata includeAllCountries impostato sul risultato dell'espressione booleana del passaggio precedente. È possibile usare questo parametro in una clausola di filtro nella query, in modo che false per i filtri booleani per i nomi di paese o area selezionati e true non applichi in modo efficace alcun filtro.
Dopo aver aggiornato la query M per tenere conto del nuovo valore Seleziona tutto, è possibile usare la funzione Seleziona tutto nei filtri dei dati o nei filtri.

Per riferimento, ecco la query completa per l'esempio precedente:

let
    selectedcountryNames = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      Text.Combine({"'", Text.Combine(countryNameMParameter, "','") , "'"})
    else
      Text.Combine({"'" , countryNameMParameter , "'"}),

    selectAllCountries = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      List.Contains(countryNameMParameter, "__SelectAll__")
    else
      false,

    KustoParametersDeclareQuery = Text.Combine({"declare query_parameters(", 
                                 "startTimep:datetime = datetime(", DateTime.ToText(StartTimeMParameter, "yyyy-MM-dd hh:mm"), "), " , 
                                 "endTimep:datetime = datetime(", DateTime.ToText(EndTimeMParameter, "yyyy-MM-dd hh:mm:ss"), "), ",   
                                 "includeAllCountries: bool = ", Logical.ToText(selectAllCountries) ,",",
                                 "countryNames: dynamic = dynamic([", selectedcountryNames, "]));" }),

   ActualQueryWithKustoParameters = 
                                "Covid19
                                | where includeAllCountries or Country in(countryNames)
                                | where Timestamp > startTimep and Timestamp < endTimep
                                | summarize sum(Confirmed) by Country, bin(Timestamp, 30d)",

    finalQuery = Text.Combine({KustoParametersDeclareQuery, ActualQueryWithKustoParameters}),

    Source = AzureDataExplorer.Contents("help", "samples", finalQuery, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Timestamp", "Date"}, {"sum_Confirmed", "Confirmed Cases"}})
in
    #"Renamed Columns"

Potenziale rischio per la sicurezza

I lettori di report che possono impostare dinamicamente i valori per i parametri di query M possono accedere a più dati o attivare modifiche al sistema di origine usando attacchi injection. Questa possibilità dipende dal modo in cui si fa riferimento ai parametri nella query M e dai valori passati ai parametri.

Ad esempio, si dispone di una query Kusto con parametri costruita come indicato di seguito:

Products
| where Category == [Parameter inserted here] & HasReleased == 'True'
 | project ReleaseDate, Name, Category, Region

Non esistono problemi con un utente descrittivo che passa un valore appropriato per il parametro, ad esempio : Games

| where Category == 'Games' & HasReleased == 'True'

Tuttavia, un utente malintenzionato può essere in grado di passare un valore che modifica la query per ottenere l'accesso a più dati, ad esempio : 'Games'//

Products
| where Category == 'Games'// & HasReleased == 'True'
| project ReleaseDate, Name, Category, Region

In questo esempio, l'utente malintenzionato può accedere alle informazioni sui giochi che non sono ancora stati rilasciati modificando parte della query in un commento.

Attenuare il rischio

Per ridurre il rischio di sicurezza, evitare la concatenazione di stringhe dei valori dei parametri M all'interno della query. Utilizzare invece i valori dei parametri nelle operazioni M che si piegano alla query di origine, in modo che il motore M e il connettore costruiscano la query finale.

Se un'origine dati supporta l'importazione di stored procedure, è consigliabile archiviare la logica di query e richiamarla nella query M. In alternativa, se disponibile, usare un meccanismo di passaggio di parametri integrato nel linguaggio di query di origine e nei connettori. Ad esempio, Azure Esplora dati offre funzionalità predefinite per i parametri di query progettate per la protezione dagli attacchi injection.

Ecco alcuni esempi di queste mitigazioni:

Esempio che usa le operazioni di filtro della query M:

Table.SelectRows(Source, (r) => r[Columns] = Parameter)

Esempio che dichiara il parametro nella query di origine o passa il valore del parametro come input a una funzione di query di origine:
```
declare query_parameters (Name of Parameter : Type of Parameter);
```

Esempio di chiamata diretta di una stored procedure:

let CustomerByProductFn = AzureDataExplorer.Contents("Help", "ContosoSales"){[Name="CustomerByProduct"]}[Data] in
CustomerByProductFn({1, 3, 5})

Considerazioni e limitazioni

Quando si usano parametri di query M dinamici, è necessario tenere presenti alcune considerazioni e limitazioni:

Un singolo parametro non può essere associato a più campi né viceversa.
I parametri di query M dinamici non supportano le aggregazioni.
I parametri di query M dinamici non supportano la sicurezza a livello di riga.
I nomi dei parametri non possono essere parole riservate DAX (Data Analysis Expressions) né contenere spazi. È possibile aggiungere Parameter alla fine del nome del parametro per evitare questa limitazione.
I nomi delle tabelle non possono contenere spazi o caratteri speciali.
Se il parametro è il Date/Time tipo di dati, è necessario eseguirne il cast all'interno della query M come DateTime.Date(<YourDateParameter>).
Se si usano origini SQL, è possibile che venga visualizzata una finestra di dialogo di conferma ogni volta che il valore del parametro cambia. Questa finestra di dialogo è dovuta a un'impostazione di sicurezza: richiedere l'approvazione dell'utente per le nuove query di database native. È possibile trovare e disattivare questa impostazione nella sezione Sicurezza delle opzioni di Power BI Desktop.
I parametri di query M dinamici potrebbero non funzionare quando si accede a un modello semantico in Excel.
I parametri di query M dinamici non sono supportati nelle Server di report di Power BI.

Tipi di parametri predefiniti non supportati

Any
Durata
Vero/falso
Binario

Filtri non supportati

Filtro dei dati temporale relativo o filtro
Data relativa
Filtro dei dati gerarchia
Filtro di inclusione multicampo
Escludere filtri/Non filtri
Evidenziazione incrociata
Filtro drill-down
Filtro di drill incrociato
Filtro N superiore

Operazioni non supportate

And
Contiene
Minore di
Maggiore di
Inizia con
Non inizia con
Non è
Non contiene
Vuoto
Non è vuoto

Per altre informazioni sulle funzionalità di Power BI Desktop, vedere le risorse seguenti: