Dynamiske M-spørringsparametere i Power BI Desktop

Artikkel
11/10/2023

Denne artikkelen beskriver hvordan du oppretter og arbeider med dynamiske M-spørringsparametere i Power BI Desktop. Med dynamiske M-spørringsparametere kan modellforfattere konfigurere filter- eller slicerverdiene som rapportlesere kan bruke for en M-spørringsparameter. Dynamiske M-spørringsparametere gir modellforfattere mer kontroll over filtervalgene som skal innlemmes i DirectQuery-kildespørringer.

Modellforfattere forstår de tiltenkte semantikkene til filtrene sine, og vet ofte hvordan de skal skrive effektive spørringer mot datakilden. Med dynamiske M-spørringsparametere kan modellforfattere sikre at filtreringsvalg innlemmes i kildespørringer på riktig tidspunkt for å oppnå de tiltenkte resultatene med optimal ytelse. Dynamiske M-spørringsparametere kan være spesielt nyttige for optimalisering av spørringsytelse.

Se Sujata forklare og bruke dynamiske M-spørringsparametere i følgende video, og prøv dem deretter selv.

Merk

Denne videoen kan bruke tidligere versjoner av Power BI Desktop eller Power Bi-tjeneste.

Forutsetning

Hvis du vil arbeide gjennom disse prosedyrene, må du ha en gyldig M-spørring som bruker én eller flere DirectQuery-tabeller.

Opprette og bruke dynamiske parametere

Eksemplet nedenfor sender én enkelt verdi gjennom til en parameter dynamisk.

Legg til parametere

Velg Transformer>datatransformingsdata> i Power BI Desktop for å åpne Power Query-redigering.
Velg Nye parametere under Behandle parametere på båndet i Power Query-redigering.
Fyll ut informasjonen om parameteren i vinduet Behandle parametere. Hvis du vil ha mer informasjon, kan du se Opprette en parameter.
Velg Ny for å legge til flere parametere.
Når du er ferdig med å legge til parametere, velger du OK.

Referere til parameterne i M-spørringen

Når du har opprettet parameterne, kan du referere til dem i M-spørringen. Hvis du vil endre M-spørringen, åpner du avansert redigeringsprogram mens du har valgt spørringen.
Referer til parameterne i M-spørringen, som uthevet i gult i følgende bilde:
Når du er ferdig med å redigere spørringen, velger du Ferdig.

Opprette tabeller med verdier

Opprett en tabell for hver parameter med en kolonne som gir de mulige verdiene som kan angis dynamisk basert på filtervalg. I dette eksemplet vil du at parameterne og EndTime parameterne skal StartTime være dynamiske. Siden disse parameterne krever en Date/Time parameter, genererer du mulige inndata for å angi datoen for parameteren dynamisk.

Velg Ny tabell under Modellering på Power BI Desktop-båndet.
Opprett en tabell for verdiene i parameteren StartTime , for eksempel:

StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))
Opprett en ny tabell for verdiene i parameteren EndTime , for eksempel:

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

Merk

Bruk et kolonnenavn som ikke er i en faktisk tabell. Hvis du bruker samme navn som en faktisk tabellkolonne, gjelder den valgte verdien som et filter i spørringen.

Binde feltene til parameterne

Nå som du har opprettet tabellene med Date feltene, kan du binde hvert felt til en parameter. Binding av et felt til en parameter betyr at når den valgte feltverdien endres, går verdien til parameteren og oppdaterer spørringen som refererer til parameteren.

Hvis du vil binde et felt, velger du det nyopprettede feltet i Power BI Desktop-modellvisningen, og velger Avansert i Egenskaper-ruten.

Merk

Kolonnedatatypen må samsvare med M-parameterdatatypen.
Velg rullegardinlisten under Bind til parameter , og velg parameteren du vil binde til feltet:

Siden dette eksemplet er for å angi parameteren til én enkelt verdi, beholder du Flervalg satt til Nei, som er standard:

Hvis du setter den tilordnede kolonnen til Nei for flervalg, må du bruke én enkelt valgmodus i sliceren, eller du må velge enkelt på filterkortet.

Hvis brukstilfellene krever at du sender flere verdier til én enkelt parameter, angir du kontrollen til Ja og kontrollerer at M-spørringen er konfigurert til å godta flere verdier. Her er et eksempel for RepoNameParameter, som tillater flere verdier:
Gjenta disse trinnene hvis du har andre felt å binde til andre parametere.

Du kan nå referere til dette feltet i en slicer eller som et filter:

Screenshot that shows referencing the fields.

Aktiver merk alle

I dette eksemplet har Power BI Desktop-modellen et felt kalt Land, som er en liste over land/områder som er bundet til en M-parameter kalt countryNameMParameter. Denne parameteren er aktivert for flervalg, men er ikke aktivert for Merk alle. Hvis du vil kunne bruke alternativet Velg alt i en slicer eller et filterkort, gjør du følgende:

Screenshot that shows an example of a multiselect M parameter.

Slik aktiverer du Velg alt for land:

Aktiver veksleknappen Merk alt i avanserte egenskaper for land, som aktiverer Velg alle verdiinndata. Rediger velg alle verdier eller legg merke til standardverdien.

Velg alle verdier går til parameteren som en liste som inneholder verdien du definerte. Når du definerer denne verdien eller bruker standardverdien, må du derfor kontrollere at verdien er unik og ikke finnes i feltet som er bundet til parameteren.
Start Power Query-redigering, velg spørringen, og velg deretter avansert redigering. Rediger M-spørringen for å bruke Velg alle-verdien for å referere til alternativet Velg alt .
I avansert redigering legger du til et boolsk uttrykk som evalueres til true hvis parameteren er aktivert for flervalg og inneholder Merk alle-verdien, og ellers returneresfalse:
Inkorporer resultatet av velg alle boolske uttrykk i kildespørringen. Eksemplet har en boolsk spørringsparameter i kildespørringen includeAllCountries kalt som er satt til resultatet av det boolske uttrykket fra forrige trinn. Du kan bruke denne parameteren i en filtersetning i spørringen, slik at false boolske filtre for de valgte land- eller områdenavnene, og true effektivt ikke bruker noe filter.
Når du oppdaterer M-spørringen til kontoen for den nye Velg alle-verdien, kan du bruke Velg alle-funksjonen i slicere eller filtre.

Her er den fullstendige spørringen for det foregående eksemplet:

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"

Potensiell sikkerhetsrisiko

Rapportlesere som dynamisk kan angi verdiene for M-spørringsparametere, kan få tilgang til flere data eller utløse endringer i kildesystemet ved hjelp av injeksjonsangrep. Denne muligheten avhenger av hvordan du refererer til parameterne i M-spørringen og hvilke verdier du sender til parameterne.

Du har for eksempel en parameterisert Kusto-spørring konstruert på følgende måte:

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

Det er ingen problemer med en egendefinert bruker som sender en passende verdi for parameteren, for eksempel Games:

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

En angriper kan imidlertid kanskje sende en verdi som endrer spørringen for å få tilgang til flere data, for eksempel 'Games'//:

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

I dette eksemplet kan angriperen få tilgang til informasjon om spill som ikke har blitt utgitt ennå, ved å endre en del av spørringen til en kommentar.

Redusere risikoen

Hvis du vil redusere sikkerhetsrisikoen, unngår du strengsammenkobling av M-parameterverdier i spørringen. Bruk i stedet disse parameterverdiene i M-operasjoner som brettes til kildespørringen, slik at M-motoren og koblingen konstruerer den endelige spørringen.

Hvis en datakilde støtter import av lagrede prosedyrer, bør du vurdere å lagre spørringslogikken der og aktivere den i M-spørringen. Hvis det er tilgjengelig, kan du også bruke en parameterpassingsmekanisme som er innebygd i kildespørringsspråket og -koblingene. Azure Data Explorer har for eksempel innebygde spørringsparameterfunksjoner som er utformet for å beskytte mot injeksjonsangrep.

Her er noen eksempler på disse begrensningene:

Eksempel som bruker M-spørringens filtreringsoperasjoner:
```
Table.SelectRows(Source, (r) => r[Columns] = Parameter)
```
Eksempel som deklarerer parameteren i kildespørringen, eller sender parameterverdien som inndata til en kildespørringsfunksjon:
```
declare query_parameters (Name of Parameter : Type of Parameter);
```

Eksempel på direkte kall til en lagret prosedyre:

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

Hensyn og begrensninger

Det finnes noen hensyn og begrensninger når du bruker dynamiske M-spørringsparametere:

En enkelt parameter kan ikke bindes til flere felt eller omvendt.
Dynamiske M-spørringsparametere støtter ikke aggregasjoner.
Dynamiske M-spørringsparametere støtter ikke sikkerhet på radnivå (RLS).
Parameternavn kan ikke være DAX-reserverte ord (Data Analysis Expressions) eller inneholde mellomrom. Du kan tilføye Parameter til slutten av parameternavnet for å unngå denne begrensningen.
Tabellnavn kan ikke inneholde mellomrom eller spesialtegn.
Hvis parameteren Date/Time er datatypen, må du kaste den i M-spørringen som DateTime.Date(<YourDateParameter>).
Hvis du bruker SQL-kilder, kan det hende du får en bekreftelsesdialogboks hver gang parameterverdien endres. Denne dialogboksen skyldes en sikkerhetsinnstilling: Krev brukergodkjenning for nye opprinnelige databasespørringer. Du kan finne og deaktivere denne innstillingen i Sikkerhets-delen av Power BI Desktop-alternativene.
Dynamiske M-spørringsparametere fungerer kanskje ikke når du får tilgang til en semantisk modell i Excel.
Dynamiske M-spørringsparametere støttes ikke på rapportserver for Power BI.

Ustøttede ut-av-boksen-parametertyper

Any
Varighet
Sann/usann
Binær

Filtre som ikke støttes

Relativ tidsslicer eller filter
Relativ dato
Hierarkislicer
Multifeltinkluder filter
Utelat filtre / Ikke filtre
Kryssutheving
Drilldown-filter
Kryssdrillingsfilter
Topp N-filter

Operasjoner som ikke støttes

And
Contains
Mindre enn
Større enn
Begynner med
Begynner ikke med
Er ikke
Inneholder ikke
Er tom
Er ikke tom

Hvis du vil ha mer informasjon om Power BI Desktop-funksjoner, kan du se følgende ressurser: