Dynaamiset M-kyselyparametrit Power BI Desktopissa

Tässä artikkelissa kuvataan, miten dynaamisia M-kyselyparametreja luodaan ja käsitellään Power BI Desktopissa. Dynaamisten M-kyselyparametrien avulla mallin tekijät voivat määrittää suodatin- tai osittaja-arvot, joita raportin katselijat voivat käyttää M-kyselyparametrissa. Dynaamiset M-kyselyparametrit antavat mallin tekijöille entistä enemmän hallintaa suodatinvalintojen käyttöön DirectQuery-lähdekyselyissä.

Mallin tekijät ymmärtävät suodattimien aiotun semantiikan ja osaavat usein kirjoittaa tehokkaita kyselyitä tietolähteelleen. Dynaamisilla M-kyselyparametreilla mallien tekijät voivat varmistaa, että suodatinvalinnat sisältyvät lähdekyselyihin oikeassa kohdassa, jotta aiotut tulokset voidaan saavuttaa optimaalisen suorituskyvyn avulla. Dynaamiset M-kyselyparametrit voivat olla erityisen hyödyllisiä kyselyn suorituskyvyn optimoinnissa.

[! NOTE] Tämän artikkelin esimerkeissä käytetään sekä Kustoa että T-SQL:ää dynaamisten M-kyselyparametrien esittelyyn. Käsitteet koskevat kaikkia tuettuja DirectQuery-lähteitä. Kun yhdistät Fabric KQL-tietokantoihin (Real-Time Intelligence), käytä Azure Data Explorer (Kusto) -liitintä — samat mallit pätevät.

Katso seuraavasta videosta, miten Sujata selittää ja käyttää dynaamisia M-kyselyparametreja, ja kokeile niitä sitten itse.

Edellytykset

Jotta voit suorittaa nämä toimenpiteet, sinulla on oltava kelvollinen M-kysely, joka käyttää yhtä tai useampaa DirectQuery-taulukkoa.

Dynaamisten parametrien luominen ja käyttäminen

Seuraava esimerkki välittää yksittäisen arvon parametriin dynaamisesti.

Parametrien lisääminen

1. Power BI Desktopissa valitse Home>Transform data>Transform data avataksesi Power Query -editori.

2. Power Query -editori:ssa valitse nauhassa New ParametersManage Parameters kohdasta.

   

3. Täytä Parametrien hallinta -ikkunassa parametrin tiedot. Lisätietoja on kohdassa Parametrin luominen.

   

4. Lisää parametreja valitsemalla Uusi .

   

5. Kun olet lisännyt parametrit, valitse OK.

Viittaa parametreihin M-kyselyssä

1. Kun olet luonut parametrit, voit viitata niihin M-kyselyssä. M-kyselyn muokkaamiseksi, kun kysely on valittuna, avaa Laajennettu editori.

   Kuvakaappaus, joka näyttää Laajennettu editori.

2. Viittaa M-kyselyn parametreihin, jotka on korostettu keltaisella seuraavassa kuvassa:

   

   Juomaraha

   Jos haluat viitata parametriin M-koodissa, käytä sen nimeä suoraan (esimerkiksi CountryParameter). Tekstiparametreille voit ketjuttaa yllä olevan kuvan mukaisesti. Varmista päivämäärä- tai numeeristen parametrien osalta, että tietotyyppi vastaa toisiaan.

   T-SQL-esimerkki (yksi parametri)

   let
         Source = Sql.Database("server", "database", [Query="SELECT * FROM Sales WHERE Country = '" & CountryParameter & "'"])
   in
         Source
   

3. Kun olet muokannut kyselyä, valitse Valmis.

Arvotaulukoiden luominen

Luo kullekin parametrille taulukko, jonka sarake tarjoaa mahdolliset arvot, jotka voidaan määrittää dynaamisesti suodattimen valinnan perusteella. Tässä esimerkissä haluat, että - ja StartTime -EndTimeparametrit ovat dynaamisia. Koska nämä parametrit edellyttävät parametria Date/Time , voit luoda mahdolliset syötteet, joiden avulla parametrin päivämäärä määritetään dynaamisesti.

1. Power BI Desktopin nauhassa, kohdassa Modeling, valitse New table.

   

2. Luo taulukko parametrin StartTime arvoille, esimerkiksi:

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

   

3. Luo toinen taulukko parametrin EndTime arvoille, esimerkiksi:

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

   

   Muistiinpano

   Käytä sarakkeen nimeä, joka ei ole todellisessa taulukossa. Jos käytät samaa nimeä kuin todellinen taulukkosarake, valittua arvoa sovelletaan suodattimena kyselyssä.

Sido kentät parametreihin

Nyt kun kentät sisältävät taulukot Date on luotu, voit sitoa kunkin kentän parametriin. Kentän sitominen parametriin tarkoittaa sitä, että kun valitun kentän arvo muuttuu, arvo siirtyy parametriin ja päivittää kyselyn, joka viittaa parametriin.

1. Kentän sitomiseksi Power BI Desktopin Model näkymästä valitse uusi kenttä, ja Properties-paneelista valitse Advanced.

   Muistiinpano

   Sarakkeen tietotyypin tulee vastata M-parametrin tietotyyppiä.

   

2. Valitse Sido parametriin -kohdan avattava valikko ja valitse parametri, jonka haluat sitoa kenttään:

   

   Koska tämä esimerkki koskee parametrin asettamista yksittäiseksi arvoksi, säilytä Monivalinta-asetuksena Ei, joka on oletusarvo:

   

   Jos määrität yhdistetyn sarakkeen arvoksi Ei monivalinnalle, käytä osittajassa yksittäisen valinnan tilaa tai edellytä yksittäistä valintaa suodatinkortissa.

   Jos käyttötapauksesi edellyttävät useiden arvojen välittämistä yhteen parametriin, määritä ohjausobjektin arvoksi Kyllä ja varmista, että M-kysely on määritetty hyväksymään useita arvoja. Tässä on esimerkki toiminnolle RepoNameParameter, joka sallii useita arvoja:

   

3. Toista nämä vaiheet, jos sinulla on muita kenttiä sidottavaksi muihin parametreihin.

   

Voit nyt viitata tähän kenttään osittajassa tai suodattimena:

Ota Valitse kaikki käyttöön

Tässä esimerkissä Power BI Desktop-mallissa on kenttä nimeltä Country, joka on lista maista/alueista, jotka on sidottu M-parametriin nimeltä countryNameMParameter. Tämä parametri on otettu käyttöön Monivalinta-parametrille, mutta se ei ole käytössä Valitse kaikki -parametrille. Voit käyttää osittajan tai suodatinkortin Valitse kaikki -vaihtoehtoa seuraavasti:

Valitse kaikki -asetuksen ottaminen käyttöön Maa-kohdassa:

1. Ota Maa-kohdan Lisäasetukset-kohdassa käyttöön Valitse kaikki -valintakytkin, joka ottaa käyttöön Valitse kaikki arvo -syötteen. Muokkaa Valitse kaikki -arvoa tai ota oletusarvo muistiin.

   

   Valitse kaikki - arvo välittää parametrin luettelona, joka sisältää määrittämäsi arvon. Kun siis määrität tämän arvon tai käytät oletusarvoa, varmista, että arvo on yksilöllinen eikä sitä ole olemassa parametriin sidotussa kentässä.

2. Käynnistä Power Query -editori, valitse kysely ja valitse sitten Laajennettu editori. Muokkaa M-kyselyä ja käytä Valitse kaikki -arvoa viittaamaan Valitse kaikki -vaihtoehtoon.

   

3. Lisätään Laajennettu editori Boolen lauseke, joka arvioi true, jos parametri on käytössä Multi-select ja sisältää Valitse kaikki arvot, ja muuten palauttaa false:

   

4. Sisällytä Valitse kaikki totuusarvo -lausekkeen tulos lähdekyselyun. Esimerkissä lähdekyselyssä on totuusarvokyselyparametri nimeltä includeAllCountries , joka on määritetty edellisen vaiheen totuusarvolausekkeen tulokseen. Voit käyttää tätä parametria kyselyn suodatinlausekkeessa siten, että false totuusarvo suodattaa valitun maan tai alueen nimet eikä true käytännössä käytä suodatinta.

   

5. Kun päivität M-kyselyn tilille uuden Valitse kaikki -arvon, voit käyttää Valitse kaikki -funktiota osittajissa tai suodattimissa.

   

Tässä on täydellinen kysely edellisessä esimerkissä:

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"

Mahdollinen tietoturvariski

Raportin lukijat, jotka voivat dynaamisesti määrittää M-kyselyparametrien arvoja, voivat ehkä käyttää enemmän tietoja tai käynnistää lähdejärjestelmän muokkauksia käyttämällä lisäyshyökkäyksiä. Tämä mahdollisuus riippuu siitä, miten viittaat M-kyselyn parametreihin ja mitä arvoja välität parametreihin.

Sinulla on esimerkiksi parametrisoitu Kusto-kysely, joka on muodostettu seuraavasti:

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

Ystävällisen käyttäjän kanssa ei ole ongelmia, jotka läpäisevät parametrille sopivan arvon, Gamesesimerkiksi :

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

Hyökkääjä saattaa kuitenkin pystyä välittämään arvon, joka muokkaa kyselyä, esimerkiksi 'Games'//:

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

Tässä esimerkissä hyökkääjä voi saada pääsyn peliä koskeviin tietoihin, joita ei ole vielä julkaistu, muuttamalla osan kyselystä kommentiksi.

Pienennä riskiä

Tietoturvariskin pienentämiseksi vältä M-parametriarvojen merkkijonon yhdistämistä kyselyssä. Käytä sen sijaan kyseisiä parametriarvoja M-toiminnoissa, jotka taittuvat lähdekyselyun, jotta M-moduuli ja liitin luovat lopullisen kyselyn.

Jos tietolähde tukee tallennettujen toimintosarjojen tuomista, harkitse kyselylogiikan tallentamista siihen ja käynnistämistä M-kyselyssä. Vaihtoehtoisesti voit käyttää lähdekyselyn kielelle ja liittimille sisäänrakennettua parametrin välitysmekanismia, jos sellainen on käytettävissä. Esimerkiksi Azure Data Explorer sisältää sisäänrakennetut kyselyparametriominaisuudet, jotka on suunniteltu suojaamaan injektiohyökkäyksiä vastaan.

Seuraavassa on joitakin esimerkkejä näistä lieventämisistä:

• Esimerkki, joka käyttää M-kyselyn suodatustoimintoja:

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

• Esimerkki, joka määrittää parametrin lähdekyselyssä tai välittää parametrin arvon syötteenä lähdekyselyfunktiolle:

  declare query_parameters (Name of Parameter : Type of Parameter);
  

• Esimerkki tallennetun toimintosarjan kutsumisesta suoraan:

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

Huomioitavat asiat ja rajoitukset

Dynaamisia M-kyselyparametreja käytettäessä on otettava huomioon joitakin huomioon otettavia seikkoja ja rajoituksia:

• Yksittäistä parametria ei voi sitoa useisiin kenttiin tai päinvastoin.
• Dynaamiset M-kyselyparametrit eivät tue koosteita.
• Dynaamiset M-kyselyparametrit eivät tue rivitason suojausta (RLS).
• Parametrien nimet eivät voi olla Daxin (Data Analysis Expressions) varattuja sanoja eivätkä sisältää välilyöntejä. Voit lisätä Parameter parametrin nimen loppuun, jotta tämä rajoitus vältetään.
• Taulukon nimet eivät voi sisältää välilyöntejä tai erikoismerkkejä.
• Jos parametrisi on Date/Time tietotyyppi, sinun on kirjoitettava se M-kyselyyn muodossa DateTime.Date(<YourDateParameter>).
• Jos käytät SQL-lähteitä, saat ehkä vahvistusvalintaikkunan aina, kun parametrin arvo muuttuu. Tämä valintaikkuna johtuu suojausasetuksesta: Edellytä käyttäjän hyväksyntä uusille alkuperäisille tietokantakyselyille. Voit löytää ja poistaa tämän asetuksen käytöstä Security-osiosta Power BI Desktopissa Options.
• Dynaamiset M-kyselyparametrit eivät välttämättä toimi, kun käytetään semanttista mallia Excel:ssä.
• Dynaamisia M-kyselyparametreja ei tueta Power BI -raporttipalvelin.
• Datalähteiden vaihtamista dynaamisilla M-kyselyparametreilla ei tueta Power BI -palvelu. Lisätietoja on kohdassa Päivitys ja dynaamiset tietolähteet .

Valmiit parametrityypit, joita ei tueta

• Mikä tahansa
• Kesto
• Tosi/epätosi
• Binaarinen

Suodattimet, joita ei tueta

• Suhteellinen aikaosittaja tai suodatin
• Suhteellinen päivämäärä
• Hierarkiaosittaja
• Monikenttä, sisällytä suodatin
• Jätä pois suodattimet / Ei suodattimia
• Ristiinkorostus
• Alaspäin porautumisen suodatin
• Ristiporautumisen suodatin
• Ylin N-suodatin

Toiminnot, jotka eivät ole tuettuja

• Ja
• Sisältää
• Pienempi kuin
• Suurempi kuin
• Alkaa merkkijonolla
• Ei ala merkkijonolla
• Ei ole
• Ei sisällä
• On tyhjä
• Ei ole tyhjä

Liittyvä sisältö

Lisätietoja Power BI Desktopin ominaisuuksista löydät seuraavista resursseista:

• DirectQuery Power BI
• Mikä on Power BI?
• kyselyn yleiskatsaus Power BI Desktopissa
• datatyypit Power BI Desktopissa
• Opastus: Muokkaa ja yhdistä dataa Power BI Desktopissa
• Suorita yleisiä kyselytehtäviä Power BI Desktopissa