Hurtownie danych w praktyce

Poniżej znajdziecie Państwo artykuły zawierające zbiór 'dobrych praktyk' związanych z budową hurtowni danych.

01. Optymalizacja hurtowni danych, czyli nie magia a konkretne kroki

02. Zastosowanie sztucznych kluczy w tabelach faktów i wymiarów - w opracowaniu

03. Wyliczanie miar kalkulowanych bezpośrednio po stronie bazy danych.

04. Implementacja kostek OLAP

04. ETL kurs praktyczny

Jak zacząć przygodę z narzędziami ETL?
Wydaje się że najlepiej rozpocząć od tutoriali opisujących podstawowe funkcjonalności narzędzi w których wykonywany będzie projekt. Proponuję przegląd najpopularniejszych narzędzi ETL (podział na popularne i nie popularne jest najbardziej subiektywną częścią bloga, ponieważ zależy od tego jakie narzędzia zna autor):

01. Talend Open Studio (TOS) - w opracowaniu
02. Sql Server Integration Services (SSIS) - w opracowaniu
03. SAS - w opracowaniu

Pierwszą umiejętnością jaką warto nabyć po wyborze narzędzia ETL jest pobranie rekordów z tabel bazy danych.

04. Kontrolki pozwalające na pobieranie danych z bazy danych w Talend Open Studio (TOS) - w opracowaniu
05. Kontrolki pozwalające na pobieranie danych z bazy danych w Sql Server Integration Services (SSIS) - w opracowaniu 
06. Kontrolki pozwalające na pobieranie danych z bazy danych w SAS - w opracowaniu

Kiedy wiadomo jak pobierać informacje z bazy danych, warto nauczyć się łączyć i mapować pobierane rekordy.

07. Mapowanie / joinowanie danych w Talend Open Studio (TOS) - w opracowaniu
08. Mapowanie / joinowanie danych w Sql Server Integration Services (SSIS) - w opracowaniu 
09. Mapowanie / joinowanie danych w SAS - w opracowaniu

Dane w odpowiednio przetworzonej formie najczęściej zapisujemy w tabeli docelowej.

10. Zapisywanie danych w Talend Open Studio (TOS) - w opracowaniu
11. Zapisywanie danych w Sql Server Integration Services (SSIS) - w opracowaniu
12. Zapisywanie danych w SAS - w opracowaniu

Hurtownie danych posiadają specjalistyczne mechanizmy pozwalające na przechowywanie danych historycznych. Poniżej opis jak korzystać z mechanizmów SCD, w omawianych narzędziach.

13. SCD w Talend Open Studio (TOS) - w opracowaniu
14. SCD w Sql Server Integration Services (SSIS) - w opracowaniu
15. SCD w SAS - w opracowaniu

Dla kogo piszę tego bloga?

Witam na blogu poświęconym tematyce szeroko pojętego Business Intelligence

  Przeszukując 'polski Internet' w poszukiwaniu rozwiązań problemów z którymi spotykałem się podczas wdrożeń systemów BI wielokrotnie napotykałem na strony wprowadzające czytelnika we wspomnianą tematykę. Niestety ciężko natrafić na informacje dla tak zwanych użytkowników średnio zaawansowanych lub zaawansowanych, którzy znają już podstawy chociażby hurtowni danych, ale napotkali na szczególny nie trywialny problem który muszą rozwiązać.
  Strona na której się Państwo znajdujecie ma z założenia nie tylko pomóc w poszerzaniu wiedzy związanej z Business Intelligence, ale również być narzędziem przydatnym w rozwiązywaniu specyficznych dla tej dziedziny IT problemów związanych z rzeczywistymi wdrożeniami. Dla mnie osobiście jest notatnikiem/'ściągawką'. Ponieważ zdarza się że w bieżących projektach spotykam problemy z którymi mierzyłem się już w przeszłości. A kilka słów spisanych podczas pierwszej analizy problemu pozwala mi w sprawny sposób przypomnieć sobie jego rozwiązanie.

Pozdrawiam
Rajmund Kulawinek

Podstawy hurtowni danych

Poniżej znajdują się artykuły pomagające zrozumieć ideę budowy hurtowni danych. Jeżeli nie posiadacie teoretycznej wiedzy na ten temat lub chcecie ją sobie odświeżyć zapraszam do lektury.

01. Kiedy zachodzi potrzeba budowy hurtowni danych?

02. ROLAP oraz MOLAP

03. Gwiazdy a płatki śniegu - konfrontacja

04. Granularność przechowywanych danych - dobór ziarna data martu

05. Wymiar czasu w hurtowni danych

06. Dane historyczne w wymiarach - w opracowaniu

07. Dane historyczne w faktach- w opracowaniu

02. ROLAP / MOLAP / HOLAP

Zakłada się że architektura systemów analitycznych (Online Analytical Processing - OLAP) może być reprezentowana przez trzy modele danych:
- relacyjny ROLAP (ang. Relational OLAP)
- wielowymiarowy MOLAP (ang. Multi dimensional OLAP)
- HOLAP (ang. Hybrid OLAP)

Często spotykałem się ze stwierdzeniami: 'sam już nie wiem czy to ROLAP, MOLAP czy HOLAP...' A w rzeczywistości sprawa nie jest taka skomplikowana jak by się mogło wydawać. Istotą jest to skąd raport czerpie dane. Jeżeli bierze je z tabel bazy (hurtowni) danych – jest to architektura ROLAP. Jeżeli z typowych kostek OLAP – jest to architektura MOLAP. Jeżeli może korzystać zarówno z kostek jak i z bazy danych, wtedy mamy do czynienia z HOLAP.
Ale po kolei:

Raporty korzystające z danych przechowywanych w modelu ROLAP, w podczas wywołania sięgają bezpośrednio do tabel hurtowni danych. Tabele te najczęściej mają postać gwiazd (ang. star schema) lub płatków śniegu (ang. snowflake). Są to tabele faktów powiązane relacjami z tabelami wymiarów. Można to zobrazować rysunkiem poniżej. Widać na nim najczęściej spotykaną architekturę - model gwiazdy. Więcej szczegółów na temat modelu gwiazdy oraz płatka śniegu można znaleźć w artykule: gwiazdy a płatki śniegu – konfrontacja. Nazwa ROLAP (Relational OLAP) wydaje się więc intuicyjna, bo przecież hurtownie danych to tak na prawdę zoptymalizowane pod kontem możliwości analitycznych relacyjne bazy danych.

Raporty korzystające z danych przechowywanych w modelu MOLAP, podczas wywołania najczęściej sięgają do kostek OLAP. Skąd nazwa MOLAP (Multi dimensional OLAP)? Dane w architekturze MOLAP mają postać wielowymiarowych struktur pozwalających na prezentowanie danych w sposób zilustrowany na poniższym rysunku. Architekturę MOLAP stosuje się najczęściej do prezentowania wstępnie agregowanych (posumowanych, uśrednionych, zliczonych... itp.) danych. Co prawda są zalane za danymi przechowywanymi w hurtowni, ale proces zasilania odbywa się najczęściej w porach nocnych kiedy kostka nie jest używana, a w momencie, kiedy raport odpytuje kostkę, model MOLAP nie ma potrzeby się gania do hurtowni danych. Więcej informacji na temat czym są kostki OLAP można znaleźć tutaj.

Ostatnim z wymienionych, jest model HOLAP (Hybrid OLAP). Zdarza się że analizy potrzebują skorzystać z obu źródeł, MOLAP oraz ROLAP. Dlaczego? Dlatego że każdy z powyższych modeli posiada swoje wady i zalety. Co za tym idzie często HOLAP jest kostką OLAP-ową która na tych poziomach agregacji na których może, używa szybkiego dostępu do danych bezpośrednio z kostki. A w momencie kiedy kostka miała by trudność z prezentacją informacji (na przykład z powodu miar nieaddytywnych) pozwala na zaciągnięcie odpowiednich danych z tabel hurtowni.

