X Konferencja PLOUG

Hurtownie danych wykład 4

Hurtownie danych oparte o Oracle9i/10g –przegląd funkcjonalności

Hurtownie danych, zwane również magazynami danych (ang. data warehouses)
stają się obecnie niezbędnym komponentem systemów informatycznych w dużych

firmach i instytucjach.

Z technologicznego punktu widzenia hurtownia danych jest ogromną bazą
danych, do której wczytuje się dane z tzw. produkcyjnych źródeł danych. Na

hurtowni danych pracują tzw. aplikacje analityczne (ang. On-line Analytical
Processing - OLAP), czy eksploracji danych (ang. data mining). Aplikacje takie są
zorientowane na przetwarzanie danych historycznych i zagregowanych.

Większość operacji realizowanych przez tego typu aplikacje obejmuje złożone
zapytania wykorzystujące łączenie, filtrowanie, agregowanie, wymagające

dostępu do milionów rekordów tabel bazy danych.

Przykładami takich zapytań mogą być:

Jaki jest trend sprzedaży towarów z branży RTV w ostatnich kilku tygodniach?

Jaki jest rozkład sprzedaży DVD-player w województwie Świętokrzyskim ?

Przetwarzanie w aplikacjach analitycznych charakteryzuje się operacjami odczytu
dużej liczby danych, przetwarzanych następnie przez złożone funkcje analityczne.

Zawartość hurtowni  danych  poddana  złożonej  analizie i eksploracji  danych  staje
się  bezcennym  źródłem  wiedzy  wykorzystywanej  w  procesach  decyzyjnych.
Dzięki szybkiej analizie bazującej na pełnej i aktualnej informacji o stanie firmy,
kadra  zarządzająca  może  podejmować  właściwe  decyzje  o  strategicznym

znaczeniu dla rozwoju danego przedsiębiorstwa.

Wiodące firmy wytwarzające oprogramowanie, m.in. Oracle dostarczają

zaawansowanych narzędzi do budowy hurtowni danych, ich optymalizacji,
zarządzania nimi, oraz do budowy aplikacji analitycznych.

Budując

hurtownię

danych

należy

sprostać

wielu

problemom

techniczno/technologicznym. Do najważniejszych z nich należą:
(1) zaprojektowanie właściwego schematu/struktury magazynu,

(2)  sposób zasilania hurtowni danymi,
(3)   zbudowanie funkcjonalnych aplikacji analitycznych,
(4)  zapewnienie efektywnego dostępu do dużych wolumenów danych.

Hurtownie danych wykład 4

2. Architektura systemu magazynu danych

Dane zasilające magazyn danych są często przechowywane w heterogenicznych
systemach informatycznych, czyli posiadających różne struktury, funkcjonalność i

wykorzystujących różne modele danych (np. hierarchiczne, relacyjne, obiektowe,
semistrukturalne), w dokumentach tekstowych, czy arkuszach kalkulacyjnych.
Często nawet w ramach tej samej instytucji wykorzystuje się różne systemy

informatyczne. Heterogeniczność źródeł danych utrudnia spójny dostęp do
informacji.

Dodatkowym problemem jest geograficzne rozproszenie źródeł. Koniecznym jest

więc dostarczenie oprogramowania, które zapewni spójny i zintegrowany dostęp
do takich źródeł.

Dane do magazynu wczytuje się w złożonym procesie, zwanym ETL (Extraction-
Translation-Loading).

Proces ten składa się z trzech następujących faz:



odczytu danych ze źródeł (Extraction),



transformacji ich do wspólnego modelu wykorzystywanego w magazynie

wraz z usunięciem wszelkich niespójności (Translation),



wczytanie danych do magazynu (Loading).

Architektura techniczna systemu magazynu danych wspiera ten proces. Na

rysunku I przedstawiono w pełni funkcjonalną architekturę takiego systemu.
Obiekty oznaczone jako ZD

, ZD

reprezentują źródła danych. Z każdym z

takich źródeł jest związana warstwa oprogramowania – konwertera i monitora.
Zadaniem pierwszego z nich jest transformowanie danych z formatu

wykorzystywanego w źródle, do formatu wykorzystywanego w hurtowni danych.
Zadaniem modułu monitora jest wykrywanie zmian w danych źródłowych i ich
przekazywanie do warstwy oprogramowania integratora.

Moduł integratora jest odpowiedzialny za integrowanie danych w jeden spójny
zbiór przed ich wczytaniem do hurtowni. Hurtownia danych zawiera zarówno

dane elementarne, bieżące i historyczne, jak i dane przetworzone – zagregowane.

Centralna hurtownia danych zawiera dane dla wszystkich grup decydentów. Ze
względu na liczbę przechowywanych w niej danych, wygodnie jest budować w

oparciu o nią, małe tematyczne hurtownie (ang. data marts), zawierające dane
opisujące wąskie dziedziny funkcjonowania firmy.

Taka tematyczna hurtownia zawiera dane zwykle na wyższym poziomie agregacji
niż w hurtowni centralnej.

Przykładowo, hurtownia danych nt. ruchu telefonicznego i opłat klientów może
posłużyć do zbudowania dwóch data marts opisujących odpowiednio natężenie
ruchu telefonicznego w ciągu doby i ranking klientów ze względu na wysokość
płaconych rachunków.

Hurtownie danych wykład 4

Modele danych – reprezentacja danych w hurtowni

Dane w magazynie można reprezentować i przechowywać w oparciu o model
relacyjny, zwany również ROLAP (ang. Relational OLAP) i wielowymiarowy,

zwanym również MOLAP lub MDOLAP (ang. Multidimensional OLAP). Często w tej
samej bazie danych reprezentuje się informacje częściowo w modelu ROLAP, a
częściowo w MOLAP.

Taki sposób reprezentacji nazywa się hybrydowym – HOLAP (ang. Hybrid OLAP).
Baza danych Oracle9i/10g umożliwia reprezentowanie i przechowywanie danych
we wszystkich wspomnianych wyżej modelach.

ROLAP

Magazyn danych w technologii ROLAP jest implementowany w postaci tabel,
których schemat posiada najczęściej strukturę gwiazdy (ang. star schema) lub

płatka śniegu (ang. snowflake schema) lub konstelacji faktów (ang. fact
constellation) lub strukturę gwiazda–płatek śniegu (ang. Starflake schema).

W przypadku implementacji relacyjnej zalecane jest zdefiniowanie dodatkowych
logicznych obiektów bazy danych opisujących ww. schematy. Obiektami tymi są
wymiar (ang. dimension), hierarchia wymiaru (ang. dimension hierarchy) i

zależności funkcyjne (ang. Functional dependencies). Rysunek 2

Sklepy, Produkty i Czas są nazywane tabelami wymiarów (ang. dimension tables),
natomiast tabela centralna jest nazywana tabelą faktów (ang. fact table).

Atrybuty tabeli faktów przechowujące informacje o sprzedaży są nazywane
miarami (ang. measures), np. wartość, l_sztuk. Tabela faktów – Sprzedaż zawiera
również atrybuty produkt_id, sklep_id, data, których wartości wskazują na

odpowiednie wymiary. W takim schemacie tabele wymiarów są denormalizowane,
por. tabele Sklepy, Produkty, Czas.

Jeśli wymiary są znormalizowane, wówczas schemat magazynu danych ma postać
płatka śniegu (ang. snowflake schema). Przykładowy schemat o takiej strukturze

został przedstawiony na rysunku 3.

W tym przypadku, wymiary Lokalizacja, Produkty i Czas mają postać hierarchii.

Przykładowo, wymiarze Lokalizacja każdy sklep (tabela Sklepy) znajduje się w
mieście (tabela Miasta), które z kolei znajduje się w województwie (tabela
Województwa).

Schemat gwiazdy lub płatka śniegu, w którym ten sam wymiar jest powiązany z
wieloma tabelami faktów nazywa się schematem konstelacji faktów (ang. fact
constellation schema).

Hurtownie danych wykład 4

Natomiast schemat, w którym część wymiarów ma postać znormalizowaną (tzn.
posiadają strukturę hierarchiczną), a część ma postać zdenormalizowaną nazywa
się

schematem

gwiazdy–płatka

śniegu.

praktyce,

względów

efektywnościowych najczęściej stosuje się schematy gwiazdy lub gwiazdy –płatka

śniegu.

Modelowanie wymiarów – logiczne obiekty bazy danych

Implementując hurtownię w oparciu o jeden z powyższych schematów, zalecane

jest zdefiniowanie dodatkowych logicznych obiektów bazy danych opisujących
powyższe schematy.

Obiektami tymi są wymiar (ang. dimension), hierarchia wymiaru (ang. dimension
hierarchy) i zależności funkcyjne (ang. functional dependencies).

Jako przykład ilustrujący tworzenie takich obiektów rozważmy tabelę wymiaru

Produkty, o następującym schemacie:

Name

Null?

Type

---------- -------- ------------

PRODUKT_ID NOT NULL

NUMBER(6)

NAZWA

NOT NULL

VARCHAR2(30)

CENA_DET

NOT NULL

NUMBER(6,2)

CENA_HURT NOT NULL

NUMBER(6,2)

PODGRUPA NOT NULL

VARCHAR2(30)

PODGR_INF

VARCHAR2(50)

GRUPA

NOT NULL

VARCHAR2(30)

GRUPA_INF

VARCHAR2(50)

Poniższe polecenie create dimension definiuje dla tabeli Produkty hierarchię
wymiaru i zależności funkcyjne. Hierarchię tę przedstawiono na rysunku 4,

natomiast zależności funkcyjne są następujące: id_prod _ {nazwa, cena_det,
cena_hurt}, podgrupa _ {podgr_inf}, grupa _{grupa_inf}.

create dimension dim_produkty

level produkt is produkty.id_prod
level podgrupa is produkty.podgrupa

level grupa is produkty.grupa

hierarchy hier_produkty

(produkt child of
podgrupa child of
grupa)

attribute produkt determines

(nazwa, cena_det, cena_hurt)

attribute podgrupa determines

(podgr_inf)

attribute grupa determines

(grupa_inf);

Omawiane obiekty są wykorzystywane przez kosztowy optymalizator zapytań w
procesie przepisywania zapytań.

Hurtownie danych wykład 4

MDOLAP

Hurtownia danych zaprojektowana w technologii MOLAP do przechowywania
danych wykorzystuje wielowymiarowe tablice (ang. multidimensional arrays,

datacubes).

Tablice te zawierają wstępnie przetworzone (m.in. zagregowane) dane

pochodzące  z  wielu  źródeł.  Przykładowa  trójwymiarowa  tablica  została
przedstawiona na rysunku 5.

Zawiera  ona  trzy  wymiary:  Lokalizacja,  Czas  i  Produkty  oraz  zagregowane

informacje (poszczególne kostki) o sprzedaży wybranych produktów
w poszczególnych latach, w wybranych miastach.

W systemie Oracle9i/10g dane wielowymiarowe są przechowywane w tzw.
przestrzeni analitycznej (ang. analytic workspace). Definiowanie tej przestrzeni i
zarządzanie nią realizuje się albo z wykorzystaniem oprogramowania Analytic

Workspace Manager, albo Warehouse Builder, albo z poziomu SQL wykorzystując
do tego celu pakiety systemowe.

Zbiór pakietów rodziny CWM2 umożliwia zarządzanie informacjami słownikowymi

(metadanymi) opisującymi przestrzeń analityczną. DBMS_AWM zawiera
procedury tworzenia przestrzeni analitycznej, natomiast DBMS_AW zawiera
procedury umożliwiające operowanie na danych wielowymiarowych, m.in.

wczytywanie danych z tabel i analizę danych.

DBMS_AW_UTITLITIES udostępnia procedury zarządzania miarami w przestrzeni

analitycznej. OLAP_TABLE umożliwia prezentowanie danych wielowymiarowych
w postaci tabelarycznej (relacyjnej).

Poniżej przedstawiono wybrane przykładowe polecenia obsługi danych w

przestrzeni analitycznej. Polecenia te zostały wykonane z wykorzystaniem
pakietu systemowego DBMS_AW.
Polecenie 1. tworzy nową pustą przestrzeń analityczną o nazwie test_ws.

Polecenie 2. definiuje wymiary sklepy, produkty i czas.
Polecenie 3. definiuje zmienną l_sztuk reprezentującą miarę. Wymiarami
dla niej są sklepy, produkty i czas.

Polecenie 4. wpisuje wartości do wymiaru sklepy.

. exec DBMS_AW.EXECUTE('AW CREATE test_ws;')

2. exec DBMS_AW.EXECUTE('DEFINE sklepy DIMENSION TEXT W 15; -

DEFINE produkty DIMENSION TEXT W 20; -
DEFINE czas DIMENSION TEXT W 15;')

3. exec DBMS_AW.EXECUTE('DEFINE l_sztuk VARIABLE DECIMAL -

<sklepy produkty czas>;')

4. exec DBMS_AW.EXECUTE('MAINTAIN sklepy ADD ''SKLEP1'' ''SKLEP2'' ''SKLEP3''

-
''SKLEP4'' ''SKLEP5'' ''SKLEP6'';')

Hurtownie danych wykład 4

Kolejne trzy polecenia służą do prezentowania wartości zmiennej l_sztuk.
Polecenie 5. zawęża wartości w wymiarze czas do miesiąca styczeń.
Natomiast polecenie 6 zawęża wartości w wymiarze sklepy do trzech sklepów.
Polecenie 7. prezentuje dane wielowymiarowe reprezentowane zmienną l_sztuk.

5. exec DBMS_AW.EXECUTE('LIMIT czas TO ''styczeń'';')

6. exec DBMS_AW.EXECUTE('LIMIT sklepy TO ''SKLEP1'' to ''SKLEP3'';')
7. exec DBMS_AW.EXECUTE('REPORT l_sztuk;')

CZAS: styczeń
------------L_SZTUK-------------
-------------SKLEPY-------------

PRODUKTY

SKLEP1

SKLEP2

SKLEP3

---------------- ---------- ---------- ----------

Clinique happy for man 1,00

2,00

Escape for man

2,00

Adidas sport

2,00

1,00

STR8

Armani mania

3,00

6,00

2,00

Bordeaux

6,00

Malibu

1,00

Glenfiddich

Zasilanie danymi

Głównym problemem w dostępie do źródeł zewnętrznych z hurtowni jest

heterogeniczność tych źródeł. Nawet jeśli źródła są bazami danych, to bardzo
często są to bazy danych pochodzące od różnych producentów, a co za tym idzie,
posiadające różną funkcjonalność, reprezentację danych i dialekt języka SQL.

Z tego względu, dostęp z jednej bazy danych do innej musi być realizowany
za pomocą dedykowanego oprogramowania dla tych baz. Oprogramowanie to
nosi nazwę gateway'a. Jego zadaniem jest m.in. transformowanie dialektów SQL i

reprezentacji (typów) danych przesyłanych między bazami danych.

Z instalacją Oracle9i/10g jest dostarczane oprogramowanie Transparent
Gateways, które umożliwia dostęp i wymianę danych z większością głównych

komercyjnych systemów relacyjnych, tj. wytwarzanych przez IBM, Sybase,
Microsoft, NCR.

Ponadto, z wykorzystaniem sterowników ODBC/JDBC można realizować dostęp
do wspomnianych wyżej i wielu innych baz danych.

Natomiast definiowanie całego procesu ETL jest wspomagane oprogramowaniem
Warehouse Builder.

Dane do hurtowni wczytuje się często również z plików tekstowych. W tym

zakresie rozwiązania Oracle9i/10g obejmują m.in.:

Hurtownie danych wykład 4



pakiet systemowy UTL_FILE,



oprogramowanie SQL*Loader,



tabele zewnętrzne i funkcje tablicowe.

Pakiet UTL_FILE zawiera procedury i funkcje odczytu i zapisu plików. SQL*Loader
umożliwia wczytywanie informacji z plików tekstowych o różnym formacie.
Możliwe jest także weryfikowanie, transformowanie i filtrowanie danych przed

ich  wczytaniem  do  bazy.  Rozszerzeniem  tej  funkcjonalności  jest  możliwość
definiowania  w  bazie  danych  tzw.  tabel  zewnętrznych  (ang.  external  tables),
których źródłem danych są pliki tekstowe systemu operacyjnego.

Do  tabel  takich  odwołuje  się  za  pomocą  standardowych  poleceń  SQL  select,  a
dane  są  dynamicznie  pobierane  z  plików  skojarzonych  z  tabelą  zewnętrzną.  W
Oracle10g  za  pomocą  tabel  zewnętrznych  można  dodatkowo  kopiować  dane  z

bazy danych do plików zewnętrznych.

Mechanizm tzw. funkcji tablicowych (ang. table functions) udostępnia dane, które

najpierw są przetwarzane, a następnie przekazywane przez funkcje napisane w
języku PL/SQL. Funkcje te są składowane w bazie danych. Źródłami danych dla
tych funkcji mogą być dowolne obiekty bazy danych (np. zwykłe tabele lub tabele

zewnętrzne) lub pliki tekstowe.

Dane  mogą  być  przetwarzane  w  bardzo  złożony  sposób,  a  algorytmy  ich
przetwarzania  implementuje  się  w  języku  proceduralnym.  Funkcje  tablicowe  w

zapytaniach wykorzystuje się w sposób identyczny jak tabele.

Kolejny problem stanowi aktualność danych przechowywanych w hurtowni.

Ponieważ zawartość źródeł danych jest bezustannie modyfikowana, więc
zawartość hurtowni po pewnym czasie staje się nieaktualna. Wobec tego, w
trakcie jej eksploatacji musi ona być odświeżania. Podczas procesu odświeżania

dąży się do tego, aby do hurtowni trafiały jedynie te dane ze źródeł, które
uległy zmianie od czasu poprzedniego odświeżania. Jest to tzw. odświeżanie
przyrostowe (ang.incremental refershing).

Baza

danych

Oracle9i/10g

posiada

dwa

mechanizmy

umożliwiające

propagowanie zmian z bazy produkcyjnej do hurtowni. Pierwszy z nich to
wyzwalacze (ang. triggers), czyli procedury wyzwalane na skutek operacji

modyfikowania  danych  źródłowych.  Procedury  te  mogą  informować  moduł
monitora  lub  propagować  dane  do  hurtowni.  Projektant  hurtowni  musi  w  tym
przypadku sam zaimplementować właściwie działające wyzwalacze.

Drugi  mechanizm  wykorzystuje  tzw.  migawki  (ang.  snapshots)  zwane  również
perspektywami  zmaterializowanymi  (ang.  Materialized  views).  Perspektywa
zmaterializowana  jest  kopią  tabeli  lub  jej  fragmentu  pochodzącego  z  innej  bazy

danych.  W  definicji  takiej  perspektywy  określa  się  najczęściej:  częstotliwość  jej
odświeżania,  sposób  odświeżania  (przyrostowe,  pełne),  zbiór  danych  ze  źródła
który  ma  być  dostępny  w  perspektywie.  System  zarządzania  hurtownią  danych

sam kontroluje proces odświeżania perspektywy.

Hurtownie danych wykład 4

Odświeżanie przyrostowe perspektyw zmaterializowanych jest możliwe dopiero
po

utworzeniu

bazie

produkcyjnej

tzw.

dziennika

perspektywy

zmaterializowanej (ang. materialized view log) dla każdej tabeli, z której
perspektywa pobiera dane.

Perspektywy zmaterializowane tworzy się albo z wykorzystaniem
oprogramowania Enterprise Manager albo z poziomu SQL. Przykładowo, poniższe

polecenie create materialized view tworzy perspektywę zmaterializowaną, która
jest odświeżana przyrostowo (klauzula refresh fast) i automatycznie
co 1 minutę (klauzula start with sysdate+(1/(24*60))) .

Źródłem danych dla tej perspektywy są dane z tabeli Sprzedaż w zdalnej bazie
danych (klauzula select).

create materialized view mv_sprzedaz

build immediate
refresh fast

start with sysdate+(1/(24*60))
next sysdate+(1/(24*60*6))
as

select * from sprzedaz@lab92
where data like '%2003';

Odświeżanie przyrostowe powyższej perspektywy będzie możliwe dopiero wtedy,
gdy w źródłowej bazie danych dla tabeli Sprzedaż zostanie zdefiniowany tzw.
dziennik perspektywy zmaterializowanej (ang. materialized view log)

rejestrujący wszystkie zmiany zawartości Tabeli sprzedaż, jak przedstawiono
poniżej.

create materialized view log on sprzedaz;

Analiza danych

Funkcjonalność OLAP

Dane przechowywane w hurtowni podlegają zaawansowanym analizom. Dla
potrzeb takich analiz i złożonego przetwarzania danych, Oracle9i/10g udostępnia
szereg klauzul i funkcji ułatwiających konstruowanie zapytań OLAP.
W zakresie wyznaczania agregatów rozszerzono język SQL o klauzule group by

cube, group by rollup, group by grouping sets umożliwiające wyliczanie
dodatkowych wartości zagregowanych (wykład 3).

Jako przykład rozważmy zapytanie obliczające sumy sprzedanych produktów w
poszczególnych sklepach, oparte o schemat z rysunku 2. W przykładowym
poniższym wyniku, pogrubiono te dodatkowe podsumowania, które zostały

wyliczone z wykorzystaniem klauzuli cube.

Hurtownie danych wykład 4

select pr.prod_nazwa produkt, sk.nazwa,
sum(l_sztuk) sprzedano
from sprzedaz sp, sklepy sk, produkty pr

where sp.sklep_id=sk.sklep_id
and sp.produkt_id=pr.produkt_id
group by cube (pr.prod_nazwa, sk.nazwa);

PRODUKT NAZWA

SPRZEDANO

---------

-------

----------

SKLEP1

SKLEP2

SKLEP3

Rioja

SKLEP1

Rioja

SKLEP2

Rioja

SKLEP3

Bordeaux

Bordeaux SKLEP1

Bordeaux SKLEP2

Bordeaux SKLEP3

W wersji Oracle10g do polecenia select dodano klauzulę model, która umożliwia

zagnieżdżenie w zapytaniu kodu napisanego w specjalnie do tego celu
dedykowanym języku proceduralnym.

Możliwe jest m.in. wykorzystanie pętli i zmiennych. Cel stosowania tej klauzuli

jest dwojaki.

Po pierwsze wyliczanie wartości hipotetycznych w celach predykcyjnych

(przewidywanie), np. predykcja sprzedaży w roku następnym na podstawie lat
poprzednich.

Po  drugie  symulowanie  w  bazie  danych  arkuszy  kalkulacyjnych.  Elementarną
funkcjonalność klauzuli model zilustrowano poniższymi przykładami.

Przyjmijmy, że w bazie danych zdefiniowano perspektywę  view_sprzedaż opartą

o  tabele  Sklepy,  Produkty,  Czas  i  Sprzedaż  (por.  rysunek  2).  Perspektywa  ta
udostępnia  sumy  kwot  sprzedaży  w  poszczególnych  sklepach,  poszczególnych
produktów, w poszczególnych miesiącach.

Poniższe zapytanie do tej perspektywy wykorzystuje klauzulę model w
następujący sposób. Dane relacyjne są transformowane do 2-wymiarowej kostki,
której wymiarami są nazwa produktu i nazwa miesiąca (atrybuty prod_nazwa i

nazwa_miesiąca w klauzuli dimension by).

Hurtownie danych wykład 4

Miarę stanowi atrybut kwota (klauzula measures(kwota)).

Klauzula  partition  by  (miasto)  umożliwia  podzielenie  kostki na  "podkostki"  –  po
jednej  dla  każdego  miasta.  Do  wartości  miary  w  każdej  "podkostce"  jest

stosowana reguła specyfikowana w klauzuli rule.

W poniższym przykładzie kwota sprzedaży kosmetyku o nazwie STR8 w kwietniu

jest hipotetycznie wyliczana jako kwota sprzedaży tego kosmetyku w marcu
powiększona o 20%. W ogólności, w klauzuli model można definiować wiele
reguł.

select miasto, prod_nazwa, nazwa_miesiaca, kwota
from view_sprzedaz

where miasto = 'Kraków'
model return all rows
partition by (miasto)

dimension by (prod_nazwa, nazwa_miesiaca)
measures (kwota)
rules( kwota['STR8', 'kwiecień'] = kwota['STR8', 'marzec'] * 1.2)

order by prod_nazwa, nazwa_miesiaca;

Przykładowy wynik omawianego zapytania przedstawiono poniżej. Rekord, który

został wyliczony klauzulą model przedstawiono jako pogrubiony i pochylony.
Pozostałe rekordy pochodzą z bazy danych.

MIASTO

PROD_NAZWA

NAZWA_MIESIACA

KWOTA

----------

-------------

---------------

----------

Kraków

Adidas sport

marzec

Kraków

Bordeaux

styczeń

450

Kraków

STR8

kwiecień

264

Kraków

STR8

marzec

220

Kraków

STR8

styczeń

660

Kraków

Malibu

styczeń