SQL Day 2022 prezentacja – 10 mitów oraz błędów związanych z Power BI – mit nr 10

W trakcie SQL Day 2022 miałem okazję zaprezentować kilka mitów i błędów związanych z wykorzystaniem Power BI podczas sesji zatytułowanej 10 mitów oraz błędów związanych z Power BI. W rzeczywistości przedstawiłem 9 różnych tematów, a ostatni dziesiąty postanowiłem opisać na blogu.

Ostatnim błędnym stwierdzeniem na jakie chciałem zwrócić uwagę brzmi następująco “Wystarczy, że nauczę się składni DAX i wszystko działa”. Stwierdzenie to w różnych formach usłyszałem i zobaczyłem w implementacjach modeli tabelarycznych już wielokrotnie. Język DAX jest dosyć specyficzny ale tak jak każdy inny język ma swoje dobre i złe praktyki. Pamiętajmy, że nawet jak coś zwraca prawidłowe wyniki to może nie działać optymalnie.

Dobrych praktyk i konstrukcji języka DAX jest naprawdę dużo i mimo wszystko powinniśmy je znać, szczególnie że większość z nich jest stosunkowo prosta w implementacji. Dla przykładu podam kilka z nich.

Używanie zmiennych zamiast powtarzania tego samego kodu

Zmienne nie były obecne w DAX od samego początku. Pamiętam jak dosyć dawno temu pisałem o zmiennych w modelu tabelarycznym w ramach serii opisującej nowości SQL Server 2016 (link), a więc trochę czasu minęło. Same zmienne dają nam możliwość nie tylko uproszczenia tworzonego kodu ale również mają wpływ na wydajność.

Przypuśćmy, że w naszym teście chcemy porównać sprzedaź z danego miesiąca ze sprzedażą z miesiąca poprzedniego po to żeby wyliczyć różnicę procentową taką jak na poniższym obrazku:

Możemy to osiągnąć bez używania zmiennych pisząc np. taki kod:

Total Sales CM vs PM (%) =
DIVIDE (
    SUM ( ‘Sales'[Total Including Tax] )
        – CALCULATE (
            SUM ( ‘Sales'[Total Including Tax] ),
            DATEADD ( ‘Date'[Date], -1, MONTH )
        ),
    CALCULATE (
        SUM ( ‘Sales'[Total Including Tax] ),
        DATEADD ( ‘Date'[Date], -1, MONTH )
    )
)

Jak możecie zauważyć powyższa miara posiada powtarzające się elementy dlatego też łatwo użyć zmiennych, które znacząco poprawiają czytelność kodu:

Total Sales CM vs PM (%) VARIABLE =
VAR vPreviousMonthSales =
    CALCULATE (
        SUM ( ‘Sales'[Total Including Tax] ),
        DATEADD ( ‘Date'[Date], -1, MONTH )
    )
RETURN
    DIVIDE (
        SUM ( ‘Sales'[Total Including Tax] ) – vPreviousMonthSales,
        vPreviousYearSales
    )

Efekt działania obu miar jest dokładnie taki sam jeśli chodzi o wyniki:

Jak to wygląda od strony wydajności? Możemy to sprawdzić w łatwy sposób wyodrębniając wysyłane przez każdą z powyższych tabel zapytanie w narzędziu Performance Analyzer:

Następnie te zapytania możemy wrzucić do DAX Studio i zbadać sposób ich wykonania używając opcji Server Timings:

Wykonanie obu zapytań dało następujące rezultaty (w górnej części zapytanie bez zmiennych, w dolnej ze zmiennymi):

To na czym się tutaj skupimy to liczba zapytań wykonanych przez Storage Engine (SE Queries) i widzimy, że przy tak prostej konstrukcji jaką my wywołaliśmy mamy o jedno mniej zapytanie w przypadku wywołania zmiennych. Czy to dużo? W tym konkretnym wypadku mamy bardzo prosty przykład ale w wielu bardziej skomplikowanych przykładach różnica może być znacząca. Jaki mamy zatem z tego morał? A no taki, że zmienne są dobre i powinniśmy je używać zamiast powtarzać wielokrotnie ten sam kod.

DIVIDE vs IF

Nieco innym przypadkiem jaki chciałbym przedstawić jest przypadek dzielenia. Chodzi mianowicie o sytuację gdzie chcemy uniknąć dzielenia przez 0 aby nie zwracać błędu. Podobnie jak w poprzedniej sytuacji tak również tutaj mamy dwa podejścia:

użycie IF gdzie sprawdzamy czy wartość w mianowniku wynosi zero lub blank:

IF =
VAR vPreviousMonthSales =
    CALCULATE (
        SUM ( ‘Sales'[Total Including Tax] ),
        DATEADD ( ‘Date'[Date], -1, MONTH )
    )
VAR vCurrentMonthSales =
    SUM ( ‘Sales'[Total Including Tax] )
RETURN
    IF (
        OR ( ISBLANK ( vPreviousMonthSales ), vPreviousMonthSales = 0 ),
        BLANK (),
        vCurrentMonthSales / vPreviousMonthSales
    )

lub użycie DIVIDE gdzie nie potrzebujemy nic sprawdzać bo funkcja ta w momencie gdzie mianownik jest równy zero lub jest pusty zwróci po prostu wartość blank:

Divide =
VAR vPreviousMonthSales =
    CALCULATE (
        SUM ( ‘Sales'[Total Including Tax] ),
        DATEADD ( ‘Date'[Date], -1, MONTH )
    )
VAR vCurrentMonthSales =
    SUM ( ‘Sales'[Total Including Tax] )
RETURN
    DIVIDE ( vCurrentMonthSales, vPreviousMonthSales )

Podobnie jak w poprzednim przypadku stworzyłem proste tabele porównujące oba podejścia:

Zebrałem zapytania i sprawdziłem sposób ich wykonania w DAX Studio – poniżej wyniki (wyżej IF, niżej DIVIDE):

Jak możecie zobaczyć podobnie jak poprzednio – użycie DIVIDE spowodowało wysłanie jednego zapytania mniej.

HASONEVALUE vs SELECTEDVALUE

W tym scenariuszu mamy taki przypadek, że musimy sprawdzić czy wybrano więcej niż jedną wartość. Możemy to zrobić oczywiście na wiele sposobów m.in wybierając HASONEVALUE lub SELECTEDVALUE. W naszym scenariuszu testowym mamy wartość sprzedaży (Sales) oraz kurs po którym możemy tą wartość przemnożyć (FxRate) przy czym kurs ten jest do wyboru w slicerze. Wynikiem tego mnożenia jest miara Sales FX:

Miara Sales FX może zostać obliczona tylko wtedy gdy pojedyncza wartość zostanie wybrana na slicerze, w przeciwnym wypadku ma być pusta:

Miara ta z HASONEVALUE wygląda następująco:

Sales FX (hasonevalue) =
SUM ( Sales[Total Including Tax] )
* IF ( HASONEVALUE ( Rates[FxRate] ), VALUES ( Rates[FxRate] ) )

W przypadku SELECTEDVALUE wygląda następująco:

Sales FX (Selectedvalue) =
SUM ( Sales[Total Including Tax] ) * SELECTEDVALUE ( Rates[FxRate] )

Co do czytelności myślę, że wersja z SELECTEDVALUE jest troszeczkę czytelniejsza, a jeśli chodzi o performance to akurat w tym przypadku nie ma żadnej różnicy ponieważ obie składnie wewnętrznie sprowadzają się do tych samych zapytań (zwróćcie uwagę, że w obu przypadkach mamy dokładnie te same treści zapytań w kolumnie Query):

IF IS BLANK vs Show Items With No Data

Wspomnę jeszcze o jednym przykładzie o którym mówiłem na sesji, chodzi mianowicie o tym, że mamy wymóg pokazania na wizualizacji wszystkich wartości z danego słownika nawet wtedy gdy nie mają wartości. Może nas jako deweloperów kusić żeby napisać w definicji miary coś takiego:

Total Sales (with zeroes) =
VAR vTotalSales =
SUM ( Sales[Total Including Tax] )
RETURN
IF ( ISBLANK ( vTotalSales ), 0, vTotalSales )
Zamiast takich kombinacji możemy po prostu na wizualizacji wykorzystać zwykłą miarę i włączyć właściwość Show Items with No data:

Efekt działania obu praktyk przedstawia się następująco:

Która opcja jest lepsza? Zdecydowanie Show Items with no data! Gdzie znajdziemy na to dowód? W fizycznym planie wykonania tego zapytania gdzie dla Show Items with no data plan posiada wiele materializacji itp ale tylko 450 operacji, a dla przykładu z obsługą IF + ISBLANK jest ich 617 gdzie w tym przypadku mniej oznacza lepiej:

Oczywiście tego typu praktyk jest zdecydowanie więcej ale całość tych przykładów ma pokazać, że ich znajomość jest kluczowa do tego aby budować dobre i skalowalne rozwiązania oparte o model tabelaryczny. Dzięki za uwagę i dziękuję wszystkim za uczestnictwo w mojej sesji!