Czym jest i do czego służy ETL
Mamy sobie trzy magiczne litery, które są podstawą jakiejkolwiek pracy z danymi – ETL, czyli Extract, Transform & Load. Po polsku – pobieranie, przekształcanie i wczytywanie danych.
ETL to proces, który służy do przetwarzania danych z różnych źródeł (np. baz danych, plików, stron internetowych) i przygotowywania ich do analizy lub przechowywania w celach biznesowych.
Trzy etapy ETL
1. Etap Extract (pobieranie danych):
Na tym etapie dane są pobierane z różnych źródeł, takich jak bazy danych, pliki tekstowe, strony internetowe itp. Podczas tego etapu dane są również normalizowane i przekształcane do postaci, która jest odpowiednia do przetwarzania.
2. Etap Transform (przekształcanie danych):
Na tym etapie dane są przetwarzane i przekształcane, aby spełniały określone wymagania i były gotowe do wczytania do docelowego systemu. Przekształcenia mogą obejmować różne czynności, takie jak oczyszczanie danych, agregacja, dezagregacja, kodowanie itp.
3. Etap Load (wczytywanie danych):
Na końcu dane są wczytywane do docelowej bazy danych lub systemu, gdzie mogą być wykorzystywane do analizy lub innych celów biznesowych.
Optymalny scenariusz
Co tu wiele mówić – w idealnym świecie masz do dyspozycji idealnie przygotowane dane już na etapie bazy.
W mniej idealnym świecie jesteś w stanie te dane w odpowiedni sposób przygotować na poziomie bazy i wczytujesz do modelu dokładnie to, czego potrzebujesz.
I to są scenariusze, które byłyby najwygodniejsze i najlepsze dla dalszego developmentu. Niestety świat rzadko kiedy bywa idealny. Ale z drugiej strony Power BI jest doskonałym narzędziem do przeprowadzenia wielu operacji w trakcie samego developmentu raportów – bez dotykania baz danych czy plików z których te dane pobieramy.
ETL w Edytorze Power Query
Zacznijmy od otworzenia Edytora Power Query i przyjrzyjmy się tabeli ’Transakcje’.
Jeżeli spojrzysz na kolumnę [ID klienta] to od razu w oczy rzuci się wiersz, w którym w pojedynczej komórce jest podany ten sam numer ID trzykrotnie, rozdzielony przecinkami.
Prawdopodobnie widzimy tutaj błąd wygenerowany przez system CRM i widząc coś takiego powinieneś zgłosić to właścicielowi danych, aby mógł ten błąd naprawić i wprowadzić odpowiednie zmiany do systemu, dzięki którym mógłby podobnych błędów unikać w przyszłości.
Ale zakładamy, że takiej możliwości nie ma. Ta wartość jest i pozostanie taka na wieki.
Co możemy z tym zrobić?
Mamy tutaj stosunkowo niewielki zbiór danych, więc możemy szybko rzucić okiem i sprawdzić, czy jest to pojedyncze wystąpienie, czy podobne błędy pojawiają się w tej kolumnie częściej.
Kliknij w prawym rogu kolumny na strzałkę (1) i w menu sprawdź, czy wizualnie pozycje się zgadzają. I co widzimy? Oczywiście – podobny błąd występuje w tej kolumnie jeszcze raz – „9463, 9463”.
Wyodrębnianie wartości
Błąd tego typu będzie bardzo prosty do usunięcia.
Ponieważ widzimy tutaj powtarzające się numery ID wystarczy, że wyciągniemy z całego ciągu tekst, który jest przed przecinkiem.
Aby to zrobić:
- Przejdź do zakładki Przekształć
- Zaznacz kolumnę, na której chcesz wykonać krok – w tym przypadku [ID klienta]
- Z paska wybierz narzędzie Wyodrębnij
- I opcję Tekst przed ogranicznikiem
Pojawi się nowe okno w którym skonfigurujemy jak ta funkcja ma się zachować.
Ponieważ nasze problematyczne numery ID są podzielone przecinkami – wpiszmy tutaj w polu Ogranicznik po prostu przecinek.
Po kliknięciu ok dla każdego wiersza w tej kolumnie zostanie wykonana operacja podczas której w komórce zostanie wartość tylko i wyłącznie znajdująca się przed pierwszym przecinkiem.
Gdybyśmy wprowadzili tam jakikolwiek inny znak – zostałaby zawartość znajdująca się przed pierwszym wystąpieniem tego znaku.
I mamy czystą kolumnę w której znajdują się tylko i wyłącznie pojedyncze, właściwe numery ID
Co istotne – ten krok będzie wykonywany za każdym razem, gdy będziemy odświeżać dane w naszym raporcie. Czyli jeżeli będzie podobny pojawiał się w przyszłości – nie musisz się już nim przejmować.
Szybki rzut oka na nasze kolumny.
I już nie mamy żadnego czerwonego koloru, typy danych się zgadzają. Możemy uznać, że mamy tutaj czyste dane.
Przejdźmy do drugiej tabeli ’Lokale’
I widzimy czerwień.
W kolumnach [Cena transakcyjna brutto] i [Cena transakcyjna mieszkania brutto] mamy błędy i to całkiem poważne. Spróbujmy je zidentyfikować.
Po kliknięciu na komórkę z wartością Error pojawi Ci się na dole mała, żółta ramka z której dokładnie wyczytasz co to za błąd i czego on dotyczy.
Tutaj mamy do czynienia z błędem formatu danych.
Spójrzmy na to, jaka w tej komórce znajduje się wartość – 1785530.8
Czy masz już pomysł, co może tutaj tworzyć błąd, skoro kolumna jest wartością liczbową i w tej komórce również jest wartość liczbowa?
Sprawcą tego całego zamieszania jest kropka. Tak jest! Kropka użyta zamiast przecinka użyta jako separator dziesiętny.
W zależności od wybranego kodu kulturowego takie problemy mogą wystąpić.
Zamiana wartości
Rozwiązanie tego problemu jest bardzo proste – po prostu podmienimy wszystkie kropki na przecinki.
Pamiętajmy, że ta kolumna oznaczona jest jako kolumna liczbowa a przecinek czy kropka ewidentnie nie są liczbami. Dlatego przed dokonaniem podmiany zmieńmy typ kolumny.
I zrobimy to dla kilku kolumn jednocześnie, ponieważ ten sam błąd występuje w nich wszystkich.
Zaznaczmy z shiftem te cztery kolumny, kliknij na którymś z nagłówków prawym przyciskiem myszy i kolejno Zmień typ -> Tekst.
Wszystkie zaznaczone kolumny staną się kolumnami tekstowymi i możemy już spokojnie wykonać naszą operację na tekście.
W każdej z tych kolumn kliknij na którejś z komórek i w oknie, które się wyświetli po prostu wpisz, że chcesz podmienić kropkę na przecinek.
Po wykonaniu tych kroków wystarczy, że znowu zmienimy typ danych w tych kolumnach na typ liczbowy i gotowe. Usunęliśmy błędy, które się w nich znajdują. W każdym wierszu mamy właściwe wartości liczbowe.
Możemy przejść do kolejnego zagadnienia, czyli
Łączenie zapytań
Dla tej części przygotowałem odrębny plik PBIX z danymi, które tutaj wykorzystamy.
Pobierz pliki do tego wpisu
Aby móc pobrać pliki musisz się zalogować
Po otwarciu pliku przejdźmy od razu do Edytora Power Query.
Struktura danych jest bardzo prosta – dwie tabele, jedna z kosztem budowy, druga z ceną sprzedaży m2 dla poszczególnych lokali.
Zarówno jedna jak i druga pozycja nie daje nam żadnej informacji bez kontekstu, bez możliwości połączenia jednej wartości z drugą dla poszczególnych lokali.
Aby połączyć jedną wartość z drugą będzie nam potrzebny Klucz podstawowy, który pozwoli nam na jednoznaczną identyfikację każdego z lokali, każdego rekordu z bazie danych. Klucz musi posiadać wartości unikatowe, z reguł będzie to w tabelach numer ID rekordu.
Tutaj, żeby nie było tak prosto przygotowałem tabele, które takiego numeru nie posiadają i pomyślmy chwilę, co możemy zrobić, aby jednak z posiadanych informacji taki klucz stworzyć.
W obu tabelach mamy trzy takie same kolumny: [Budynek], [Piętro], [Numer lokalu].
Żadna z tych kolumn sama w sobie nie będzie mogła być kluczem, ponieważ mamy tam powtarzające się wartości, które w żaden sposób nie pozwolą nam na jednoznaczne określenie konkretnego lokalu.
czy możemy z nimi zrobić coś, co jednak nam na to pozwoli?
Oczywiście, że tak!
Jeżeli połączymy w jeden ciąg te trzy wartości to każdy lokal otrzyma swój unikatowy numer ID.
Więc dodajmy sobie nową kolumnę – [Ident]
Powtórzmy to samo dla drugiej tabeli i już mamy w obu tabelach klucz, na podstawie którego będziemy w stanie stworzyć relację albo (jak tutaj) połączyć dane z tych tabel w jedną.
Scalamy zapytania
Dobra. Dane są gotowe, możemy przystąpić do scalenia ich w jedną tabelę.
Zależy nam na tym, aby w tej jednej tabeli mieć oczywiście dane identyfikujące konkretny lokal, kolumnę z kosztem budowy m2 i kolumnę z ceną sprzedaży m2.
Przejdź do narzędzi głównych – > scalanie zapytań
I w oknie, które wyskoczyło wybieramy dwie tabele, które chcemy ze sobą scalić oraz zaznaczamy w obu kolumny z naszymi kluczami.
W tabeli pojawia się nowa kolumna, którą możemy rozwinąć – tutaj wybierz tylko [Koszt Budowy m2]
I jesteśmy w domu – scaliliśmy ze sobą dane z dwóch tabel.
Dołączanie zapytania
Scalając zapytania pracowaliśmy w poziomie – używając prostego przykładu – do istniejącego wpisu w książce adresowej dodaliśmy dodatkową informację.
Dołączając zapytania – pracujemy w poziomie, czyli znowu używając przykładu książki adresowej – do istniejącej książki przepisujemy wpisy z innej.
Po co w ogóle dołączać zapytania?
Czasami zdarza się, że z systemu możesz wyciągnąć dane tylko z określonego przedziału czasowego. Przykładem niech tu będzie Facebook, który nie pozwala na eksport danych z całego okresu istnienia fanpage. I tutaj pojawia się zgrzyt. Dzięki funkcji dołączania zapytań możemy scalić wiele plików w jedną tabelę i dalej z nią pracować.
Tutaj też przygotowałem dla Ciebie paczkę z danymi właśnie z Facebooka.
Pobierz pliki do tego wpisu
Aby móc pobrać pliki musisz się zalogować
Zaimportuj te dwa pliki do Power BI.
I tutaj szybka uwaga
Dołączając do siebie zapytania zwróć uwagę na nazwy kolumn – muszą być identyczne. W przeciwnym wypadku nagle okaże się, że zostały dane dołączone jako nowe kolumny i nic ze sobą się nie zgrywa.
Dodatkowo w przypadku tych plików – usuńmy z nich pierwsze wiersze.
Jeżeli przyjrzysz się teraz tym dwóm tabelom – zobaczysz, że struktura danych w nich jest identyczna, różnią się tylko i wyłącznie okresami.
Aby je sprawnie analizować połączmy je w jedną tabelę.
Zrobimy to podobnie jak w przypadku scalania, tylko tym razem użyjemy funkcji Dołącz zapytania.
Tym razem skorzystajmy z dołączenia jako nowe.
Dzięki temu zapytania nie zostaną dołączone do istniejącej już tabeli a stworzymy zupełnie nową tabelę, która będzie składała się z tych, które ze sobą złączyliśmy.
I znowu wybieramy tabele, które chcemy ze sobą połączyć, na koniec ustawiamy właściwe typy danych i gotowe.
Otrzymaliśmy jedną tabelę ze wszystkimi wierszami z połączonych.
Właściwe typy danych
A skoro już jesteśmy prze zmianie typów danych. Z czasem przekonasz się, że wiele problemów w pracy z Power BI potrafi wynikać właśnie z niewłaściwie dobranych lub nieustawionych typów lub kategorii danych.
Kilkukrotnie już zmienialiśmy typ danych w Edytorze Power Query, ale możesz to zrobić w inne sposoby.
Wróćmy na chwilę do naszego podstawowego pliku, tego z tabelami [Lokale] i [Transakcje].
Załadujmy tutaj sobie kolejny plik – cleverdev-kontakty.xlsx
Po załadowaniu przejdź do zakładki Dane (1)
Widzisz podgląd zaimportowanej tabeli i wszystko niby się zgadza. Poza kolumną [Id]. Ma ona typ Liczbowy całkowity, co jest całkowitym bezsensem w tym przypadku 🙂 Nie będziemy wykonywać na numerach id żadnych operacji matematycznych, więc przekonwertujmy ją na zwykły tekst.
Zaznacz kolumnę [Id] i z lewej strony szarfy zmień typ danych na Tekst.
Kolejnym miejscem w którym możesz zmienić typ danych jest zakładka Model. Tutaj również możesz po wybraniu konkretnej kolumny zmienić jej typ we właściwościach.
Przestawienie kolumn
Częściej niż Ci się wydaje będziesz mieć do czynienia z tabelami, które nie są stworzone w sposób optymalny do pracy z nimi. Najczęściej będzie to w przypadku importowania danych z tworzonych przez ludzi albo eksportowanych z systemów raportów, zestawień i tabel.
Oczywiście, dane przedstawiane w ten sposób są łatwiejsze do ogarnięcia przez ludzkie oko, ale już do celów analizy nadają się średnio.
Plik przygotowany właśnie w ten sposób znajdziesz pod nazwą dzikie-dane.xlsx
Jeżeli otworzysz go najpierw w arkuszu kalkulacyjnym to pewnie postukasz się w głowę i pomyślisz „Co ten gość za bzdury opowiada, przecież to idealnie przejrzysta tabelka”. I tak będzie do momentu, w którym będziesz chciał na tej tabelce pracować w Power BI.
Więc… Załadujmy ją.
Co tu wiele mówić… Spróbuj mając dane w takiej formie zwizualizować mi chociażby sprzedaż w poszczególnych miastach. Powodzenia życzę.
Aby ta tabela nadawała się do dalszej pracy musimy przekształcić ją w taki sposób, aby odpowiednie wartości znalazły się w odpowiednich kolumnach.
W tym przypadku będą nam potrzebne kolumny:
| Rok | Miasto | Grupa produktu | Wartość sprzedaży |
Tabele tego typu jak już wspomniałem nie są niczym nietypowym i w Power BI możemy stosunkowo prosto przekształcić je do formy bardziej BI friendly.
Zacznijmy od… Cofnięcia się.
W ustawieniach zapytania kliknij na ten krzyżyk przy ostatnim kroku i usuń Nagłówki o podwyższonym poziomie
Otrzymujemy bazową tabelę, którą możemy transponować.
Wygląda to odrobinę lepiej, ale nadal jesteśmy w polu. Mamy tam jakieś puste wartości przy miastach, lata w kolumnach…
Zacznijmy od tego, aby nazwy lat wskoczyły nam jako nagłówki naszej tabeli. Czyli po prostu używamy pierwszego wiersza tabeli jako nagłówków.
Dalej – zajmijmy się pustymi wartościami w kolumnie z Miastami. Też w prosty sposób możemy je uzupełnić.
Kliknij prawym przyciskiem myszy na nagłówku tej kolumny i wybierz Wypełnij -> W dół
I teraz tabela wygląda już naprawdę dobrze! Pozostała nam kwestia lat. Mamy każdy rok w oddzielnej kolumnie a chcielibyśmy, żeby wszystkie lata były w jednej kolumnie.
Wystarczy, że teraz zaznaczymy kolumny z Miastami i Kategoriami, prawy klik na nagłówku którejś z tych kolumn i wybierz opcję Anuluj przestawienie innych kolumn i jesteśmy w domu.
Pozostało nam tylko ustawić właściwe nagłówki kolumn, typy danych i mamy gotową do dalszej pracy tabelę.
Konwencje nazw
Tylko jakie tutaj ustawić nagłówki, i w jaki sposób je zapisać, aby było to jasne, przejrzyste i ułatwiające dalszą pracę nie tylko nam ale także innym osobom?
W Power BI nie mamy jednego, właściwego schematu nazywania. To, w jaki sposób będziesz nazywać kolumny, tabele, miary czy jakiekolwiek inne elementy zależy tylko od Ciebie. Mogę Ci podpowiedzieć w jaki sposób ja podchodzę do nazywania.
Nazwy kolumn, miar czy zapytań powinny od razu wskazywać na to, co w tych kolumnach się znajduje. Nie oznacza to, że masz tam robić pełną opisówkę – wystarczy jasna nazwa na przykład „Sprzedaż netto” zamiast niezrozumiałych skrótów. Wierz mi, że powrót do raportu który tworzyłeś kilka miesięcy temu i próby rozszyfrowania skrótów w nazwach kolumn czy miar może być naprawdę źródłem poważnej frustracji i marnowaniem cennego czasu.
Zmieniaj te nazwy od razu. Sam czasami tworząc raporty w pewnym momencie łapałem się za głowę gdy w ferworze tworzenia definiowałem nazwy według nieokreślonych skrótów i później zastanawiałem się co jest co i z czym to się je.
W filmie pokazuję też w jakich miejscach możesz definiować nazwy poszczególnych elementów.
Korzystaj z pola Opis w zakładce Modelowanie.
Możesz tam zrobić opisówkę dotyczącą zawartości danej kolumny czy miary.
Edytor zaawansowany Power Query
Wróćmy do Edytora Power Query.
W pasku narzędzi znajdziesz taką niepozorną pozycję – Edytor zaawansowany.
Kliknij na nią.
Pojawi Ci się edytor tekstowy kodu, który składa się na wszystkie operacje, które na danej tabeli wykonaliśmy.
Power BI jest bardzo fajne pod kątem prostego wyklikiwania różnych podstawowych operacji i większości przypadków to wyklikiwanie będzie wystarczające. Ale z czasem zaczną pojawiać się sytuacje, w których ręczne napisanie albo zmodyfikowanie kilku linijek kodu będzie szybsze a przede wszystkim bardziej wydajne niż doprowadzenie do takiego samego efektu w edytorze wizualnym.
Operacje, które wykonujesz w Edytorze Power Query korzystają z M code, czyli skryptowego języka programowania wykorzystywanego w aplikacjach aplikacjach Microsoft Office i Power Platform, takich jak Excel, Power Query i Power BI.
M code jest używany do tworzenia i modyfikowania zapytań danych, a także do tworzenia i modyfikowania obiektów i funkcji w tych aplikacjach. Jest to język zorientowany na dane, który umożliwia łatwe przetwarzanie i przekształcanie danych w różnych formatach i źródłach. M code jest zoptymalizowany do pracy z dużymi ilościami danych i może być używany do wykonywania wielu różnych zadań, takich jak agregowanie danych, zmiana formatu danych, łączenie danych z różnych źródeł itp.
W filmie pokazuję prosty przykład ręcznego wprowadzenia i modyfikacji kodu w naszej tabeli.
Wyłączenie ładowania i odświeżania danych
Pracując z danymi zauważysz, że ich źródła są różne. Załóżmy, że przygotowujesz raport dla sklepu online. Będziesz pracować z danymi, które mają różne wymagania dotyczące ich odświeżania.
I tak na przykład będziesz mieć listę transakcji – wiadomo. Codziennie coś sprzedajesz i, żeby mieć wgląd w aktualną sytuację te dane też muszą być często odświeżane. Podobnie będzie sprawa wyglądała z danymi z Google Analytics, wpłatami, przelewami etc.
Z drugiej strony będą też dane, które są stosunkowo stałe jak chociażby drzewo kategorii, lista produktów z kodami kreskowymi etc. Ba! W tym przypadku nawet często będą zdarzały się sytuacje w których dane praktycznie w ogóle nie będą wymagały odświeżania – tak często będzie się działo na przykład przy schematach księgowych.
Czy w takim przypadku warto zużywać zasoby na to, aby te tabele też wczytywały dane za każdym razem, gdy odświeżasz istotne dane?
Oczywiście, że nie.
Dlatego z poziomu Power BI Desktop masz możliwość wyłączenia ładowania i odświeżania danych.
Klikając prawym przyciskiem myszy na zapytaniu w Edytorze Power Query możesz zarządzać tym, czy to zapytanie będzie ładowane i odświeżane wraz z raportem.
Uwzględnij w odświeżaniu raportu
Tutaj sprawa jest prosta – dane, które zostały załadowane – pozostają w pamięci i dokładnie w takiej formie pozostają. Z tej tabeli możesz korzystać cały czasy, po prostu odświeżanie będzie pomijać dane zapytanie.
Wyłącz / Włącz ładowanie
Tutaj sprawa jest trochę bardziej złożona i na pierwszy rzut oka wydaje się bezsensowna. W końcu po wrzucać tutaj dane (mało tego, również je odświeżać!) skoro nie będą ładowane do modelu i nie będziemy mogli z nich korzystać.
Aby nie komplikować – czasami będziesz chciał załadować dane jako kilka odrębnych zapytań. Nie ma większego sensu w tym, aby ładować to samo źródło kilkukrotnie. Możesz wczytać dane ze źródła do zapytania, wyłączyć jego ładowanie i na podstawie tego zapytania z wyłączonym ładowaniem stworzyć już docelowe, robocze zapytania.
Tutaj już wchodzimy w kwestie związane z optymalizacją i z pewnością napiszę coś więcej na ten temat. Na razie jednak wystarczy Ci informacja, że takie coś można zrobić.
