DAX – Calculation groups

Jedną z nowości jakie w ostatnim czasie pojawiły się w języku DAX są tzw. Calculation Groups. Na ten moment możemy je zobaczyć w Analysis Services 2019 ale z całą pewnością można powiedzieć, że coś podobnego prędzej czy później pojawi się również w Power BI i Azure Analysis Services. Czym są Calculation Groups, jakie nowe konstrukcje języka DAX są z nimi związane oraz do czego mogą one nam się przydać w praktyce? Na te wszystkie pytania postaram się odpowiedzieć w ramach tego artykułu. Opiszemy sobie wstępnie cały mechanizm oraz pokażemy kilka dodatkowych podstawowych konstrukcji po to aby w ramach osobnych artykułów móc sobie ten temat nieco rozszerzyć.

Myślę, że większość ludzi którzy pracują z miarami w języku DAX natrafiło w swojej karierze na przypadek gdzie wiele różnych miar ma kilka typowych kombinacji jak np.:

• YTD – rok narastająco,
• MTD – miesiąc narastająco,
• QTD – kwartał narastająco,
• Last Year – wartość miary w poprzednim roku,
• Last Month – wartość miary w poprzednim miesiącu,
• Last Quarter – wartość miary w poprzednim kwartale,
• % of Year Total – udział miary w danym okresie w stosunku do wartości miary dla całego roku.

Wyobraźmy sobie sytuację gdzie mamy takie miary jak suma sprzedaży, ilość sprzedanych sztuk, należny podatek i jeszcze kilka dodatkowych. Oprócz podstawowej definicji każdej z tych miar musimy mieć ich “wariacje” opierające się na wymienionych wyżej funkcjach opartych o wymiar czasu. Ilość miar jakie musimy stworzyć rośnie wykładniczo bo posiadając jedną miarę główną musimy stworzyć dodatkowo np. siedem kombinacji. Może nam to przysporzyć niemały ból głowy bo przy dwóch miarach głównych mamy do zrobienia aż 16 różnych kombinacji, przy trzech miarach głównych 24 itp itd, a to tylko początek! Czy nie fajnie byłoby mieć szablon gdzie tworzymy sobie główną miarę, a te wszystkie kombinacje zostaną zdefiniowane tylko raz i będą działać ze wszystkimi interesującymi nas miarami głównymi? Byłoby bardzo dobrze! W tym miejscu pojawia się bohater naszego dzisiejszego artykułu którym są Calculation Groups, które pełnią taką właśnie rolę! Sprawdźmy ich działanie na praktycznym przykładzie oraz opiszmy sobie pokrótce jak to wszystko działa.

Do celów testowych mój model standardowo opiera się na AdventureWorksDW i przybrał preferowaną formę gwiazdy:

Aby móc użyć Calculation Group zainstalowałem najnowszą dostępną wersję Data Tools gdzie w oknie Tabular Model Explorer powinienem zobaczyć dedykowany folder o nazwie “Calculation Groups” tak jak zostało to zaprezentowane na poniższym zrzucie ekranowym:

Zanim przejdziemy do samych Calculation Groups stwórzmy sobie dwie miary główne dla których będziemy tworzyć różne kombinacje oparte o Time Intelligence. Będą to proste miary będące sumą poszczególnych pól w centralnej tabeli faktów (ale nic nie stoi na przeszkodzie aby były to inne bardziej skomplikowane wyliczenia):

Total:= SUM(FactInternetSales[ExtendedAmount])

Total Qty:= SUM(FactInternetSales[OrderQuantity])

Przechodząc dalej wyobraźmy sobie, że naszym wymogiem biznesowym jest to, aby użytkownicy mieli możliwość porównywania wyżej stworzonych miar głównych z jej wartością z różnych przedziałów czasowych. W standardowym scenariuszu powinniśmy dodać kolejne miary, które wywołają każdą z powyższych głównych miar i zmodyfikują jej zachowanie w CALCULATE np.:

CALCULATE(
[Total],
DATESYTD(DimDate[FullDateAlternateKey])
)

W ten sposób postąpilibyśmy “po staremu” i musielibyśmy tą formułę powtórzyć również dla miary Total Qty. Tego jednak nie zrobimy i wykorzystamy Calculation Groups – klikamy zatem prawym przyciskiem myszy na wspomniany wyżej folder Calculation Groups i z menu kontekstowego wybieramy opcję New Calculation Group. Naszym oczom powinna ukazać się następująca struktura będąca pustym szablonem naszego konstruktu:

Warto sobie uzmysłowić już na samym początku, że stworzona przez nas grupa w narzędziu klienckim będzie widoczna jako zwykła tabela z pojedynczą kolumną. Nadajmy naszej “tabeli” odpowiednią nazwę, a mianowicie “Time Intelligence” – zrobimy to w standardowy sposób wyszukując właściwość Calculation Group Name i wpisując wybraną przez nas nazwę:

W przypadku kolumny nazwę zmienimy w podobny sposób z tym, że tym razem właściwość Column Name przyjmie wartość Mode:

Dalej przechodzimy do definiowania wszystkich interesujących nas wariacji dla naszych miar, które są tutaj widoczne jako tzw. Calculation Items. W naszym scenariuszu wykorzystamy pięć różnych wariacji:

