Power BI – Aggregations

Do tej pory jeśli ktoś mówił o agregacjach w kontekście narzędzi Business Intelligence spod znaku Microsoft to zapewne miał na myśli prekalkulowane wartości w kostkach wielowymiarowych w Analysis Services Mutlidimensional lub specjalne tabele w hurtowni wypełniane agregatami podczas ETL, ewentualnie widoki zmaterializowane pełniące dokładnie tą samą rolę. Od niedawna pojęcie to wiąże się również z Power BI gdzie takowa funkcjonalność została wprowadzona. Agregacje są kolejną funkcjonalnością po odświeżaniu Inkrementalnym i Composite Models, która została dodana wyłącznie dla użytkowników Premium i na ten moment nie jest dostępna nigdzie indziej i ciężko oczekiwać, że będzie inaczej. Jak działa owa funkcjonalność i w jakich scenariuszach może ona się sprawdzić? Zapraszam do lektury niniejszego artykułu, który ma za zadanie wprowadzić w funkcjonalność i dać pogląd dla czytelników gdzie można jej użyć we własnych projektach.

Agregacje w swoim zamierzeniu mają się sprawdzać wtedy gdy nie chcemy do naszego modelu w Power BI pobierać całej wielomilionowej tabeli, a jej agregacja do pożądanych wartości w trybie Direct Query jest nieefektywna. Ogólnie rzecz biorąc agregacje znajdują zastosowanie w modelach będących w trybie złożonym tj. Composite Models (pisałem o tym niedawno, artykuł znajdziecie tutaj) i w rzeczywistości jest rozszerzeniem tej właśnie funkcjonalności. W ramach niniejszego artykułu zbudujemy przykładowy model i zobaczmy jak całość działa w praktyce.

Do zbudowanie przykładowej struktury gwiazdy wybrałem klasyczny zestaw danych i pobrałem z AdventureWorksDW tabelę FactInternetSales wraz ze wszystkimi połączonymi z nią wymiarami:

Każda z tabel została podłączona do modelu w trybie Direct Query tj. każde działanie na raporcie wyśle zapytanie do mojej źródłowej instancji SQL Server. Kolejnym krokiem w implementacji agregacji jest zbudowanie tabeli agregacji czyli tabeli na innym poziomie szczegółowości niż tabela FactInternetSales. Ze względu na fakt, iż tak jak wspomniałem tabela agregacji ma przyspieszyć działanie aplikacji bez konieczności odpytywanie tabeli źródłowej za każdym razem gdy użytkownik wykona określone działanie, to nasza tabela musi być podłączona do modelu w trybie Import. Do zbudowania tejże tabeli posłużyło mi następujące zapytanie SQL:

SELECT
  ProductKey
  ,OrderDateKey
  ,CurrencyKey
  ,SUM(OrderQuantity) AS OrderQuantity
FROM
  dbo.FactInternetSales AS FactInternetSales_agg
GROUP BY
  ProductKey
  ,OrderDateKey
  ,CurrencyKey

 

Patrząc na powyższe zapytanie SQL widać, że nasza tabela będzie miała ustalony granulat na poziomie ProductKey, OrderDateKey oraz CurrencyKey. Po połączeniu relacjami tabelą agregującą z wymiarami te drugie zostaną przełączone w tryb działania Dual czyli w zależności od scenariusza raz będą zachowywały się jak tabele DirectQuery, a raz jakby były zaimportowane. Samą tabelę agregacji ukryłem bo jest ona niejako tabelą techniczną i nie ma sensu uwidaczniać ją dla użytkowników skoro i tak nie powinni jej używać bezpośrednio:

Po zbudowaniu modelu testowego możemy przejść na widok raportu i ustawić zachowanie agregacji. Ze względu na fakt, iż na moment pisania tego artykułu agregacje są w fazie “Preview” aby móc ją wypróbować to najpierw musimy ją włączyć w opcjach narzędzia:

Kiedy już funkcjonalność jest włączona, a samo Power BI Desktop zrestartowane musimy kliknąć prawym przyciskiem myszy na naszą tabelę agregującą i z menu kontekstowego wybrać opcję Manage Aggregations (tabela jest oczywiście ukryta więc należy kliknąć prawym przyciskiem myszy na dowolną inną tabelę i z menu kontekstowego wybrać View hidden):

W oknie zarządzania agregacjami musimy stworzyć swoistego rodzaju mapowanie pomiędzy tabelą agregującą, a tabelą szczegółową, aby narzędzie znało kontekst relacji pomiędzy tabelami:

Mapowanie oznacza iż, dla każdej kolumny z tabeli agregowanej przypisujemy opcję SUMMARIZATION czyli czy kolumna powinna służyć do grupowania czy też ma być agregowana. Do dyspozycji mamy kilka podstawowych funkcji agregacji takich jak SUM, MIN, MAX czy też COUNT. W tym miejscu warto wspomnieć, że jeśli chcemy agregować daną kolumnę kilkukrotnie używając różnych funkcji agregacji to powinniśmy w tabeli agregującej dostarczyć tyle jej kopii ile funkcji chcemy użyć np. OrderQuantity_SUM, OrderQuantity_MAX itp itd. Dalej w DETAIL TABLE wybieramy, która z tabel jest agregowana przez naszą tabelą agregującą i po prostu mapujemy odpowiadające kolumny.
O właściwości Precedence powiemy sobie w dalszej części artykułu na ten moment chciałbym tylko wspomnieć, że służy ona do wskazywania priorytetu użycia tabeli agregującej w przypadku gdy mamy ich w modelu wiele. Po zakończeniu naszej pracy w otwartym oknie wystarczy kliknąć Apply all aby tabela agregacji zaczęła działać.

PRzy definiowaniu mapowania mamy kilka wymogów, o których powinniśmy pamiętać. Wyglądają one następująco.:

• zmapowane kolumny powinny mieć ten sam typ danych (wyjątkiem są funkcje agregacji COUNTROWS i COUNT),
• niemożliwe jest stworzenie kilku tabel agregacji polegających na sobie tzn. tabela agregacji nie może być agregacją dla innej tabeli agregacji. Możliwe jest natomiast posiadanie kilku agregatów dla tej samej tabeli o innym poziomie szczegółowości,
• w momencie posiadania kilku tabel agregacji nie możliwe jest stworzenie mapowania na tym samym poziomie szczegółowości.

Aby przekonać się jak cały mechanizm działa w praktyce można wywołać określone zapytanie w DAX oparte o załadowaną przez nas tabelę faktów. Ja ten eksperyment wykonałem poprzez przeciągnięcie atrybutów DimPromotion[PromotionKey] oraz FactInternetSales[OrderQuantity] na planszę raportu do zwykłej tabeli:

Patrząc na powyższy rezultat może pojawić się pytanie skąd mamy wiedzieć czy użyta została tabela agregacji czy tabela szczegółowa w Direct Query? Ze względu na fakt, iż atrybut PromotionKey nie jest częścią tabeli agregującej to z pewnością użyta została tabel źródłowa, a możemy zdobyć potwierdzenie tego faktu używając np. Extended Events (możemy użyć wbudowanego szablonu TSQL):

W porządku, teraz spróbujmy doprowadzić do użycia tabeli agregacji. Na wstępie zastanówmy się gdzie znajdziemy informację, że została ona użyta. Microsoft dodał wraz z agregacjami wydarzenie do Profilera, które pozwoli nam to wyłapać, a znajduje się ono w grupie Query Processing pod nazwą Aggregate Table Rewrite Query. Jak podłączyć Profilera pod Power BI Desktop? Mamy kilka sposobów! Adres serwera to oczywiście nasz lokalny komputer na którym pracujemy czyli localhost, nieco problematyczne jest odnalezienie określonego portu na którym działa model wewnątrz Power BI. Najłatwiej zdobyć tą informację korzystając z narzędzia DAX Studio (dostępne za darmo tutaj) gdzie po połączeniu do otwartego Power BI Desktop otrzymamy informację o porcie na którym on działa:

Jeśli nie możemy lub nie chcemy nic instalować to w cmd należy wywołać następującą komendę:

TASKLIST /FI "imagename eq msmdsrv.exe" /FI "sessionname eq console"

Interesuje nas PID czyli Identyfikator procesu usługi, który będzie argumentem naszej kolejnej komendy:

netstat /ano|findstr "7444" |findstr "LISTENING"

No powyższym zrzucie widzimy, że 50156 to nasz poszukiwany numer portu. Teraz możemy uruchomić Profilera i wpisać po prostu localhost:numer portu i będzie on podłączony pod naszą lokalną instancję Power BI Desktop. Tam możemy wybrać wspomniane wyżej wydarzanie tj. Query Processing: Aggregate Table Rewrite Query

Po tych działaniach możemy już przerzucić do tabeli DimProduct[ProductKey] oraz OrderQuantity aby wygenerować zapytanie w DAX, które przynajmniej teoretycznie powinno pobrać dane z tabeli agregującej. W rezultacie Profiler zarejestrował wspomniane wydarzenie:

Wynik jest przedstawiony w JSONie i można go zinterpretować w taki sposób, że silnik znalazł odpowiednie mapowanie i po prostu użył tabeli agregacji. Oczywiście nie jest tak, że musimy idealnie trafić w granulat tabeli agregującej. W przypadku gdy nasze zapytanie będzie miało taką formę, że będzie się dało wyprowadzić wynik z tabeli agregującej tak też się stanie. Dla przykładu w momencie gdy policzyłem zwykłą sumę na polu Quantity Profiler wyłapał następujące JSON:

{
  "table": "FactInternetSales",
  "mapping": {
    "table": "FactInternetSales_agg"
  },
  "matchingResult": "matchFound",
  "dataRequest": [
    {
      "aggregation": "sum",
      "table": "FactInternetSales",
      "column": "OrderQuantity",
      "mapping": {
        "column": "OrderQuantity"
      }
    }
  ]
}

Może zdarzyć się również tak, że Power BI sprawdzi czy może użyć agregacji i gdy po sprawdzeniu mapowania odrzuci tą możliwość (np. w sytuacji gdy zapytanie odwołuje się do pól niebędących częścią tabeli agregatów) to w ramach śledzonego wydarzenia otrzymamy komunikat “attempt failed“:

{
  "table": "FactInternetSales",
  "mapping": null,
  "matchingResult": "attemptedFailed",
  "dataRequest": [
    {
      "aggregation": "sum",
      "table": "FactInternetSales",
      "column": "OrderQuantity"
    },
    {
      "table": "DimCustomer",
      "column": "EnglishEducation"
    }
  ]
}

Przechodząc dalej spróbujmy teraz innego scenariusza. Pobierzmy do modelu jeszcze jedną tabelę agregatów ale na nieco innym poziomie agregacji (tak jak na poniższych zrzutach ekranowych):

Po jej zaimportowaniu mamy taką sytuację, że obie tabele agregacji dają nam możliwość wyliczenia sumy OrderQuantity po ProductKey, która zatem zostanie użyta? Do tego właśnie służy wspomniana wcześniej właściwość Precedence w oknie definiowania agregacji. Jako pierwsza do użycia będzie rozważana ta tabela, która posiada wyższą wartość w Precedence. Dzięki temu nie musimy tworzyć łańcucha powiązań pomiędzy tabelami agregacji tylko ustawić odpowiednią wartość we właściwości i o nic więcej się nie martwić.

Mamy już prawie komplet informacji jeśli chodzi o agregacje w Power BI. W celu uzupełnienia ostatnią rzeczą jaką chciałbym poruszyć w ramach niniejszego artykułu są agregacje na tabelach mocno zdenormalizowanych, które nie posiadają relacji. Rozważmy taki scenariusz jaki został przedstawiony na poniższym zrzucie ekranowym:

Dwie tabele, jedna szczegółowa a druga to jej agregacja. Czy możemy w takim przypadku zdefiniować agregacje na FactResellerSales używając FactResellerSales_agg i czy będzie to działać zgodnie z oczekiwaniami?
Oczywiście, że tak – struktury szerokich tabel płaskich zdarzają się stosunkowo często i dodatkowe “wymiary” nie są potrzebne. Przy zdefiniowaniu relacji każde zapytanie na tabeli szczegółowej, które może być spełnione przez tabelę agregującą zostanie wykonane właśnie na niej w analogiczny sposób jak to miało miejsce w przypadku standardowych tabel z relacjami pomiędzy nimi.

Mamy zatem kilka scenariuszy użycia funkcjonalności agregacji w Power BI:

• nasza tabela źródłowa jest zbyt duża aby umieścić ją w pamięci operacyjnej, a my nie chcemy odcinać użytkowników od najbardziej szczegółowych danych,
• większość analiz modelu odbywa się na zagregowanym poziomie jednakże dostęp do danych źródłowych ma również być zapewniony,
• Direct Query nie działa dostatecznie wydajnie dla posiadanego zbioru na poziomie zagregowanym.

Powoli kończąc chciałbym również wspomnieć, że tabela agregacji ze względu na fakt, iż jest zaimportowana powinna być odświeżana według określonego harmonogramu tak aby dane szczegółowe pochodzące z trybu Direct Query odpowiadały danym z harmonogramu. Wszystko zależy oczywiście od konkretnego przypadku użycia, jednakże wyobrażam sobie że tabela agregacji nie musi być wynikiem określonego zapytania wysyłanego przez Power BI, ale może być podłączeniem do tabeli agregacji/widoku zmaterializowanego wyliczonego już na poziomie ETL. Tak jak wspomniałem wszystko zależy i musi być odpowiednio dopasowane do naszych potrzeb. W wielu przypadkach trzeba bardzo dobrze przemyśleć jak wykorzystać opisywaną technikę, jednakże warto pamiętać o tym, że wcześniej takiej możliwości nie mieliśmy w ogóle i trzeba było naprawdę się napracować aby osiągnąć to co teraz dostajemy “z pudełka”.

Podsumowując muszę przyznać, że bardzo ciekawa koncepcja rozszerzająca po raz kolejny możliwości implementacji Power BI. Jest to kolejny krok w kierunku tego aby nasze ulubione narzędzie raportowe było używane bezpośrednio na ogromnych zbiorach danych narzędzi Big Data, co patrząc na pojawiające się na rynku trendy jest dobrym posunięciem. Jedyne na co pozostaje nam czekać to rozszerzenie funkcjonalności Composite Models o wsparcie dla modeli tabelarycznych w Live Connection co będzie świetnym dopełnieniem tych wszystkich nowości, które pojawiły się w ostatnim czasie.