Power BI -Merge i Append queries czyli łączenie i unia zbiorów

Kontynuując naszą serię związaną z Power BI dziś powiemy sobie kilka słów na temat łączenia i unii zbiorów, które są jednymi z najpopularniejszych transformacji jakich dokonuje się na danych – zapraszam do lektury.

Łączenie danych horyzontalnie (czyli popularny JOIN dostępny w języku SQL) czy też wertykalnie (sqlowy UNION) stało się standardem jeśli chodzi o manipulowanie danymi w relacyjnych bazach danych. Nie inaczej jest z narzędziami do budowy procesów ekstrakcji, transformacji czy ładowania danych (ETL) – każde lepsze czy tez gorsze narzędzia implementują tą funkcjonalność z różnym skutkiem. W przypadku Power Query (zwyczajowo będę tak nazywał dodatek do programu Excel oraz komponent Power BI służący do budowy procesów ETL) tak jak możecie się domyślać funkcjonalność  ta również została wdrożona. Dodatkowo, tak jak prawie wszystko w Power BI, jest to transformacja bardzo prosta w użyciu co postaram się udowodnić w dalszej części niniejszego artykułu. Przed opisaniem mechanizmu złączeń w Power BI chciałbym wyjaśnić kwestię, która może się pojawić w głowach czytelników – chodzi mianowicie o zasadność występowania takiej funkcjonalności. Bardzo często słyszę pytanie “po co tego używać skoro w zapytaniu źródłowym mogę sobie napisać SQLowego JOINa i połączyć określone dane” – odpowiedź wbrew pozorom jest bardzo prosta. Nie zawsze dane, które musimy połączyć pochodzą z jednego systemu źródłowego to po pierwsze – część deweloperów próbuje na siłę użyć SQL i tworzą np. Linked Server aby napisać rozproszone zapytanie TSQL co jest ogromnym ciosem wydajnościowym (same serwery połączone charakteryzują się wieloma niezbyt dobrymi cechami, o których możecie przeczytać tutaj). Po drugie naszym źródłem wcale nie musi być relacyjna baza danych, a np. plik Excel, plik płaski, XML, JSON czy chociażby potok danych, których nie za bardzo da się łączyć poza narzędziem ETL. W tych przypadkach obligatoryjnie powinniśmy łączyć dane właśnie w narzędziu ETL – co natomiast w przypadku gdy naszym źródłem jest pojedyncza relacyjna baza danych? Używać języka SQL czy też transformacji Power Query? Odpowiedź jak wszystko w IT brzmi “to zależy”. Zależy  przede wszystkim od tego czy:

• możemy bazę danych obciążać dodatkowymi transformacjami
• nasze transformacje nie będą zbyt długo blokować źródłową bazę danych przed zapisem ( w zależności od poziomu izolacji jedno długie zapytanie może zablokować kilka tabel)
• źródłowa baza danych potrafi dokonać transformacji w sposób wystarczająco wydajny
• architektura naszego rozwiązania przewiduje przeniesienie logiki transformacji na źródło

Jeśli wszystkie powyższe założenia są spełnione to możemy używać SQL w przeciwnym wypadku jesteśmy skazani na używanie narzędzia ETL. W moim odczuciu najważniejsze jest to aby zdecydować się na jedno podejście i skutecznie go propagować w całym rozwiązaniu. Jeśli chodzi o Power Query to wcale nie jest powiedziane, że jeżeli używamy transformacji wbudowanych w narzędzie to, że będą one wykonane własnie przez narzędzie bez wykorzystania źródła. Mechanizm ten nazywamy Query Folding i z pewnością przyjrzymy mu się w ramach serii “Poznaj Power BI”.

Koniec (nie)zbędnej teorii – przejdźmy do praktyki 🙂 Za źródło posłuży nam AdventureWorksDW2014 i zawarte w niej tabele DimProduct, DimProductSubCategory oraz DimProdutCategory. Jak zapewne wiecie DimProduct jest słownikiem produktów, każdy produkt ma swoją podkategorię i każda kategoria ma swoją kategorię – pobierzmy zatem pożądane dane do modelu zatwierdzając przyciskiem Load.

Po załadowaniu danych należy przejść do okna Power Query i na zakładce Home kliknąć Edit Queries i z menu rozwijanego wybrać opcję o tej samej nazwie.

Jak już wspominałem w jednym z poprzednich artykułów każda tabela jest w ramach procesu ETL rozpoznawana jako osobne zapytanie i każda posiada swój zestaw kroków.

Naszym zadaniem jest dorzucenie do DimProduct atrybutów EnglishProductCategoryName i EnglishProductSubcategoryName znajdujących się w pozostałych tabelach. Aby to zrobić należy zaznaczyć zapytanie do którego chcemy dołączać dane z innych zapytań i na zakładce Home w sekcji Combine wybrać Merge Queries.

