Trochę o hurtowniach danych

https://xpil.eu/1yMtd 📋

Dzisiejszy wpis dotyczy starego jak komputery problemu ładowania danych do hurtowni z wielu różnych źródeł.

Załóżmy, że mamy trzy różne systemy: SA, SB i SC. Z każdego z nich chcemy ładować dane o klientach. Docelową tabelą będzie tabela KLIENT w hurtowni danych. A co dzieje się po drodze?

Zobaczmy.

Niech system SA ma klienta w takiej postaci:
Tabela: KLIENCI
Kolumny:KLIENT_ID, KLIENT_NAZWA, KLIENT_KOD, KLIENT_KATEGORIA, KLIENT_NR_VAT

W systemie SB klient jest reprezentowany jako:
Tabela: KLIENT
Kolumny: ID_KLIENTA, NAZWA_KLIENTA, KOD_KLIENTA, VAT_KLIENTA, PRIORYTET_KLIENTA

Wreszcie w systemie SC mamy:
Tabela: NASI_KLIENCI
Kolumny: NAZWA, KOD, NUMER_POLISY, ADRES, TELEFON, AKTYWNY_TN

Jak widać, każdy z systemów ma inne podejście do tego w jaki sposób dane klienta są reprezentowane. Załóżmy teraz, że docelowa tabela KLIENT w naszej hurtowni ma następujące kolumny:

KLIENT_ID, KLIENT_KOD, KLIENT_NAZWA, KLIENT_VAT, SRC_SYS_BITMASK

KLIENT_ID jest kluczem głównym, KLIENT_KOD - kluczem alternatywnym (biznesowym), a KLIENT_NAZWA i _VAT - dodatkowymi atrybutami. O kolumnie SRC_SYS_BITMASK napiszę na końcu.

W jaki sposób zorganizować ładowanie danych o klientach z systemów SA, SB i SC do tabeli docelowej?

Po pierwsze, w większości hurtowni danych można wydzielić obszar STAGING, do którego kopiowane są dane z systemów zewnętrznych, na ogół w postaci 1:1 (a więc każda tabela w obszarze STAGING odpowiada jednej i dokładnie jednej tabeli źródłowej w jednym systemie źródłowym). Ważne jest przyjęcie jakiegoś standardu nazewnictwa, który w łatwy sposób pozwoli jednoznacznie ustalić skąd dana tabela przychodzi. Przyjmijmy następujący standard:

STG_<SYSTEM>_<TABELA>

Przy takim założeniu, tabela z danymi klientów z systemu SA będzie nazywać się STG_SA_KLIENCI, a z pozostałych systemów odpowiednio STG_SB_KLIENT oraz STG_SC_NASI_KLIENCI.

Ponadto warto sobie trzymać osobną listę wszystkich systemów źródłowych, każdemu z nich przypisując kod tekstowy (czyli właśnie SA, SB i SC) oraz identyfikator numeryczny, będący potęgą dwójki (dlaczego? zaraz się okaże).

Po utworzeniu tabel STAGING oraz załadowaniu ich danymi, kolejnym krokiem jest załadowanie tych danych do obszaru LANDING.

Tabele w obszarze LANDING mają za zadanie zgromadzenie danych z poszczególnych systemów w jednym miejscu. Tak więc każdej tabeli docelowej (u nas: KLIENCI w hurtowni) przypisujemy dokładnie jedną tabelę w obszarze LANDING. Tu również trzeba przyjąć jakiś standard nazewnictwa - dla potrzeb niniejszego wpisu przyjmijmy, że jest to L_<TABELA>. Czyli tutaj: L_KLIENT.

Tabela LANDING powinna mieć wszystkie niezbędne kolumny ze wszystkich systemów źródłowych. Co to znaczy "niezbędne"? To znaczy te, które mają wpływ na wartości w tabeli docelowej. W naszym przypadku tabela L_KLIENT będzie miała następujące kolumny:

KLIENT_ID_SA, KLIENT_NAZWA_SA, KLIENT_KOD_SA, KLIENT_NR_VAT_SA, ID_KLIENTA_SB, NAZWA_KLIENTA_SB, KOD_KLIENTA_SB, VAT_KLIENTA_SB, NAZWA_SC, KOD_SC, AKTYWNY_TN_SC, SRC_ID, SRC_SYS_ID, SRC_SYS_CD

Kilka uwag do powyższej tabeli L_KLIENT:
1. Każda kolumna tabeli LANDING (z wyjątkiem kilku kolumn technicznych na końcu) ma w nazwie kod systemu źródłowego - dzięki temu unikamy ryzyka zdublowanych nazw kolumn.
2. W kolumnie SRC_ID zapisujemy wartość unikalnego identyfikatora rekordu w danym systemie źródłowym (na ogół jest to kolumna znakowa, ponieważ w różnych systemach unikalność może być na jednej lub wielu kolumnach). Kolumna SRC_ID wraz z kolumną SRC_SYS_CD tworzą klucz unikalny tabeli LANDING.
3. W dwóch ostatnich kolumnach zapisujemy dodatkowo identyfikator systemu źródłowego (potęga dwójki) oraz jego kod (na ogół krótki, 2-5-znakowy) . Identyfikator przyda nam się później, a kod warto mieć pod ręką tak czy siak.
4. W przypadku większej ilości danych, istotne jest również, żeby tabela LANDING miała partycje dla każdej wartości SRC_SYS_CD, dzięki czemu możliwe będzie równoległe ładowanie jej danymi z różnych systemów.

Jak już załadujemy dane do tabeli LANDING (tu: L_KLIENT), kolejnym krokiem jest umieszczenie ich w tabeli XREF (cross-reference czyli po naszemu "referencje skrośne", aczkolwiek nie słyszałem nigdy, żeby ktoś używał polskiej wersji tego pojęcia). Tabela XREF jest prawie identyczna z tabelą LANDING, dochodzi tylko dodatkowa kolumna ze współdzielonym identyfikatorem:

X_KLIENT: KLIENT_ID, KLIENT_ID_SA, KLIENT_NAZWA_SA, KLIENT_KOD_SA, KLIENT_NR_VAT_SA, ID_KLIENTA_SB, NAZWA_KLIENTA_SB, KOD_KLIENTA_SB, VAT_KLIENTA_SB, NAZWA_SC, KOD_SC, AKTYWNY_TN_SC, SRC_ID, SRC_SYS_ID, SRC_SYS_CD

Ten współdzielony identyfikator (tu: KLIENT_ID) nie jest unikalny, służy bowiem do tego, żeby zidentyfikować rekordy pochodzące z różnych systemów jako reprezentujące ten sam obiekt (tu: tego samego klienta). Dzięki temu po wypełnieniu tabeli XREF wiemy które rekordy źródłowe dotyczą tych samych klientów, a które - różnych.

Ładowanie LANDING => XREF jest najbardziej wymagającym krokiem w procesie, ponieważ wymaga ustalenia reguł dopasowujących dane. Tutaj jest pole do popisu dla analityków (mogą definiować algorytmy dopasowań) i programistów (żeby te algorytmy były poprawnie i wydajnie zaimplementowane). Czym prostsze reguły dopasowujące, tym lepiej. Przykładem może być taka reguła:

"Uznajemy, że klient w systemie SA i SB to ten sam klient, jeżeli KLIENT_NAZWA = NAZWA_KLIENTA, z pominięciem wielkości znaków oraz znaków niebędących literą lub cyfrą."

Jak już poustalamy i zaimplementujemy reguły dopasowań, oraz wypełnimy tabelę XREF danymi ze wszystkich systemów źródłowych, ostatnim krokiem jest załadowanie danych z tabeli XREF do tabeli docelowej (tu: KLIENT).

Dane powinny być ładowane z uwzględnieniem priorytetów (zwanych czasem "regułami zaufania") poszczególnych systemów, na poziomie pojedynczych kolumn. A więc należy zdefiniować które systemy są bardziej zaufane, a które mniej, dla poszczególnych kolumn. Przykład takiej reguły zaufania:

"Numer VAT klienta powinien pochodzić z systemu SB. Jeżeli system SB nie dostarcza numeru VAT, używamy informacji z systemu SA."

Implementacja takich reguł to na ogół kombinacja operatorów SUM, DISTINCT, CASE i COALESCE.

Czemu SUM i DISTINCT?

Ano właśnie tutaj dochodzimy do tego w jaki sposób będą użyte indentyfikatory systemów źródłowych (potęgi dwójki).

Otóż ładując dane z XREF do hurtowni, chcemy zachować możliwość prześledzenia skąd dany rekord pochodzi. Jeżeli zsumujemy unikalne wartości SRC_SYS_ID, dostaniemy wartość, która będzie miała "zapalone" jedynki na miejscach odpowiadających systemom źródłowym, z których dany rekord pochodzi.

Załóżmy, że system SA ma ID=1024 (binarnie: 10000000000), SB: 2048 (100000000000) a SC: 4096 (1000000000000). Jeżeli rekord klienta Adam Kowalski pochodzi z systemów SA i SB, suma tych identyfikatorów wyniesie 3072, a więc 0110000000000 - jak widać zapalone są bity odpowiadające 1024 oraz 2048 (czyli SA i SB).

Kolumna SRC_SYS_BITMASK w tabeli docelowej przechowuje właśnie tę wartość. W ten sposób łatwo "wyśledzić" skąd pochodzi każdy rekord w hurtowni. I dlatego też wymagane jest, żeby identyfikator systemu źródłowego był potęgą dwójki.

Jeżeli powyższe podejście wydaje się przekombinowane, proszę sobie wyobrazić sytuację, w której mamy kilkanaście systemów źródłowych oraz kilkadziesiąt tabel docelowych. Jeżeli ponadto założymy, że firma jest na tyle duża, że z tytułu naturalnej rotacji kadr programiści zmieniają się w tempie przynajmniej 10% składu na rok, takie standardowe podejście znacznie skróci czas potrzebny nowemu pracownikowi na "przyuczenie się" do struktury danych - w zasadzie zapoznawszy się z danymi klienta z powyższego przykładu, nowy pracownik bez większych trudności zrozumie logikę ładowania pozostałych encji.

Na zakończenie dodam, że powyższy przykład jest dość uproszczoną wersją rozwiązań stosowanych w rzeczywistości (aczkolwiek dotyka wszystkich jego kluczowych elementów). Możliwe są rozmaite wariacje opisanego procesu. Pomijamy tutaj natomiast całkowicie zagadnienie masteringu danych (zna ktoś polski odpowiednik?), Data Marts oraz paru innych bardziej zawiłych kwestii, o których kiedy indziej.

https://xpil.eu/1yMtd 📋