Dynamické parametry dotazu M v Power BI Desktopu

Tento článek popisuje, jak v Power BI Desktopu vytvořit dynamické parametry dotazu M a pracovat s jejich pomocí. S dynamickými parametry dotazu M můžou autoři modelu nakonfigurovat hodnoty filtru nebo průřezu, které můžou čtenáři sestav použít pro parametr dotazu M. Dynamické parametry dotazu M poskytují autorům modelu větší kontrolu nad výběry filtrů, aby je mohli začlenit do zdrojových dotazů DirectQuery.

Autoři modelů chápou zamýšlenou sémantiku svých filtrů a často vědí, jak psát efektivní dotazy na jejich zdroj dat. Díky dynamickým parametrům dotazu M můžou autoři modelů zajistit, aby výběry filtrů byly začleněny do zdrojových dotazů ve správném bodě, aby dosáhli zamýšlených výsledků s optimálním výkonem. Dynamické parametry dotazu M můžou být užitečné zejména pro optimalizaci výkonu dotazů.

[! POZNÁMKA] Příklady v tomto článku používají Kusto i T-SQL k předvedení dynamických parametrů dotazu M. Koncepty platí pro libovolný podporovaný zdroj DirectQuery. Při připojování k databázím Fabric KQL (Real-Time Intelligence) použijte konektor Azure Data Explorer (Kusto) – platí stejné vzory.

Podívejte se, jak Sujata vysvětluje a používá dynamické parametry dotazu M v následujícím videu, a vyzkoušejte si je sami.

Poznámka:

Toto video může používat starší verze Power BI Desktopu nebo služba Power BI.

Požadavky

Abyste mohli tyto postupy procházet, musíte mít platný dotaz M, který používá jednu nebo více tabulek DirectQuery.

Vytvoření a použití dynamických parametrů

Následující příklad předává jednu hodnotu do parametru dynamicky.

Přidání parametrů

1. V Power BI Desktopu vyberte Home>Transform data>Transform data a otevřete Editor Power Query.

2. V Editor Power Query vyberte Nové parametry v části Manage Parameters na pásu karet.

   

3. V okně Spravovat parametry vyplňte informace o parametru. Další informace naleznete v tématu Vytvoření parametru.

   

4. Vyberte Nový pro přidání dalších parametrů.

   

5. Až budete hotovi s přidáváním parametrů, vyberte OK.

Odkazování na parametry v dotazu M

1. Po vytvoření parametrů na ně můžete odkazovat v dotazu M. Pokud chcete upravit dotaz M, když máte vybraný dotaz, otevřete Rozšířený editor.

   

2. Odkazujte na parametry v dotazu M, jak je zvýrazněné žlutě na následujícím obrázku:

   

   Návod

   Pokud chcete odkazovat na parametr v kódu M, použijte jeho název přímo (například CountryParameter). U textových parametrů můžete zřetězit, jak je znázorněno výše. U kalendářních nebo číselných parametrů se ujistěte, že se datový typ shoduje.

   Příklad T-SQL (jeden parametr)

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

3. Až dokončíte úpravy dotazu, vyberte Hotovo.

Vytvoření tabulek hodnot

Vytvořte tabulku pro každý parametr se sloupcem, který poskytuje možné hodnoty, které se dají dynamicky nastavit na základě výběru filtru. V tomto příkladu chcete, aby parametry StartTime a EndTime byly dynamické. Vzhledem k tomu, že tyto parametry vyžadují Date/Time parametr, vygenerujete možné vstupy, které dynamicky nastaví datum parametru.

1. Na pásu karet Power BI Desktop v části Modelování vyberte Nová tabulka.

   

2. Vytvořte tabulku pro hodnoty parametru StartTime , například:

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

   

3. Vytvořte druhou tabulku pro hodnoty parametru EndTime , například:

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

   

   Poznámka:

   Použijte název sloupce, který není ve skutečné tabulce. Pokud použijete stejný název jako skutečný sloupec tabulky, použije se vybraná hodnota jako filtr v dotazu.

Vytvoření vazby polí k parametrům

Teď, když jste vytvořili tabulky s Date poli, můžete každé pole svázat s parametrem. Vazba pole s parametrem znamená, že když se změní hodnota vybraného pole, hodnota se předá parametru a aktualizuje dotaz, který odkazuje na parametr.

1. Pokud chcete svázat pole, vyberte v zobrazení Power BI Desktop Model nově vytvořené pole a v podokně Vlastnosti vyberte Pokročilé.

   Poznámka:

   Datový typ sloupce by měl odpovídat datovému typu parametru M.

   

2. Vyberte rozevírací seznam v části Bind to parameter a vyberte parametr, který chcete svázat s polem:

   

   Vzhledem k tomu, že tento příklad slouží k nastavení parametru na jednu hodnotu, ponechte možnost Vícenásobný výběr nastavenou na Ne, což je výchozí nastavení:

   

   Pokud nastavíte mapovaný sloupec na Ne pro Vícenásobný výběr, musíte v řezači použít režim jednoho výběru nebo požadovat jeden výběr na kartě filtru.

   Pokud vaše případy použití vyžadují předání více hodnot jednomu parametru, nastavte ovládací prvek na Ano a ujistěte se, že je dotaz M nastavený tak, aby přijímal více hodnot. Tady je příklad , RepoNameParameterkterý umožňuje více hodnot:

   

3. Tento postup opakujte, pokud máte další pole, která chcete svázat s jinými parametry.

   

Poznámka:

Rozevírací seznam Bind to parameter je k dispozici pouze pro podporované zdroje DirectQuery a konkrétní datové typy. Pokud tuto možnost v zobrazení modelu nevidíte, ujistěte se, že používáte podporovaný zdroj a správně jste vytvořili parametr. Tato funkce byla představena v Power BI Desktopu v červnu 2022.

Teď můžete na toto pole odkazovat v průřezu nebo jako filtr:

Povolit výběr všech

V tomto příkladu má desktopový model Power BI pole s názvem Country, což je seznam zemí/oblastí vázaných na parametr M s názvem countryNameMParameter. Tento parametr je povolený pro vícenásobný výběr, ale není povolený pro možnost Vybrat vše. Pokud chcete mít možnost Vybrat vše v průřezu nebo kartě filtru, proveďte následující přidané kroky:

Povolení možnosti Vybrat vše pro zemi:

1. Ve vlastnostech Upřesnit pro zemi povolte přepínač Vybrat vše, který povolí vstup Vybrat všechny hodnoty. Upravte možnost Vybrat všechny hodnoty nebo si poznamenejte výchozí hodnotu.

   

   Funkce Vybrat všechny hodnoty předává parametru seznam, který obsahuje vámi definovanou hodnotu. Proto při definování této hodnoty nebo použití výchozí hodnoty se ujistěte, že hodnota je jedinečná a neexistuje v poli, které je vázané na parametr.

2. Spusťte Editor Power Query, vyberte dotaz a pak vyberte Rozšířený editor. Upravte dotaz M tak, aby použil Vybrat všechny hodnoty k odkazu na možnost Vybrat vše.

   

3. V Rozšířený editor Přidejte logický výraz, který se vyhodnotí jako true, pokud je parametr povolený pro Multi-select a obsahuje hodnotu Select all value a jinak vrátí false:

   

4. Začleňte výsledek výrazu Select all Boolean do zdrojového dotazu. Příklad obsahuje logický parametr dotazu ve zdrojovém dotazu, includeAllCountries který je nastavený na výsledek logického výrazu z předchozího kroku. Tento parametr můžete použít v klauzuli filtru v dotazu, například false pro logické filtry pro vybrané názvy zemí nebo oblastí a true efektivně nepoužije žádný filtr.

   

5. Jakmile aktualizujete dotaz M tak, aby zohlednil novou hodnotu Select all, můžete použít funkci Vybrat vše v průřezech nebo filtrech.

   

Tady je úplný dotaz pro předchozí příklad:

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"

Možné riziko zabezpečení

Čtenáři sestav, kteří můžou dynamicky nastavit hodnoty parametrů dotazu M, můžou mít přístup k více datům nebo aktivovat úpravy zdrojového systému pomocí útoků prostřednictvím injektáže. Tato možnost závisí na tom, jak odkazujete na parametry v dotazu M a na jaké hodnoty předáte parametrům.

Například máte parametrizovaný dotaz Kusto vytvořený takto:

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

Neexistují žádné problémy s přívětivým uživatelem, který předává odpovídající hodnotu parametru, Gamesnapříklad:

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

Útočník ale může být schopen předat hodnotu, která dotaz upraví, aby získal přístup k dalším datům, 'Games'//například:

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

V tomto příkladu může útočník získat přístup k informacím o hrách, které ještě nebyly vydány změnou části dotazu na komentář.

Zmírnění rizika

Pokud chcete zmírnit riziko zabezpečení, vyhněte se zřetězení řetězců hodnot parametrů M v rámci dotazu. Místo toho tyto hodnoty parametrů spotřebovávejte v operacích M, které se skládají do zdrojového dotazu, aby modul M a konektor vytvořily konečný dotaz.

Pokud zdroj dat podporuje import uložených procedur, zvažte uložení logiky dotazu tam a vyvolání v dotazu M. Případně pokud je k dispozici, použijte mechanismus předávání parametrů, který je integrovaný do zdrojového dotazovacího jazyka a konektorů. Například Azure Data Explorer má integrované možnosti parametrů dotazu, které jsou navržené pro ochranu před útoky prostřednictvím injektáže.

Tady je několik příkladů těchto omezení rizik:

• Příklad, který používá operace filtrování dotazu M:

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

• Příklad, který deklaruje parametr ve zdrojovém dotazu, nebo předá hodnotu parametru jako vstup do funkce zdrojového dotazu:

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

• Příklad přímého volání uložené procedury:

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

Úvahy a omezení

Poznámka:

U Fabric databází KQL a Azure Data Explorer odešlete do zdroje logiku agregace (make-series, summarize, series_decompose_anomalies) místo vrácení nezpracovaných událostí do Power BI. To je obzvlášť důležité pro data časových řad s velkým objemem – vizuály využívající předem agregované výsledky zachovávají interaktivní výkon dotazů.

Při použití dynamických parametrů dotazu M je potřeba vzít v úvahu některé aspekty a omezení:

• Jeden parametr nemůže být vázán na více polí ani naopak.
• Dynamické parametry dotazu M nepodporují agregace.
• Dynamické parametry dotazu M nepodporují zabezpečení na úrovni řádků (RLS).
• Názvy parametrů nemohou být vyhrazená slova jazyka DAX (Data Analysis Expressions) ani neobsahují mezery. Na konec názvu parametru můžete připojit Parameter , abyste se tomuto omezení vyhnuli.
• Názvy tabulek nemohou obsahovat mezery ani speciální znaky.
• Pokud je vaším parametrem Date/Time datový typ, musíte ho přetypovat v dotazu M jako DateTime.Date(<YourDateParameter>).
• Pokud používáte zdroje SQL, může se při každé změně hodnoty parametru zobrazit potvrzovací dialogové okno. Toto dialogové okno je způsobené nastavením zabezpečení: Vyžaduje schválení uživatele pro nové nativní databázové dotazy. Toto nastavení najdete a vypnete v části Security Power BI Desktop Options.
• Dynamické parametry dotazu M nemusí při přístupu k sémantickému modelu v Excel fungovat.
• Dynamické parametry dotazu M se v Server sestav Power BI nepodporují.
• Přepínání zdrojů dat pomocí dynamických parametrů dotazu M se v služba Power BI nepodporuje. Další informace najdete v tématu aktualizace a dynamické zdroje dat.

Nepodporované výchozí typy parametrů

• Jakýkoli
• Doba trvání
• Pravda/Nepravda
• Binární

Nepodporované filtry

• Řezač nebo filtr relativního času
• Relativní datum
• Průřez hierarchie
• Filtr pro zahrnutí více polí
• Vyloučit filtry / Ne filtry
• Křížové zvýraznění
• Filtr přechodu k podrobnostem
• Křížový filtr
• Filtr horních N

Nepodporované operace

• A
• Obsahuje
• Je menší než
• Je větší než
• Začíná na
• Nezačíná na
• Není
• Neobsahuje
• Je prázdné.
• Není prázdné.

Související obsah

Další informace o možnostech Power BI Desktopu najdete v následujících zdrojích informací:

• DirectQuery v Power BI
• Co je Power BI?
• Přehled dotazů v Power BI Desktopu
• Datové typy v Power BI Desktopu
• Tutorial: Tvarování a kombinování dat v Power BI Desktopu
• Proveďte běžné dotazové úlohy v Power BI Desktopu