SQL Server 2016 – Tabular bidirectional filtering

Usługi analityczne na przestrzeni ostatnich lat przybrały całkiem nową formę w postaci modelu tabelarycznego. Każda kolejna wersja SQL Server przynosiła mniejsze bądź większe zmiany zarówno w języku DAX jak i w samych strukturach wspomnianego modelu – nie inaczej jest z SQL Server 2016 – w poprzednich wpisach opisałem translacje i tabular model explorer oraz zmienne, komentowanie miar oraz tabele kalkulowane jednakże to nie wszystko – dziś opiszemy kolejną nowść a mianowicie nowy typ relacji i filtracji tzw. bidirectional.

Aby zobrazować funkcjonalność musimy stworzyć sobie nowy projekt tabelaryczny w standardowy sposób. Następnie musimy pobrać dane, które posłużą nam w demonstracji – standardowo użyjemy bazy AdventureWorksDW i z niej wybierzemy trzy tabele tj. FactInternetSales, DimProduct oraz DimCustomer – po zaimportowaniu nasz model powinien wyglądać w następujący sposób:

Jak możecie zauważyć pomiędzy tabelami istnieją relacje jeden do wielu. Relacje w modelu tabelarycznym determinują kierunek filtrowania danych tj. dane zawsze filtrowane są od strony jeden do strony wiele – na powyższym diagramie można to utożsamić z tym, że tabela produktów może filtrować wyliczenia w tabeli sprzedaży internetowej jednak analogiczna operacja w drugą stronę nie jest możliwa tj. sprzedaż internetowa nie może filtrować produktów itd. Sytuacja jest identyczna jeśli weźmiemy pod uwagę tabelę DimCustomer. Czym to skutkuje? Prześledźmy to na przykładzie – stwórzmy sobie prostą miarę w tabeli DimCustomer, która zliczy unikalną liczbę klientów:

Distinct Count of CustomerKey:=DISTINCTCOUNT([CustomerKey])

Następnie przetestujmy jej działanie w Excel używając opcji Analyze in Excel dostępnej na pasku:

Opcja ta sprawi, że utworzone połączenie do naszego modelu i będziemy mogli na stworzyć raport w postaci tabeli przestawnej. Do wierszy wrzućmy atrybut Class, a do wartości stworzoną przez nas miarę:

Otrzymaliśmy rezultat jak na zrzucie tj. 18484 dla każdej wartości atrybutu Class – dlaczego tak się dzieje?

Ponieważ filtr z tabeli produkt sięga jedynie do tabeli FactInternetSales i dalej nie jest już propagowany – dlatego też liczba unikalnych wartości nie jest przez naszą tabelę przestawną w ogóle filtrowana. Aby zmienić to zachowanie musimy użyć opisywanego w ramach niniejszego artykułu rodzaju relacji, dlatego też kliknijmy dwukrotnie relacje pomiędzy DimCustomer i FactInternetSales aby wyświetlić edytor tejże relacji:

Jedyną rzeczą jaką musimy zmienić jest opcja Filter Direction ustawione na To Both Tables. Kierunek relacji można rozpoznać w widoku diagramu poprzez odpowiedni znak graficzny:

Spójrzmy zatem jak wygląda nasz raport w Excelu:

Dane uzgodniły się w pożądany przez nas sposób – w tym momencie tabela przestawna przedstawia liczbę klientów którzy kupili określone klasy produktów. Stworzony przez nas raport mógł się uzgodnić dzięki odpowiedniej propagacji filtrów. We wcześniejszych wersjach modelu tabelarycznego nie było możliwe osiągnięcie podobnych rezultatów bez tworzenia odpowiednich zapisów w języku DAX. Oprócz tego typu scenariuszy warto pamiętać o bidirectional filtering przy scenariuszach wiele-do-wielu (many to many) np. poniższy diagram pokazuje sprzedaż internetową wraz z powodami dla którego klienci dokonali zakupu przy czym klient mógł podać więcej niż jeden powód – dlatego też mamy do czynienia z typowym scenariuszem wiele do wielu:

Gdy chcemy przejrzeć sprzedaż po powodach zakupu otrzymamy podobny rezultat jak w poprzednim przypadku tzn. w żaden sposób nie przefiltrowaną sumę sprzedaży:

Tym razem zmienimy typ relacji na bi-directional pomiędzy każdą z tabel:

Po sprawdzeniu raportu widzimy, że bez dodatkowych działań pokazuje on poprawne rezultaty:

Oczywiście w niektórych przypadkach tego typu podejście przy relacjach many-to-many będzie mniej wydajne niż zapis w DAX jednak moim zdaniem jest to świetna opcja dla bardzo wielu modeli tabelarycznych zawartych w Analysis Services Tabular oraz Power BI. Jedynym pytaniem jakie pojawia się na horyzoncie to dlaczego tej funkcjonalności brak w Power Pivot for Excel 2016? Ale prawdopodobnie na to pytanie odpowiedni nie poznamy. Mam nadzieje, że udało mi się przybliżyć Wam funkcjonalność dwukierunkowej filtracji – jak dla mnie to jedna z najbardziej przydatnych funkcjonalności jakie przynosi ze sobą SQL Server 016 dla usług analitycznych.