EDW #7: Landing & XREF

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

Warstwy Landing oraz Cross Reference (w skrócie: XREF) idą zawsze w parze.

Dla dowolnej encji E w naszej hurtowni danych prawdziwe jest jedno z dwóch zdań:

1. E pochodzi z dokładnie jednego systemu źródłowego
2. E pochodzi z więcej niż jednego systemu źródłowego

Warstwy Landing oraz XREF są wykorzystywane w przypadku numer 2, a więc wówczas, kiedy jakaś encja w naszej hurtowni pochodzi w kilku różnych systemów źródłowych.

Załóżmy dla przykładu, że dane o klientach (encja [Klient]) pochodzą zarówno z systemu finansowo-księgowego (kod: FIKS) jak też z systemu zarządzania relacjami z klientem (kod: CRM). Dane o dowolnie wybranym kliencie K mogą istnieć albo tylko w FIKS, albo tylko w CRM, albo w obydwu na raz.

Do DW chcemy załadować JEDEN rekord klienta K. Jak to zrobić?

Właśnie za pomocą warstw Landing / XREF.

Załóżmy, że mamy w Stage dwie tabele klientów:

1. STG_FIKS_KLIENCI (ID, NAZWISKO, IMIE, NUMER_VAT, różne inne kolumny...)
2. STG_CRM_CLIENT_DATA (ID, NAME, SURNAME, VAT_NO, BIRTHDATE, różne inne kolumny...)

Załóżmy dodatkowo, że w obydwu tabelach mamy dostępny jakiś klucz biznesowy wspólny dla obydwu systemów.

Spełnienie tego założenia jest wymagane do poprawnego działania warstw Landing / XREF. Czasami znalezienie wspólnych atrybutów jednoznacznie identyfikujących rekordy po obu stronach jest czasochłonne, a czasem ich po prostu nie ma, i wtedy, jak mawiają Eskimosi, dupa zimna.

Kluczem tym może być na przykład numer VAT. Oprócz tego chcemy też załadować do DW następujące kolumny: NAZWISKO, IMIE, DATA_URODZENIA.

W pierwszym kroku wypełniamy warstwę Landing.

Najpierw tworzymy tabelę L_KLIENT

Prefix L oznacza warstwę Landing

Tabela L_KLIENT ma następujące kolumny:

NR_VAT, NAZWISKO, IMIE, DATA_URODZENIA, CRM_ID, CRM_NAME, CRM_SURNAME, CRM_VAT_NO, CRM_BIRTHDATE, FIKS_ID, FIKS_NAZWISKO, FIKS_IMIE, FIKS_NUMER_VAT, SRC_SYSTEM_CODE, SRC_SYSTEM_ID

Czujny Czytelnik być może już zauważył, że tabela L_KLIENT ma cztery zestawy kolumn:

Zestaw 1: NR_VAT, NAZWISKO, IMIE, DATA_URODZENIA - to kolumny, które chcemy uzyskać na wyjściu całego procesu. Innymi słowy, docelowe atrybuty encji Klient

Zestaw 2: CRM_ID, CRM_NAME, CRM_SURNAME, CRM_VAT_NO, CRM_BIRTHDATE - to kolumny z tabeli STG_CRM_CLIENT_DATA (ale nie wszystkie, tylko te, których potrzebujemy)

Zestaw 3: FIKS_ID, FIKS_NAZWISKO, FIKS_IMIE, FIKS_NUMER_VAT - to kolumny z tabeli STG_FIKS_KLIENCI.

Nazwy kolumn w zestawach 2 i 3 są dodatkowo poprzedzone przedrostkami odpowiadających im systemów źródłowych - to na wypadek, gdyby kolumna o tej samej nazwie istniała w obydwu tabelach Stage.

Zestaw 4: SRC_SYSTEM_CODE, SRC_SYSTEM_ID - kolumny oznaczające system źródłowy, z którego dany rekord pochodzi (tak naprawdę wystarczyłoby samo SRC_SYSTEM_ID, ale dla wygody programistów warto tu poświęcić dodatkową kolumnę na kod znakowy systemu źródłowego)

Wypełniamy tabelę L_KLIENT danymi, w następujący sposób:

1. Kasujemy jej zawartość do zera (TRUNCATE TABLE L_KLIENT)
2. Wstawiamy do niej dane z tabeli STG_FIKS_KLIENCI, wypełniając wszystkie kolumny z wyjątkiem kolumn z zestawu 2 (w kolumny z zestawu 1 wstawiamy odpowiednio wartości z kolumn z zestawu 3 - a więc mamy zduplikowane dane, ale tak ma być). W kolumny zestawu 4 wpisujemy 'FIKS' oraz 1 (zakładam, że system FIKS ma ID=1 w tabeli SOURCE_SYSTEMS)
3. Wstawiamy do niej dane z tabeli STG_CRM_CLIENT_DATA, wypełniając wszystkie kolumny z wyjątkiem kolumn z zestawu 3 (w kolumny z zestawu 1 wstawiamy odpowiednio wartości z kolumn z zestawu 2 - a więc znów mamy zduplikowane dane, jest ok). W kolumny zestawu 4 wpisujemy 'CRM' oraz 2 (zakładam, że system CRM ma ID=2 w tabeli SOURCE_SYSTEMS)

W tym momencie mamy tabelę L_KLIENT wypełnioną danymi o wszystkich klientach z obydwu systemów (FIKS i CRM). Niektórzy klienci powtarzają się, bo pochodzą z obu systemów na raz.

Kolejnym krokiem jest wypełnienie warstwy XREF.

Tworzymy tabelę X_KLIENT.

Prefiks X_ oznacza wartwę XREF - Cross Reference

Tabela X_KLIENT zawiera zestaw kolumn identyczny jak tabela L_KLIENT, z jednym, jedynym wyjątkiem: na samym początku dodajemy tam kolumnę ID, która będzie identyfikatorem (kluczem) technicznym klienta w naszej DW.

Uwaga: kolumna ID nie jest kluczem w tabeli X_KLIENT. Wartość tej kolumny dopiero stanie się kluczem w kolejnej warstwie DW.

Następnie kasujemy zawartość tabeli X_KLIENT i wypełniamy ją danymi z tabeli L_KLIENT w taki sposób, że kopiujemy z niej wszystkie rekordy, a w kolumnę ID wstawiamy kolejne wartości numeryczne ID, takie same dla rekordów mających wspólny klucz biznesowy (w naszym przypadku: numer VAT).

Uwaga: opis powyższego kroku jest na razie niekompletny - pominęliśmy tu jeden ważny element, wymagający danych z warstwy EDW, której jeszcze nie omawialiśmy. Pamiętamy o tym i na razie zostawiamy, jak jest.

Po wypełnieniu tabeli X_KLIENT danymi mamy w niej dokładnie tyle samo rekordów, co w tabeli L_KLIENT, oraz identyczne kolumny, z wyjątkiem dodatkowej całkowitoliczbowej kolumny ID, która ma różne wartości dla rekordów o różnych numerach VAT, a identyczne wartości dla rekordów o identycznych numerach VAT.

Właśnie dopasowaliśmy do siebie dane o klientach z dwóch różnych systemów źródłowych. Każdy klient ma swoje własne, unikalne ID, niezależne od jakiegokolwiek identyfikatora technicznego w systemie źródłowym.

Jeśli chodzi o indeksy, to w tabeli X_ warto założyć indeks unikalny na kolumnach odpowiadających kluczowi biznesowemu w kolumnach z zestawu 1 (patrz wyżej), wraz z kolumną SRC_SYSTEM_CODE (dzięki temu zapewnimy unikalność rekordom pochodzącym z wielu źródeł).

Jesteśmy gotowi do wypełnienia danymi kolejnej warstwy DW, ale o tym dopiero za tydzień.