Przychodzi taki moment, kiedy okazuje się, że raport w Power BI działają zbyt wolno. Jeżeli nie korzystamy ze skomplikowanych wizualizacji, to prawdopodobnie przyczyną jest miara, której rezultaty zwracane są zbyt wolno. Aby przyspieszyć raport należałoby zoptymalizować miary, czyli napisać je w taki sposób, aby działały szybciej. Nie można oczywiście podać złotej zasady jak definiować takie miary, ale jest kilka rzeczy, o których należy pamiętać. Opowiem o nich w dzisiejszym artykule.
Jak identyfikować wąskie gardła?
Rzecz jasna, aby przystąpić do optymalizacji konieczna jest wiedza na temat tego, co będziemy optymalizować. Aby znaleźć wąskie gardło musimy przede wszystkim przechwycić wolno działające zapytanie. Do tego celu można wykorzystać kilka narzędzi: SQL Server Profiler, DAX Studio lub Power BI Performance Analyser. Dwa pierwsze narzędzia pozwolą nam śledzić wszystkie zapytania, które działają na bazie analitycznej. Ostatnie umożliwi nam z kolei przechwycenie zapytania z poziomu Power BI Desktop. Wszystkie narzędzia prezentują także czas, w którym zapytanie zostało wykonane. Którego zatem użyć? Ja zwykle korzystam z DAX Studio, które w bardzo wygodny sposób umożliwia nie tylko przechwytywanie zapytań, ale także analizę planów i czasów serwera.
1. Unikaj funkcji DISTINCTCOUNT
Jak nietrudno się domyślić jest to funkcja, która zwraca unikalne wystąpienia wartości. Mimo tego, że algorytmy zaimplemetowane w do takiego zliczania są relatywnie bardzo wydajne, to mimo wszystko przy dużych zbiorach może to być, i raczej na pewno będzie, wąskie gardło. Często można uniknąć stosowania tej funkcji, wręcz nie należy jej używać, jeśli nie ma takiej potrzeby. Oczywiście istnieją pewne techniki, które umożliwiają zastosowanie innych funkcji do zliczenia unikalnych wartości na przykład Count(Values([kolumna])). Natomiast zwykle rezultat nie jest znacznie lepszy. Co ciekawe, jeśli nie potrzebujemy dokładnych rezultatów, możemy wykorzystać funkcję APPROXIMATEDISTINCTCOUNT ,która zadziała szybciej (ale tylko w trybie Direct Query na Azure SQL DWH), ponieważ bazuje na nieco innym algorytmie. Ogólnie temat funkcji DISTINCTCOUNT jest prosty, ale jeśli naprawdę nie ma konieczności, nie należy jej używać. Poniżej załączam ciekawe artykuły, które rozwijają temat:
Analiza różnych technik liczenia unikalnych wartości
Analiza podejścia do Approximate Distinct Count w języku DAX
2. Zwracaj uwagę, jak filtrujesz wartości
Filtrowanie jest jedną z podstawowych funkcjonalności języka DAX. Bez niej trudno sobie wręcz wyobrazić jakiekolwiek raportowanie. Sęk w tym, że jest kilka możliwości realizacji tego zadania. Najbardziej oczywistą jest użycie funkcji Filter, która przyjmuje dwa parametry: tabelę i wyrażenie filtrujące. Problem w tym, że taka konstrukcja sprawia, że silnik operuje na wszystkich rekordach i kolumnach danej tabeli, co znacznie wpływa na wydajność. Oczywiście jeśli tabela nie zawiera zbyt dużo wierszy, to prawdopodobnie nie zauważmy strat wydajności, ale w przeciwnym razie rezultaty będą zwracane dłużej. Aby uniknąć takich problemów, powinniśmy rozważyć użycie konstrukcji z fuknkcją KEEPFILTERS. Takie rozwiązanie odfiltruje konkretne rekordy, ale nie będzie bazować na wszystkich kolumnach danej tabeli. Podsumowując – jeśli to możliwe, bazuj na konstrukcji z KEEPFILTERS.
3. IF vs SWITCH
Właściwie jeśli kiedykolwiek miałeś styczność z programowaniem, to te funkcje są Ci dobrze znane. Pierwsza zwraca konkretną wartość, w zależności od tego czy warunek jest prawdą czy fałszem. Druga pełni funkcję przełącznika, w zależności od wybranej opcji zwracana jest wartość. Innymi słowy – jeśli bazujesz na wartości prawda lub fałsz, wybierasz IF. Jeśli bazujesz na kilku opcjach, SWITCH będzie odpowiedni. Warto natomiast zwrócić uwagę na zachowanie tych dwóch funkcji. Obie mogą przysporzyć nam problemów, jeśli mamy bardzo skomplikowany system zagnieżdżeń. Niestety SWITCH często może powodować problemy wydajnościowe. Oczywiście te znaczące pojawiają się dopiero przy bardzo dużych modelach. Budując swoje miary w DAX, zwracaj uwagę na to, że wykonanie powinno zając tyle samo czasu z zastosowaniem wyrażenia IF, co bez niego. Jeśli na przykład korzystasz z funkcji SWITCH, a w niej masz 5 opcji do wyboru, to całe wyrażenie powinno trwać tyle, co wykonanie wybranej opcji bez SWITCH’a. Ta sama sytuacja tyczy się funkcji IF. Niestety, ale często zdarza się, że zagnieżdżone warunki mają negatywny wpływ na wydajność i należy w miarę możliwości je upraszczać.
4. Row Level Time Intelligence
Time Intelligence umożliwia analizę danych w różnych zakresach czasowych. Właściwie ciężko się obejść bez tej funkcjonalności. Dla tych czytelników, którzy o niej nie słyszeli – chodzi tutaj o miary, które wyliczają konkretną wartość (np. sprzedaży) w pewnym kontekście czasowym. Może to być np. okres od początku roku do bieżącego dnia (Year To Date) czy od początku miesiąca do dnia bieżącego (Month To Date). Nie można zapomnień także o kalkulacji dla zeszłego roku (Last Year), która pozwala porównywać bieżące rezultaty z zeszłorocznymi. Takie kalkulacje można bardzo łatwo obsłużyć rozmaitymi wbudowanymi funkcjami w DAX. Przy małych zbiorach danych zwykle będzie to działać dobrze – co innego w przypadku dużych zbiorów.
Dlatego też warto w swoich rozwiązaniach stosować Row Base Time Intelligence. Co to takiego? Otóż jest to tak naprawdę dodanie jednej kolumny do wymiaru daty i jednej nieaktywnej relacji, między tabelami daty i faktu – zakładając, że Twoja tabela daty ma dwie kolumny, datę kalendarzową i ID w formacie YYYYMMDD. Z kolei tabela faktu ma tylko kolumnę z ID daty. Aby policzyć wartość LY można wykorzystać funkcję SAMEPERIODLASTYEAR. Jest ona całkiem ok, natomiast jeśli zależy nam na wydajności, skorzystajmy z Row Lvel Time Intelligence. Dodajmy do tabeli daty kolumnę zawierającą ID z poprzedniego roku, utwórzmy nieaktywną relację między tabelami daty i faktu, bazującą na nowoutworzonej kolumnie. Napiszmy miarę, która będzie wyglądała następująco:
CALCULATE(MIARA;USERELATIONSHIP(FACT[DATEID];DATA[DATEIDLY])).
Takie rozwiązanie zwykle zadziała szybciej ze względu na wykorzystanie relacji między tabelami. Oczywiście możliwości wykorzystania Row Level Time Intelligence jest znacznie więcej i można je dopasować do niemalże każdej kalkulacji. Więcej ciekawych rozwiązań w tym artykule.
5. Korzystaj ze zmiennych
Zmienne czasami potrafią nie tylko wpłynąć na wydajność, ale również na czytelność kodu. W przypadku bardzo długich zapytań warto rozważyć skorzystanie ze zmiennych. Pamiętaj, że do zmiennej możesz przypisać zarówno tabelę, jak i wartość skalarną. Zmienne przydają się szczególnie w pracy właśnie z funkcjami IF i SWITCH.
To wszystko!
Oczywiście dobrych praktyk jest znacznie więcej i aby je wszystkie uwzględnić, należałby napisać książkę. Powyższe są moim osobistym zbiorem, który staram się stosować podczas tworzenia nowych miar. Najważniejsze w przypadku optymalizacji jest zidentyfikowanie wąskiego gardła i praca nad nim. Zapraszam także do obejrzenia filmu związanego z tematem 🙂