ETL (Ekstrakcja, Transformacja, Ładowanie)
Narzędzia wspomagające proces pozyskiwania danych dla baz danych i hurtowni danych
Potrzebujesz wykorzystać dane dostępne z różnych źródeł? Pokazujemy jak to można zrealizować. Przedstawimy narzędzia ETL (ang. Extract, Transform and Load) czyli narzędzia wspomagające proces pozyskiwania danych dla baz danych, a szczególnie dla hurtowni danych (temat czym jest hurtownia danych będzie poruszony w kolejnym artykule na Naszym blogu). Narzędzia te automatyzują pracę całych zespołów, przez co proces ETL może ciągu nocy (zazwyczaj w nocy, aby nie obciążać systemów produkcyjnych) wykonywał wszelkie prace związane z przepływem danych.
Dane w firmie generowane są w systemach takich jak: CRM, ERP, platformy e-commerce (magento, Google Analytics). Jak sprawić aby te wszystkie dane znalazły się w systemie analitycznym lub hurtowni danych? Tutaj wkracza właśnie temat budowy procesów ETL.
Podpowiadamy jakie rozwiązania w tym obszarze są dostępne i w jaki sposób wybrać rozwiązanie, które będzie dopasowane do Twoich potrzeb.
Z artykułu dowiesz się:
- Po co tworzy się procesy ETL w nowoczesnej firmie?
- Czym jest ETL, czyli jak wygląda taki proces?
- Jakie dodatkowe możliwości dają nam narzędzia ETL?
- Jak wybrać narzędzie ETL?
- Proces ETL- jakie rozwiązania oferują dostawcy?
Po co tworzy się procesy ETL w nowoczesnej firmie?
Dane gromadzone w systemach informatycznych i archiwizowane na nośnikach pamięci nabierają coraz większego znaczenia dla firmy w dobie wszechobecnej konkurencji. Bazy danych obejmują przede wszystkim dane transakcyjne wykorzystywane dla bieżącej obsługi przedsiębiorstwa. Z biegiem czasu tak gromadzone przez wiele lat informacje stanowią kopalnię wiedzy na temat procesów biznesowych i otoczenia biznesu. Ważnym aspektem dla zarządu jest również ocena kondycji finansowej spółki, co w dużych firmach wymaga zebrania i podsumowania wielkich zestawów danych każdego dnia. Wykorzystanie tych informacji ma ogromny wpływ na funkcjonowanie firmy, jej efektywność i pozycję na rynku. Analityka danych rozproszonych pomiędzy różnymi systemami i bazami danych wymaga w pierwszej kolejności integracji tych danych. Rozwiązaniem jest dobrze zaprojektowana hurtownia danych (ang. Data Warehouse). Hurtownia danych jest systemem zarządzania danymi, która pozwala na dostarczenie danych dla bardziej zaawansowanych systemów analitycznych jakimi są systemy inteligencji biznesowej (ang. BI Business Intelligence – pojawi się ten temat w kolejnym artykule na Naszym blogu). Jednak zanim będziemy dysponować zestawem zintegrowanych danych dla hurtowni danych, należy przejść przez proces ich pozyskiwania. Do tego służą narzędzia ETL, dla których należy zdefiniować proces pozyskiwania danych, zwany procesem ETL.
ETL – opis procesu
Proces ETL obejmuje 3 etapy:
Extract – przeniesienie (wyładowanie) danych z rozproszonych źródeł (CRM, ERP, WorkFlow, WMS) i ulokowanie ich w magazynie tymczasowym
Transform – sprawdzenie danych źródłowych – poprawienie i uzupełnienie, eliminacja danych zduplikowanych, odrzucenie danych o wątpliwej jakości, dopasowanie danych do zdefiniowanych wzorców, grupowanie a nawet agregacja danych według ustalonych kryteriów, konwertowanie danych do jednego formatu
Load – załadowanie danymi hurtowni danych na serwerze docelowym
Etap 1. Ekstrakcja
Proces ekstrakcji polega na pobieraniu danych ze wszystkich źródeł, takich jak: bazy danych, serwisy internetowe, pliki pochodzące z aplikacji, kanały RSS i inne. Zanim pobierzemy dane mamy okazję zidentyfikować źródła danych, a następnie zapoznać się z ich strukturą. Warto też poznać problemy z wprowadzaniem i opracowaniem danych i wstępnie ocenić poziom występujących błędów. Może też okazać się, że potrzebny będzie audyt przeprowadzony pod kątem jakości danych. Audyt pozwoli na:
- poznanie właścicieli danych,
- mapowanie lokalizacji danych wraz z określeniem użytkowników i zakresu wykorzystania danych,
- ocenę jakości z uwzględnieniem postaci i formatów danych, metod i częstotliwości aktualizacji danych,
- ocenę wielkości danych i wielkości przyrostów danych,
- wydanie zaleceń co do sposobu kontroli jakości danych.
Etap 2. Transformacja
W kolejnym etapie przeprowadzamy czyszczenie, naprawianie i uzupełnianie danych w sposób automatyczny za pomocą odpowiednich narzędzi, programów i skryptów. Poszczególne czynności powinny być udokumentowane, ponieważ w przyszłości będziemy chcieli je powtórzyć. W przypadku gdy zajdzie potrzeba zmodyfikowania niektórych czynności (np. skryptów automatycznych) będziemy mogli łatwiej i szybciej przeprowadzić niezbędne zmiany. Po oczyszczeniu danych przystępujemy do ich przekształcenia i dopasowania zgodnie z ustalonymi wzorcami (np. ujednolicenie jednostek miar) oraz formatowania danych. Ważne jest też, aby pamiętać o uzupełnieniu rekordów danych (np. dodanie kolumn), rozdzieleniu zawartości pól (split), oraz w miarę potrzeb pogrupowaniu i agregacji danych. Teraz możemy poddać nasze dane testowaniu za pomocą gotowych narzędzi, lub korzystając z własnych testów.
Etap 3. Ładowanie
Po upewnieniu się, że nasze dane są oczyszczone i przekształcone możemy przystąpić do procesu ładowania. Możemy to wykonać jako ładowanie zbiorcze (full load) lub jako ładowanie przyrostowe (incremental load). Warto rozważyć metodę ładowania przyrostowego, ponieważ ta metoda może przyspieszyć proces ładowania. Takie decyzje są podejmowane przez architekta procesu ETL.
Dlaczego warto precyzyjnie zdefiniować proces ETL?
Dane źródłowe poza tym, że są rozproszone, są często niekompletne i nie zawsze gwarantują odpowiedni poziom jakości i wiarygodności. W procesie ETL dane są weryfikowane i dzięki temu możemy uzyskać wiarygodne wyniki analiz z Hurtowni Danych. Gdy proces ETL jest gotowy do zastosowania należy określić harmonogram wykonywania procesu ETL w zależności od tego czy proces dotyczy wstępnego ładowania danych, czy też będziemy stosować ładowanie przyrostowe w miarę pojawiania się nowych danych.
Podsumowując, proces ETL odpowiada za przygotowanie danych pochodzących z różnych źródeł oraz ocenę ich jakości zanim te dane zostaną dostarczone do hurtowni danych, mając na celu zautomatyzowanie tych działań, oszczędzając czas i koszty roboczogodzin zespołów analitycznych.
Jakie dodatkowe możliwości dają nam narzędzia ETL?
Dzięki zastosowaniu narzędzi ETL uzyskujemy dodatkowe możliwości, takie jak:
Identyfikacja i tworzenie katalogów danych
Poznając struktury danych w obszarach poszczególnych źródeł (gałęzi działalności biznesowej) następuje ich identyfikacja oraz ocena przydatności dla dalszego ich wykorzystania. Efektem tych działań jest stworzenie katalogów danych dla całego przedsiębiorstwa. A to oznacza, że mamy rozeznanie jakie dane są dostępne i zaczynamy panować nad naszymi danymi. Takie przygotowanie pozwala na stworzenie spójnego obrazu całej firmy, opartego o dane.
Poprawa jakości danych
Dość często zdarza się, że nie przywiązujemy wystarczającej uwagi na jakość danych na etapie ich wprowadzania w ramach poszczególnych systemów. Dobrym przykładem jest ocena działań handlowych, których powiązanie z faktycznymi efektami, bez rzetelnej jakości danych, jest wręcz niemożliwa do przeprowadzenia. Ocenianie po samym wzroście/spadku przychodów, jest jak patrzenie na działania w kosmosie przez słabej jakości lunetę. W wyniku zaniedbań w tym zakresie nasze dane mogą być nieprecyzyjne, niekompletne, a nawet mogą zawierać błędy. Mamy wtedy do czynienia z efektem Garbage In = Garbage Out, co oznacza, że dane obarczone wadami wpływają na wynik naszej analizy. Proces ETL pozwala nam na poprawienie i uzupełnienie danych a nawet na zastąpienie błędnych danych pochodzących z jednego źródła danymi z innego źródła.
Ujednolicony punkt dostępu
W wyniku ekstrakcji danych z wielu źródeł możemy utworzyć jeden kanał dostępu i uzyskać w ten sposób wgląd do Magazynu Tymczasowego. Dzięki temu możemy dokonać transformacji wszystkich zidentyfikowanych danych korzystając z opracowanych wzorców oraz nadać danym odpowiedni format. W ten sposób możemy uporządkować nasze dane i przygotować gotowe do raportowania dane operacyjne w ramach jednego miejsca dostępu (systemu).
Optymalizacja zasobów
Dzięki zastosowaniu odpowiednich narzędzi ETL optymalizujemy zarządzanie danymi stosując spójne reguły i procedury przetwarzania dostępne zazwyczaj na jednym serwerze ETL. Jednocześnie możemy zredukować koszty sprzętu i oprogramowania w porównaniu z tradycyjnymi sposobami zarządzania danymi. Jeszcze lepsze efekty możemy uzyskać decydując się na rozwiązania chmurowe.
Warstwy danych
W zależności od profilu użytkownika, istnieje możliwość przygotowania warstw danych, które mogą być wykorzystane do raportowania oraz zaawansowanej analityki w poszczególnych obszarach operacyjnych przedsiębiorstwa.
Skonsolidowany widok danych
Narzędzia ETL pozwalają na integrację informacji z wielu źródeł i zapewniają skonsolidowany widok danych. W ten sposób analityk ma łatwiejszy dostęp do danych, a obciążenia serwerów przetwarzających dane jest znacząco mniejsze w porównaniu do zastosowania tradycyjnych narzędzi raportujących.
Skalowalność
Nowoczesne narzędzia ETL są projektowane tak, aby w łatwy i przejrzysty sposób budować proces, skalować go i działać w czasie rzeczywistym. Dzięki tej elastyczności zarówno małe przedsiębiorstwa jak i duże korporacje mogą dobrać odpowiednie do potrzeb zasoby sprzętowe i narzędzia do osiągnięcia cyfrowego sukcesu.
Jak wybrać narzędzie ETL?
Zanim przystąpimy do projektowania procesu ETL powinniśmy zadać sobie pytanie czy dostępne na rynku narzędzia ETL spełnią nasze oczekiwania i czy poniesione koszty zwrócą się w możliwie krótkim czasie? Możemy też rozważyć kwestię czy warto zainwestować w zespół programistów, którzy opracują i przygotują od podstaw narzędzie dopasowane do potrzeb naszego przedsiębiorstwa i czy koszty w tym wypadku nie będą zbyt wysokie? Odpowiedź na te pytania daje Ralph Kimball w swojej książce „The Data Warehouse ETL Toolkit”. Uważa on, że jest to zależne od wielkości i stopnia komplikacji hurtowni danych. W przypadku gdy projekt hurtowni jest duży i skomplikowany zakup odpowiedniego narzędzia powinien się szybko zwrócić. Jednakże zwraca on uwagę na to, że własne oprogramowanie narzędzi ETL powinno umożliwić większą elastyczność w projektowaniu procesu ETL. Z biznesowego punktu widzenia, jest to jednak znacząco większy koszt i nakład pracy, który z naszego doświadczenia często jest nieuzasadniony, dlatego po zapoznaniu się z infrastrukturą przedsiębiorstwa skrupulatnie analizujemy i doradzamy w kontekście podjęcia wydajnej kosztowo decyzji.
Projektowanie procesu ETL staje się o wiele bardziej efektywne jeśli utworzymy Logiczną Mapę Danych, czyli mapowanie pomiędzy danymi źródłowymi, a hurtownią danych. Logiczna Mapa Danych opisuje także transformacje konieczne do przeniesienia danych z systemu źródłowego do systemu docelowego.
Proces ETL- jakie rozwiązania oferują dostawcy?
Mamy dwa rodzaje dostępnych Narzędzi ETL: komercyjne i Open-Source (darmowe). Do pierwszej, komercyjnej grupy należą:
SQL Server Integration Services (SSIS) firmy Microsoft
Informatica PowerCenter
Oracle Warehouse Builder (OWB)
Oracle Data Integrator (ODI)
SAP Business Objects Data Integrator (BODI)
Syncsort DMExpress
IBM InfoSphere DataStage
SQL Warehouse Tool firmy IBM (element IBM InfoSphere Warehouse)
Pervasive Data Integrator
SAS Data Integration Studio
Do drugiej grupy spośród tych najbardziej znanych należą:
Pentaho Data Integration
Talend Open Studio
Apatar
CloverETL
Scriptella ETL
ETL Solutions IBM dB2 Universal Database
Przedstawimy teraz charakterystykę dwóch najbardziej popularnych w obszarze biznesu narzędzi wspierających tworzenie systemów ETL:
Microsoft SQL Server Integration Services
Narzędzie to umożliwia ekstrakcję plików płaskich, XML, XLSX, a także umożliwia połączenia do obiektów ActiveX Data Objects (ADO), do źródeł danych za pomocą .NET provider, konta Data Lake Analytics, konta Azure Data Lake Storage, klastra Azure HDInsight, konta Azure Storage, klastra Hadoop, Web serwera za pomocą HTTP, źródła OData, bazy danych z zastosowaniem specyfikacji Open Database Connectivity (ODBC), SAP BW i wielu innych źródeł danych.
Przykładowe procedury transformacji ETL:
Podział, kopiowanie i łączenie wierszy oraz wykonanie operacji przeszukiwania,
Aktualizacja wartości kolumn i tworzenie nowych kolumn z zastosowaniem transformacji,
Wykonanie operacji takich jak czyszczenie danych, eksploracja tekstu, przetwarzanie zapytań predykcyjnych,
Tworzenie nowych wierszy zawierających wartości zagregowane lub posortowane, przykładowe dane, lub dane po transformacji i w postaci pierwotnej (pivoted and unpivoted),
Wykonanie zadań takich jak eksportowanie i importowanie danych, dodanie informacji z audytu, aktualizacja wolno zmieniającego się wymiaru.
Oprócz tego dostarcza również transformacje umożliwiające rozdzielenie przetwarzanych danych na kilka strumieni, połączenie kilku osobnych strumieni w jeden. Umożliwia to między innymi poddanie danych wydobytych z jednego źródła różnym transformacjom, zależnie od spełnienia przez nie określonego warunku lub zestawu reguł.
Oracle Warehouse Builder
Narzędzie służy do projektowania i budowy hurtowni danych dostarczonej wraz z bazą danych Oracle. Zaletą Oracle Warehouse Builder jest to, że może być uruchomiony na systemach Linux/Unix, Solaris a także Windows. Oracle Warehouse Builder umożliwia bezproblemowe podłączenie do kilku systemów zarządzania bazami danych. Są to między innymi: IBM DB2, Microsoft SQL Server, Sybase, Informix. OWB obsługuje również źródła ODBC. Wady: podłączenie do źródeł innych niż Oracle jest skomplikowane i pracochłonne; zawiera znacznie mniej komponentów niż narzędzie Microsoftu. Zalety: możliwość utworzenia własnych, skomplikowanych mapowań, pisanie własnych skryptów, możliwość tworzenia zadań profilowania danych oraz tworzenie zasad danych.
Opiszemy jeszcze narzędzie Open-Source Pentaho Data Integration
Pentaho Data Integration
Jest to narzędzie wspierające tworzenie procesów ETL. Narzędzie to oferuje wbudowane sterowniki do 28 źródeł danych, między innymi SAP, Sybase, MS SQL, MySQL, PostgreSQL, Oracle a nawet AS/400 czy IBM DB2. Edytor graficzny przeznaczony do projektowania procesów ETL oferuje szeroki wybór elementów operujących na danych: sortowanie, usuwanie duplikatów, elementy mapujące i aktualizacja wolno zmieniającego się wymiaru. Wady: niepełna dokumentacja, błędy w interfejsie kreatorów, nie w pełni działające komponenty – ze względu na charakter narzędzia (oprogramowanie otwarte)!
Funkcje narzędzia transformacji:
Add checksum – dodaje sumę kontrolną do wierszy;
Calculator – dodaje nowe pole na podstawie prostych obliczeń;
Replace in string – zamienia występujące w ciągu znaków słowo na inne;
Split fields – rozdziela jedno pole na kilka;
Unique rows – usuwa duplikaty z przetwarzanych danych;
Value Mapper – mapuje wartość pola na inną;
Analytic Query – wykonuje zapytanie analityczne na dostarczonych danych.
Chętnie odpowiemy na pytania w tym temacie. Pomożemy w wyborze odpowiednich narzędzi i zaprojektowaniu procesu ETL, a także podejmiemy się opieki wdrożeniowej i powdrożeniowej.
