Wirtualne relacje w DAX i funkcja TREATAS

W ostatnim czasie zostało zadane mi dosyć ciekawe pytanie (pozdrawiam kolegów 🙂 ) o relacje wirtualne w DAX i jak sobie z nimi radzić – dosyć ciekawe i niestandardowe pytanie prawda? Troszeczkę mnie zaskoczyło ze względu na fakt, iż ostatnimi czasy nieco bardziej “siedzę” w kostkach wielowymiarowych i silniku bazodanowym niż w modelach tabelarycznych, jednakże zawsze warto nauczyć się czegoś nowego! Szczególnie, że temat dotyczy dosyć nowej składowej języka DAX jakim jest słowo kluczowe TREATAS, którego wcześniej nie znałem i nie używałem – postanowiłem zatem bardziej zgłębić tę wiedzę i podzielić się nią w ramach tego wpisu – zapraszam do lektury.

Czym więc są relacje wirtualne? Są to relacje, które same w sobie nie są zdefiniowane w modelu tj. np. sytuacja gdy pomiędzy tabelami nie ma żadnych powiązań. W gruncie rzeczy same relacje powodują to, że automatycznie propagowany jest kontekst filtra lub możemy pobrać powiązane dane używając funkcji RELATED (gdy pobieramy ze strony jeden) lub RELATEDTABLE (gdy pobieramy ze strony wiele) – bez fizycznych relacji nie będzie żadnej automatycznej propagacji ani te funkcję nie będą działać. Czy zatem możemy w jakiś sposób pobrać dane z tabeli bez połączenia między tabelami? Oczywiście, że tak! Po pierwsze możemy użyć funkcji LOOKUPVALUE działającej bardzo podobnie jak excelowy VLOOKUP. Za przykład posłużą nam dwie tabele z AdventureWorksDW tj. Product i InternetSales, które nie będą w naszym modelu połączone żadną relacją:

Jak więc teraz pobrać mapujące się wartości z Product do InternetSales? Użyć wspomnianego LOOKUPVALUE – pobierzmy zatem nazwę produktu do kolumny wyliczanej w tabeli faktów używając następującego kodu DAX:

LookupProductName =
LOOKUPVALUE (
    'Product'[EnglishProductName],
    'Product'[ProductKey], InternetSales[ProductKey]
)

Jak można zauważyć na poniższym zrzucie – mimo braku relacji wartość została pobrana prawidłowo:

Podejście to ma zarówno zalety jak i wady – zaletą jest to, że kolumna ta będzie materializowana w momencie procesowania, a co za tym idzie nie będzie musiała być “pobierana” w momencie jej wywołania w zapytaniu. Wadą z kolei jest fakt, że duplikujemy wartości i cały nasz model będzie potrzebował więcej pamięci – ale tak jak ze wszystkim “coś za coś”. Co natomiast gdy chcemy coś obliczać dynamicznie uwzględniając filtry z tabel, które nie są połączone żadną relacją? Oczywiście możemy nadpisać kontekst używając CALCULATE, a jako parametr filtrujący podać listę wartości kolumny, której kontekst nadpisujemy. Aby osiągnąć coś takiego przydałaby się nam konstrukcja podobna do SQLowego IN – mamy coś takiego w DAX – jest to mianowicie funkcja CONTAINS:

VirtualMeasure =
CALCULATE (
    SUM ( [SalesAmount] ),
    FILTER (
        ALL ( 'InternetSales'[ProductKey] ),
        CONTAINS (
            VALUES ( 'Product'[ProductKey] ),
            'Product'[ProductKey], 'InternetSales'[ProductKey]
        )
    )
)

Jak to działa? Zwracamy listę ProductKey z tabeli InternetSales, która zawiera wartości równe wartościom ProductKey z tabeli Product. Ze względu na to, że jako warunek CONTAINS podaliśmy VALUES(Product[ProductKey]) to kontekst filtru będzie propagowany do naszego wyboru i zwracane będą tylko te wartości, które zostały przefiltrowane w bieżącym kontekście – rezultat można zobaczyć poniżej:

Tak więc wartości zostały wyliczone prawidłowo dla każdego ProductKey – podobny efekt można uzyskać korzystając z podejścia wykorzystującego funkcję INTERSECT:

VirtualMeasure2 =
CALCULATE (
    SUM ( [SalesAmount] ),
    INTERSECT (
        ALL ( 'InternetSales'[ProductKey] ),
        VALUES ( 'Product'[ProductKey] )
    )
)

Czyli analogicznie jako wartości filtrujące do CALCULATE podajemy część wspólną wartości ProductKey z tabeli InternetSales oraz z tabeli ProductKey – rezultat wygląda dokładnie tak samo:

Właśnie podczas wspomnianej rozmowy dowiedziałem się, że istnieje jeszcze trzecie podejście, które pojawiło się dosyć niedawno bo w styczniu tego roku w jednej z aktualizacji Power BI – chodzi mianowicie o TREATAS:

VirtualMeasure3 =
CALCULATE (
    SUM ( [SalesAmount] ),
    TREATAS ( VALUES ( 'Product'[ProductKey] ), InternetSales[ProductKey] )
)

Pierwszy argument funkcji TREATAS to kolumna z której chcemy odczytać kontekst filtru, druga z kolei jest to kolumna gdzie chcemy go zastosować – rezultat bardzo podobny do dwóch wcześniejszych podejść:

Ponadto możliwe jest osiągnięcie pożądanego rezultatu używając wielu kolumn złączenia – wystarczy zmodyfikować powyższy zapis tak aby używał SUMMARIZE. Aby to zobrazować stwórzmy sobie tabelę wyliczaną na podstawie InternetSales:

TestTable =
CROSSJOIN (
    DISTINCT ( 'InternetSales'[SalesOrderNumber] ),
    DISTINCT ( 'InternetSales'[SalesOrderLineNumber] ),
    DISTINCT ( 'InternetSales'[RevisionNumber] )
)

Pomijając uzasadnienie istnienia powyższej tabeli będziemy jej używać do filtracji tabeli faktów FactInternetSales – aby móc to osiągnąć użyjmy wspomnianego zapisu z SUMMARIZE, który zgrupuje nam atrybuty tabeli:

VirtualMeasure4 =
CALCULATE (
    SUM ( InternetSales[SalesAmount] ),
    TREATAS (
        SUMMARIZE (
            TestTable,
            'TestTable'[SalesOrderNumber],
            TestTable[SalesOrderLineNumber]
        ),
        InternetSales[SalesOrderNumber],
        InternetSales[SalesOrderLineNumber]
    )
)

W rezultacie rekordy z testowej tabeli w prawidłowy sposób przefiltrowały naszą miarę:

Dosyć ciekawa i pomocna składnia – daje mi to do myślenia, że muszę co miesiąc zarezerwować trochę czasu na przeglądanie nowości w Power BI bo jak widać pojawiają się dosyć ciekawe funkcjonalności. Czy jest między nimi jakaś różnica? Z tego co udało mi się dowiedzieć to powinniśmy używać tych funkcji TREATAS, INTERSECT, CONTAINS w takiej kolejności – jednakże moim zdaniem raczej rzadko powinna pojawiać się potrzeba ich użycia – mimo wszystko powinniśmy dążyć do struktury gwieździstej z ustalonym granulatem i fizycznymi relacjami, które niejako automatycznie dają nam pożądane rezultaty.