Ładowanie faktów

Ładowanie faktów

Ładowanie wymiarów

Przykład jobów ETL ładujących wymiary zgodnie z ideą wymiarów wolnozmiennych

Data
SCD0

Produkt
SCD1

Obszar
SCD2

Optymalizacja hurtowni danych

Klucze sztuczne

...

Zmieniające się fakty - co da się zrobić?

Wymiar czasu w hurtowni danych

Jak wcześniej wspomniałem, w hurtowniach danych najczęściej wykorzystuje się dwa rodzaje tabel. Tabele faktów i tabele wymiarów. Fakty służą do przechowywania wartości które będzie można odpowiednio agregować w procesie analizy zebranych danych (np. sumować, wyznaczać średnią, itp.). Wymiary (najczęściej, choć nie tylko) służą do zawężania zbioru analizowanych danych. Wymiarem bez którego ciężko wyobrazić sobie jakąkolwiek hurtownię danych jest wymiar czasu. Rzadko występuje potrzeba budowy raportu który nie prezentuje analizowanych wartości w czasie, bądź nie posiada ograniczeń czasowych w formie parametrów wejściowych.
Można sobie wyobrazić wiele sposobów na przechowanie informacji o czasie zaistnienia danego faktu. Jednak dobre praktyki budowania hurtowni danych nakazują wyodrębnienie informacji o czasie, w postaci osobnego wymiaru. Dlaczego w ten sposób, a nie na przykład bezpośrednio w tabeli faktów jako typ datetime? Głównie dlatego że typ taki charakteryzuje się wysoką gęstością danych, która z punktu widzenia analizy danych najczęściej nie jest konieczna. Aby pokryć wszystkimi wartościami zakres pełnego roku przy użyciu typu datetime potrzeba 31622400 wpisów (60s * 60m * 24h * 366d). Budowa indeksu który obsługiwał by taką kolumnę nie jest niemożliwa ale jest nie optymalna. W takim przypadku warto utworzyć wymiar daty przechowujący wpisy o dacie z granularnością do dnia. Dlaczego akurat do dnia? Ponieważ zdecydowana większość raportów prezentuje dane w których dzień jest elementarnym ziarnem analizy. Wymiar taki warto zaprezentować w formie hierarchii: rok, kwartał, miesiąc, dzień, gdzie tabela wymiaru mogła by mieć następującą postać:

W tym wypadku indeks zbudowany na polu kluczącym do wymiaru daty musiał by obsłużyć jedynie 366 wartości, dla każdego roku - czyli do 86400 raza mniej niż dla pola typu datetime.

Powyższy sposób pokazuje jak optymalnie przechowywać informację o dniu wystąpienia zdarzenia. Jednak często analiza wymaga przechowywania informacji o godzinie, a nawet minucie bądź sekundzie wystąpienia zdarzenia. Wyobraźmy sobie hurtownie danych operatora telekomunikacyjnego. W gwieździe przechowującej informacje o nawiązanych połączeniach istotne będą zarówno: data nawiązania połączenia, godzina nawiązania połączenia, jak i czas trwania połączenia. Jest to wyjątkowo dobry przykład na pokazanie że każdą z przytoczonych wartości warto przechowywać w inny sposób. O dacie już wspomniałem. teraz kilka słów o czasie. Czas, w raportach dla klientów z branży telekomunikacyjnej musi być określony bardzo precyzyjnie. Często analizy uwzględniają podział na: dzień/noc, szczyt/poza szczytem, co automatycznie wymusza precyzje definiowania czasu uwzględniającą co najmniej godzinę, a czasami nawet minutę i sekundę. W takich przypadkach warto utworzyć osobny wymiar czasu i przy okazji rozważyć budowę hierarchii czasu, zawierającą informację o np.: porze (noc/dzień), godzinie, minucie. Tabela takiego wymiaru mogla by wyglądać następująco:

Dlaczego warto stworzyć osobny wymiar czasu a nie jeden wspólny z datą? Po pierwsze dlatego że stracili byśmy większość zalet wynikających z przechowywania informacji o dacie i czasie w wymiarach a nie w polu typu datetime. Po części dlatego że, analogicznie jak to miało miejsce w przypadku daty, samemu dobierzemy granularność danych. Ale kluczowe w rozdzieleniu wspomnianych wymiarów jest to że, często analizy będą prowadzone na dwóch osobnych płaszczyznach (czasu i daty) nie koniecznie bezpośrednio związanych ze sobą. Na przykład, można sobie wyobrazić kwartalny raport prezentujący ilość nawiązanych połączeń w porze nocnej, pomiędzy godziną 23:00 a 5:00. Słupki wykresu mogły by prezentować ilości połączeń dla każdej godziny. Wymiar czasu miał by 24 rekordy a dzięki odpowiedniemu indeksowi (nałożonemu na klucz do wymiaru czasu z tabeli faktów) w bardzo wydajny sposób ze zbioru wszystkich połączeń wybierali byśmy tylko te które wystąpiły między 23:00 a 5:00 (6 z 24 wartości klucza). Natomiast klucz do wymiaru daty związany z parametrami wyboru kwartału zawierał by się w zbiorze około 92 z 366 wartości (1/4 z 366dni w roku), przy założeniu że mamy do czynienia ze zbiorem danych rocznych.

Wróćmy do ostatniej informacji jaką założyliśmy że przechowa przykładowa gwiazda - czasu trwania połączenia. Jest to wartość która na raportach będzie agregowana (sumowana, uśredniana, itp), nie tak jak dwie poprzednie, które służyły do ograniczania analizowanego zbioru danych. Taka wartość nie jest więc wymiarem. Jest miarą. Dlatego nie występuje w wymiarach, lecz bezpośrednio w tabeli faktów w postaci numerycznej.

Czy w związku z powyższym jest sens przechowywania informacji o dacie zdarzenia bezpośrednio w tabeli faktów? Na przykład w formacie datetime? Czasami tak. W niektórych przypadkach warto przechowywać taką informację, jako atrybut który będzie wyświetlany w tak zwanych raportach wykazów bądź szczegółów. Czyli raportach które nie prezentują agregowanych danych (np. sumy wartości sprzedaży w zadanym miesiącu), tylko prezentują listę elementarnych zdarzeń mających wpływ na wyliczaną wartość (zgodnie z wcześniejszym przykładem może to być lista wykonanych połączeń). W takim wypadku parametry wejściowe powinny zostać oparte o wymiary: daty i czasu dzięki czemu odpowiednie dane zostaną sprawnie odfiltrowane. Natomiast kolumna atrybutu szczegółowej daty i godziny będzie mogła zostać użyta aby w prosty sposób wyświetlić datę i czas połączenia. Raporty tego typu często występują jako raporty podrzędne wywoływane poprzez opcję drążenia wszerz (drill through).

04. Wymiary wolnozmienne - slowly changing dimension?

03. Gwiazdy a płatki śniegu - konfrontacja najpopularniejszych architektur hurtowni danych

Zarówno architektura gwiazdy (ang. star schema), jak i płatka śniegu (ang. snowflake schema) to podejścia typu ROLAP. Oba składają się z centralnie umieszczonej tabeli faktów, którą otaczają tabele wymiarów. Tabela faktów przechowuje informacje o miarach - czyli polach które posłużą do agregacji. Przykładem może być tabela faktów o nazwie sprzedaż zawierająca miary: kwota oraz ilość, pozwalająca na zliczanie wartości sprzedaży lub ilości sprzedanych produktów. Trzeba mieć na uwadze fakt, że rzadko analizowane są wszystkie wiersze jakie przechowuje tabela. Mało komu potrzebna jest informacja o ilości sprzedanych produktów od początku istnienia przedsiębiorstwa, bez możliwości wyznaczenia wartości w konkretnych latach. Aby ograniczyć dane do zbioru wartości interesujących odbiorcę, potrzebne są również wymiary. Każda tabela faktów posiada referencje do odpowiednich wymiarów, dzięki czemu możliwe jest zidentyfikowanie okoliczności wystąpienia faktu. Co za tym idzie, istnieje możliwość grupowania zebranych danych, bądź ograniczenia zbioru danych do interesującego podzbioru. Przykładem wymiaru może być data określająca moment w którym dokonano sprzedaży lub obszar określający miejsce sprzedaży.

Tabela faktów zawiera dane najczęściej w 3 postaci normalnej. Budowa tabeli faktów w obu architekturach jest praktycznie taka sama. Zawiera ona: swój klucz główny, wartości miar - czyli pól które będą agregowane na potrzeby raportów i analiz, oraz klucze główne do wymiarów. 

Różnice pomiędzy schematem płatka śniegu i gwiazdy pojawiają się w budowie tabel opisujących wymiary. Oba podejścia mają swoje plusy i minusy. Ale zaczynając od podstaw, ... skąd te nazwy?

Schemat gwiazdy

Płatek śniegu

	Gwiazda	Płatek śniegu
Łatwość utrzymania	Posiada nadmiarowe dane (ze względu na denormalizacje) w związku z tym wymiary są trudniejsze w utrzymaniu	Nie posiada nadmiarowych danych w związku z tym wymiary są łatwiejsze w utrzymaniu
Łatwość odpytywania	Wyjątkowo proste zapytania	Zapytania wymagają dodatkowego joinowania przez co są bardziej skomplikowane
Wydajność zapytań	Minimalna ilość kluczy obcych sprawia że zapytania wykonywane są w maksymalnie krótkim czasie	Więcej kluczy obcych powoduje wydłużenia czasu wykonania zapytania
Normalizacja wymiarów	Postać zdenormalizowana	Wymiary w formie znormalizowanej
Rekomendacja	Jeżeli projektant nie przewiduje problemów z utrzymaniem zbyt dużej ilości wierszy w wymiarze zaleca się korzystać z gwiazd, charakteryzujących się prostotą i szybkością działania	Kiedy rozmiar wymiaru jest na tyle duży że może stwarzać problemy związane z utrzymaniem struktury, lepiej jest użyć płatka śniegu który przechowuje dane w postaci znormalizowanej, co za tym idzie redukuje objętość danych. Płatki śniegu pozwalają przechowywać informacje które wymagają relacji wiele do wielu.

A czy można by spróbować uprościć powyższe architektury i rozbić wymiary posiadające hierarchie na odrębne tabele? Na przykład zamiast wymiaru obszar przechowującego informacje o: kraju, województwie, powiecie, miejscowości, zbudować osobne wymiary: kraj, województwo, powiat i miejscowość. Można by, ale rozwiązanie tego typu miało by poważne wady. Począwszy od tego że ilość wymiarów wzrosła czterokrotnie (4 tabele zamiast pojedynczej). Jednak bardziej bolesnym skutkiem, nie widocznym na pierwszy rzut oka, jest utrata bezpośredniej informacji o relacji pomiędzy elementami hierarchii: nadrzędnym i podrzędnym. Bazując na analizowanym przykładzie, aby jednoznacznie określić które miasta znajdują się w wybranym województwie trzeba przeszukać całą tabelę faktów, aby wybrać wiersze które w kluczach obcych mają wartości łączące oba wymiary. Przed rozbiciem wymiaru hierarchicznego na kilka pojedynczych wymiarów obszar (zaprojektowany jako gwiazda, lub płatek śniegu) posiadał informacje pozwalające na szukanie takich powiązań w tabelach wymiaru, bez odczytu rekordów tabeli faktów. A to znacząco upraszcza na przykład budowę parametrów wejściowych raportu.