Power BI – mechanizm wydajnościowy Query Folding

Podstawowym mechanizmem wydajnościowym wbudowanym w Power Query jest Query Folding. Mimo, że wiele osób o nim słyszało nie każdy rozumie czym on tak naprawdę jest i jak duży ma wpływ na wydajność procesów ETL w Power BI. Z tego też powodu postanowiłem napisać na ten temat parę słów i podzielić się paroma ciekawymi aspektami związanymi z tym właśnie mechanizmem.

Nie owijając w bawełnę na samym wstępie powiedzmy sobie czym jest omawiane zagadnienie. Tak więc mechanizm Query Folding polega na tym, że Power BI tłumaczy wykonane przez nas operacje transformacji danych na język źródła do którego się łączymy. Innymi słowy w przypadku gdy np. łączymy się do SQL Server i wykonamy szereg operacji to na ich podstawie wygenerowane jest odpowiednie zapytanie w języku TSQL. Wszystko było by pięknie i przejrzyście gdyby nie fakt, że nie wszystkie operacje mogą być przerzucone na źródło i nie każde źródło obsługuje Query Folding. Jeśli naszym źródłem jest plik płaski csv to w jaki sposób przerzucić coś na źródło? No właśnie… W takim przypadku wszystkie transformacje zostaną wykonane lokalnie przez Power BI. Jeśli chodzi o źródła, które wspierają wspomniany mechanizm to z pewnością wspierają go:

• relacyjne bazy danych
• modele Analysis Services
• Azure Marketplace
• Listy sharepoint
• Exchange
• Active Directory
• HDFS i niektóre operacje na systemie plików

Jasne powinno być to, że każde z tych źródeł wspiera różny zakres transformacji. Oczywiście jest to lista, którą możemy stworzyć według własnych doświadczeń ponieważ żadna oficjalna lista tego typu w dokumentacji nie istnieje. Podobnie ma się rzecz z transformacjami, które mogą być przerzucone na źródło i tak w przypadku baz danych mogą to być transformacje takie jak:

• agregacja
• filtracja
• pivot/unpivot
• proste kalkulacje liczbowe
• proste transformacje tekstowe
• złączenia wertykalne (union)
• złączenia horyzontalne (join)

To tylko kilka wybranych transformacji i z całą pewnością nie jest to lista skończona. Mamy kilka sposobów na monitorowanie czy określone transformacje zostały przerzucone na źródło czy też nie (ale o tym trochę później). Na tym etapie warto zdać sobie sprawę, że w ramach pojedynczego zapytania z określoną listą kroków część z nich może być przerzucona, a część nie. Sytuacja gdy tylko część zapytania jest przerzucona na źródło danych określana jest jako partially query folding i to jest w moim odczuciu najczęstszy scenariusz. Dla przykładu poniżej mamy zapytanie składające się z czterech kroków:

Które z tych kroków zostaną przerzucone na bazę danych? Oznaczyłem je zielonym kolorem:

W tym przypadku jedynie transpozycja została wykonana lokalnie. W takim wypadku baza wykonała zapytanie wykonujące określone transformacje i zwrócony rezultat został poddany transpozycji – ten konkretny przypadek nie jest najgorszy wystarczy, że spojrzymy na kolejny przykład:

Powyżej możemy zobaczyć, że transpozycja będzie wykonana jako pierwsza, a Query Folding działa w ten sposób, że wszystkie kroki które nastąpią po kroku wykonanym lokalnie są wykonywane lokalnie. Więc można sobie wyobrazić sytuację podobną do poniższego:

Zestaw kroków zdefiniowanych w Power Query spowodował wygenerowanie określonego zapytania z warunkiem filtrującym w rezultacie baza danych wygenerowała rezultat w postaci 10 tysięcy wierszy. W drugim przykładzie mamy nieco inne realia i wygenerowane zapytanie nie zawiera warunku filtrującego:

Dlaczego nie ma warunku filtrującego? A no np. dlatego, że przed krokiem filtrującym mieliśmy transformację, która nie może zostać przetransformowana na TSQL. Tak więc cały zestaw z tabeli w postaci 100 milionów wierszy został wysłany z bazy do Power BI i dopiero potem, lokalnie została wykonana filtracja.W takim przypadku całość mogła trwać nieakceptowalnie długo, a my odczujemy na własnej skórze, że coś jest nie w porządku. W tym wypadku morał jest dosyć jasny, powinniśmy unikać transformacji, które nie mogą być przerzucone na źródło, a jeżeli musimy ich użyć to postarajmy wykonać je jak najpóźniej tak aby jak najwięcej kroków mogło być przerzucone.

Kolejnym bardzo ważnym aspektem związanym z Query Folding jest monitorowanie, która część zapytania została przerzucona. Od pewnego czasu nie musimy używać narzędzi zewnętrznych jak Profiler czy Extended Events – mamy gotowy mechanizm wewnątrz edytora zapytań. Wystarczy, że klikniemy prawym przyciskiem myszy na wybrany krok zapytania i z menu kontekstowego wybierzemy opcję View native query:

Jeżeli możemy wybrać tą opcję tj. nie jest ona wyszarzona oznacza to, że wszystkie kroki do wybranego w całości są przerzucone na źródło. Naszym oczom powinno ukazać się okno z dokładnym zapytaniem wysłanym do źródła, który odpowiada wszystkim zdefiniowanym przez nas krokom:

Mając wgląd w zapytanie możemy dostosować indeksy po stronie bazy danych itp. W przypadku gdy wybrany krok ma niedostępną opcję View native query oznacza to po prostu, że zawiera on (lub któryś z poprzednich kroków) transformację która nie może zostać przetłumaczona. Sytuacja taka ma miejsce np. w przypadku transformacji Merge (jest ona dostępna po zaznaczeniu dwóch kolumn i wybraniu z menu kontekstowego opcji Merge Columns) – opcja ta przeprowadza prostą konkatenację i jest graficzną reprezentacją funkcji Table.CombineColumns:

Na powyższym zrzucie możecie zauważyć, że opcja rzeczywiście jest niedostępna. Na szczęście filtracja została wykonana dużo wcześniej więc ogólny wpływ na wydajność mojego zapytania jest niewielki. Jeśli jednak chciałbym aby wszystko było wykonane przez bazę wystarczy, że nie wykorzystam operacji Merge Columns, a po prostu dodam kolumnę wyliczaną z wykorzystaniem operatora &:

W tym przypadku Query Folding już zadziała i przyjmie następującą formę w TSQL:

Nie zawsze oczywiście praca z mechanizmami wydajnościowymi w Power BI jest taka prosta.

Trzecim czynnikiem wpływającym na omawiany mechanizm są tzw. Privacy Levels, które w zamierzeniu mają zapobiegać przesyłaniu danych wrażliwych do niepowołanych źródeł w ramach mechanizmu Query Folding. Załóżmy hipotetyczną sytuację gdzie mamy w naszym pliku pbix dane z trzech baz: Bazy A, Bazy B oraz Bazy C. Nasz proces ładujący jest tak zdefiniowany, że dane z Bazy A są używane do filtracji Bazy B, oraz dane z Bazy B są używane do filtracji Bazy C. Załóżmy, że wszystkie dane są wrażliwe. Cała sytuacja została przedstawiona na poniższej grafice:

Jak można bardzo łatwo wywnioskować taka wymiana danych wrażliwych nie jest pożądana, a czasem wręcz niedopuszczalna. Tutaj z pomocą przychodzą nam Privacy Levels, które możemy ustawić na poziomie całego źródła. Dla przykładu w momencie gdy ustawimy dla Bazy B poziom prywatności Private to mówimy do Power BI, że żadne dane z tego źródła nie mogą opuścić Power BI. Ponadto żadne dane z innych źródeł nie mogą być wysłane do źródła oznaczonego jako Private. Przy takim ustawieniu sytuacja przedstawia się następująco:

W takim razie gdzie nastąpi filtracja Bazy B danymi z Bazy A? Wykona ją lokalnie Power BI co oczywiście może mieć negatywny wpływ na wydajność ale ostatecznie nasze dane są bezpieczne.

Jeśli chodzi o Privacy Levels to ustawienie to może przyjąć następujące wartości:

• Public
• Organizational
• Private
• None

Bez wchodzenia w szczegóły (poświęcę temu osobny wpis) interakcje pomiędzy poszczególnymi poziomami chciałem przedstawić na poniższym zestawieniu:

Czy zatem warto w ogóle ustawiać Privacy Levels? To zależy od konkretnego scenariusza ale jeśli łączymy dane z wielu źródeł i nie powinno pomiędzy tymi źródłami interakcji wtedy ustawienie to jak najbardziej ma sens. Trzeba jednak pamiętać o implikacjach wydajnościowych związanych z tym mechanizmem. W przypadku Excela mieliśmy pewną opcję o nazwie Fast Combine, która determinowała czy Privacy Levels w ogóle były używane czy też nie. W przypadku Power BI odpowiednikiem tej właściwości jest opcja, którą znajdziemy na zakładce Privacy o nazwie Ignore the Privacy Levels and potentially improve performance. Po ustawieniu tej opcji niezależnie od ustawień prywatności poszczególnych źródeł będą one ignorowane, a dane będą przesyłane bez żadnych przeszkód.

Ostatnim aspektem o którym chciałbym wspomnieć, a który ma ogromny wpływ na Query Folding są funkcje użytkownika. Podsumowując całkowicie wyłączają one opisywany mechanizm! Spójrzmy na konkretny przykład, poniższy proces ETL niemal w całości jest przenoszony na bazę danych:

W międzyczasie stworzyłem sobie najprostszą możliwą funkcję w języku M, która wykonuje zamienia wszystkie litery na duże dla zadanej kolumny w danej tabeli. Ciało tejże funkcji wygląda następująco:

(paramColumn as text)=>
let
in
    Text.Upper(paramColumn)

Wywołałem tą funkcję na przykładowym jako Custom Column:

= Table.AddColumn(#"Expanded DimProductSubcategory", "StatusUpperCase", each udf_Upper([Status]))

W momencie jak sprawdzimy czy Query Folding zadziałał dostrzeżemy, że niestety nie…:

Tak więc wywołanie funkcji jest świetnym sposobem na to, żeby pozbawić się dobrodziejstw QF. Jeśli chcemy ten mechanizm celowo wyłączyć to zamiast wywoływać specjalnie spreparowaną funkcję możemy wywołać funkcję z wbudowanego katalogu o nazwie Table.Buffer lub List.Buffer jeśli działamy na liście. Funkcja ta cachuje wszystkie dane po stronie Power BI, tak więc jeśli umieścimy ją na samym początku naszego skryptu ładującego mamy pewność, że wszystkie transformacje wykonane zostaną lokalnie. Jak tego użyć?

Wystarczy dodać krok zaraz po kroku wskazującym konkretny zbiór danych wywołać funkcję, która jako parametr przyjmie krok poprzedni i właściwie to by było na tyle – możemy pożegnać się z QF:

Byłbym zapomniał… na QF wpływ mają jeszcze inne czynniki. Jest nim m.in. użycie własnego zapytania jako źródła dla Power BI. Bo nie bez przyczyny opcja ta znajduje się w “opcjach zaawansowanych”:

W skrócie jeśli napiszemy własnego TSQLa przy pobieraniu danych to automatycznie wyłączamy QF i również w tym przypadku morał jest prosty – jeśli już chcemy pisać własne zapytania na źródle to zawrzyjmy tam wszystkie transformacje i nie używajmy Power Query. Jeśli natomiast chcemy korzystać z tego naprawdę potężnego narzędzia wtedy zrezygnujmy z własnych zapytań. Oczywiście zawsze znajdzie się jakieś obejście i nie inaczej jest i tym razem, jeśli chcecie pisać własne zapytania to zapiszcie je jako widok po stronie bazy. Power Query potraktuje wtedy taki widok standardowo jak tabele i wygeneruje np. takie zapytanie:

Tak więc pamiętajmy o tym kompromisowym wyjściu z sytuacji.

To by było na ten moment odnośnie mechanizmu Query Folding w ramach niniejszego artykułu. Mam nadzieję, że pomógł on Wam zrozumieć jego istotę i to, że jest on najważniejszą rzeczą na której powinniśmy się skupić podczas problemów wydajnościowych w ładowaniu danych do Power BI.