Power BI – wstęp do Power Query

Power BI wyposażony jest w sprawny mechanizm do tworzenia procesów ETL. Dziś zapoznamy się z interfejsem do tworzenia tychże procesów o nazwie Power Query oraz poznamy kilka podstawowych transformacji dostępnych w tym narzędziu – zaczynajmy!

Pierwszym krokiem jest pobranie tych danych ze źródła, którym w naszym wypadku będzie standardowo SQL Server i baza AdventureWorksDW2014. Do naszej transformacji pobierzemy dane wybierając Get Data na zakładce Home i z menu rozwijanego wybierając SQL Server – naszym oczom powinno się ukazać okno podobne do poniższego:

W oknie tym tak jak już wspomniałem w poprzednim artykule należy podać nazwę serwera oraz nazwę bazy danych (jeśli tego nie zrobimy w tym miejscu to będzie można ją wybrać w dalszej części kreatora). Dodatkowo można w tym miejscu wybrać tryb połączenia tj: Import oraz DirectQuery. Pierwszy z nich pozwala na kopiowanie danych do modelu – wtedy też działania użytkownika na raporcie będą odpytywać model zawarty w raporcie. W przypadku gdy wybierzemy DirectQuery to żadne dane nie będą kopiowane, a działania użytkownika spowodują, że odpowiednie zapytanie będzie wysłane do bazy źródłowej. Oba rozwiązania znajdują zastosowanie w praktyce i można je wybrać w zależności od konkretnej logiki biznesowej jaką chcemy zastosować. Na ten moment (i właściwie w większości przypadków) wybieramy Import. W aplikacjach produkcyjnych powinniśmy kliknąć Advanced options i tam wpisać zapytanie SQL pozwalające pobrać dane – jeżeli tego nie zrobimy to do źródłowego silnika bazodanowego będzie wysłane “SELECT *”, oczywiście będziemy mogli wykonać transformacje na danych jednakże nie będą one wykonywane na źródle, a już na pobranej kopii danych. Na ten moment nie wpisujemy żadnego zapytania tylko przejdźmy dalej. W kolejnym oknie wybieramy tabele, które nas interesują – wybieram DimProduct, DimProductCategory i DimProductSubcategory:

W tym miejscu możemy również użyć przycisku Select Related Tables który zaznaczy dodatkowo tabele połączone relacjami z tymi, które zaznaczyliśmy ręcznie. Warto mieć na uwadze, iż przycisk ten będzie działał tylko wtedy gdy w źródłowej bazie danych zdefiniowane zostały relacje klucz główny – klucz obcy. Okno to jest również bardzo ważne ze względu na istnienie przycisku Edit, który włącza interfejs edytora zapytań Power Query. Po jego przyciśnięciu naszym oczom powinno ukazać się okno podobne do poniższego:

W tym miejscu możemy definiować reguły jakim poddawane są nasze dane. Każda tabela jest tutaj nazywana zapytaniem (ang. Query) i każde z nich posiada własne kroki związane z przekształcaniem danych. Wykonajmy więc jakąś prostą transformację – upewnijmy się, że modyfikujemy dane dla tabeli Dim Product i wybierzmy transformację Choose Columns z sekcji Manage Columns dostępnej na zakładce Home. Opcja ta pozwala na wybór tylko tych kolumn, które są dla nas istotne i chcemy je pobrać do naszej aplikacji. Wybór ten jest bardzo prosty i sprowadza się do zaznaczenia określonych kolumn – pamiętajmy, że im mniejszy model tym lepsza kompresja i wydajność naszego rozwiązania więc powinniśmy wybrać tylko niezbędne atrybuty. W celach demonstracyjnych wybrałem kilka istotnych dla nas kolumn widocznych na poniższym zrzucie ekranowym:

Po zatwierdzeniu klawiszem OK powinniśmy w podglądzie zobaczyć tylko wybrane przez nas kolumny.

Mamy pierwszą transformację za sobą i możemy dodawać kolejne. Tak więc następnym zadaniem jest usunięcie atrybutów ListPrice oraz Size. Aby to zrobić można zaznaczyć kolumnę, którą chcemy usunąć i nacisnąć klawisz delete lub też wybrać opcję Remove dostępną w menu kontekstowym pod prawym przyciskiem. Oczywiście istnieje również dedykowany przycisk dostępny we wspomnianej wcześniej sekcji Manage columns zakładki Home.

