Proces ETL – dlaczego żaden system BI nie może bez niego istnieć?

Hurtownie danych, jak wskazuje sama nazwa, służą do przechowywania danych, które mogą pochodzić z różnych systemów. Jednak aby mogło do tego dojść, dane należy tam wcześniej załadować. To zadanie bardzo często bywa skomplikowane z uwagi na różnorodność struktur i źródeł danych. Co więcej – zdarza się, że w zależności od systemu, te same nazwy (np. ulic), mogą być zapisywane w różnej formie, co wprowadza niepotrzebny chaos. Aby go uniknąć należy doprowadzić wszystkie nazwy do jednolitej postaci – wykorzystuje się do tego proces ETL (z ang. Extract, Transform and Load). W dzisiejszym artykule wyjaśnię czym jest ETL i przedstawię narzędzia, które są niezbędne przy jego budowaniu.

Czym jest proces ETL?

W poprzednim artykule omawiałem teorię hurtowni danych. Jak już wiadomo – w hurtowni składowane są dane z wielu źródeł. Dzieje się tak dlatego, że bardzo często w obrębie jednej organizacji wdrożone są różne systemy, które generują dane. Osoby na stanowiskach decyzyjnych często chcą monitorować działanie firmy i mieć te wszystkie wygenerowane dane w jednym miejscu. Idealnie, gdyby były one zaprezentowane na ładnym dashboardzie. W takim przypadku hurtownie danych są wykorzystywane jako centralna składnica. Aby hurtownia nie była pusta należy ją załadować, a tak naprawdę ładować ciągle danymi z systemów. Właśnie w tym celu buduje się procesy ETL – Extract, Transform and Load. Jak wskazuje nazwa, dane są najpierw pozyskiwane, później transformowane, a na końcu ładowane.

Extract – pozyskiwanie

Na tym etapie dane są pozyskiwane z systemów na kilka sposobów. Pierwszym z nich jest bezpośrednie podłączenie się do bazy danych systemu źródłowego (a tak naprawdę do jakiejś kopii bazy „tylko do odczytu”, aby nie obciążać systemu). W tym przypadku łączymy się narzędziem i pobieramy dane przyrostowe, a następnie przenosimy je do naszego systemu.

Drugie podejście to wystawianie danych przez system źródłowy, np. w postaci plików płaskich. Takie pliki umieszczane są na serwerze FTP lub w innym udostępnionym miejscu.

Ostatni sposób to wszelkiego rodzaju API – Application Programming Interface, dzięki któremu możliwe jest pobranie danych z konkretnego okresu. Oczywiście istnieje wiele innych możliwości pozyskiwania danych, nie sposób omówić tutaj wszystkich. Warto jednak wiedzieć, że możliwości nie kończą się na trzech wymienionych przeze mnie sposobach.

Transform – transformacja

W momencie, kiedy mamy już dane z systemu źródłowego, powinniśmy je przekształcić do wybranej przez nas formy. W sytuacji, gdy mamy dane z wielu różnych źródeł, musimy umieścić je w jednej, wspólnej strukturze. Na tym etapie wykonywane są różne transformacje, takie jak mapowanie pól, skracanie nazw, ustalanie wspólnego formatu łańcuchów znaków czy nadawanie kluczy surogatkowych. Chodzi o to, aby różne struktury połączyć w jedną. Zwykle ten krok wymaga od dewelopera wiele pracy i mnóstwa pomysłów. Często relatywnie proste transformacje, przy dużym wolumenie danych, potrafią być niewydajne i konieczne jest znalezienie inne metody.

Load – ładowanie

Ten etap polega na załadowaniu danych do tabel docelowych, czyli tych, w których będą one składowane i odczytywane. Dane powinny być oczyszczone i ujednolicone.

Proces ETL vs ELT

W tym miejscu warto zaznaczyć pewną istotną kwestię. W codziennej komunikacji bardzo często korzysta się z określenia ETL mając na myśli transformację i ładowanie danych. Warto jednak pamiętać, że obok procesu ETL, o którym pisałem wyżej, funkcjonuje również proces ELT. Jego przebieg nieco różni się od klasycznego ETLa. W ELT dane po przechwyceniu najpierw są ładowane do jakiegoś obszaru tymczasowego, a dopiero później transformowane i ładowane do schematu docelowego.  

Data Cleansing – oczyszczanie danych

Ze względu na to, że dane trafiają do hurtowni z różnych systemów, a także są ładowane w różnej postaci (pliki płaskie, bazy danych, interfejsy sieciowe), poszczególne wartości mogą być niezgodne ze strukturą hurtowni. Mogą również zdarzać się też takie błędy, jak literówki czy wartości numeryczne zapisane w różnej postaci. W najgorszym przypadku możliwe jest wystąpienie całkowicie błędnego wiersza, np. w miejscu, gdzie powinny znajdować się wartości numeryczne wstawione są łańcuchy znaków. Ze względu na to, że takie wartości nie mogą trafiać do hurtowni, powinny zostać wyłapane i naprawione, bądź obsłużone w inny sposób. W procesie ETL możemy również umieścić dodatkowy etap – oczyszczanie danych. Te elementy, które można w jakiś sposób naprawić – naprawiamy. Te, które całkowicie nie spełniają wymagań, powinny być „odkładane na bok” i przeglądane przez osobę zajmującą się jakością danych (Data steward).

Narzędzia wykorzystywane przy budowaniu procesów ETL

Jak można się domyślić, do budowania procesu ETL dostępne są specjalne narzędzia 🙂 Oczywiście możemy wszystko obsłużyć sami (np. pisząc program do migracji w jakimś frameworku). W większości przypadków część transformacji jest jednak powtarzalna i nie jest konieczne wymyślanie wszystkiego na nowo.

