Dynaamiset M-kyselyparametrit Power BI Desktopissa

Tässä artikkelissa kerrotaan, miten voit luoda ja käsitellä dynaamisia M-kyselyparametreja 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.

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

Muistiinpano

Tässä videossa saatetaan käyttää Power BI Desktopin tai Power BI -palvelun aiempia versioita.

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. Avaa Power Query -editori valitsemalla Power BI Desktopissa Aloitus>Muunna tiedot>Muunna tiedot.

2. Valitse Power Query -editorissa valintanauhan Parametrien hallinta -kohdasta Uudet parametrit.

   

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ä. Jos haluat muokata M-kyselyä, kun kysely on valittuna, avaa Laajennettu editori.

   

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

   

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 EndTime -StartTimeparametrit 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. Valitse Power BI Desktopin valintanauhan Mallinnus-kohdasta Uusi taulukko.

   

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. Jos haluat sitoa kentän, valitse Power BI Desktopin mallinäkymässä juuri luotu kenttä ja valitse Ominaisuudet-ruudusta Lisäasetukset.

   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ä Maa, joka on luettelo maista tai 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ää Laajennettu editori totuusarvolauseke, joka antaa tulokseksi sentrue, onko parametri käytössä Multi-Select-parametrissa ja sisältää Valitse kaikki -arvon, ja palauttaa muussa tapauksessa arvon 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ää valmiita kyselyparametrin ominaisuuksia, jotka on suunniteltu suojaamaan lisäyshyökkäyksiltä.

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 etsiä ja poistaa käytöstä tämän asetuksen Power BI Desktopin asetusten Suojaus-osiosta.
• Dynaamiset M-kyselyparametrit eivät välttämättä toimi, kun käytät semanttista mallia Excelissä.
• Dynaamisia M-kyselyparametreja ei tueta Power BI -raporttipalvelimessa.
• Tietolähteiden vaihtamista dynaamisten M-kyselyparametrien avulla ei tueta Power BI -palvelussa. Lisätietoja on kohdassa Päivitys ja dynaamiset tietolähteet .

Valmiit parametrityypit, joita ei tueta

• Any
• 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

• And
• 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 on seuraavissa resursseissa:

• DirectQuery Power BI:ssä
• Mikä on Power BI Desktop?
• Kyselyn yleiskatsaus Power BI Desktopissa
• Power BI Desktopin tietotyypit
• Opetusohjelma: Tietojen muotoilu ja yhdistäminen Power BI Desktopissa
• Yleisten kyselytehtävien suorittaminen Power BI Desktopissa