MSCW jako proces normalizacji

Procesem normalizacji nazywa się proces, który przekształca dane źródłowe w ustandaryzowany format, nazwy oraz wartości bazując na pewnych regułach. Dane wyjściowe uzyskujemy z danych wejściowych oraz wykonanych regułach na tych danych. W tym przypadku będzie to normalizacja MSCW:

Reguły MSCW są regułami, które zapewniają istotne informacje (lub ich brak) w danych wyjściowych. Zapewne, nie świadomie korzystasz z tych reguł, gdyż są bardzo popularne, ale bardzo rzadko nazywa się je. Są bardzo ważne w bezpieczeństwie przesyłanych danych.

Znaczenie skrótu MSCW wyjaśnia wszystko:
Must - pole musi być w danych wejściowych oraz musi być sprawdzone i zwalidowane
Should - pole musi być w danych wejściowych, a jak nie ma wartości to zostanie zastąpione przez inną (być może domyślną) wartością
Could - jeżeli pole będzie w danych wejściowych, to te pole będzie procesowanie dalej
Won't - jeżeli pole będzie w danych wejściowych, to te pole będzie zablokowane

Przykładem normalizacji MSCW jest proces rejestracji użytkownika na stronie internetowej. Po rejestracji, dostępne informację dla innych użytkowników strony są ograniczone do podstawowych pól:

Normalizacja MSCW występuje również w dużych projektach ETL. Zawsze powinniśmy wiedzieć jakie dane powinny być procesowane, a jakie powinny być ukryte.

Enkapsulacja widoku raportu ETL

I znów post z cyklu wstęp do ETL. Tym razem opowiem Ci o końcówce procesu, która jest tą przyjemniejszą częścią. Wartość procesu ETL można ocenić na podstawie jego wizualizacji wyników. Na ogół cały proces ETL jest niewidoczny dla użytkownika. Wygenerowany raport z procesu ETL jest jedynym etapem w cyklu ETL, gdzie managerowie wyższego szczebla mogą dokonać oceny tego cyklu. Aby dane mogły być odpowiednio przedstawione w raporcie to wygląd tych danych musi być wielokrotnie przekształcany.

Na początku dane były w źródłach, a następnie dzięki procesowi ETL, dane te zostają przekształcone i załadowane do miejsca docelowego. Z wygenerowanych danych tworzony jest model raportu, w którym przedstawiamy jakie dane są potrzebne do wyświetlenia. Oczywiście nie wszystkie informacje powinny być przedstawiane. Widok raportu (report view) składa się z modelu raportów oraz formy przedstawiania tego raportu. Do wyglądu formy zaliczają się takie modyfikacje wyświetlanych danych jak :
- sortowanie
- filtrowanie
- grupowanie
- format wyświetlania wartości (np. dla daty)

Poniższy rysunek przedstawia przejście z surowych danych do wyglądu raportu:

Monitorowanie procesu ETL

Jak przy każdym projekcie, wcześniej czy później zespół dochodzi do wniosku, że potrzebne jest narzędzie/metody/procesy do monitorowania działania projektu. Chciałbym Ci przedstawić przykład monitorowania procesu ETL.


Proces ETL może być bardzo skomplikowany i niektóre zadania mogą nie działać poprawie. W tym celu dokonuje się logowania błędów. Logowanie pozwala na szybszą diagnozę, aby wywnioskować, co mogło wywołać błąd. Im więcej loguje się zdarzeń występujących w procesie ETL tym diagnoza jest dokładniejsza, ale każde logowanie wymaga dodatkowej przestrzeni pamięci. Same logowanie błędów nie wystarcza.

Oprócz logowania zdarzeń wymaga się narzędzia, które mogłoby sprawdzać różne części całego procesu ETL. Sprawdzenie połączenia do różnych zasobów jest dokonywana za pomocą mechanizmu pulsacji (heartbeat), które co dany interwał czasowy sprawdza zasób. Jeżeli dany zasób nie jest dostępny to wywoływany jest alarm (alert). Niektóre zasoby procesu ETL nie muszą być sprawdzane za każdym razem, a wystarcza, że zostaną sprawdzone raz przy uruchomieniu monitora (single alert). Monitor może sprawdzać czy istnieje połączenie do danych źródłowych. Na przykład, monitor sprawdza czy jest połączenie z bazą danych. Czasami samo sprawdzenie schematu bazy danych nie wystarcza i należy sprawdzić format zapisanych danych. W projektach rozproszonej odpowiedzialności, dane w bazie danych są zmieniane i zmiana formatu może spowodować nie wyekstraktowanie wszystkich danych lub co jest gorsze – ekstraktownie błędnych danych. Takie sprawdzenie ostrzeże nas przed uruchomieniem proces ETL. Dodatkowo możemy sprawdzić czy mamy dostęp do wymaganych plików oraz czy jest możliwość dostępu do sieci wewnętrznej. Sprawdzenie dostępu do sieci wewnętrznej nie tylko pozwoli nam na dostęp do danych referencyjnych, ale mamy możliwość sprawdzenia czy nie mamy problemów z połączeniem sieciowym. Czasami baza danych lub pliki są dostępne z jednego komputera (z danej podsieci), ale ten zasób może być niedostępny dla servera (z innej podsieci). Oprócz monitorowania danych wejściowych to dane wyjściowe (targets) również są monitorowane. Sprawdza się czy jest możliwość połączenia się do hurtowni danych (lub innego docelowego miejsca przechowywania danych).

Można monitorować komunikację w procesie ETL. Poszczególne komponenty ETL mogą komunikować się ze sobą za pomocą kolejki komunikatów (message queues) (lub przez Interprocess Communications). Każda kolejka jest wypełniana (produkcja) informacjami z jednego komponenty, a następny komponent wykorzystuje (konsumpcja) te informacje do dalszego działania. Jeżeli kolejka nie jest konsumowana to wiadomo, że następny komponent nie został wystartowany. Oprócz konsumpcji komunikatów sprawdzana jest ilość tych komunikatów. Jeżeli ilość jest za mała lub za duża to jeden z komponentów musiał wygenerować błąd lub istnieją duplikaty informacji.


Poniżej jest zawarty rysunek z przykładowym schematem monitorowania procesu ETL:

Typy transformacji pól w ETL

Dalszy ciąg krótkiego wprowadzenia do ETL. Tym razem chciałbym Ci troszkę więcej powiedzieć o transformacji, a dokładnie o transformacji elementów ściąganych z źródła,


Transformacje pojedynczych elementów w procesie ETL dzielą się na transformacje pojedynczego pola oraz transformacje wielu pól. Transformacje pojedynczego pola są bardzo proste. Najczęściej jest to pewna modyfikacja starej wartości w nową wartość. Oprócz wartości możemy zmienić sposób prezentacji tej wartości. Można np. zamienić stopnie Fahrenheita na stopnie Celisiusza. Takie zamiany nazywają się transformacjami algorytmicznymi (algorithmic transformation), które wymagają obliczeń matematycznych.

Szczególnym przypadkiem transformacji pojedynczego pola jest transformacja wymagająca dodatkowej tabeli z odnośnika wartości (table lookup). Poniżej przedstawiona jest transformacja, która zamienia wartość „code” ze źródła na wartość „name”, wykorzystując przy tym dodatkową tabelę z wartościami. Taka transformacja może zamieniać skróty państw na całe nazwy państw. Głównym zadaniem tej transformacji jest zwiększenie czytelności docelowych danych.

Wśród transformacji wielu pół mamy transformacje „wiele do jeden” (zapisuje się w postaci M:1). Modyfikacja ta ma za zadanie scalanie wartości z różnych pól i zapisanie ich w jednym polu. Taką transformację stosuje się, w celu identyfikacji danego elementu. Przykładem może być tworzenia unikatowego klucza, który składa się z nazwy produktu, producenta oraz numeru dodatkowego.

Drugim typem transformacji wielu pól jest transformacja z jednego pola do wielu pól (1:M). Przykładem takiej transformacji może być w sytuacji kiedy wartość źródła składała się z wielu zmiennych. Powodem, dla którego przechowuje się kilka wartości w jednym polu jest wydajność jej metody archiwizacji w bazie danych. Jednak przy takiej transformacji istnieje niebezpieczeństwo zmiany formatu przechowywania wartości w źródle co może prowadzić do zakłócenia przebiegu procesu.

Oczywiście istnieje transformacja "wiele do wielu", ale jest to połączenie dwóch powyższych typów transformacji.

Data Marty - uszczuplona hurtownia danych

Wcześniej pisałem o procesie ETL i w nim wspominałem o Data Martach jako zbiorze załadowanych danych. Data Marty są nazywane tematycznymi hurtownie danych. Są tworzone w celu zapewnienia wsparcia procesu podejmowania decyzji osobom odpowiedzialnym za konkretny obszar biznesowy. W przypadkach, gdy tylko część danych jest poddawana analizie, warto rozważyć użycie data martów. Data Mart jest generowany na bazie hurtowni danych i zawiera zagregowane dane zorientowane na jeden wybrany temat, które są często wyświetlane oraz łatwo i szybko dostępne dla użytkowników.

Hurtownia danych operuje na poziomie wszystkich dostępnych danych, przy czym tematyczna hurtownia danych jest używana z reguły przez jeden obszar danych w niej zawartych dotyczących jednego konkretnego tematu biznesowego. Dla banków może to być obszar produktów kredytowych lub obszar transakcji kontraktów krótkoterminowych

Typową i najczęściej spotykaną architektura w korporacjach jest jedna globalna hurtowna danych i bezpośrednio zależne i czerpiących z niej dane Data Marty.

Powyżej przedstawiony jest sposób działania Data Martów. Dostęp do nich mają tylko wyznaczone osoby (lub grupy ludzi). Dział raportujący ma dostęp tylko do danych potrzebnych do tworzenia i przeglądania raportów. Dział analiz ma dostęp do Data Martów do analizy oraz raportów. Natomiast grupa administracyjna ma dostęp do wszystkich obszarów danych, gdyż zarządzają tymi danymi.

Kluczowym problemem jest utrzymanie spójności z hurtownią danych. Najważniejsze te kwestie to definicja danych, sposób aktualizacji oraz zarządzanie danymi. Sposobem na ominięcie części problemów jest tworzenie data martu niezależnego od hurtowni danych. Zdarza się to najczęściej, gdy do utworzenia data martu wymagane jest dodatkowe źródło danych spoza hurtowni. Wtedy tematyczne hurtownie danych są zasilane i zarządzane przez procesy ETL (Extract-Translate-Load). Należy jednak pamiętać o tym, że tego typu architektura jest narażone na ryzyko niespójności w rozumowaniu danych.

Najczęstsze powody tworzenia data martów to:
- większa denormalizacja
- dane z zagregowanymi danymi
- dane ze specyficznego okresu czasowego istnienia tych danych ( np. wartość akcji z poprzedniego roku)
- dane dostępne tylko dla specyficznej grupy (dane tylko dla analityków, testerów lub administratorów)

Co to jest ETL ?

Jak zaczynałem swoją pracę przy bardzo dużym projekcie ETL, to na początku nie wiedziałem co to jest i do tej pory nie ma zbyt dużo materiałów na ten temat. Przedstawię Ci krótki wstęp do ETL.

Skrót ETL oznacza ekstrakcję (extract), transformację (transform) i ładowanie (load). Jest to metoda procesowania danych. ETL składa się z 3 etapów:
1) ekstrakcja danych ze źródła danych - proces ten determinuje bazowe źródła dla hurtowni danych.
2) transformacja danych - etap ten ma za zadanie standaryzować dane, filtrację oraz sprawdzanie reguł biznesowych.
3) ładowanie danych do docelowego zbioru danych - etap zapisu wygenerowanych danych.

Na rysunku przedstawiony jest ogólny schemat działania procesu ETL. Do danych wejściowych (source) należą aplikacje (m.in. bazy danych), dodatkowe pliki oraz inne pomocne dane (m.in. metadane). Proces ETL przetwarza te dane i zapisuje je do wyznaczonego zbioru danych (target). Docelowy zbiór danych jest określony przez projektanta i może to być hurtowania danych, data mart lub zbiór plików zapisanych na serwerze. W praktyce 80% czasu spędzonego na rozwijaniu systemu BI jest poświęcone na rozwijanie procesu ETL. Jest to najbardziej czasochłonne zadanie przy budowaniu hurtowni danych i jednocześnie wymaga najwięcej wiedzy i doświadczenia. Bardzo często proces ETL nazywany jest procesem integracji danych, natomiast narzędzia ETL nazywane są platformą integracyjną.

Pierwsza częścią - ekstrakcja danych ze źródła danych - jest to najbardziej wymagający etap całego ETL. Większość hurtowni danych integruje dane z różnych źródeł danych. Każde źródło danych może się różnić organizacją danych czy sposobem dostępu. Najczęściej spotykanym źródłem danych są relacyjne bazy danych oraz pliki w formacie CSV. Celem ekstrakcji danych jest przekonwertowanie danych do ujednoliconego formatu, który jest przystosowany dla procesu transformacji.

Etap transformacji jest częścią opisującą przepływ danych, który zawiera zaaplikowane reguły biznesowe oraz szczegółowy opis kalkulacji i mapowania między danymi. Niektóre dane wymagają bardzo mało, a nawet brak modyfikacji danych. W większości przypadkach etap ten składa się pezynajmniej z jednego z poniższych zadań:
- wyselekcjonowanie tylko niektórych kolumn danych z tabeli
- transformacja sposobu zapisu danych. Jeżeli dana jest zapisana w postaci binarnej (1-0) to można zapisać słownie (Tak – Nie)
- transformacja formatu zapisu danych. Część dat jest zapisana w formacie skoordynowanego czasu uniwersalnego (UTC) lub czasu lokalnego
- wyliczenie nowej wartości, która jest zależna od wartości innych zmiennych
- sortowanie według danego kryterium
- łączenie danych z innymi danymi z innych źródeł - wzbogacanie danych
- rozdzielenie kolumny na kilka kolumn
- usuwanie zduplikowanych danych
- agregacja danych. Można wprowadzić data rollup ( wiersz podsumowujący dane)
- tworzenie unikatowego klucza (surrogate key) dla wszystkich elementów
- dokonywanie transpozycji oraz tworzenie tabeli przestawnej. Jest to proces, w którym kolumny zamieniane są na wiersze.

Faza ładowania danych odnosi się do zapisu do specjalnego obiektu docelowego (najczęściej jest to hurtowna danych). Obiekt docelowy jest zależny od wymagań danego projektu. Najczęściej istniejące dane w hurtowni danych są nadpisywane przez nowo wygenerowane. Inne hurtownie danych są czyszczone i na nowo załadowane, ale takie rozwiązanie niesie ze sobą problem „pustych danych”, gdyby wystąpił problem w etapie ładowania danych.