Do dyspozycji mamy dwie opcje: Merge Queries czyli dołączanie danych z innych zapytań (tabel) do bieżącej tabeli. Merge Queries as New łączy dane z zapytań tworząc nowe zapytanie. Na ten moment wybierzmy zaznaczmy DimProduct i Merge Queries. Naszym oczom ukaże się okno gdzie możemy skonfigurować połączenie.

Sama konfiguracja jest bardzo prosta i sprowadza się do wyboru tabeli której będziemy używać do połączenia z naszą bieżącą tabelą oraz do zaznaczenia kolumn w obu tabelach po których ma nastąpić złączenie. Ponadto w sekcji Join Kind możemy wybrać rodzaj złączenia – jest ich aż sześć:

• Left Outer – pobiera wszystkie wiersze z pierwszej tabeli i tylko pasujące z drugiej .Odpowiednik zapisu w języku TSQL:

FROM DimProduct 
LEFT OUTER JOIN DimProductSubcategory 
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey

• Right Outer – pobiera wszystkie wiersze z drugiej tabeli i tylko pasujące z pierwszej. Odpowiednik zapisu w języku TSQL:

FROM DimProduct 
RIGHT OUTER JOIN DimProductSubcategory 
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey

• Full Outer – pobiera wszystkie wiersze z obu tabel bez względu na to czy się połączyły czy też nie

FROM DimProduct 
FULL OUTER JOIN DimProductSubcategory 
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey

• Inner – pobiera tylko te wiersze z obu tabel, które się połączyły

FROM DimProduct 
INNER JOIN DimProductSubcategory 
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey

• Left Anti – pobiera tylko wiersze z pierwszej tabeli, które nie mają połączenia z drugą tabelą

FROM DimProduct 
LEFT JOIN DimProductSubcategory 
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey
WHERE DimProductSubcategory.ProductSubcategoryKey IS NULL

• Right Anti – pobiera tylko wiersze z drugiej tabeli, które nie mają połączenia z pierwszą tabelą

FROM DimProduct 
RIGHT JOIN DimProductSubcategory 
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey
WHERE DimProductSubcategory.ProductSubcategoryKey IS NULL

Jak widać mamy więc całkiem spore pole do popisu – póki co wybierzmy opcję LEFT JOIN. Warto również zwrócić uwagę na komunikat w dolnej części ekranu mówiący o tym ile mechanizm Power Query znalazł powiązanych wierszy pomiędzy tqbelami -ponadto w tym miejscu wyświetlony zostanie komunikat  w przypadku błędu złączenia np. o niezgodności typów dlatego też warto go obserwować. Po potwierdzeniu transformacji można zauważyć, że na samym końcu dodana została kolumna o nazwie NewColumn, która po kliknięciu dwóch strzałek nagłówkowych pozwala na pobranie tych kolumn, które nas interesują z dołączanej tabeli – w naszym przypadku wybieramy EnglishProductSubcategoryName oraz ProductCategoryKey potrzebny do dołączenia tabeli DimProductCategory.

W przypadku gdy zaznaczymy opcję Use original column as prefix nasze nowo dodane kolumny otrzymają Prefix od nazwy kolumny tzn. np. EnglishProductSubcategoryName będzie nazywać się NewColumn.EnglishProductSubcategoryName. Jeżeli chcemy użyć tej opcji warto wcześniej zmienić nazwę kolumny na odpowiedniejszą. Analogiczną operację złączenia należy przeprowadzić z tabelą DimProductCategory aby dołączyć nazwę kategorii produktu.

Następnie możemy rozszerzyć DimProduct w taki sposób aby dołączyć kolumnę EnglishProductCategoryName.

Tak więc wiemy już jak dołączać dane do tabeli – jednakże nie jest to jedyny sposób. Jeżeli przyjrzymy się dokładniej naszej tabeli DimProduct to zobaczymy, że jest tam kilka kolumn symbolizujących tabele – Power BI odczytał relacje ze źródła i dał nam możliwość dołączenia tych danych bez konieczności jawnego definiowania łączenia. Wystarczy kliknąć na przycisk rozszerzenia dostępny w nagłówku i wybrać te kolumny które są dla nas istotne i właściwie to wszystko – Power BI wygeneruje odpowiednie transformacje i doładuje do określonej tabeli wybrane kolumny.

To czy takie możliwości rozszerzania się pojawią czy też nie zależy od tego czy przy pobieraniu danych zaznaczyliśmy opcję Include relationship columns.

