Bazy danych 279

Hurtownie danych i narzędzia ETL

280 Bazy danych

Przegląd zagadnie ń

Hurtownie danych to specjalne bazy danych, które świetnie nadają się jako źródło raportów i analiz. W dzisiejszych czasach firmy operują na ogromnych ilościach danych. Samo przechowywanie danych nie jest juŜ celem. Równie waŜną funkcją magazynów danych i systemów stało się wyciąganie wniosków i ukrytych informacji z danych produkcyjnych. W tym module dowiesz się, jak planuje się strukturę hurtowni danych i jak wygląda proces aktualizacji danych w stworzonej hurtowni.

Tworzenie hurtowni danych

Aktualizacja danych

ETL w relacyjnych bazach danych

ETL a hurtownie danych

Podsumowanie

Laboratorium

Bazy danych 281

Tworzenie hurtowni danych

Proces tworzenia hurtowni przebiega w kilku etapach:

• zdefiniowanie wymagań analitycznych, • wybór źródeł danych do analizy, • projektowanie struktury hurtowni, • implementacja projektu, • import danych.

Wymagania analityczne

Zródla danych

Projektowanie hurtowni danych

Struktury tabel wymiarów

Import danych

282 Bazy danych

Wymagania analityczne

Aby zbudować hurtownię danych, naleŜy najpierw określić cele biznesowe. Musisz wiedzieć, jaki jest powód, dla którego projektujesz i implementujesz nowy system. Struktura hurtowni jest ściśle związana z celami analizy. KaŜda analiza przeprowadzana jest przy uŜyciu odpowiedniego zestawu odpowiednio zorganizowanych tabel. Taki zestaw tabel w zachodniej literaturze nosi nazwę data mart i w wolnym tłumaczeniu na język polski moŜe oznaczać zestaw danych do analizy. A zatem hurtownia jest bazą danych składającą się z tabel zorganizowanych w zestawy do analizy.

Źródła danych

PoniewaŜ analiza dotyczy często danych, które są pierwotnie rozproszone, źródłami danych dla hurtowni są na ogół róŜne systemy i pliki. RóŜnorodność ta dotyczy nie tylko platform lub typów magazynów (bazy danych, pliki tesktowe, arkusze kalkulacyjne), ale przede wszystkim róŜnorodności formatów i szczegółowości danych. Ten ostatni aspekt jest głównym czynnikiem określającym tzw. ziarnistość danych w hurtowni. Ziarnistość to najniŜszy poziom szczegółowości, jaki istnieje w danych. Dzięki ziarnistości projektant jest w stanie określić, z jaką dokładnością będzie moŜna analizować dane (np. analiza sprzedaŜy względem czasu z dokładnością do tygodnia).

Projektowanie hurtowni danych

Projektowanie hurtowni danych polega na zaplanowaniu struktury specyficznej bazy danych. Struktura ta obejmuje:

• tabele faktów, które przechowują dane liczbowe, których analiza jest celem biznesowym,

• tabele wymiarów, które przechowują dane określające kryteria analizy, • tabele pomocnicze, które są wykorzystywane w fazie importu danych

do tabel wymiarów i faktów. Data mart, czyli układ tabel słuŜący do analizy jednego wybranego celu biznesowego (np. sprzedaŜy), ma praktycznie zawsze strukturę budowaną wokół jednej tabeli faktów. Tabele wymiarów są powiązane z tabelą faktów związkami (takimi samymi, jak w transakcyjnych relacyjnych bazach danych). Dzięki temu jeden fakt jest określany przez dokładnie jeden rekord z kaŜdej tabeli wymiarów i jeden rekord z tabeli faktów. Kolumny w tabelach wymiarów określają tzw. ziarnistość danych, czyli maksymalny poziom szczegółowości, na jakim moŜna prowadzić analizę.

Struktury tabel wymiarów

Pojedynczy wymiar w hurtowni jest zawsze zbudowany według jednego z określonych szablonów. W związku z tym mamy wymiary:

Bazy danych 283

• strukturze gwiazdy (ang. star), • strukturze płatka śniegu (ang. snowflake), • z hierarchią rodzic-dziecko (ang. parent-child).

Mówimy, Ŝe wymiar ma strukturę gwiazdy, jeŜeli jego dane są przechowywane w jednej tabeli. Nazwa gwiazda bierze się z graficznej ilustracji pojemnika data mart, którego wszystkie wymiary mają strukturę gwiazdy - układ tabel na diagramie przypomina gwiazdę. Przykładem typowego wymiaru o strukturze gwiazdy jest zapisany w jednej tabeli wymiar Czas, który jest jednym z najczęściej spotykanych wymiarów.

Rys. 15.1 Struktura gwiazdy

Zwróć uwagę, Ŝe w kaŜdej tabeli wymiarów na powyŜszym diagramie (tabele z sufiksem _dim) pojawiają się dwa klucze: klucz główny sztuczny (kolumny z sufiksem _dim_key) i klucz pochodzący z bazy źródłowej (np. kolumny z sufiksem _app). Jest to powodowane tym, Ŝe hurtownia danych musi najczęściej przechowywać takŜe historię zmian wymiarów. Przykładowo: dziś klient jest kawalerem, za rok będzie Ŝonaty - w analizie jego aktualne zakupy muszą być uwzględnione jako zakupy dokonane przez kawalera, a zakupy wykonane w przyszłym roku muszą być rozpatrywane jako zakupy dokonane przez męŜczyznę Ŝonatego. Czyli w hurtowni muszą znaleźć się dwie wersje rekordu dotyczącego tego klienta.

Z kolei strukturę płatka śniegu posiada wymiar, którego hierarchia jest stworzona z kilku tabel. Podobnie, jak w przypadku gwiazdy, nazwa bierze się od układu tabel na diagramie pojemnika data mart, którego wymiary mają strukturę płatka śniegu. Przykład: jeśli wymiar Produkt jest zbudowany przy uŜyciu dwóch tabel - Kategoria i Produkt - które są ze sobą powiązane związkiem, mówimy, Ŝe wymiar ma strukturę płatka śniegu.

284 Bazy danych

Hierarchia rodzic-dziecko jest wykorzystana do stworzenia wymiarów, które mają za zadanie obrazowanie zaleŜności hierarchiczne między członkami wymiaru (rekordami w tabeli wymiaru). Przykładem moŜe być wymiar Pracownik, którego struktura oparta jest o hierarchię firmy (kaŜdy pracownik moŜe mieć szefa i podwładnych). Implementacją takiego wymiaru jest pojedyncza tabela i związek unarny (związek, w którym uczestniczą dwie kolumny jednej tabeli).

Import danych

Po stworzeniu struktury hurtowni pozostaje zasilić hurtownię danymi. Do importu danych wykorzystywane narzędzia ETL opisane w dalszej części modułu. Import danych najczęściej wykonywany jest w kilku krokach powtarzanych dla kaŜdego fragmentu hurtowni (dla kaŜdej struktury data mart):

• Import danych do tabel wymiarów. • Import danych do tabeli przejściowej, która następnie posłuŜy do

wypełnienia tabeli faktów. • Import danych do tabeli faktów na podstawie danych z tabeli

przejściowej i danych z tabel wymiarów.

Bazy danych 285

Aktualizacja danych

PoniewaŜ hurtownia danych nie jest zazwyczaj bazą jednokrotnego uŜytku, istotną kwestią staje się problem aktualizacji danych.

Istota aktualizacji danych

Przechowywanie danych historycznych

Implementacja aktualizacji danych

286 Bazy danych

Istota aktualizacji danych

Aktualizacja danych polega w większości przypadków na dodaniu rekordów w tabelach wymiarów. Nowe rekordy mogą pochodzić od nowych danych w źródłowej bazie danych lub nowej wersji istniejącego juŜ w hurtowni rekordu. To, jak często będzie dokonywana aktualizacja zaleŜy najczęściej od potrzeb analizy oraz od częstotliwości raportowania z hurtowni danych. Właściwie moŜna ze względu na przebieg procesu aktualizacji podzielić hurtownie na dwie kategorie: hurtownie aktualizowane cyklicznie z ustalonym interwałem oraz hurtownie czasu rzeczywistego, czyli takie w których dane źródłowe pojawiają się z minimalnym opóźnieniem w hurtowni danych.

Przechowywanie historycznych danych

Hurtownie na ogół muszą umoŜliwiać analizę zarówno danych bieŜących, jak i historycznych. O ile przechowywanie danych aktualnych nie stanowi problemu, o tyle dane historyczne mają wpływ na strukturę tabel w hurtowni danych.

Aby zapobiec nadpisywaniu przez nowsze wersje rekordów starszych wersji projektanci hurtowni umieszczają w tabelach wymiarów klucze sztuczne (tzw. surrogate key) - kolumny, które na ogół są typu liczby całkowitej z włączoną autonumeracją i pełnią rolę kluczy głównych w tabelach wymiarów.

Implementacja aktualizacji danych

Do aktualizacji hurtowni danych wykorzystywane są narzędzia ETL. Właściwie kaŜde narzędzie ETL posiada mechanizmy dedykowane pod aktualizacje danych w hurtowni. Mechanizmy te pozwalają między innymi wyizolować te wiersze w tabelach wymiarów, które zmieniły się od ostatniej aktualizacji danych.

Bazy danych 287

ETL w relacyjnych bazach danych

ETL to skrót od słów Extract Transform Load - w tłumaczeniu na język polski - Wypakuj Przekształć Załaduj. Pod tym skrótem kryje się klasa narzędzi do transferu danych. Narzędzia te znajdują szereg zastosowań w pracy z bazach danych - od prostego transferu danych między bazami do zaawansowanych operacji pozwalających na wypełnienie hurtowni danych.

W codziennej pracy z danymi narzędzia ETL mogą wypełniać wiele rozmaitych zadań. Wszystko zaleŜy od moŜliwości i wydajności oferowanej przez narzędzie.

Zastosowania narzedzi ETL

Wymagania stawiane narzedziom ETL

288 Bazy danych

Zastosowania narzędzi ETL

Standardowe zastosowania ETL w pracy z relacyjnymi bazami danych to:

• proste kopiowanie danych między tabelami, bazami danych, serwerami,

• transfer danych między heterogenicznymi (róŜnymi) źródłami danych, np. między róŜnymi SZBD,

• transfer obiektów między bazami danych (np. transfer procedur składowanych),

• transfer obiektów między serwerami (np. transfer loginów), • scalanie rozproszonych danych, • zasilanie hurtowni danych.

Wymagania stawiane narzędziom ETL

Dobre narzędzie ETL spełnia pewne wymagania, jakie stawia się dziś narzędziom tej klasy. Oto niektóre z tych wymagań:

• wydajność - często okno czasowe, które jest dostępne do wykonywania transferu danych jest coraz mniejsze,

• łatwość obsługi - narzędzie powinno oferować graficzny interfejs uŜytkownika i umoŜliwiać szybkie budowanie rozwiązań,

• bogactwo funkcji - im więcej moŜna w prosty sposób osiągnąć przy pomocy narzędzia, tym lepiej,

• niska cena - niektórzy producenci dołączają do SZBD bezpłatne narzędzia ETL,

• moŜliwości utrwalenia i cyklicznego powtarzania procesu transferu danych,

• moŜliwości wykorzystania narzędzia do wykonywania rutynowych czynności administracyjnych.

Rys. 15.2 Integration Services - narzędzie ETL w systemie Microsoft SQL

Server 2005

Bazy danych 289

ETL a hurtownie danych

Jednym z podstawowych zastosowań narzędzi ETL jest zasilanie danymi hurtowni danych. Proces tworzenia hurtowni danych, w którym wykorzystywane są narzędzia ETL przedstawiony jest na poniŜszym rysunku.

Rys. 15.3 Proces tworzenia hurtowni danych

Scalanie danych

Oczyszczanie danych

Odswiezanie danych

290 Bazy danych

PowyŜszy proces przebiega w kilku fazach:

• pobranie danych z wielu źródeł, • "oczyszczanie" danych, • ustalenie jednej struktury danych, • zapisanie danych we wspólnym magazynie, • budowa struktur Data Mining, • raportowanie i udostępnianie danych.

Scalanie danych

Jednym z pierwszych etapów tworzenia hurtowni danych jest scalanie danych. Hurtownia danych często słuŜy do zgromadzenia danych pochodzących z róŜnych źródeł - baz danych, plików binarnych i tekstowych, plików XML, arkuszy kalkulacyjnych. Narzędzia ETL umoŜliwiają transformacje danych, to znaczy pomagają w takim transferze danych, dzięki któremu dane mające pierwotnie róŜną strukturę są umieszczane w jednej bazie danych.

Oczyszczanie danych

Dane, które mają być wstawione do hurtowni danych, bardzo często wymagają wstępnego przygotowania, tak zwanego "oczyszczenia". Operacja ta polega na przefiltrowaniu danych i odrzuceniu niepoprawnych zapisów (chodzi między innymi o przypadkowe zapisy danych, powtarzające się rekordy itd.). Problem "brudnych danych" dotyczy zarówno plików tekstowych (głównie ich, poniewaŜ często mają nieustandaryzowaną strukturę), jak i baz danych.

OdświeŜanie danych

Co pewien czas kaŜda hurtownia przyjmuje nowe porcje danych (niektóre hurtownie funkcjonują niemal w czasie rzeczywistym, to znaczy, Ŝe zmiany są transferowane z baz relacyjnych zaraz po ich wstawieniu). Większość narzędzi ETL posiada specjalne mechanizmy, które obsługują ten proces poprzez umoŜliwienie rozpoznawania istniejących w hurtowni danych i importowanie tylko zmian lub nowych rekordów ze źródłowych magazynów danych.

Bazy danych 291

Podsumowanie

Relacyjne bazy danych, na skutek rozproszenia danych między duŜą ilość tabel, nie są wydajnym źródłem dla raportów. O wiele lepiej w systemach raportowych sprawdzają się hurtownie danych. Hurtownie są takŜe znakomitym źródłem danych dla wielowymiarowych baz analitycznych OLAP. Metodologia projektowania hurtowni jest zatem waŜną umiejętnością dla osób pracujących z systemami bazodanowymi.

Narzędzia ETL pełnią waŜną funkcję w procesie budowy i obsługi hurtowni danych. Są takŜe elementem SZBD pomocnym administratorom przy ich codziennej pracy z bazami danych. Dzięki swoim moŜliwościom znajdują coraz więcej zastosowań. Z pewnością będą one cały czas rozwijane i integrowane z SZBD, poniewaŜ posiadanie takiego narzędzia moŜe być powaŜnym atutem kaŜdego systemu.

Tworzenie hurtowni danych

Aktualizacja danych

ETL w relacyjnych bazach danych

ETL a hurtownie danych

292 Bazy danych

Laboratorium

W tym ćwiczeniu zapoznasz się ze strukturą hurtowni danych AdventureWorksDW , w szczególności z podzestawem tabel do analizy sprzedaŜy.

Bazy danych 293

Tworzenie hurtowni danych

Krok 1 - Tworzenie diagramu dla problemu biznesowego

► Zaloguj się do maszyny wirtualnej ZBD jako uŜytkownik Administrator z hasłem P@ssw0rd.

► Kliknij Start. Z grupy programów Microsoft SQL Server 2005 uruchom SQL Server Management Studio.

► W oknie logowania kliknij Connect. ► W oknie Object Explorer po lewej stronie ekranu rozwiń folder

Databases. ► Rozwiń folder bazy AdventureWorksDW . ► Kliknij prawym przyciskiem myszy na folderze Database Diagrams. ► Na ewentualne pytanie programu o tworzenie niezbędnych obiektów

odpowiedz Yes. ► Z menu kontekstowego wybierz New Database Diagram. ► W oknie Add Table trzymając wciśnięty klawisz Control kliknij na

tabelach: DimCustomer, DimProduct, DimProductCategory, ProductDimSubcategory, DimTime i FactInternetSales.

► Kliknij Add.

Rys. 15.4 Dodawanie tabel do diagramu bazy danych ► Kliknij Close.

294 Bazy danych

Rys. 15.5 Diagram data mart ► Zapisz diagram jako InternetSalesDiagram. Przyjrzyj się utworzonemu diagramowi pojemnika data mart (zajrzyj do wykładów, jeśli obce jest Ci to pojęcie). PowyŜszy rysunek przedstawia uproszczony diagram. Odpowiedz na poniŜsze pytania.

Krok 2 - Analiza struktury data Mart

Pytanie: Ile tabel faktów zawiera data mart, którego diagram stworzyłeś? Odpowiedź: jedną - FactInternetSales. Pytanie: Ile tabel wymiarów zawiera data mart, którego diagram stworzyłeś i ile wymiarów te tabele implementują? Odpowiedź: data mart zawiera 5 tabel wymiarów; tabele te implementują 3 wymiary. Pytanie: Czy w data mart istnieje wymiar o strukturze płatka śniegu? Odpowiedź: tak, taką strukturę ma wymiar zbudowany z tabel DimProduct, DimProductCategory, ProductDimSubcategory. Pytanie: Czy czas w tabeli DimTime jest zorganizowany w jedną hierarchię? Odpowiedź: nie, w tabeli tej znajdują się takŜe kolumny, które tworzą inne hierarchie czasu niŜ rok kalendarzowy.

Bazy danych 295

Krok 3 - Projektowanie zasilania hurtowni danymi

Problem: Zastanów się, w jaki sposób wypełniłbyś danymi wspomniany data mart? Odpowiedź: Na początek naleŜałoby zaimportować dane do tabel wymiarów. Na ogół wystarcza do tego najprostsze zadanie transformacji. Do wygenerowania niektórych kolumn w tabeli DimTime najlepiej uŜyć procedury składowanej korzystającej z funkcji daty i czasu. Dane, które posłuŜą do zamapowania tabeli faktów do tabel wymiarów naleŜy najpierw zaimportować do tabeli pomocniczej o strukturze podobnej do struktury tabeli FactInternetSales, ale z kluczami obcymi podmienionymi na klucze aplikacji. Finalnym krokiem byłoby dokonanie złączenia tabeli pośredniej i tabel faktów w celu wstawienia do tabeli faktów odpowiednich kluczy i wartości miar.

Jeśli czas Ci pozwoli, przyjrzyj się bazie danych AdventureWorks i postaraj się wykonać hurtownię danych z jednym pojemnikiem data mart (powiedzmy analiza sprzedaŜy), do której moŜnaby zaimportować dane z tej bazy.

296 Bazy danych

Proste transfery danych przy uŜyciu ETL

W tym ćwiczeniu dokonasz prostego tranferu danych przy uŜyciu

narzędzia Import/Export Wizard. Transfer będzie polegał na skopiowaniu struktury i zawartości tabeli Production.Product do nowej tabeli Production.ProductCopy.

Krok 1 - Realizacja transferu danych

► Zaloguj się do maszyny wirtualnej ZBD jako uŜytkownik Administrator z hasłem P@ssw0rd.

► Kliknij Start. Z grupy programów Microsoft SQL Server 2005 uruchom SQL Server Management Studio.

► W oknie logowania kliknij Connect. ► W oknie Object Explorer (po lewej stronie ekranu) rozwiń listę baz

danych i kliknij prawym przyciskiem na bazie AdventureWorks. Z menu kontekstowego wybierz Tasks - Export Data....

► W oknie SQL Server Export and Import Wizard kliknij Next. ► W oknie SQL Server Export and Import Wizard - Choose a Data

Source kliknij Next.

Rys. 15.6 Import/Export Wizard - wybór źródła danych ► W oknie SQL Server Export and Import Wizard - Choose a Data

Destination z listy rozwijanej Database wybierz bazę AdventureWorks kliknij Next.

Bazy danych 297

Rys. 5.7 Import/Export Wizard - wybór magazynu docelowego dla transferu ► W oknie SQL Server Export and Import Wizard - Specify Table

Copy or Query kliknij Next.

Rys. 15.8 Import/Export Wizard - wybór rodzaju transferu ► W oknie SQL Server Export and Import Wizard - Select Source

Tables and Views zaznacz w kolumnie Source tabelę [AdventureWorks].[Production].[Product] ,

► W kolumnie Destination wpisz [AdventureWorks].[Production].[ProductCopy] (patrz rysunek poniŜej) i kliknij Next.

298 Bazy danych

Rys. 15.9 Import/Export Wizard - opcje wykonania i zapisu pakietu

Import/Export Wizard pozwala na zapisanie pakietu Integration Services. Dzięki temu moŜna klikając w szybki sposób stworzyć prosty szablon pakietu.

► W oknie SQL Server Export and Import Wizard - Save and

Execute Package kliknij Next.

Rys. 15.10 Import/Export Wizard - wybór źródła danych

Bazy danych 299

► W oknie SQL Server Export and Import Wizard - Select Source Tables and Views kliknij Finish. 12. Po zakończeniu transferu danych kliknij Close.

Rys. 15.11 Import/Export Wizard - potwierdzenie wykonania transferu Krok 2 - Weryfikacja operacji

► OdświeŜ w programie SQL Server Management Studio listę tabel w bazie danych AdventureWorks (zaznacz folder Tables w tej bazie danych i wciśnij F5) i sprawdź, czy istnieje tabela Production.ProductCopy (klikając na tej tabeli prawym przyciskiem i wybierając Open Table moŜesz takŜe obejrzeć zawartość tabeli).