Hierarchie Parent-Child w DAX

Hierarchie parent-child są bardzo popularne w modelowaniu procesów biznesowych, ponieważ w bardzo łatwy sposób potrafią przedstawić wielopoziomową strukturę o znanej lub nieznanej liczbie poziomów. Skoro jest ona tak popularna to narzędzia analityczne powinny ją wspierać i tak też jest w przypadku Analysis Services. W tradycyjnym trybie multidimensional (czyli kostkach wielowymiarowych) mamy bardzo szerokie wsparcie tychże hierarchii, które razem z operatorami unarnymi i custom formulas dawały nam bardzo duże możliwości modelowania nawet skomplikowanej logiki biznesowej. W przypadku modelu tabelarycznego i DAX nie mamy aż takich możliwości przez to, że model ten ma tylko częściowe wsparcie dlatego typu hierarchii. Dziś chciałbym napisać parę słów na ten temat i przedstawić dostępne dla nas opcje. Zapraszam serdecznie do lektury.

Nie owijając w bawełnę przejdźmy od razu do przykładu. Nasz zbiór testowy pochodzi z bazy AdventureWorksDW2014, a konkretnie z tabeli DimEmployee:

USE AdventureWorksDW2014
GO

SELECT 
	EmployeeKey,
	ParentEmployeeKey,
	LastName+' '+FirstName AS EmployeeNAme 
FROM dbo.DimEmployee

Według struktury przedstawionej na powyższym zrzucie ekranowym, pracownicy o identyfikatorach 4,5 oraz 11 są podwładnymi pracownika o identyfikatorze 3, który z kolei jest podwładnym pracownika o identyfikatorze 14 i tak dalej. Odwzorowanie czegoś takiego
w DAX jest możliwe, ale tak jak już wspomniałem tylko częściowo. Załadujmy te dane do modelu i zobaczmy co możemy osiągnąć z tego typu strukturą.

Po zaciągnięciu danych do modelu (w dowolnej jego implementacji tj. Power BI, SSAS Tabular lub Power Pivot będzie to wyglądało podobnie) możemy użyć kilku funkcji języka DAX. Pierwszą z nich, jaką chciałbym Wam przedstawić będzie funkcja PATH, która pozwoli nam przejrzeć całą ścieżkę hierarchii. W naszym przypadku zastosowanie funkcji PATH wygląda następująco:

Path = PATH(Employees[EmployeeKey],Employees[ParentEmployeeKey])

Jako pierwszy argument tejże funkcji podajemy identyfikator tabeli, na drugim miejscu podajemy klucz parent (czyli klucz przełożonego). Rezultat prezentuje się następująco:

Dla przykładu biorąc wiersz o identyfikatorze 1, jego przełożonymi w hierarchii organizacyjnej są kolejno 18, którego przełożonym jest 23 itd. Warto pamiętać, że do tej funkcji możemy przekazać kolumny typu TEXT lub INTEGER. Ponadto nie może wystąpić taka sytuacja, że w kolumnie “parent” będzie odwołanie do wartości której nie ma w kolumnie podanej jako pierwszy argument. Ciekawie wygląda również sprawa z duplikatami. Jeśli występuje taka sytuacja, że pracownik o tym samym ID występuje dwukrotnie i jednocześnie odwołuje się do dwóch innych rodziców to zostanie zwrócony błąd. Sytuacja ta nie stanowi problemu, gdy duplikat odwołuje się do tego samego menedżera.

Teraz gdy już wiemy jak ta funkcja działa to co możemy zrobić z takim zestawem? Przede wszystkim możemy odwołać się do każdego elementu tego ciągu i poznać długość całej ścieżki. Aby dokonać tego drugiego należy użyć funkcji PATHLENGTH, która w parametrze przyjmie właśnie kolumnę ze ścieżką (lub bezpośrednie wywołanie funkcji PATH jednakże dla przejrzystości pozostawię obie funkcje w osobnych kolumnach):

PathLength = PATHLENGTH(Employees[Path])

To co otrzymaliśmy jest już całkiem przydatną informacją prawda? Spróbujmy teraz odwołać się do konkretnych “poziomów” hierarchii. Do tego typu działań służy funkcja PATHITEM, która jako pierwszy parametr przyjmuje ścieżkę, następnie musimy podać poziom który nas interesuje. Numeracja elementów rozpoczyna się od 1, który w tym wypadku oznacza najwyższy poziom. Istnieje jeszcze trzeci opcjonalny parametr, który definiuje czy zwrócona wartość ma być typu liczbowego (INTEGER), czy też tekstowego (TEXT). Możemy również niejako “odwrócić” logikę w taki sposób, że poziom 1 będzie zwracał najniższy poziom hierarchii zamiast najwyższego. Do tego celu posłuży nam funkcja PATHITEMREVERSE, której składnia wygląda dokładnie tak samo jak w przypadku PATHITEM.

Przechodząc dalej zwróćmy najwyższego przełożonego w ścieżce używając odwołania do elementu 1. W tym przypadku sprawa jest naprawdę prosta, ponieważ każdy ma przełożonego na poziomie 1 (CEO ma tam wpisanego samego siebie):

EmployeeLevel1ID = PATHITEM(Employees[Path],1,1)

Oczywiście dla każdego pracownika przełożonym najwyższego szczebla jest pracownik o ID 112. Mając tą informację możemy już użyć funkcji VLOOKUP aby poznać jego personalia:

LOOKUPVALUE(Employees[EmployeeName],Employees[EmployeeKey],Employees[EmployeeLevel1ID])

To samo musimy zrobić dla wszystkich poziomów – w przedstawionym przeze mnie przypadku jest ich 5. Efekt końcowy powinien wyglądać następująco:

Aby zaoszczędzić nieco miejsca (i pamięci) nie tworzyłem kolumny z ID dla każdego poziomu, a funkcję PATHITEM umieściłem wewnątrz LOOKUPVALUE. Gdy już mamy wszystkie poziomy hierarchii to może pojawić się dosyć zasadne pytania, a mianowicie co w przypadku gdy mamy bardzo dużo elementów takiej hierarchii? Co jeżeli chcielibyśmy aby w naszych wizualizacjach była możliwość poruszania się po hierarchii dynamicznie w zależności od ilości poziomów tak jak ma to miejsce w przypadku kostki analitycznej? Odpowiedź niestety nie jest zadowalająca, ponieważ takiej możliwości nie ma i zawsze musimy obsługiwać hierarchię w sposób przedstawiony powyżej.

Co w takim razie jeszcze możemy zrobić z hierarchiami Parent Child w modelu tabelarycznym? Przede wszystkim możemy sprawdzić czy dany identyfikator znajduje się ścieżce hierarchii, a służy do tego funkcja PATHCONTAINS:

ContainsEmployee5 = PATHCONTAINS([Path],5)

Zwraca ona TRUE jeśli, któryś z elementów ścieżki to 5:

Mimo, że funkcja ta może się wydawać mało użyteczna, to dzięki niej możemy osiągnąć naprawdę ciekawe rezultaty. Jednym z przykładów, które mógłbym w tym miejscu przytoczyć jest możliwość stworzenia miary, która sprawdzi ilu ludzi (pośrednio oraz bezpośrednio) podlega pod danego pracownika. Miara ta wygląda następująco:

NumberOfSubordinates =
VAR vCurrentEmployee =
    MAX ( Employees[EmployeeKey] )
RETURN
    IF (
        COUNTROWS ( Employees ) = 1,
        CALCULATE (
            COUNTROWS (
                FILTER ( ALL ( Employees ), PATHCONTAINS ( Employees[Path], vCurrentEmployee ) )
            )
        )-1,
        BLANK ()
    )

Dla przejrzystości stworzyłem miarę, która pokaże na jakim poziomie hierarchii się znajdujemy:

PathLengthMeasure =
IF (
    COUNTROWS ( Employees ) = 1,
    CALCULATE ( SUM ( Employees[PathLength] ) ),
    BLANK ()
)

W efekcie możemy analizować ilu pracowników pod sobą ma dany pracownik. Poniżej przedstawiona została przykładowa tabela wynikowa z wykorzystaniem wyżej stworzonych miar:

Oczywiście możliwości w tym wypadku są o wiele większe i możemy zrobić osiągnąć niemal wszystko. Jeśli źródłem naszego modelu jest baza danych to zawsze podobne efekty możemy osiągnąć w TSQL  używając np. takiego zapytania:

WITH ParentChild(EmployeeKey, EmployeeName, ParentId, Path)
AS
(
    SELECT EmployeeKey, LastName +' '+FirstName, ParentEmployeeKey, CAST('' AS VARCHAR(MAX))
        FROM DimEmployee AS FirtGeneration
        WHERE ParentEmployeeKey IS NULL    
    UNION ALL
    SELECT NextGeneration.EmployeeKey, 
           NextGeneration.LastName+' '+NextGeneration.FirstName, 
           Parent.EmployeeKey,
    CAST(CASE WHEN Parent.Path = ''
        THEN(CAST(NextGeneration.ParentEmployeeKey AS VARCHAR(MAX)))
        ELSE(Parent.Path + '|' + CAST(NextGeneration.ParentEmployeeKey AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM DimEmployee AS NextGeneration
        INNER JOIN ParentChild AS Parent ON NextGeneration.ParentEmployeeKey = Parent.EmployeeKey    
)
SELECT *,IIF(ParentID IS NULL,0,LEN(Path)-LEN(REPLACE(Path,'|',''))+1) As HierarchyLevel
    FROM ParentChild
ORDER BY HierarchyLevel
OPTION(MAXRECURSION 32767)

W tym miejscu warto zaznaczyć, że łatwiej i zazwyczaj wydajniej będzie odwoływać się do poszczególnych elementów hierarchii w DAX i to podejście polecam. Jeśli jednak z jakiegoś powodu bardzo chcemy, to również tą operację możemy wykonać w TSQL jednak nie jest to ani proste, ani tym bardziej wydajne.

Warto zaznajomić się z tą funkcjonalnością szczególnie, że hierarchie parent child są standardem i ich występowanie jest powszechne. Póki co nie mamy możliwości dynamicznego zwijania hierarchii tego typu tak jak w kostkach analitycznych, ale być może w przyszłości coś takiego będzie dla nas dostępne.