Budowanie ETL z wykorzystaniem takich narzędzi sprowadza się głównie do metody „przeciągnij i upuść”. Odpowiednie bloki odpowiedzialne za transformacje konfigurowane są w interfejsie graficznym. W przypadku jakichś nietypowych transformacji mamy oczywiście możliwość „dopisania” sobie funkcjonalności w wybranym języku programowania. W przypadku narzędzi Microsoft może to być C# lub VBA. Na rynku funkcjonuje wiele różnych narzędzi do procesu transformacji i integracji danych, niektóre z nich to:

  • Informatica Power Center
  • Oracle Data Integrator
  • SQL Server Integration Services
  • Azure Data Factory

Wybór odpowiedniego narzędzia zależy główne od wymogów projektowych. W przypadku gdy korzystamy z baz SQL Server, to prawdopodobnie SQL Server Integration Services będzie dobrym wyborem. Nie jest to jednak zasada. Każdy zespół projektowy dobiera narzędzia biorąc pod uwagę swoje doświadczenie, koszt narzędzi, a także wymogi projektowe.

Proces ETL budowany z wykorzystaniem SQL Server Data Tools.
Proces ETL budowany z wykorzystaniem SQL Server Data Tools (paczki SQL Server Integration Services)

Różne podejścia do przechowywania danych surowych – ODS, STE, STG

Jak pisałem wyżej – często spotykamy się z modelem ELT, w którym dane są najpierw ładowane, a dopiero później transformowane. Otóż w niektórych przypadkach istnieje potrzeba, aby wszystkie dane, pochodzące z różnych źródeł, przechowywane były w takiej formie, w jakiej je dostaliśmy. Wynika to z tego, że takie „surowe” dane mogą być później wykorzystane do ponownego załadowania hurtowni, załadowania Data Martów lub ewentualnej weryfikacji błędnych rekordów. Do celów przechowywania takich danych wykorzystuje się czasami osobną bazę danych ODS (Operational Data Store) lub w hurtowni danych wydziela się specjalną przestrzeń na dane przed transformacją. Taki obszar w hurtowni, w którym znajdują się surowe dane, nazywamy obszarem przejściowym (stage area).Czasami wykorzystuje się dodatkowo dwa schematy – STE i STG (ale nazwy mogą być inne). Do STE trafiają tylko dane z bieżącego ładowania (np. z poprzedniego dnia), z kolei w STG przechowywane są wszystkie dane surowe.

Slowly Changing Dimensions – wymiary wolnozmienne

Czasami zachodzi konieczność zmiany wartości jakiegoś atrybutu w źródłowej bazie danych. Za przykład weźmy sytuację, w której pracownik dostaje awans i konieczna jest zmiana jego stanowiska w systemie. Z punktu widzenia systemu źródłowego możemy takie dane nadpisać i po prostu przechowywać stan aktualny – takie rozwiązanie może być w zupełności wystarczające. Hurtownia danych ma za zadanie przechowywać również dane historyczne i być może to, kiedy pracownik awansował będzie istotne dla zarządu, np. ktoś z managerów zechce porównywać, jakie pracownik miał osiągnięcia przed awansem i po nim. W celu przechowywania historii zmian w danym wymiarze (przez wymiar rozumiemy tutaj pracownika) stosuje się wymiary wolnozmienne. Istnieje 5 podstawowych typów wymiarów wolnozmiennych:

  • Typ 0 – w tym przypadku rekordy nie mogą być aktualizowane, w omawianym przypadku pracownik nie może awansować 🙂 A tak naprawdę to może, z tym, że w momencie, kiedy to się wydarzy, do bazy dodany zostanie nowy rekord. Innymi słowy – pracownik będzie miał w bazie inne ID.
  • Typ 1 – ten typ dopuszcza wprowadzanie zmian na rekordach, jednak podobnie jak w przypadku typu 0, nie umożliwia przechowywania historii.
  • Typ 2 – przechowywana jest cała historia zmian, co oznacza, że z każdą nową wersją nowy rekord zostaje umieszony w hurtowni danych. W tabeli z takim wymiarem zostaje dodana kolumna z datą, od której ten rekord obowiązuje. Tzn. – jeżeli pracownik dostał awans, to w hurtowni istnieją dwa rekordy, niemalże identyczne, różnią się tylko stanowiskiem i datą efektywną. Dzięki temu możliwe jest śledzenie całej historii zmian.
  • Typ 3 – w tym przypadku także przechowywana jest poprzednia wartość atrybutu, ale w jednym rekordzie.
  • Typ 4 – tworzona jest specjalna tabela w której przechowywane są wszystkie historyczne zmiany.

W praktyce wybór odpowiedniego typu SCD zależy od potrzeb biznesowych. Czasami pełna historia nie jest potrzebna i wystarczy nam SCD 1, ale zdarzają się sytuacje, w których niezbędne są inne typy.

Podsumowanie

Proces ETL jest jednym z najistotniejszych elementów systemu klasy Business Intelligence. Dzięki niemu możemy integrować dane ze wszystkich naszych systemów i gromadzić je w jednym miejscu. Co za tym idzie, dane w naszej hurtowni są wysokiej jakości i dzięki ich analizie jesteśmy w stanie wyciągać wnioski o funkcjonowaniu organizacji. Często przyjmuje się, że budowa procesu zajmuje ponad 80% czasu tworzenia całego rozwiązania BI. Wynika to z faktu, że podczas budowy ETL napotykamy na wiele trudności. Ze względu na to, że tematyka ETL jest dosyć rozległa, będę ją kontynuował w kolejnych artykułach.