Po przyciśnięciu Remove Columns zostaną usunięte wszystkie zaznaczone kolumny. Remove Other Columns z kolei pozwalają na usunięcie wszystkich kolumn, które aktualnie nie są zaznaczone. Po usunięciu kolumn ListPrice oraz Size posortujmy zestaw dostępnych danych według kolumny EnglishProductName – znów możemy to zrobić używając dedykowanych przycisków w sekcji Sort lub też używając przycisku nagłówka pozwalającego działać na danych w analogiczny sposób jak w programie Microsoft Excel.

Po kliknięciu tego przycisku pojawi się okno sortowania i filtracji dla konkretnej kolumny.

Jak widać implementacja transformacji jest bardzo prosta i intuicyjna. Każdą transformację powinniśmy traktować jako sekwencję kroków i tak też jest postrzegana z technicznego punktu widzenia – po prawej stronie okna Power Query widoczna jest zakładka Query Settings. W ramach tego okna widzimy wszystkie transformacje, które zostały wykonane na zestawie danych – w każdej chwili możemy kliknąć określony krok i w oknie podglądu zobaczyć jak wyglądały dane na tamten moment. Warto tutaj zaznaczyć, iż jako krok jest uznawana każda operacja wykonana przez nas czy to ze wstążki, czy też z poziomu podglądu danych.

Ponadto możliwe jest usuwanie kroków poprzez kliknięcie krzyżyka dostępnego po lewej stronie transformacji. Zapewne przychodzi Wam do głowy pytanie czy można usuwać, modyfikować czy też wstawiać kroki pomiędzy istniejące transformacje? Odpowiedź jest niejednoznaczna i można powiedzieć, że to zależy. Od czego? Przede wszystkim od tego czy zaburza nam to kolejne kroki cz też nie. Na przykład spróbujmy dodać krok zmiany nazwy zaraz po kroku Removed Columns – aby to zrobić zaznaczmy Removed Column i kliknijmy dwukrotnie na nagłówek kolumny English ProductName  i zmieńmy nazwę na ProductName. Power Query powinien wyświetlić nam ostrzeżenie, że wstawienie transformacji pomiędzy istniejące transformacje może powodować błędy w krokach następujących po wstawianej transformacji. Na ten moment kliknijmy Insert godząc się z tym ryzykiem 🙂

Nowy krok powinien pojawić się pomiędzy kroki Removed Columns i Sorted Rows tak jak zostało to przedstawione poniżej.

Wszystko wydaje się w porządku natomiast po przejściu do kroku Sorted Rows naszym oczom pojawi się błąd o treści: the specified sort criteria is invalid.

Dlaczego tak się stało? Ponieważ sortowaliśmy po kolumnie EnglishProductName, która w tym kroku nie istnieje gdyż zmieniliśmy jej nazwę na ProductName. Część z was może pomyśleć, że to złe zachowanie gdyż nazwy w takim przypadku powinny zmieniać się we wszystkich kolejnych krokach – jednakże nie było by to dobre zachowanie gdyż po pierwsze nie mielibyśmy kontroli nad tym co się dzieje, a po drugie niektóre błędy nie dawały by się zmienić automatycznie więc mielibyśmy do czynienia z pewną niekonsekwencją.  Oczywiście jest możliwe naprawienie tego błędu jednakże na ten moment usuńmy ten krok i dodajmy go na nowo tym razem używając poprawnej nazwy kolumny.

Przejdźmy teraz do tabeli DimProductSubcategory – jak można zauważyć każda tabela posiada swój zestaw niezależnych kroków. Dzięki temu podejściu możliwe jest oczyszczenie danych z różnych źródeł, a następnie ich połączenie ( jeśli istnieje taka potrzeba) i załadowanie do modelu. Po zakończeniu modyfikowania zapytań dla tabel należy kliknąć dostępny na pasku przycisk Close & Apply aby dane zostały pobrane, przetransformowane i załadowane do modelu. Można również wykonać samą operację Apply aby załadować dane jednakże nie zamykając okna, oraz Close która nie pobierze danych ale zamknie okno edytora procesu ładowania.

Dane zaczną być ładowane do modelu.

Po załadowaniu dane są dostępne w modelu i można na nich bezpośrednio tworzyć wizualizacje.

Tak więc postawiliśmy pierwsze kroki w Power Query. Jak widać jest to bardzo proste i niezbyt wymagające póki co narzędzie. Jednakże to dopiero początek i mamy do omówienia naprawdę bardzo wiele różnego rodzaju funkcjonalności związanych nie tylko z Power Query ale również z modelem i wizualizacją danych.