Optymalizacja raportów Power BI cz.4

Artykuł poświęcony jest usprawnieniom, jakie możemy zastosować do pisanego przez nas kodu DAX. Dokładną analizę czasu wykonywania kodów możemy przeprowadzić w DAX Studio oraz VertiPaq Analyzer.

W kolejności od najbardziej wpływającej na czas egzekucji kodu zasady prezentują się następująco:

  1. Zmniejszenie materializacji. W największym skrócie mówiąc, materializacja to całkowita liczba kolumn i wierszy przez które silnik DAX musi przejść, aby uzyskać pożądany przez nas wynik. Jeżeli materializacja zapytania posiada znacznie większy wymiar niż wymiar uzyskanych przez nas wyników, oznacza to że prawdopodobnie jest spore pole do poprawy w formule. Pisząc zapytania DAX, iterujemy tylko przez kolumny i tabele, które są potrzebne. Jeśli nie musimy iterować przez całą tabelę, przechodzimy tylko przez jej kolumnę. Np. załóżmy że w tabeli Clients posiadamy kolumnę Discount %, która oznacza wartość procentową udzielonego rabatu. Posiadamy także miarę obliczającą wartość zamówienia przed obniżką – [Sales Amount]. Chcąc obliczyć sumę zamówień po rabacie dla wszystkich klientów najbardziej intuicyjnie napiszemy następującą miarę: SUMX(Clients, [Sales Amount] * Discount %). Przechodzimy tutaj jednak przez całą tabelę Clients, przez co czas zapytania jest dłuższy. Po zastanowieniu dojdziemy do wniosku, że następująca miara da w rezultacie te same wyniki: SUMX(VALUES(Discount %), [Sales Amount] * Discount %) – miara [Sales Amount] jest dynamiczna i zwiększy odpowiednio wyniki. W zoptymalizowanym zapytaniu przechodzimy tylko poprzez unikatowe wartości Discount % i obliczone dla nich wartości Sales Amount, zatem materializacja jest znacznie mniejsza. Inny przykład: zamiast SUMX(Sales,Sales[Quantity]*ROUND(Sales[Net Price],0)) piszemy SUMX(VALUES(Sales[Net Price]), CALCULATE(SUM(Sales[Quantity])) * ROUND(Sales[Net Price],0)) W zmniejszeniu materializacji funkcje VALUES lub DISTINCT są naszymi sprzymierzeńcami.
  2. Zmniejszenie przejść między kolumnami. Jest to uwaga związana z działaniem silnika VertiPaq i tworzeniem przez niego kolumnowej bazy danych. Im mniej kolumn i przechodzenia między nimi, tym lepiej. Przykładem optymalizacji dla tego punktu jest także przykład wyżej.
  3. Argumenty filtrowania CALCULATE filtrują kolumny, nie tabele. Znów, najbardziej intuicyjnym zapytaniem może być dla nas zagnieżdżenie funkcji FILTER(tabela) np. CALCULATE ([Sales Amount], FILTER (Sales, Sales[Importance]*Sales[Quantity]>100). Jednak przez to że filtrujemy całą tabelę, potrzebny jest duży bufor danych. Zamiast tego następująca miara da identyczne rezultaty: CALCULATE([Sales Amount], KEEPFILTERS(FILTER(ALL(Sales[Importance], Sales[Quantity]),Sales[Importance]*Sales[Quantity]>100))). Kod jest dłuższy, ale wydajniejszy. Funkcja KEEPFILTERS jest konieczna aby zachować filtry z wizualizacji.
  4. Niekorzystanie z funkcji IF. Funkcja ta jest realizowana tylko przez silnik formuł, który jest wolniejszy od magazynowego. Ponadto, silnik musi przetwarzać wszystkie scenariusze osobno. Najlepiej więc unikać tej funkcji kiedy to tylko możliwe. Przykłady niepotrzebnego stosowania funkcji IF: Warunek IF zamiast filtra w CALCULATE. Np. zamiast IF(Client[Age]>18,[Sales Amount]), piszemy: CALCULATE([Sales Amount], KEEPFILTERS(Client[Age]>18)). IF do sprawdzania czy nie dzielimy przez zero. Zamiast IF(Sales>0, Income/Sales), piszemy DIVIDE(Income, Sales) – funkcja DIVIDE w domyśle nie dzieli przez 0. Używanie IF w iteratorach. Zamiast instrukcji warunkowej wydajniejszą miarą będzie połączenie wyników z dwóch warunków np. zamiast CALCULATE(SUMX(Sales, Sales(Amount)*Sales(Price)*IF(Sales(Amount)>1, 0.5,0.1))), lepiej napisać CALCULATE(SUMX(Sales,Sales(Amount)*Sales(Price)*0.5), KEEPFILTERS(Sales(Amount)>1)) + CALCULATE(SUMX(Sales,Sales(Amount)*Sales(Price)*0.1), KEEPFILTERS(Sales(Amount)<=1))
  5. Nieużywanie zagnieżdżonych iteratorów