• Current – element mający reprezentować bieżącą wartość miary,
• MTD – wartość miary narastająco po miesiącu,
• PreviousDay – wartość miary z poprzedniego dnia
• QTD – wartość miary narastająco po kwartale,
• YTD – wartość miary po roku.

Po implementacji powinny one wyglądać następująco:

Pod każdą wariacją mamy odpowiedni kod i tak w elemencie Current odwołujemy się do bieżącej wartości miary dlatego też możemy użyć funkcji nowej funkcji SELECTEDMEASURE. Funkcja ta nie robi nic innego jak to, że wywołuje kod aktualnej w danym kontekście miary:

SELECTEDMEASURE()

Pozostałe elementy opierają się w dużej mierze na funkcjach Time Intelligence i myślę, że nie wymagają większego wytłumaczenia z mojej strony i tak np. dla MTD – odwołujemy się do bieżącej miary i wywołujemy ją w kontekście narastająco od początku miesiąca:

CALCULATE(SELECTEDMEASURE(),DATESMTD(DimDate[FullDateAlternateKey]))

Previous Day:

CALCULATE(SELECTEDMEASURE(),DATEADD(DimDate[FullDateAlternateKey],-1,DAY))

QTD:

CALCULATE(SELECTEDMEASURE(),DATESQTD(DimDate[FullDateAlternateKey]))

YTD:

CALCULATE(SELECTEDMEASURE(),DATESYTD(DimDate[FullDateAlternateKey]))

Przetestujmy działanie stworzonych przez nas kalkulacji, a użyjemy do tego celu Excela (oczywiście w Power BI powinno to wyglądać oczywiście analogicznie). Jak możecie zauważyć poniżej Calculation Group wygląda jak normalna tabela z atrybutami, którą możemy bez problemu wykorzystać w naszej tabeli czy też wykresie przestawnym:

Sama tabel przestawna potwierdza, że kalkulacje działają tak jak powinny:

To co jest najlepsze w całym opisywanym mechanizmie to fakt, że jak podmienię miary to oczywiście Calculation Group dostosuje się do mojego wyboru i miara zostanie przeliczona w prawidłowy sposób:

Oczywiście to jeszcze nie wszystko bo kilka kwestii pozostaje otwartych. W przypadku chcemy aby poszczególne elementy wewnątrz grupy były sortowane w inny sposób niż alfabetycznie to pierwszym krokiem jaki należy wykonać jest dodanie nowej kolumny do Calculation Group typu Whole Number i jej ukrycie (ponieważ posłuży ona tylko do sortowania). Następnie musimy dla kolumny “Mode” ustawić Sortowanie po utworzonej przed chwilą kolumnie mniej więcej tak jak zostało to przedstawione na poniższych zrzutach ekranowych:

W kolejnym kroku ustawiamy dla każdego elementu (Calculation Items) ustawiamy właściwość Ordinal:

Po tych zabiegach wszystko już powinno być posortowane w poprawny sposób.

Kolejną kwestią o jakiej warto powiedzieć jest przypisanie Calculation Group tylko do określonych miar np. wyobraźmy sobie, że chcemy mieć możliwość wykonania kalkulacji wewnątrz grupy tylko jeśli wybraną miarą jest “Total”, zrobimy to przy pomocy jednej z dwóch funkcji ISSELECTEDMEASURE lub SELECTEDMEASURENAME. Dla przykładu zmieniłem kod przygotowanego wcześniej elementu Current na następujący:

IF(ISSELECTEDMEASURE([Total]),SELECTEDMEASURE(),BLANK())

Dzięki temu zapisowi element Current będzie obliczony tylko i wyłącznie w przypadku wybrania miary Total. Analogiczny zapis z wykorzystaniem SELECTEDMEASURENAME:

if(SELECTEDMEASURENAME()="Total",SELECTEDMEASURE(),BLANK())

Jednak jak słusznie zauważył Marco i Alberto z SQLBI (link) druga opcja jest bardziej ryzykowana bo jeśli zmienimy nazwę miary to będziemy musieli również zmienić formułę w grupie co może być czasem uciążliwe, a w przypadku pierwszej funkcji wszystko dostosuje się automatycznie. Po tej modyfikacji możecie zauważyć, że pierwszy element został obliczony tylko dla miary Total:

Oprócz wspomnianych właściwości związanych z Calculation Groups istnieją jeszcze dwa aspekty o tkórych nie wspomniałem tj. formatowanie poszczególnych elementów oraz właściwość Precedence. co do formatowania to wiemy, że różne elementy mogą być formatowane na różne sposoby i to formatowanie możemy przypisać statycznie lub dynamicznie. Precedence to nic innego jak przypisanie kolejności ewaluacji poszczególnych Calculation Group co jest niezwykle istotne w przypadku gdy mamy w pojedynczym modelu więcej niż jedną grupę. Obu tym zagadnieniom postaram się poświęcić osobne artykułu na ten moment dziękuję za poświęcony czas i zachęcam do własnych testów opisywanego mechanizmu. Daje nam ono kolejne możliwości i nieco rozszerza to co możemy osiągnąć. Warto podkreślić, że również w tym przypadku powinniśmy kierować się zasadą utrzymania jak najprostszego modelu ponieważ skomplikowane Calculation Groups mogą przysporzyć więcej problemów niż korzyści. Z tym wnioskiem zostawiam Was na dziś i pozdrawiam serdecznie!