EDW #8: EDW – ignormatyk

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.

https://xpil.eu/dSK

Leave a Comment Anuluj pisanie odpowiedzi