Time Intelligence w Power BI

Daty to dosyć specyficzny typ danych, który w narzędziach raportowych powinien dawać ciekawe możliwości raportowe. Często spotyka się anglojęzyczne pojęcie Time Intelligence, które opisuje możliwości filtrowania i obsługiwania dat w modelu analitycznym lub narzędziu raportowym. To właśnie tym funkcjonalnościom dostępnym w Power BI przyjrzymy się w ramach niniejszego artykułu. Zapraszam do lektury.

Na wstępie pokażmy sobie jakich danych będziemy używać. Cały model prezentuje się następująco:

Bazujemy na AdventureWorks, a dokładnie na tabelach FactInternetSales, DimSalesTerritory oraz DimDate. Pomiędzy tabelą faktów, a DimDate mamy trzy różne relacje. Tak jak zapewne wiecie, w Power BI tylko jedna relacja może być aktywna co powoduje, że pozostałe relacje oznaczone jako nieaktywne mogą być tylko i wyłącznie wykorzystane za pomocą języka DAX.

W przypadku modelu analitycznego bardzo ważne jest to aby mieć wymiar czasu. Wymiar taki możemy skonstruować  na kilka sposobów:

• za pomocą TSQL na poziomie hurtowni danych,
• w dowolnym narzędziu ETL
• przy pomocy DAX.

Jeśli chodzi o ostatnie ze wspomnianych podejść to tworzenie wymiaru czasu sprowadza się do funkcji CALENDAR i CALENDARAUUTO, które generują pożądany zestaw danych za nas – przedstawmy tę funkcjonalność na przykładzie. Wspomniane funkcje są funkcjami zwracającymi tabele dlatego też na samym początku stworzymy sobie tabele kalkulowaną:

W jej definicji możemy wpisać np.CALENDAR, który generuje daty od do podanych parametrów:

DimDate =
CALENDAR ( DATE ( 2001, 1, 1 ), DATE ( 2020, 12, 31 ) )

Jednokolumnowy słownik daty i czasu może być czasem przydatny, czasem jednak przydaje się nieco więcej kolumn jak np. rok. Bardziej szczegółowy słownik możemy wygenerować jedną definicją wyrażenia tablicowego np. przy pomocy ADDCOLUMNS – prosty przykład znajdziecie poniżej:

DimDate2 =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2001, 1, 1 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Week day", WEEKDAY ( [Date], 2 ),
    "End of Month", EOMONTH ( [Date], 0 ),
    "Quarter", SWITCH (
        MONTH ( [Date] ),
        1, 1,
        2, 1,
        3, 1,
        4, 2,
        5, 2,
        6, 2,
        7, 3,
        8, 3,
        9, 3,
        10, 4,
        11, 4,
        12, 4
    )
)

Powyższa definicja może oczywiście zostać dowolnie rozszerzona według potrzeb:

Funkcja CALENDARAUTO różni się tym od poprzedniczki, że tworzy tabele kalendarza automatycznie bez potrzeby definiowania daty początkowej i końcowej. Skąd zatem Power BI wie jakich dat krańcowych użyć? Robi to na podstawie modelu! Przeszuka pola oznaczone jako data i poszuka minimum oraz maksimum aby stworzyć na tej podstawie zakres dat. Dla przykładu możemy zobaczyć, że użycie CALENDARAUTO stworzyło słownik z datami od  2005-01-01 do 2014-12-31 co w pełni zgadza się z tym co znajduje się w tabeli faktów:

Funkcja ta przyjmuje opcjonalny parametr wskazujący ostatni miesiąc roku fiskalnego, który oczywiście nie musi być tożsamy z rokiem kalendarzowym. Dzięki temu parametrowi wygenerowane zostaną dodatkowe wiersze tak aby pokryć również rok fiskalny. Wydzielona tabela z datą i czasem z całą pewnością jest nam potrzebna i powinna się znaleźć w niemal każdym modelu analitycznym. Nie ma większego znaczenia czy będzie to tabele wygenerowana bezpośrednio w modelu czy też gdziekolwiek indziej.

Przechodząc dalej jak już mamy do dyspozycji model, to aby móc skorzystać z całej palety funkcji daty i czasu musimy upewnić się, że kolejne kolumny mają odpowiedni typ danych. Nadawanie typu danych nie powinno nikomu sprawić problemów, wystarczy wybrać odpowiednią opcję typu i formatu ze wstążki:

Ogólnie rzecz biorąc mamy do dyspozycji typ DATE lub DATE/TIME, które same w sobie są kontenerami wszystkich dostępnych typów (formatów) daty. Jeśli chodzi o sposób wyświetlania dat to możemy tym sterować wybierając odpowiedni format, a mamy ich dla daty całkiem sporo (o czym świadczy chociażby długość paska przewijania:)):

Ostatnią rzeczą o jaką należy zadbać w naszych słownikach dat jest ustawienie sortowania kolumn. Dla wszystkich pól opisowych jak np. nazwa miesiąca powinniśmy ustawić kolumnę po której następuje sortowanie. Opcja ta również jest dostępna ze wstążki i sprowadza się do wybrania odpowiedniej kolumny z listy rozwijanej:

Gdy już ustawimy dla naszych kolumn odpowiedni typ, format oraz sortowanie, możemy przejść do omówienia kilku wybranych funkcji języka DAX. Zaczniemy od zestawu funkcji związanych z wyliczaniem agregatów narastająco. W biznesie bardzo często pojawiają się wymagania tego typu i zamiast liczyć wszystko po stronie źródła, lub “na piechotę” z wykorzystaniem funkcji CALCULATE możemy użyć bardzo prostych funkcji wbudowanych w DAX.

Pierwszą funkcją ze wspomnianej grupy jest TOTALYTD, której użycie wygląda następująco:

SumOfQtyYTD =
TOTALYTD (
    SUM ( FactInternetSales[OrderQuantity] ),
    'DimDate'[FullDateAlternateKey].[Date]
)

Rezultat otrzymany po użyciu powyższej miary jest dosyć łatwy do przewidzenia i jest to nic innego jak suma narastająco per rok:

Podobne efekty można osiągnąć oczywiście na wiele innych sposobów. Dla przykładu poniższa miara z wykorzystaniem DATESYTD daje te same wyniki:

SumOfQty_DatesYTD =
CALCULATE (
    SUM ( FactInternetSales[OrderQuantity] ),
    DATESYTD ( DimDate[FullDateAlternateKey] )
)

 

Zaraz, zaraz… Czyżby nasza miara nie działała? Na to wygląda! Otóż miara ta będzie działała bardzo dobrze w przypadku gdy tabela czasu zostanie oznaczona jako tabela kalendarza. Power BI automatycznie nie wie, że  określona tabela jest kalendarzem, a po co ma to wiedzieć? A no po to żeby dla miar Time Intelligence ignorować w odpowiedni sposób filtry! O tym jak oznaczyć tabelę jako czas powiemy sobie w dalszej części artykułu, na ten moment zignorujmy filtry “ręcznie” używając funkcji ALL:

SumOfQty_DatesYTD =
CALCULATE (
    SUM ( FactInternetSales[OrderQuantity] ),
    DATESYTD ( DimDate[FullDateAlternateKey] ),
    ALL ( DimDate )
)

Po tym małym zabiegu miara zaczęła działać prawidłowo:

W tym miejscu warto zapamiętać złotą zasadę: zawsze oznaczajmy tabelę słownikową z czasem jako kalendarz, a jeśli nie to manipulujmy filtracją dla miar opartych o czas. Kolejną ciekawostką jest to co się stanie gdy użyjemy slicera i przefiltrujemy dane:

Na powyższym zrzucie można zauważyć, że filtr został zignorowany i miara została policzona od początku roku. Takie zachowanie jest jak najbardziej prawidłowe i w zdecydowanej większości przypadków tego właśnie oczekujemy (oczywiście możemy je zmienić za pomocą DAX). Mimo iż wspomniałem, że powinniśmy usunąć filtry za pomocą DAX w przypadku funkcji daty i czasu to nie zrobiłem tego w przykładzie z TOTALYTD. Nie zrobiłem tego, a rezultat okazał się prawidłowy – czy aby na pewno? Przeciągnijmy miarę do tabeli raz jeszcze i nie usuwajmy selekcji pochodzącej ze slicera:

Na pierwszy rzut oka możemy dostrzec tutaj dwa problemy. Po pierwsze miara nie liczy się od początku roku tylko od pierwszej daty w selekcji. Po drugie liczy się nawet wtedy gdy przekroczona zostanie górna granica w slicerze. Ostatnim problemem jest brak podsumowania (które domyślnie mimo wszystko powinno zostać przedstawione i w razie potrzeby usunięte). Tak więc morał z tego przykładu powinien być jasny (bez wchodzenia w szczegóły kontekstów wykonania) – TOTALYTD nie działa prawidłowo bez dodatkowych zabiegów nawet jeśli sprawiał dobre, pierwsze wrażenie. Wszystkie powyższe problemy możemy z łatwością ominąć dodając funkcję ALL:

SumOfQtyYTD =
TOTALYTD (
    SUM ( FactInternetSales[OrderQuantity] ),
    'DimDate'[FullDateAlternateKey],
    ALL ( DimDate )
)

Wspominałem wcześniej, że innym sposobem na poradzenie sobie z tego typu problemami jest oznaczenie tabeli jako kalendarza.
W Power BI funkcjonalność ta pojawiła się aktualizacji “Luty 2018” i znacznie ułatwiła nam życie gdyż nie musimy już kombinować aby osiągnąć pożądany efekt. Opcję tą znajdziemy oczywiście na wstążce przechodząc na widoku danych, na zakładkę Modelling:

Po wybraniu tej opcji naszym oczom ukaże się następujące okno, w którym musimy wskazać kolumnę przechowującą pełną datę:

Warty odnotowania jest komunikat widoczny na powyższym zrzucie. Ostrzega on nas przed tym, że jeżeli oznaczymy tabele jako Kalendarz, to wbudowane odpowiedniki zostaną usunięte. Może to oczywiście “zepsuć” nasze kalkulacje.

Aby przetestować działanie tabeli czasu stworzyłem miarę, która będzie kopią SumOfQtyYTD z tym, że usuniemy z niej zapis funkcji ALL:

SumOfQtyYTD2 =
TOTALYTD (
    SUM ( FactInternetSales[OrderQuantity] ),
    'DimDate'[FullDateAlternateKey]
)

Ta dam – rezultat jest dokładnie taki jak oczekiwaliśmy:

Oprócz wyżej wspomnianych funkcji związanych z datą i czasem, mamy również całą masę innych możliwości. Przede wszystkim możemy porównywać dwa wybrane okresy czy też “podróżować w czasie”. Pomogą nam w tym m.in następujące funkcje:

• DATEADD
• DATESBETWEEN
• DATESINPERIOD
• NEXTDAY
• PREVIOUSDAY
• FIRSTDAY
• LASTDAY

Dla wybranych zrobimy przykład tak, aby użycie pozostałych było dla Was intuicyjne. Na pierwszy ogień idzie znany z TSQL DATEADD, który oczywiście zwraca datę “oddaloną” w przyszłość lub przeszłość o wskazaną jednostkę czasu. Poniższy zapis zwróci po prostu datę jaka była dokładnie 2 dni wcześniej ( w stosunku do bieżącego kontekstu):

TwoDaysEarlier = DATEADD(DimDate[FullDateAlternateKey],-2,DAY)

Funkcję tą możemy wrzucić do CALCULATE lub też stworzyć kolumnę kalkulowaną – ogólnie nie powinna nam sprawić problemów. Nieco ciekawszą funkcją jest DATESBETWEEN oraz DATESINPERIOD. Pierwsza z nich zbiera zestaw dat jakie znajdują się pomiędzy datami podanymi jako parametr. Przy jej użyciu możemy np. policzyć ile dni znajduje się pomiędzy datami wybranymi przez użytkownika:

NumberOfDays =
CALCULATE (
    COUNTA ( DimDate[FullDateAlternateKey] ),
    DATESBETWEEN (
        DimDate[FullDateAlternateKey],
        FIRSTDATE ( DimDate[FullDateAlternateKey] ),
        LASTDATE ( DimDate[FullDateAlternateKey] )
    )
)

DATESINPERIOD jest zbliżoną funkcją do dwóch powyższych. Zwraca ona listę dat od zadanej daty do daty oddzielonej o określoną ilość interwałów. Poniższa definicja tabeli zwróci cztery kolejne dni rozpoczynając od 2016-01-01:

Table =
DATESINPERIOD ( DimDate[FullDateAlternateKey], DATE ( 2006, 1, 1 ), 4, DAY )

Oczywiście podobny efekt otrzymalibyśmy używając DATESBETWEEN wraz z DATEADD jednakże powyższy zapis jest po prostu nieco bardziej kompaktowy. Funkcja ta jest bardzo przydatna przy tworzeniu kalkulacji opartych o okno czasowe, np. poniższa miara sumuje pole OrderQuantity od dnia przed bieżącym dniem do dnia następującego:

QtyFromPrevToNext =
CALCULATE (
    SUM ( FactInternetSales[OrderQuantity] ),
    UNION (
        DATESINPERIOD (
            DimDate[FullDateAlternateKey],
            LASTDATE ( DimDate[FullDateAlternateKey] ),
            2,
            DAY
        ),
        DATESINPERIOD (
            DimDate[FullDateAlternateKey],
            LASTDATE ( DimDate[FullDateAlternateKey] ),
            -2,
            DAY
        )
    )
)

Bardziej wnikliwi zauważyli zapewne, że wpisując liczbę -2 cofnąłem się tylko o jeden dzień, a wpisując 2 przesunąłem się tylko o jeden dzień do przodu – warto o tym pamiętać w swoich miarach o tym specyficznym zachowaniu żeby uniknąć błędnych obliczeń.

Ostatnią rodziną funkcji, które chciałbym przedstawić są konstrukcje pozwalające porównywać okres do okresu. W tym miejscu posłużę się siostrzanymi funkcjami tj. SAMPERIODLASTYEAR oraz PARALLELPERIOD. Zacznijmy od tej pierwszej, która pozwala na obliczenie miary dla tego samego okresu z poprzedniego roku. Poniżej mamy prostą miarę, która powinna dynamicznie się policzyć dla każdego wybranego okresu z przed roku:

QtyPreviousYear =
CALCULATE (
    SUM ( FactInternetSales[OrderQuantity] ),
    SAMEPERIODLASTYEAR ( DimDate[FullDateAlternateKey] )
)

W rzeczywistości tak też się stało i miara policzyła się na każdym ze zdefiniowanych poziomów (rok, kwartał, miesiąc):

Bardzo przydatna funkcjonalność dostępna z pudełka, nie musimy definiować tutaj niczego specjalnego. Przedstawiona funkcja jest skrótem od funkcji PARALLELPERIOD, która pozwala osiągać podobne rezultaty ale dla dowolnych przedziałów (a nie tylko dla porównania poprzedniego roku z bieżącym). Poniżej mamy miarę, która oblicza sumę pola OrderQuantity dla dwóch lat wstecz w stosunku do roku w bieżącym kontekście:

QtyPrev2Years =
CALCULATE (
    SUM ( FactInternetSales[OrderQuantity] ),
    PARALLELPERIOD ( DimDate[FullDateAlternateKey], -2, YEAR )
)

Jako pierwszy parametr funkcja przyjmuje oczywiście kolumnę z kalendarza z pełną datą, następnie wskazujemy o ile okresów chcemy się cofnąć, na samym końcu wskazujemy czy poruszamy się po dniach, miesiącach czy może np. po latach. Jak możecie zauważyć na poniższym zrzucie ekranowym rezultat jest zgodny z oczekiwanym:

Jeżeli nie wiecie jak się zabrać do tworzenia miary opartej o czas, a nie wiecie gdzie zacząć to polecam mechanizm Quick Measures (pisałem o nim jakiś czas temu tutaj), który stworzy podstawowy kod dla nas, który następnie możemy dostosować.

Lista może nie jest wyczerpująca, ale z pewnością może być dla nas w wielu przypadkach punktem startowym.

Oczywiście lista miar jest dużo bardziej obszerna i możecie znaleźć niemal wszystko co was interesuje bez zbędnego kombinowania. Niniejszy artykuł miał za zadanie przedstawić podstawowe pojęcia związane z datą i czasem w modelu tabelarycznym i mam nadzieję, że spełnił swoje zadanie. Dla chcących dogłębnie zapoznać się z listą dostępnych funkcji zapraszam do dokumentacji oraz do tego artykułu.