Dynamiczne parametry zapytania języka M w programie Power BI Desktop

  • Artykuł

W tym artykule opisano sposób tworzenia i pracy z dynamicznymi parametrami zapytania języka M w programie Power BI Desktop. Dzięki dynamicznym parametrom zapytania języka M autorzy modeli mogą skonfigurować wartości filtru lub fragmentatora, których osoby przeglądające raport mogą używać dla parametru zapytania M. Dynamiczne parametry zapytania języka M zapewniają autorom modelu większą kontrolę nad wyborami filtrów w celu uwzględnienia ich w zapytaniach źródłowych DirectQuery.

Autorzy modeli rozumieją zamierzone semantyki swoich filtrów i często wiedzą, jak pisać wydajne zapytania względem źródła danych. Dzięki dynamicznym parametrom zapytania języka M autorzy modeli mogą zapewnić, że wybrane filtry są uwzględniane w zapytaniach źródłowych w odpowiednim momencie, aby osiągnąć zamierzone wyniki z optymalną wydajnością. Dynamiczne parametry zapytania języka M mogą być szczególnie przydatne w przypadku optymalizacji wydajności zapytań.

Obejrzyj, jak Sujata wyjaśnia i używa dynamicznych parametrów zapytania języka M w poniższym filmie wideo, a następnie wypróbuj je samodzielnie.

Uwaga

To wideo może używać wcześniejszych wersji programu Power BI Desktop lub usługa Power BI.

Wymagania wstępne

Aby wykonać te procedury, musisz mieć prawidłowe zapytanie języka M, które używa co najmniej jednej tabeli DirectQuery.

Tworzenie i używanie parametrów dynamicznych

W poniższym przykładzie pojedyncza wartość jest przekazywana dynamicznie do parametru.

Dodawanie parametrów

  1. W programie Power BI Desktop wybierz pozycję Narzędzia główne>Przekształć dane Przekształć dane>, aby otworzyć Edytor Power Query.

  2. W Edytor Power Query wybierz pozycję Nowe parametry w obszarze Zarządzaj parametrami na wstążce.

    Screenshot that shows the Ribbon menu.

  3. W oknie Zarządzanie parametrami wypełnij informacje o parametrze. Aby uzyskać więcej informacji, zobacz Tworzenie parametru.

    Screenshot that shows parameter information.

  4. Wybierz pozycję Nowy , aby dodać więcej parametrów.

    Screenshot that shows New to create another parameter.

  5. Po zakończeniu dodawania parametrów wybierz przycisk OK.

Odwoływanie się do parametrów w zapytaniu języka M

  1. Po utworzeniu parametrów można odwoływać się do nich w zapytaniu języka M. Aby zmodyfikować zapytanie języka M, po wybraniu zapytania otwórz edytor zaawansowany.

    Screenshot that shows opening the Advanced Editor.

  2. Odwołaj się do parametrów w zapytaniu M, jak wyróżniono na żółtej ilustracji:

    Screenshot that shows referencing the parameter.

  3. Po zakończeniu edytowania zapytania wybierz pozycję Gotowe.

Tworzenie tabel wartości

Utwórz tabelę dla każdego parametru z kolumną, która zapewnia możliwe wartości dostępne do dynamicznego ustawiania na podstawie wyboru filtru. W tym przykładzie chcesz StartTime , aby parametry i EndTime powinny być dynamiczne. Ponieważ te parametry wymagają parametru Date/Time , należy wygenerować możliwe dane wejściowe, aby dynamicznie ustawić datę dla parametru.

  1. Na wstążce programu Power BI Desktop w obszarze Modelowanie wybierz pozycję Nowa tabela.

    Screenshot that shows selecting New table.

  2. Utwórz tabelę dla wartości parametru StartTime , na przykład:

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

    Screenshot that shows the first table.

  3. Utwórz drugą tabelę dla wartości parametru EndTime , na przykład:

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

    Screenshot that shows the second table.

    Uwaga

    Użyj nazwy kolumny, która nie znajduje się w rzeczywistej tabeli. Jeśli używasz tej samej nazwy co rzeczywista kolumna tabeli, wybrana wartość ma zastosowanie jako filtr w zapytaniu.

Wiązanie pól z parametrami

Po utworzeniu tabel z polami Date można powiązać każde pole z parametrem. Powiązanie pola z parametrem oznacza, że w miarę zmiany wartości wybranego pola wartość jest przekazywana do parametru i aktualizuje zapytanie odwołujące się do parametru.

  1. Aby powiązać pole, w widoku Modelu programu Power BI Desktop wybierz nowo utworzone pole, a następnie w okienku Właściwości wybierz pozycję Zaawansowane.

    Uwaga

    Typ danych kolumny powinien być zgodny z typem danych parametru M.

    Screenshot that shows binding the field to a parameter.

  2. Wybierz listę rozwijaną w obszarze Powiązanie z parametrem i wybierz parametr, który chcesz powiązać z polem:

    Screenshot that shows binding the parameter to the field.

    Ponieważ ten przykład służy do ustawiania parametru na jedną wartość, zachowaj ustawienie Wyboru wielokrotnego na Nie, co jest wartością domyślną:

    Screenshot that shows multi-select set to No.

    W przypadku ustawienia zamapowanej kolumny na Wartość Nie dla wyboru wielokrotnego należy użyć trybu wyboru pojedynczego we fragmentatorze lub wymagać pojedynczego zaznacznia na karcie filtru.

    Jeśli przypadki użycia wymagają przekazania wielu wartości do jednego parametru, ustaw dla kontrolki wartość Tak i upewnij się, że zapytanie języka M zostało skonfigurowane tak, aby akceptowały wiele wartości. Oto przykład dla elementu RepoNameParameter, który zezwala na wiele wartości:

    Screenshot that shows a multivalue example.

  3. Powtórz te kroki, jeśli masz inne pola, aby powiązać je z innymi parametrami.

    Screenshot that shows configuring more parameters.

Teraz możesz odwoływać się do tego pola we fragmentatorze lub jako filtr:

Screenshot that shows referencing the fields.

Włącz zaznacz wszystko

W tym przykładzie model programu Power BI Desktop ma pole o nazwie Country (Kraj), które jest listą krajów/regionów powiązanych z parametrem M o nazwie countryNameMParameter. Ten parametr jest włączony dla wyboru wielokrotnego, ale nie jest włączony dla opcji Zaznacz wszystko. Aby móc użyć opcji Zaznacz wszystko we fragmentatorze lub karcie filtru, wykonaj następujące czynności:

Screenshot that shows an example of a multiselect M parameter.

Aby włączyć opcję Wybierz wszystko dla kraju:

  1. We właściwościach Zaawansowane dla kraju włącz przełącznik Zaznacz wszystko , co powoduje włączenie danych wejściowych Wybierz wszystkie wartości . Edytuj pozycję Wybierz całą wartość lub zanotuj wartość domyślną.

    Screenshot that shows Select all for an M parameter.

    Pole Wybierz wszystkie wartości przekazuje do parametru jako listę zawierającą zdefiniowaną wartość. Dlatego podczas definiowania tej wartości lub używania wartości domyślnej upewnij się, że wartość jest unikatowa i nie istnieje w polu powiązanym z parametrem.

  2. Uruchom Edytor Power Query, wybierz zapytanie, a następnie wybierz pozycję Edytor zaawansowany. Zmodyfikuj zapytanie języka M, aby użyć opcji Zaznacz wszystkie, aby odwoływać się do opcji Zaznacz wszystko .

    Screenshot that shows an M query.

  3. W Edytor zaawansowany dodaj wyrażenie logiczne, które obliczatrue, czy parametr jest włączony dla wyboru wielokrotnego i zawiera wartość Zaznacz wszystkie, a w przeciwnym razie zwraca falsewartość :

    Screenshot that shows an example Boolean expression for Select all.

  4. Uwzględnij wynik wyrażenia warunkowego Select all Boolean (Zaznacz wszystkie wartości logiczne) w zapytaniu źródłowym. Przykład zawiera parametr zapytania warunkowego w zapytaniu źródłowym o nazwie includeAllCountries , który jest ustawiony na wynik wyrażenia logicznego z poprzedniego kroku. Tego parametru można użyć w klauzuli filtru w zapytaniu, tak aby false dla filtrów logicznych do wybranych nazw krajów lub regionów i true skutecznie nie stosuje filtru.

    Screenshot that shows the Select all Boolean used in the source query.

  5. Po zaktualizowaniu zapytania języka M do konta nowej wartości Wybierz wszystkie możesz użyć funkcji Select all we fragmentatorach lub filtrach.

    Screenshot that shows Select all in a slicer.

Poniżej przedstawiono pełne zapytanie dla poprzedniego przykładu:

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"

Potencjalne zagrożenie bezpieczeństwa

Czytelnicy raportów, którzy mogą dynamicznie ustawiać wartości parametrów zapytania M, mogą mieć dostęp do większej liczby danych lub wyzwalać modyfikacje systemu źródłowego przy użyciu ataków polegających na wstrzyknięciu. Ta możliwość zależy od tego, jak odwołujesz się do parametrów w zapytaniu M i jakie wartości są przekazywane do parametrów.

Na przykład masz sparametryzowane zapytanie Kusto skonstruowane w następujący sposób:

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

Nie ma problemów z przyjaznym użytkownikiem, który przekazuje odpowiednią wartość dla parametru, na przykład Games:

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

Jednak osoba atakująca może przekazać wartość, która modyfikuje zapytanie w celu uzyskania dostępu do większej liczby danych, na przykład 'Games'//:

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

W tym przykładzie osoba atakująca może uzyskać dostęp do informacji o grach, które nie zostały jeszcze wydane, zmieniając część zapytania na komentarz.

Eliminowanie ryzyka

Aby ograniczyć ryzyko bezpieczeństwa, należy unikać łączenia ciągów wartości parametrów języka M w zapytaniu. Zamiast tego użyj tych wartości parametrów w operacjach języka M, które składają się do zapytania źródłowego, aby aparat M i łącznik skonstruować końcowe zapytanie.

Jeśli źródło danych obsługuje importowanie procedur składowanych, rozważ przechowywanie tam logiki zapytań i wywoływanie jej w zapytaniu M. Alternatywnie, jeśli jest dostępna, użyj mechanizmu przekazywania parametrów wbudowanego w źródłowy język zapytań i łączniki. Na przykład usługa Azure Data Explorer ma wbudowane funkcje parametrów zapytań, które są przeznaczone do ochrony przed atakami polegającymi na wstrzyknięciu.

Oto kilka przykładów tych środków zaradczych:

  • Przykład, który używa operacji filtrowania zapytania M:

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

  • Przykład, który deklaruje parametr w zapytaniu źródłowym lub przekazuje wartość parametru jako dane wejściowe do funkcji zapytania źródłowego:

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

  • Przykład bezpośredniego wywoływania procedury składowanej:

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

Rozważania i ograniczenia

Istnieją pewne zagadnienia i ograniczenia dotyczące używania dynamicznych parametrów zapytania języka M:

  • Pojedynczy parametr nie może być powiązany z wieloma polami ani odwrotnie.
  • Dynamiczne parametry zapytania języka M nie obsługują agregacji.
  • Dynamiczne parametry zapytania języka M nie obsługują zabezpieczeń na poziomie wiersza.
  • Nazwy parametrów nie mogą być zastrzeżonymi słowami języka DAX ani spacjami. Możesz dołączyć Parameter na końcu nazwy parametru, aby uniknąć tego ograniczenia.
  • Nazwy tabel nie mogą zawierać spacji ani znaków specjalnych.
  • Jeśli parametr jest typem Date/Time danych, musisz rzutować go w zapytaniu M jako DateTime.Date(<YourDateParameter>).
  • Jeśli używasz źródeł SQL, możesz uzyskać okno dialogowe potwierdzenia za każdym razem, gdy wartość parametru ulegnie zmianie. To okno dialogowe jest spowodowane ustawieniem zabezpieczeń: Wymagaj zatwierdzenia przez użytkownika dla nowych natywnych zapytań bazy danych. To ustawienie można znaleźć i wyłączyć w sekcji Zabezpieczenia w obszarze Opcje programu Power BI Desktop.
  • Dynamiczne parametry zapytania języka M mogą nie działać podczas uzyskiwania dostępu do modelu semantycznego w programie Excel.
  • Dynamiczne parametry zapytania języka M nie są obsługiwane w Serwer raportów usługi Power BI.

Nieobsługiwane gotowe typy parametrów

  • Dowolne
  • Czas trwania
  • Prawda/fałsz
  • Plik binarny

Nieobsługiwane filtry

  • Fragmentator czasu względnego lub filtr
  • Data względna
  • Fragmentator hierarchii
  • Filtr dołączania wielopolowego
  • Wykluczanie filtrów/Brak filtrów
  • Wyróżnianie krzyżowe
  • Filtr przechodzenia do szczegółów
  • Filtr przeglądania krzyżowego
  • Filtr pierwszych N

Nieobsługiwane operacje

  • And
  • Contains
  • Mniejsze niż
  • Większe niż
  • Rozpoczyna się od
  • Nie zaczyna się od
  • Nie jest
  • Nie zawiera
  • Jest puste
  • Nie jest puste

Powiązana zawartość

Aby uzyskać więcej informacji na temat możliwości programu Power BI Desktop, zapoznaj się z następującymi zasobami: