W O J S K O W A AK A D E M I A

T E C H N I C Z N A

SPRAWOZDANIE

Z LABORATORIUM

Z PRZEDMIOTU

HURTOWNIE DANYCH

Prowadzący laboratoria: mgr inż. Andrzej Rozmus

Skład zespołu: Katarzyna Ciereszko

Agnieszka Lach

Rafał Lemancewicz

Radosław Karpiński

Grupa: I8E1S1

Uzasadnienie biznesowe dla realizowanego projektu

Celem projektu jest stworzenie hurtowni danych, która ma ułatwiać przeprowadzanie analizy rentowności klienta. Dzięki zrealizowaniu takiego projektu użytkownik hurtowni danych będzie mógł w prosty, szybki sposób uzyskać wszystkie niezbędne informacje na temat poszczególnych kontrahentów. Będzie miał dostęp do historii kupna każdego klienta. Użytkownik będzie mógł ocenić ‘wartość’ nabywcy dzięki zestawieniom: ile towarów za jaką cenę i w jakim czasie zakupił dany klient. Dzięki takiej wiedzy będą mogły zostać podjęte odpowiednie działania promocyjne względem odbiorców (np. rabat, newsletter, gazetka reklamowa, decyzja o zaprzestaniu wysyłania katalogu ofert).Będzie ułatwiona również analiza rentowności klientów według miejscowości lub województw dla danych produktów. Pozwoli ona na podjęcie decyzji o aktualnie oferowanych produktach na poszczególnych rynkach lokalnych. Poza tym użytkownicy hurtowni danych uzyskają informację na temat rentowności poszczególnych grup wiekowych klientów. Jeśli jakaś grupa wiekowa kupuje tylko jeden dany typ produktu, to możliwe że nie warto jest skupiać się na tej grupie docelowej i zrezygnować z produkcji określonego towaru.

Powodem wdrożenia hurtowni danych jest brak możliwości analizy rentowności klienta, co powoduje straty przedsiębiorstwa poprzez ‘inwestowanie’ w nierentownych klientów (np. drukując i wysyłając katalogi z ofertami osobom, które tylko raz dokonały zakupu jakiegoś produktu i więcej tego nie uczynią). Poza tym znając profil docelowych klientów można dopasować produkty do ich wymagań (biorąc pod uwagę lokalizację oraz wiek klienta) a co za tym idzie zwiększyć zysk przedsiębiorstwa.

Wdrożenie hurtowni danych usprawni pracę analityków, wymusi zmianę sposobu działania organizacji, która od tej pory będzie zorientowana na usługi oferowane kontrahentom, co jest celem strategicznym firmy.

Kolejne korzyści wynikające z realizacji projektu stworzenia hurtowni danych to: zwiększenie penetracji rynku oraz ułatwienie utworzenia społeczności wokół usług.

Dzięki szczegółowej analizie rentowności klienta będą realizowane takie zadanie statutowe firmy jak: wysoka jakość świadczonych usług, utrzymywanie odpowiednich relacji z docelową grupą klientów, zwiększenie sprzedaży dla stałych klientów, zdobycie nowych rynków zbytu.

Koncepcja hurtowni danych wspomagającej działalność przedsiębiorstwa w odniesieniu do postawionego problemu

Hurtownia danych jest centralnie zarządzaną i zintegrowaną bazą danych, gromadzącą dane ze źródeł operacyjnych organizacji. Baza, na której oparta jest hurtownia danych zawiera dane będące źródłem analiz i do których użytkownicy mogą mieć bezpośredni dostęp. Hurtownia danych może być uaktualniana w dowolnym czasie, bez konieczności wyłączania systemów operacyjnych lub produkcyjnych firmy. Jest to efekt implementacji procesu ETL. Stworzona hurtownia danych zapewnia integrację danych z heterogenicznych źródeł oraz przetwarzanie dużych wolumenów danych w ramach pojedynczego zapytania do systemu. Zaprojektowana hurtownia danych jest podstawą systemu wspomagania decyzji. W hurtowni przechowywana jest pełna historia zmian, dane nie są usuwane, tylko nadpisywane. Dane w hurtowni są przechowywane w strukturach niezależnych od struktur aplikacji, która je wygenerowała. Hurtownia danych jest dedykowaną bazą danych zawierającą szczegółowe, historyczne, stałe i spójne dane, które mogą być analizowane w zależności od zmiennej czasu.

Opis środowiska wytwórczego, w którym realizowany jest projekt

1. System operacyjny: Arch Linux z włączonymi pakietami z repozytorium testin Arch Linux jest dystrybucją przeznaczoną dla użytkowników, którzy mają pewne doświadczenie związane z systemami GNU/Linux. Arch jest oparty na systemie pakietów binarnych kompilowanych dla architektur i686 oraz x86_64 zarządzanych przez program pacman. Umożliwia on instalację, aktualizację oraz usuwanie pakietów.

Wynik komendy „uname –a”:

Linux myhost 2.6.38-ARCH #1 SMP PREEMPT Tue Mar 22 13:38:29 CET 2011 x86_64 Intel(R) Core(TM)2 Duo CPU P8400 @ 2.26GHz GenuineIntel GNU/Linux

1. Baza danych: Oracle Database 11g Release 2

System bazy danych Oracle składa się z:

* instancji – struktur pamięciowych i procesów systemu operacyjnego obsługujących bazę danych. W skład typowego procesu wchodzi PMON (process monitor) i SMON (system monitor).

* struktur przechowywania danych

Struktura instancji

SCHEMA – Schemat jest to zbiór obiektów należących do użytkownika.

Domyślne schemy zawierają:

* SYS (podstawowe struktury bazy danych i narzędzia)

* SYSTEM (dodatkowe struktury bazy danych i narzędzia oraz konto uprzywilejowane)

* OUTLN (wykorzystywana do przechowywania metadanych dla query-optimizera.

* BI, IX, HR, OE, PM, SH (rozszerzone przykładowe schemy zawierające więcej danych i struktur niż oryginalna schema SCOTT)

1. Klient: Oracle Warehouse Builder 11g Release 2

Środowisko projektowe

W pełni skalowalne repozytorium metadanych to podstawowy składnik systemu OWB. Repozytorium to jest zestawem tabel umieszczonych w bazie danych Oracle, do których dostęp jest możliwy dzięki graficznym aplikacjom klienckim, napisanym w języku Java i HTML.

Repozytorium OWB zasilane jest przez dane pochodzące z różnorodnych systemów źródłowych. Ten przepływ danych jest całkowicie zautomatyzowany, dzięki zastosowaniu tak zwanych integratorów, czyli komponentów przeznaczonych do pobierania metadanych i danych płynących z określonych typów źródeł.

W oparciu o zmiany zachodzące w systemach źródłowych, OWB umożliwia aktualizowanie zgromadzonych w repozytorium metadanych, a także ułatwia rozsyłanie danych do procesów ETL i systemu docelowego.

Aby zapewnić jakość i kompletność metadanych znajdujących się w repozytorium, OWB oferuje narzędzia sprawdzające poprawność danych (walidatory). Dzięki nim możliwe jest zapewnienie spójności systemu, tworzonego przez wielu użytkowników.

Dostępne w OWB narzędzia do tworzenia raportów pozwalają programistom i użytkownikom przeglądać elementy systemu, bez konieczności używania oprogramowania projektowego. Bardzo ważnym elementem środowiska umożliwiającego tworzenie raportów, jest Impact Analysis oprogramowanie pozwalające badać wpływ zmian w systemie, jeszcze przed ich propagacją

Środowisko wykonawcze

Po zaprojektowaniu logicznej struktury mechanizmów ETL, należy przenieść ją na grunt fizycznego środowiska bazy danych. Aby realizacja tego etapu była możliwa, trzeba skonfigurować wspomnianą strukturę logiczną, przekazując jej informacje na temat środowiska bazy danych. Dopiero po zakończeniu konfiguracji, można przystąpić do generowania kodu.

W procesie pobierania danych z systemów źródłowych, OWB posługuje się odpowiednimi językami, a do operowania na obiektach bazy danych używa instrukcji SQL DDL. Wygenerowany kod umieszczany jest bądź to w pliku tekstowym, bądź w bazie danych.

Proces wykonywania funkcji ETL oznacza uruchamianie wygenerowanego kodu. W wyniku tego procesu dane źródłowe są przesyłane do docelowej bazy danych. Należy przy tym zaznaczyć, że "obce" fragmenty kodu wykonywane są w swoich macierzystych środowiskach.

W systemie OWB możliwe jest raportowanie procesu wczytywania danych, dzięki udostępnionym procedurom monitorującym, których zadaniem jest zapisywanie odpowiednich informacji do tabel bazy danych wykorzystywanych w środowisku wykonawczym. Na informacje te składają się ilości rekordów jakie zostały wybrane, zapisane, bądź zaktualizowane oraz komunikaty o ewentualnych błędach występujących podczas wczytywania danych.

Ad. b) i c) Instalacja środowiska (bazy danych, repozytorium, klienta) była bardzo problematyczna. Należało m.in. utworzyć kilkanaście linków symbolicznych do bibliotek, doinstalować kilka wymaganych pakietów, zwiększyć limity na semafory w pliku /etc/sysctl.conf (inaczej proces tworzenia bazy danych był przerywany z błędem), zwiększyć swap’a.

OWB działa w oparciu o Javę java version "1.6.0_22" OpenJDK Runtime Environment (IcedTea6 1.10.1) (ArchLinux-6.b22_1.10.1-1-x86_64) OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode) i jest dość stabilne, jednak przy dłuższym użytkowaniu pojawiają się problemy z wydajnością.

Procedura uruchomienia repozytorium i bazy danych:

Rysunek 1. Wyeksportowanie zmiennych środowiskowych

Rysunek 2. Uruchomienie listenera, który będzie nasłuchiwał na porcie 1521

Rysunek 3. Uruchomienie bazy danych.

Model punktowy wraz z opisem

Faktem, który podlega analizie jest sprzedaż – jest to zdarzenie, o którym informacja jest przechowywana w hurtowni danych.

Miary – cechy ilościowe opisujące zdarzenie (sprzedaż):

1. Liczba sprzedanych produktów

2. Łączna wartość zamówienia

Wymiary – atrybuty opisujące kontekst wystąpienia faktu:

1. Czas

Poziom szczegółowości:

1. Dzień

2. Miesiąc

3. Rok

1. Klient

Poziom szczegółowości:

1. Klient

2. Grupa wiekowa

1. Produkt

Poziom szczegółowości:

1. Produkt

2. Grupa produktów (podział ze względu na cenę)

Atrybuty opisujące poszczególne wymiary/poziomy:

1. Czas:

1. Rok

2. Miesiąc

3. Dzień

1. Klient:

1. Id_klient

2. Klientid

3. Id_grupa_wiekowa

4. Imie

5. Nazwisko

6. Data_urodzenia

1. Grupa wiekowa

1. Id_grupa_wiekowa

2. Id_klient

3. Nazwa_grupy_wiekowej

1. Produkt

1. Id_produkt

2. Produktid

3. Name

4. Cena

1. Grupa produktów

1. Id_produkt

2. Id_przedzial_cenowy

3. Przedzial_cenowy

Model punktowy:

Architektura hurtowni danych wraz z uzasadnieniem wyboru

Tworząc wymiary wybraliśmy strukturę typy ROLAP, czyli tworzyliśmy naszą hurtownie na podstawie relacyjnej bazy danych. Wybraliśmy ROLAP ze względu na łatwiejszą aktualizację danych, czyli możliwość wprowadzania poprawek, niż w przypadku MOLAP. Również istotny okazał się krótszy czas wykonania procesów zasilenia danych. Ponadto ROLAP charakteryzuje się o wiele krótszym czasem odpowiedzi niż MOLAP.

Główną cechą hurtowni danych jest przechowywanie danych historycznych, zagregowanych, z dostępem do odczytu przez użytkowników. Z punktu widzenia analizy zapytań SQL, znaczna większość zapytań do hurtowni danych zaczyna się od instrukcji SELECT. Podczas wyboru odpowiedniego rodzaju architektury hurtowni danych kierowaliśmy się głównie łatwością zastosowania odpowiedniej architektury.

Schemat gwiazdy charakteryzuje się centralną tabelą, nazywana tabelą faktów, z którą połączone są tabele wymiarów. Model taki umożliwia przeglądanie poszczególnych kategorii, sumaryzację, agregację, drążenie i filtrowanie danych. W modelu gwiazdy tabela faktów w hurtowni danych jest w trzeciej postaci normalnej, podczas gdy tabele wymiarów są zdenormalizowane, czyli reprezentują drugą postać normalną.

Tabela faktów jest zaprojektowana zupełnie inaczej niż typowa tabela relacyjnej bazy danych. Jej denormalizacja jest celowa - aby zwiększyć wydajność i reponsywność zapytań kierowanych do bazy. Tabela faktów zwykle zawiera rekordy gotowe do eksploracji, zwykle łatwo dostępne poprzez zapytaniami pisane 'od ręki' (ad hoc). Rekordy w tabeli faktów mogą być postrzegane jako wydarzenia, co jest spowodowane naturą hurtowni danych. Klucz główny tabeli faktów jest zwykle złożony z wszystkich kolumn z wyjątkiem wartości numerycznych (takich jak LICZBA, ŁACZNA_WARTOSC_SPRZEDAZY). Prawie wszystkie informacje w typowej tabeli faktów są reprezentowane również w jednej lub wielu tabeli wymiarów. Głównym celem utrzymywania tabeli wymiarów jest umożliwienie przeglądania kategorii prosto i szybko.

Klucz główny każdej z tabeli wymiarów jest związany z tabelą faktów i jest to składowa złożonego klucza głównego tabeli faktów. W schemacie gwiazdy występuje tylko jedna zdenormalizowana tabela dla każdego z wymiarów.

1. Charakterystyka źródła (źródeł) danych

Baza źródłowa dostarcza nam podstawowych i niezbędnych informacji z zakresu transakcji i zamówień klientów. Oprócz tego mamy dane dotyczące dostawców oraz pracowników danej firmy. Tabele użyte do pobierania danych do hurtowni danych

• Klient

• Pozycja_Faktury

• Towar

• Zamówienie

• Pozycja_Zamowienia

Pozwalają nam one na pozyskiwanie odpowiednich danych.

Wykaz tabel zaimportowanym z bazy źródłowej:

1. Charakterystyka procesu ETL

Ładowanie wymiarów poprzez nadpisanie czyli proces czasu rzeczywistego.

Opracowanie projektu procesu ETL:

1. Odwzorowanie modelu logicznego źródeł danych na model logiczny hurtowni danych (jeśli nie zbudowano wcześniej w ramach modelu zasilania hurtowni danych).

2. Model struktur tymczasowych datastage.

3. Specyfikacja niestandardowych algorytmów transformacji danych.

4. Specyfikacja procedur obsługi błędnych danych.

Klient

Mapowanie klienta – Z tabeli zewnętrznej(źródłowej)Klient_1wiersz DATA_UR łączymy za pomocą operatora EXPRESSION z wymiaremKLIENT_2. Wyjście z EXPRESSION jest wyjściem do wymiaru KLIENT_2do wierszy ID_GRUPA_WIEKOWA i GRUPA_WIEKOWA_ID_KLIENT. Zadaniem operatora jest pogrupowanie klientów w grupy wiekowe według ich dat urodzenia. Pozostałe wiersze (ID_KLIENT, IMIE, NAZWISKO, DATA_UR) przechodzą do wymiaru KLIENT_2.

Expression OUTPUT1:

CASE

WHEN TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) >= 1990 THEN 1

WHEN TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) < 1990 AND TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) >= 1980 THEN 2

WHEN TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) < 1980 AND TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) >= 1970 THEN 3

WHEN TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) < 1970 AND TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) >= 1960 THEN 4

WHEN TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) < 1960 AND TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) >= 1950 THEN 5

WHEN TO_NUMBER(TO_CHAR(INGRP1.DATA_UR,'YYYY')) <= 1950 THEN 6

END

W naszej hurtowni danych mapowanie klienta wygląda następująco:

Produkt

Mapowanie produktu – Z tabeli zewnętrznej POZYCJA_FAKTURYwiersz CENA_SPRZEDAZY łączymy za pomocą operatora EXPRESSION i JOINER z tabelą PRODUKT. Wyjście z JOINERA jest wyjściem do wymiaru PRODUKT.Zadaniem operatora EXPRESSION jest pogrupowanie cen sprzedaży w przedziały cenowe według cen sprzedaży. Operator JOINER służy do łączenia tabel za pomocą klucza IDPRODUKT.

EXPRESSION OUTPUT1:

CASE

WHEN INGRP1.CENA_SPRZEDAZY >= 8000 THEN 9

WHEN INGRP1.CENA_SPRZEDAZY < 8000 AND INGRP1.CENA_SPRZEDAZY >= 7000 THEN 8

WHEN INGRP1.CENA_SPRZEDAZY < 7000 AND INGRP1.CENA_SPRZEDAZY >= 6000 THEN 7

WHEN INGRP1.CENA_SPRZEDAZY < 6000 AND INGRP1.CENA_SPRZEDAZY >= 5000 THEN 6

WHEN INGRP1.CENA_SPRZEDAZY < 5000 AND INGRP1.CENA_SPRZEDAZY >= 4000 THEN 5

WHEN INGRP1.CENA_SPRZEDAZY < 4000 AND INGRP1.CENA_SPRZEDAZY >= 3000 THEN 4

WHEN INGRP1.CENA_SPRZEDAZY < 3000 AND INGRP1.CENA_SPRZEDAZY >= 2000 THEN 3

WHEN INGRP1.CENA_SPRZEDAZY < 2000 AND INGRP1.CENA_SPRZEDAZY >= 1000 THEN 2

WHEN INGRP1.CENA_SPRZEDAZY <= 1000 THEN 1

END

W naszej hurtowni danych mapowanie produktu wygląda następująco:

Rentowność

Mapowanie rentowności (faktu) – Ogólnym celem tego mapowania jest przesłanie z bazy źródłowej do hurtowni wartości sprzedaży z danego miesiąca dla danego klienta i produktu, czyli przesłanie zagregowanej wartości sprzedaży i kluczy biznesowych produktu, czasu i klienta.

Joiner został tu użyty w celu zebrania wartości faktur, kluczy biznesowych produktu, czasu i klienta w jednej tabeli. Z tabeli zewnętrznej ZAMOWIENIE oraz POZYCJA_ZAMOWIENIA łączymy za pomocą operatora JOINER wybrane rekordy. Wyjście z JOINERA oraz tabela POZYCJA_FAKTURY są wejściem do następnego JOINERA_0 . Operator JOINER służy do łączenia tabel za pomocą klucza IDPRODUKT (INGRP1.IDPRODUKT = INGRP2.IDPRODUKT).

Wyjście z JOINERA_0 jest wejściem do operatora EXPRESSION, jego zadaniem jest usunięcie informacji o konkretnym dniu bo nie jest to nam potrzebne, a ułatwi potem porównywanie (data = data).

Wyjście z JOINERA_0 i operatora EXPRESSION jest wejściem do operatora AGGREGATOR. Agregator został użyty w celu zsumowania wszystkich wartości z danego miesiąca dla danego klienta i produktu. Wyjście z AGGREGATORA jest wejściem do naszej głównej tabeli SPRZEDAZ. Klucz biznesowy do czasu wstawia się automatycznie i dla tego nie ma przejścia między kluczem biznesowym w agregatorze i tabeli SPRZEDAZ.

W naszej hurtowni danych mapowanie rentowności wygląda następująco:

1. Podsumowanie i wnioski

PODSUMOWANIE

Po raz pierwszy mieliśmy możliwość stworzenia własnej hurtowni danych od podstaw. Okazało się to bardzo ciekawym doświadczeniem, ponieważ hurtownie danych dają więcej możliwości niż same bazy danych.

Główną różnicą pomiędzy budową bazy danych a hurtownią danych jest to, że relacyjny model systemu źródłowego w hurtowni danych jest zdenormalizowany do tabel faktów (fact table) i wymiarów (dimension table) , które są typowe dla architektury hurtowni. Różnice te wynikają przede wszystkim z celu ich istnienia i ich przeznaczeniu. W typowym systemie wydajność bazy danych jest kluczowa, ponieważ interfejs użytkownika musi szybko odpowiadać i reagować na to, co się dzieje w systemie. Jakość i użyteczność takiego systemu jest w znacznym stopniu uzależniona od tego, jak szybko dane mogą być zapisane do bazy danych. Aby to osiągnąć baza danych est z reguły zoptymalizowana pod kątem szybkości operacji INSERT, UPDATE, DELETE i nastawiona na przechowywanie jak najmniejszej ilości rekordów. Hurtownia Danych to system który powinien być w stanie odpowiedzieć natychmiastowo na praktycznie każde pytanie związane z działalnością przedsiębiorstwa. W większości przypadków informacja dostarczana z hurtowni danych jest używana przez managerów, którzy podejmują decyzje biznesowe. Przez to informacja powinna być dostępna szybko i w prosty sposób, ale nie koniecznie musi ona być jak najświeższa i bardzo detaliczna (czyli manager w hurtowni danych raczej będzie szukał odpowiedzi na pytanie o sumę sprzedaży w podziale na regiony na dzień wczorajszy, niż będzie szukał listy dzisiejszych faktur z ostatniej godziny).

Hurtownia danych ma wspomagać przetwarzanie informacji dla celów strategicznych i analitycznych (w przeciwieństwie do systemów transakcyjnych realizujących przetwarzanie dla celów operacyjnych).

Hurtownia danych jest to rodzaj bazy danych, która jest zorganizowana i zoptymalizowana pod kątem pewnego wycinka rzeczywistości. Hurtownia danych jest wyższym szczeblem abstrakcji niż zwykła baza danych, (choć do jej tworzenia używane są także podobne technologie). W skład hurtowni wchodzą zbiory danych zorientowanych tematycznie (np. hurtownia danych klientów). Dane te często pochodzą z wielu źródeł, są one zintegrowane i przeznaczone wyłącznie do odczytu. Dane przechowywane w hurtowni obejmują długie okresy czasu, często są to dane sumaryczne.

W praktyce hurtownie są bazami danych integrującymi dane z wszystkich pozostałych systemów bazodanowych w przedsiębiorstwie.

Głównym celem budowy hurtowni danych jest dostarczenie właściwych informacji we właściwym miejscu i czasie by na ich podstawie można było podejmować decyzję.

Podczas procesu wdrożenia (ang. Deploy) napotkaliśmy na błąd wynikający z braku uprawnień użytkownika gredest2 do wykonywania niektórych instrukcji.

Rysunek 4. Podgląd błędu w Control Center Jobs

Rysunek 5. Właściwości błędu.

We właściwościach błędu była zawarta instrukcja, że należy wykonać skrypt grant_upgrade_privileges.sql

Rysunek 6. Wynik wykonania skryptu sql-a z nazwą użytkownika podaną jako parametr.

Po tej operacji deploy zakończył się poprawnie: