EDW #6: Stage

https://xpil.eu/FFk

Ten wpis należy do serii wpisów poświęconych architekturze hurtowni danych.

Tydzień temu omówiliśmy sobie warstwę systemów źródłowych, dziś czas na warstwę Stage.

Jak napisałem na samym początku tej serii, każda z warstw w DW pełni jakąś konkretną, dobrze zdefiniowaną funkcję. W przypadku Stage funkcją tą jest przechowywanie dokładnej kopii danych źródłowych, dzięki czemu możemy z nimi eksperymentować, kombinować, analizować pod kątem dalszej użyteczności w DW i tak dalej.

Począwszy od warstwy Stage aż po przedostatnią, szóstą warstwę (czyli Data Marts), wszystkie dane naszej DW będą przechowywane w rozmaitych tabelach w bazie (bądź w bazach) danych. Stąd też wymóg, żeby każdy system źródłowy był w stanie udostępnić nam dane w postaci tabelarycznej, bądź też "łatwo tabelaryzowalnej", na przykład XML czy JSON.

Nazewnictwo tabel

Nazwy tabel w warstwie Stage budujemy w następujący sposób:
{stały_prefiks_stage}_{kod_systemu_źródłowego}_{nazwa_tabeli_w_systemie_źródłowym}

Przykładowa nazwa tabeli Stage, do której będziemy ładować dane z tabeli Pracownicy z systemu HR:
STG_HR_PRACOWNICY
gdzie:
- STG to stały prefiks nadawany każdej nazwie tabeli w warstwie Stage
- HR to kod systemu źródłowego (z tabeli SOURCE_SYSTEMS - patrz poprzedni wpis w serii)
- PRACOWNICY to nazwa tabeli w systemie źródłowym

Prefiks STG można zastąpić dowolnym innym, na przykład STAGE albo nawet S - nie ma tu jakiejś żelaznej reguły, ważne tylko, żeby prefiks w jakiś sposób kojarzył się z warstwą Stage. Nie jest on wymagany z przyczyn technicznych, a psychologicznych - budując procesy ETL programiście łatwiej się będzie zorientować, jeżeli każda z tabel w tej warstwie będzie miała stały prefiks. Krótszy prefiks oznacza odrobinę krótsze zapytania SQL, ja zawsze wybieram prefiksy jednoznakowe.

Użycie skrótu nazwy systemu w nazwie tabeli w Stage ma dwie zalety: po pierwsze unikniemy zduplikowanych nazw tabel w sytuacji, kiedy dwa różne systemy źródłowe mają tabelę o tej samej nazwie, a po drugie wszystkie tabele z danego systemu źródłowego będą wyświetlone koło siebie, o ile tylko posortujemy je alfabetycznie.

Ponieważ zależy nam na dużej wydajności i krótkim czasie ładowania danych (czytanie danych z systemu źródłowego obciąża ten system), nie wykonujemy na tym etapie żadnych przekształceń danych. Typy danych w poszczególnych kolumnach powinny być kopiami typów z systemu źródłowego lub - jeżeli ich nie znamy - być ustawione na NVARCHAR(MAX), w celu minimalizacji błędów konwersji danych. Pamiętajmy, że głównym celem tej warstwy jest przechowywanie kopii danych, nic ponadto.

Dane historyczne

Kolejna sprawa, to przechowywanie danych historycznych. Tu najpierw należy zadać sobie pytanie, czy dane do Stage ładujemy przyrostowo, różnicowo czy też wczytujemy całość danych za każdym razem.

Najprostszym od strony implementacji jest oczywiście każdorazowe ładowanie całości danych: kasujemy zawartość tabeli w Stage a następnie ładujemy do niej wszystkie dostępne w danym momencie dane ze źródła. To w najprostszej wersji dwie linijki kodu SQL oraz gwarancja, że dane będą zawsze poprawne. Wadą takiego rozwiązania jest to, że jeżeli ilość danych jest znaczna (miliony, miliardy rekordów), a dane między kolejnymi ładowaniami danych zmieniają się nieznacznie, wówczas niepotrzebnie ładujemy każdorazowo te same dane historyczne, marnując zasoby serwera.

Drugie, nieco lepsze podejście, to ładowanie danych przyrostowe: "dogrywamy" tylko te dane, których wcześniej nie było w systemie źródłowym. To jest wszakże możliwe tylko wtedy, jeżeli dane u źródła nie zmieniają się (a tylko dopisywane są nowe rekordy, na przykład tabele logujące zdarzenia, albo tabela z fakturami, chociaż tu już trzeba ostrożnie). W wariancie "przyrostowym" potrzebujemy dodatkowej tabeli kontrolnej przechowującej identyfikator ostatnio załadowanego rekordu (o ile takowy istnieje), co już nieco komplikuje proces, ale daje potencjalnie ogromne korzyści na wydajności.

Trzecia metoda to ładowanie danych różnicowe - tutaj ładujemy do Stage nowe dane, a także dane, które od poprzedniego razu uległy zmianie. Metod jest kilka: sumy kontrolne, log transakcyjny silnika bazy danych i tym podobne. Jest to rozwiązanie najbardziej uniwersalne z trzech tu wymienionych, ale też wymaga najwięcej przygotowań, żeby działało poprawnie, szybko i bezawaryjnie.

Na razie przyjmę, że skorzystamy z metody pierwszej, czyli każdorazowo kasujemy wszystkie dane z tabeli w Stage, a następnie wypełniamy ją wszystkimi danymi aktualnie dostępnymi w źródle. Być może w wolnej chwili opiszę szczegóły implementacji dwóch pozostałych metod, ale to już innym razem.

Indeksy

Bardzo istotną sprawą jest, aby tabele w Stage podczas ładowania danych nie miały żadnych indeksów - zagwarantuje to najkrótszy możliwy czas zapisu danych do bazy. Wyjątkiem jest sytuacja, w której system źródłowy serwuje nam dane z kluczem unikalnym, w kolejności wartości tego klucza - wówczas możemy sobie pozwolić na "luksus" założenia indeksu klastrowanego na tej samej kolumnie w tabeli Stage.

Po załadowaniu danych do tabeli w Stage możemy następnie utworzyć niezbędne indeksy - pamiętajmy, że dane w Stage będą następnie wykorzystane do dalszego ładowania DW, więc rozsądne ich poindeksowanie może pomóc. Starajmy się jednak w miarę możliwości unikać indeksów unikalnych - nigdy nie mamy gwarancji, że dane u źródła są na tyle unikalne, na ile nam się wydaje.

https://xpil.eu/FFk

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]

Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.