Creare parametri di query M dinamici in Power BI Desktop

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 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 si incorporino 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 che spiega e usa i parametri di query M dinamici nel video seguente e quindi provarli manualmente.

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

1. In Power BI Desktop selezionare Home>Trasforma dati>Trasforma dati per aprire l'editor di Power Query.

2. Nell'editor di Power Query selezionare Nuovi parametri in Gestisci parametri nella barra multifunzione.

   

3. Nella finestra Gestisci parametri compilare le informazioni sul parametro. Per altre informazioni, vedere Creazione di un parametro.

   

4. Selezionare Nuovo per aggiungere altri parametri.

   

5. Al termine dell'aggiunta di parametri, selezionare OK.

Fare riferimento ai parametri nella query M

1. Una volta creati i parametri è possibile farvi riferimento nella query M. Per modificare la query M, mentre è selezionata la query, aprire l'Editor avanzato.

   

2. Fare riferimento ai parametri nella query M, evidenziati in giallo nell'immagine seguente:

   

3. Dopo aver finito di modificare la query, fare clic su Fine.

Creare tabelle di valori

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

1. Nella barra multifunzione di Power BI Desktop, in Modellazione selezionare Nuova tabella.

   

2. Creare una tabella per i valori del parametro StartTime, ad esempio:

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

   

3. Creare una seconda tabella per i valori del parametro EndTime, 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 campi Date, è 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.

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

   Nota

   Il tipo di dati della colonna deve corrispondere al tipo di dati del parametro M.

   

2. Selezionare l'elenco a discesa in Associa a parametro e selezionare il parametro che si vuole associare al campo:

   

   Poiché questo esempio si riferisce al parametro impostato su un valore singolo, è consigliabile impostare Selezione multipla su No, che è l'impostazione predefinita:

   

   Se la colonna di cui è stato eseguito il mapping ha la Selezione multipla impostata su No, è necessario usare una modalità di selezione singola nel filtro dei dati oppure 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. Ecco un esempio per RepoNameParameter, che consente più valori:

   

3. Se sono presenti altri campi da associare ad altri parametri, ripetere questi passaggi.

   

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

Abilitare Seleziona tutto

In questo esempio, il modello di Power BI Desktop ha un campo denominato Paese, che è un elenco di paesi/aree associate a un parametro M denominato countryNameMParameter. Questo parametro è abilitato per 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, seguire questa procedura:

Per abilitare Seleziona tutto per Paese:

1. In Proprietà avanzate per Paese abilitare l'interruttore Seleziona tutto, che abilita l'input del valore Seleziona tutto. Modificare il valore Seleziona tutto o prendere nota del valore predefinito.

   

   Il valore Seleziona tutto passa al parametro come elenco contenente il valore definito. Pertanto, quando si definisce questo valore o si usa il valore predefinito, assicurarsi che sia univoco e che non esista nel campo associato al parametro.

2. Avviare l'editor di Power Query, selezionare la query e quindi selezionare Editor avanzato. Modificare la query M per usare il valore Seleziona tutto in modo da fare riferimento all'opzione Seleziona tutto.

   

3. Nell'Editor avanzato aggiungere un'espressione booleana che restituisce true se il parametro è abilitato per Selezione multipla e contiene il valore Seleziona tutto e che, in caso contrario, restituisce false:

   

4. Incorporare il risultato dell'espressione booleana Seleziona tutto 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, come false per i filtri booleani per i nomi dei paesi o delle aree geografiche selezionati e per far sì che di fatto true non applichi alcun filtro.

   

5. Dopo aver aggiornato la query M per tenere conto del nuovo valore Seleziona tutto, è possibile usare la funzione Seleziona tutto nei filtri dei dati.

   

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 potrebbero 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 sono presenti 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 potrebbe riuscire a 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'autore dell'attacco può ottenere l'accesso alle informazioni sui giochi che non sono ancora stati rilasciati cambiando parte della query in un commento.

Attenuare il rischio

Per attenuare i rischi per la sicurezza, evitare la concatenazione di stringhe dei valori di parametro M all'interno della query. Usare invece i valori dei parametri nelle operazioni M che si riducono 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 dei parametri predefinito per il linguaggio della query di origine e i connettori. Ad esempio, Esplora dati di Azure ha funzionalità predefinite per i parametri di query progettate per la protezione da attacchi di tipo injection.

Ecco alcuni esempi di queste mitigazioni:

• Esempio di uso delle operazioni di filtro della query M:

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

• Esempio di dichiarazione del parametro nella query di origine o del passaggio del 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

Esistono alcune considerazioni e limitazioni da tenere in considerazione quando si usano i parametri di query M dinamici:

• Non è possibile associare un singolo parametro 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 tipo di dati Date/Time, è 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: Richiedi approvazione 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 nel server di report di Power BI.

Tipi di parametri predefiniti non supportati

• Any
• Durata
• Vero/falso
• Binario

Filtri non supportati

• Filtro dei dati o filtro per intervallo di tempo relativo
• Data relativa
• Filtro dei dati della gerarchia
• Filtro di inclusione multicampo
• Esclusione filtro/Nessun filtro
• Evidenziazione incrociata
• Filtro di drill-down
• Filtro di cross-drill
• Filtro Top N

Operazioni non supportate

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

Contenuto correlato

Per altre informazioni sulle capacità disponibili in Power BI Desktop, vedere le risorse seguenti:

• DirectQuery in Power BI
• Che cos'è Power BI Desktop?
• Panoramica sulle query in Power BI Desktop
• Tipi di dati in Power BI Desktop
• Esercitazione: Data shaping e combinazione di dati in Power BI Desktop
• Eseguire attività di query comuni in Power BI Desktop