Do tej pory rozszerzaliśmy tabele o określone kolumny – ma to sens w przypadku gdy jesteśmy po stronie wiele relacji (jeden do wielu). Co jednak w przypadku gdy jesteśmy po stronie jeden? Taka relacja występuje pomiędzy DimProduct (jeden) i FactInternetSales (wiele). W momencie gdy rozszerzamy tabelę DimProduct o np. kolumnę OrderQuantity z tabeli FactInternetSales możemy ją agregować – w oknie rozszerzenia wystarczy zaznaczyć opcję Aggregate i wybrać odpowiednią kolumnę.

Nie mamy tutaj do dyspozycji całej gamy agregacji, a jedynie dwie podstawowe tj. sumę dla pól liczbowych oraz zliczenie (count) na kolumnach nie będących liczbami. Oczywiście możemy użyć innych funkcji agregacji jednakże poprzez użycie innych mechanizmów – tutaj mamy jedynie możliwość szybkiego obliczenia agregatu przy rozszerzaniu kolumny. Po dołączeniu określonych kolumn do tabeli DimProduct można zdać sobie sprawę, że nie potrzeba ładować tabel DimProductSubcategory i DimProductCategory do ostatecznego modelu – tabele te są potrzebne jedynie w ramach procesu ETL. Aby ich nie ładować  wystarczy kliknąć w oknie Queries na te tabele prawym przyciskiem myszy i z menu kontekstowego odznaczyć opcję Enable Load tak jak zostało to przedstawione poniżej.

Po odznaczeniu tej opcji otrzymamy komunikat o nazwie Possible Data Loss Warning, które ostrzega nas o tym, że jeżeli jakieś wizualizacje opierają się na tej tabeli to mogą przestać funkcjonować i tabela nie będzie dostępna z poziomu raportu. Wiemy, że obie tabele będą nam pomocne jedynie podczas ładowania dlatego też odznaczamy im opcję Enable Load. Power Query odróżnia zapytania, które nie są ładowane do modelu kolorem szarym dzięki czemu wizualnie możemy ocenić co jest ładowane, a co nie.

Wiemy więc jak rozszerzać tabele horyzontalnie poprzez joiny, teraz omówimy sobie rozszerzanie wertykalne poprzez union. Aby to zrobić stwórzmy sobie nowy plik Power BI Desktop i pobierzmy do niego dane z tabeli FactInternetSales korzystając z dwóch poniższych zapytań:

SELECT * FROM dbo.FactInternetSales
where OrderDate BETWEEN '20100101' AND '20101231'

SELECT * FROM dbo.FactInternetSales
where OrderDate BETWEEN '20110101' AND '20111231'

Zmieńmy nazwy obu zapytań na kolejno Sales2010 i Sales2011 – możemy użyć do tego okna Query Settings we właściwości Name lub też używając menu kontekstowego:

Teraz chcemy połączyć oba zestawy w jeden używając unii – aby to zrobić należy użyć transformacji Append Queries w secji Combine zakładki Home.

Podobnie jak w przypadku Merge Queries tak i tutaj mamy możliwość połączenia do nowego lub istniejącego zapytania. Na ten moment stworzymy nowe zapytanie będące połączeniem dwóch poprzednich – wybieramy więc Append Queries as New.

Jak widać powyżej okno konfiguracyjne jest bardzo proste i nie posiada wielu ustawień. Jedynie co musimy zrobić to wybrać ile zapytań chcemy łączyć oraz wybrać konkretne zapytania do połączenia i zatwierdzić klawiszem OK. Po przetransformowaniu danych powstanie nowa tabela – teraz wystarczy wyłączyć ładowanie do modelu tabel składowych i zmiana nazwy nowo powstałej tabeli. Dla zaznajomionych z językiem SQL może pojawić się pytanie czy jest to UNION czy UNION ALL? Z całą pewnością jest to UNION ALL gdyż nie usuwa zduplikowanych wierszy – całe szczęście gdyż deweloperzy nie zawsze zdają sobie sprawę, że podczas wykonywania tej operacji tak naprawdę oprócz łączenia wertykalnego wykonują dodatkową operację związaną z usuwaniem duplikatów. Aby osiągnąć funkcjonalność UNION w Power BI wystarczy wybrać transformację Append Queries, a następnie zaznaczyć wszystkie kolumny i klikając prawym przyciskiem myszy na te kolumny wybrać z menu kontekstowego Remove duplicates.

Jak widać transformacje łączące są proste, intuicyjne i w bardzo wielu przypadkach przydatne. Mam nadzieję, że powyższy opis okazał się dla Was przydatny – zachęcam również do regularnego przeglądania nowych artykułów w ramach serii Poznaj Power BI które już niebawem.