EDW #8: EDW
Ten wpis należy do serii wpisów poświęconych architekturze hurtowni danych.
Tytuł dzisiejszego wpisu nieco pachnie rekurencją, ale to tylko pozór. Tak wyszło. Albowiem omawiać dziś będziemy najważniejszą z warstw logicznych hurtowni danych, czyli warstwę EDW.
EDW przechowuje dane w postaci znormalizowanej (na ogół 3NF lub prawie-3NF - po szczegóły odsyłam do Wikipedii). Główny model danych (o modelowaniu być może napiszę kilka odcinków w tej serii, a może nawet poświęcę mu osobną serię, bo temat jest niewąski) zawiera właśnie tabele z warstwy EDW. To tutaj mieszkają wszystkie encje naszej hurtowni, w postaci w zasadzie gotowej do konsumpcji przez końcowych odbiorców. Warstwa EDW jest zaufana, ponieważ - o ile tylko mamy w naszej hurtowni danych jakiekolwiek rozwiązanie typu DQ czy MDH - właśnie do niej trafiają dane przefiltrowane przez systemy kontroli jakości danych (w jaki sposób? być może uda mi się poświęcić temu osobny wpis).
*Przy okazji wyjaśniło się, dlaczego przez całą tę serię używam skrótu DW a nie EDW - właśnie po to, żeby się te dwa skróty nie pokitwasiły. *
Nazwy tabel
Tabele w warstwie EDW nazywamy tak, jak nazwane są encje w modelu fizycznym. Czyli: KLIENT, FAKTURA, WPIS, RODZAJ_OPERACJI i tak dalej. Nie ma żadnych prefiksów, żadnych ozdobników itp. Jest samo gęste.
Identyfikatory
Każda tabela w EDW ma unikalny identyfikator numeryczny, wygenerowany w trakcie wypełniania warstwy XREF (o ile encja pochodzi z więcej niż jednego systemu źródłowego) bądź też bezpośrednio na poziomie EDW, jeżeli encja ma tylko jedno źródło (w którym to przypadku warstwy Landing / XREF są pomijane i EDW ładujemy bezpośrednio ze Stage).
Na ogół identyfikatory te generowane są z globalnej (dla całej hurtowni) sekwencji typu BIGINT, dzięki czemu po pierwsze gwarantujemy globalną unikalność każdego identyfikatora w całej hurtowni, a po drugie - co czasem się przydaje, a czasem nie - możemy łatwo stwierdzić, który rekord był wygenerowany wcześniej, a który później. Tak czy siak, unikalny identyfikator rekordu w każdej tabeli w EDW jest zawsze pojedynczą kolumną całkowitoliczbową.
Oprócz tego każda tabela w EDW powinna mieć założony indeks unikalny na kolumnach odpowiadających kluczowi biznesowemu.
Raz jeszcze powtórzę kluczową cechę warstwy EDW: każda tabela w tej warstwie odpowiada dokładnie jednej encji w hurtowni. Jeżeli mamy piętnaście różnych systemów źródłowych dostarczających dane o klientach, w EDW będzie jedna tabela KLIENT, z jednym rekordem dla każdego klienta - nawet jeżeli ten klient istnieje w każdym z piętnastu systemów źródłowych.
Jak się to odbywa?
Uważny Czytelnik zapewne już się domyślił: identyfikator unikalny został już wygenerowany i czeka grzecznie w warstwie XREF. Jedyne, co trzeba teraz zrobić, to wykonać operację MERGE (a więc: zaktualizować rekordy istniejące oraz dopisać nowe, używając numerycznej kolumny ID z warstwy XREF).
Jeżeli dane pochodzą tylko z jednego systemu źródłowego, warstwy Landing i XREF dla danej encji są pomijane i identyfikator generujemy bezpośrednio w EDW, używając kluczy biznesowych wziętych z warstwy Stage.
Jak sobie niejasno przypominamy, przy wypełnianiu danymi warstwy XREF pominęliśmy jeden istotny szczegół, który mieliśmy sobie omówić przy okazji warstwy EDW. Tym "drobiazgiem" jest sposób, w jaki generujemy identyfikatory rekordów w XREF. Otóż zanim wygenerujemy NOWE wartości ID dla rekordów, których jeszcze nie ma w EDW, musimy najpierw skopiować wartości ID ISTNIEJĄCE już w EDW. W tym celu wykonujemy JOIN między warstwami XREF i EDW, po kluczu biznesowym (ponieważ to jest jedyny klucz, na podstawie którego możemy stwierdzić, że dany rekord już istnieje w EDW).
Przykład:
Dajmy na to, że w EDW mamy tabelę KLIENT z jednym rekordem:
ID, NAZWISKO, IMIE, DATA_URODZENIA, NR_VAT
1, Kowalski, Adam, 1963/04/14, AA923875
Dajmy na to, że w tabeli L_KLIENT (warstwa Landing) mamy trzy rekordy:
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
AA923875,Kowalski,Adam,19630414,28346,Kowalski,Adam,AA923875,19630414,NULL,NULL,NULL,NULL,CRM,1
AA923875,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,837465,Kowalski,Adam,AA923875,FIKS,2
AB923498765,Malinowska,Anna,19531203,5234,Malinowska,Anna,AB923498765,19531203,NULL,NULL,NULL,NULL,CRM,1
Widać, że Adam Kowalski został nam podesłany zarówno z CRM-u jak też s FIKS-a. Ale Adama Kowalskiego już mamy w EDW, pod tym rekordem:
ID,NR_VAT, NAZWISKO, IMIE, DATA_URODZENIA,SRC_SYSTEM
17,AA923875,Kowlaski,Adam,19630414,3
Niestety, jak widać, w nazwisku Adama mamy w EDW literówkę ("Kowlaski zamiast "Kowalski").
Ładując dane z Landing do XREF sprawdzimy, czy klient o numerze VAT = "AA923875" już istnieje w EDW i - jeżeli tak - przepiszemy jego ID=17 do XREF, zarówno do rekordu pochodzącego z CRM, jak też do rekordu pochodzącego z FIKS. A więc dane Adama w XREF będą wyglądały o, tak:
ID, 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
17,AA923875,Kowalski,Adam,19630414,28346,Kowalski,Adam,AA923875,19630414,NULL,NULL,NULL,NULL,CRM,1
17,AA923875,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,837465,Kowalski,Adam,AA923875,FIKS,2
Załóżmy, że Anna Malinowska w EDW jeszcze nie istnieje (sprawdzone po VAT_NO, który jest kluczem biznesowym) - zostanie więc dla niej wygenerowany nowy, unikalny identyfikator ID=983274, który trafi do XREF:
ID, 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
983274,AB923498765,Malinowska,Anna,19531203,5234,Malinowska,Anna,AB923498765,19531203,NULL,NULL,NULL,NULL,CRM,1
OK, mamy więc dane w warstwie XREF (tym razem załadowane poprawnie i kompletnie), teraz zgrywamy je, najlepiej operatorem MERGE, do warstwy EDW:
MERGE KLIENT TGT
USING (
SELECT ID
, NR_VAT
, MAX(NAZWISKO) NAZWISKO
, MAX(IMIE) IMIE
, MAX(DATA_URODZENIA) DATA_URODZENIA
, SUM(SRC_SYSTEM_ID) SOURCE_SYSTEMS
FROM X_KLIENT
GROUP BY ID, NR_VAT
) SRC
ON TGT.ID=SRC.ID
WHEN MATCHED THEN UPDATE SET
TGT.NAZWISKO = SRC.NAZWISKO
, TGT.IMIE=SRC.IMIE
, TGT.DATA_URODZENIA = SRC.DATA_URODZENIA
, TGT.SOURCE_SYSTEMS = SRC.SOURCE_SYSTEMS
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (
SRC.ID
, SRC.NR_VAT
, SRC.NAZWISKO
, SRC.IMIE
, SRC.DATA_URODZENIA
, SRC.SOURCE_SYSTEMS
)
Wszystko ładnie i pięknie, z jednym wyjątkiem: w powyższym zapytaniu całkowicie pomijamy priorytety systemów źródłowych i ładujemy wszystkie atrybuty jak leci, agregując je za pomocą operatora MAX(). W ten sposób wprawdzie dane faktycznie trafią do EDW (i poprawimy literówkę, o ile została ona poprawiona w systemie źródłowym), jednak rozwiązanie jest jeszcze niekompletne. Zamiast bowiem agregacji atrybutów między XREF a EDW powinniśmy wziąć pod uwagę priorytety systemów źródłowych i załadować konkretne wartości, a nie wyniki agregacji.
Ale o tym już w następnym odcinku, żeby nie wrzucać wszystkiego do jednego garnka, bo zamiast smacznej i pożywnej zupy wyjdzie nam niestrawna breja.
Komentarze