Transcript PPT - pjwstk.edu.pl

Hurtownie danych a bazy danych
Przygotował Lech Banachowski na podstawie:
1.
Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, McGrawHill, 2000
(książka i slide’y).
2.
Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wyd. PJWSTK,
2007.
1
Wprowadzenie
v
v
Firmy i organizacje muszą dokonywać analizy aktualnych i
historycznych danych aby zidentyfikować użyteczne
wzorce i trendy w celu wspomagania strategii
rozwojowych ich firmy lub organizacji.
Analiza jest często złożona, ma charakter interakcyjny i
odkrywczy, dotyczy bardzo dużych zbiorów danych
tworzonych w wyniku integracji danych pochodzących z
różnych części firmy; analizowane dane nie zmieniają się
(tylko przybywa ich w czasie).
– Takie przetwarzanie danych nazywa się On-Line Analytical
Processing (OLAP).
– Poprzednio rozważaliśmy On-line Transaction Processing (OLTP)
- transakcje złożone z “krótkich” zapytań i modyfikacji.
2
Przykładowe pytania istotne dla osób
podejmujących decyzje
v
v
v
v
v
v
v
Które produkty przynoszą firmie najwięcej zysku?
Którzy klienci przynoszą najwięcej zysku? Co mają ze
sobą wspólnego? Jak często korzystają z usług
bezpośredniej pomocy telefonicznej?
W których regionach kraju firma osiąga największe zyski,
z jakiego rodzaju działalności te zyski pochodzą?
Jakie usługi są najbardziej pożądane dla klientów firmy?
Kiedy powinno się wprowadzić na rynek nowe
produkty?
Które produkty powinno się wycofać z rynku?
Którzy dostawcy są najmniej pewni?
3
Trzy komponenty
v
Hurtownia danych: Zintegrowane dane
pochodzące z wielu źródeł:
– Ładowanie nowych danych z wielu źródeł.
– Integracja semantyczna (np. uzgodnienie walut, miar, formatów).
v
OLAP:
– Złożone zapytania i perspektywy SQL.
– Zapytania podobne do operacji na arkuszach kalkulacyjnych i na
wielowymiarowych danych.
– Zapytania interakcyjne i “online”.
v
Data Mining: Eksploracyjne wyszukiwanie
interesujących trendów i anomalii.
4
ZEWNĘTRZNE ŹRƠDŁA DANYCH
Hurtownia danych
v
v
v
v
Zintegrowane dane obejmujące
EXTRACT
długie okresy czasu, często
TRANSFORM
LOAD
rozszerzone o informacje
REFRESH
sumaryczne.
Rozmiar w gigabajtach i
terabajtach.
HURTOWNIA
Oczekuje się szybkiej
Repozytorium DANYCH
odpowiedzi na złożone
metadanych
zapytania – dlatego jest
konieczne wcześniejsze
WSPOMAGA
przetworzenie danych.
Modyfikacje danych praktycznie
nie występują.
DATA
OLAP
MINING
5
Podejście
v
Projektując hurtownię danych stosujemy inne podejście niż
przy projektowaniu baz operacyjnych. Zamiast:
– “Po co przechowywać miesięczne salda kont bankowych, skoro
można je wyliczyć mając zapis ciągu wykonanych na nim operacji?”
v
stawiamy pytanie:
– “Czemu nie wyliczyć raz i nie przechowywać miesięcznych stanów
kont, skoro 90% wykonywanych analiz wymaga właśnie tak
przygotowanych danych?”
v
Dobrze zaprojektowane wstępne przetworzenie danych
może istotnie przyśpieszyć czas wykonywania 90% analiz!
6
Metody stosowane w hurtowni danych aby
zmniejszyć złożoność obliczeń
v
v
Agregacje - wstępne wyliczenie pewnych miar
przydatnych w późniejszych analizach.
Podział na partycje - podział tabel na części tak by
zmniejszyć rozmiar danych, które trzeba będzie
przeczytać w trakcie wykonywania analiz
ewentualnie tak aby przetwarzać dane równolegle.
7
Tematy
v
Dane w hurtowni danych grupuje się według
tematów, np.
–
–
–
–
–
v
finanse,
produkty,
pracownicy,
klienci,
sprzedaż.
Typowym pytaniem zadawanym przy analizie
danych jest:
– Jakie są dane o ... w rozbiciu na ... ? np.
u
u
u
dane o sprzedaży tegorocznej i ubiegłorocznej w rozbiciu na
produkty i miesiące roku;
dane o zyskach w rozbiciu na regiony kraju i rodzaj działalności;
dane o obrotach w rozbiciu na poszczególnych klientów i sektory
rynku.
8
Wielowymiarowy model danych
v
Mając ustalony temat, wyróżniamy dla niego dwa rodzaje
danych:
– wielkości analizowane (fakty, miary) - dane ilościowe opisujące
pewne fakty: sprzedaż, zyski, obroty,
– wielkości klasyfikujące (wymiary) - dane klasyfikujące opisywane
fakty wg okoliczności ich zaistnienia - czas, miejsce, osoba itp.
v
Stąd wynika, że każdy fakt istnieje w wielowymiarowej
przestrzeni np. fakt pojedynczej sprzedaży istnieje w
wielowymiarowej przestrzeni, w której poszczególne
wymiary to:
–
–
–
–
czas,
struktura sprzedaży,
struktura klientów,
struktura produktów itp.
9
sprzedaż
mid
cid
pid
Wielowymiarowa kostka
11 1 1 25
Kolekcja liczbowych miar, które zależą 11 2
od szeregu wymiarów.
11 3
– Np. miara Sprzedaż, wymiary: Produkt
12 1
(klucz: pid), Miejsce (klucz: mid) i Czas
12 2
(klucz: cid).
12 3
8 10 10
Przekrój kostki
13 1
mid=1:
30 20 50
13 2
13
3
25 8 15
mid
11 1
1
2
3
pid
11 12 13
v
cid
1 8
1 15
1 30
1 20
1 50
1 8
1 10
1 10
2 35
10
Hierarchie wymiarów
v
v
W naturalny sposób obrazują drogę przejścia od danych
szczegółowych do coraz bardziej ogólnych.
Dla wymiaru czasu:
– dzień -> miesiąc -> kwartał -> rok; dzień -> tydzień -> rok
v
Dla wymiaru geograficznego:
– miasto -> województwo -> kraj; oddział -> kraj
v
Dla wymiaru produktów:
– opakowanie -> produkt -> grupa produktów
v
Hierarchie nie muszą być jednoznaczne np. jeden oddział
firmy może obsługiwać dwa województwa, oraz
jednocześnie może istnieć województwo z dwoma
oddziałami.
11
Schemat gwiazda
W schemacie tym mamy jedną tabelę faktów w środku układu i zbiór tabel
wymiarów, każda z nich opisuje jeden wymiar. Identyfikatory wartości wymiarów
stanowią klucze obce w tabeli faktów.
Przykład: tabela faktów sprzedaży w podziale względem wymiarów struktury
sprzedaży, czasu, wymiaru geograficznego i wymiaru produktów.
Struktura
sprzedaży
id_sprz
nazwisko
oddział
Czas
id_czas
miesiąc
kwartał
rok
Wymiar
geograficzny
Sprzedaż
id_sprz
id_geo
id_czas
id_prod
ilość
cena
zniżka
wartość
id_geo
miasto
wojew
makroreg
Produkty
id_prod
produkt
opakow
grupa_pr
12
Tabele w hurtowni danych (c.d.)
v
Tabele wymiarów mogą posłużyć do znajdowania
odpowiedzi na pytania dotyczące klasyfikacji danych bez
sięgania w ogóle do tabeli faktów, np.
– Na jakie regiony geograficzne zostały podzielone dane?
– Jakie województwa zawiera dany makroregion?
– Ile pozycji będzie zawierał raport w rozbiciu na oddziały firmy?
v
Gdzie
zapisywać
(podsumowujących)?
wyniki
operacji
agregujących
– Albo używać tego samego zestawu tabel dodając w każdej tabeli
wymiaru atrybut poziomu agregacji danych względem którego są
agregowane wartości analizowane,
– albo zapisywać wyniki agregacji w osobnych tabelach.
13
Wielowymiarowe tablice - MOLAP
v
v
Reprezentacja kostki wielowymiarowej za pomocą
wielowymiarowej tablicy ( wielowymiarowego arkusza
kalkulacyjnego). Przy dużej liczbie elementów trzeba
przechowywać zawartość tej tablicy na dysku i tylko
częściami sprowadzać do pamięci wewnętrznej w
celu wykonania obliczeń.
Każdą tabelę wymiaru można też reprezentować za
pomocą tablicy i ze względu na niewielki jej rozmiar
i częste użycie można ją przechowywać w pamięci
wewnętrznej.
14
Operacje
v
pivoting (zestawienie krzyżowe) – wskazanie do analizy:
miary np. sprzedaży oraz dwóch wymiarów np. miasta i roku
oraz przypisaniu wartościom tych wymiarów pewnej
agregacji wartości wybranej miary np. sumarycznej
sprzedaży w danym mieście w danym roku.
15
Operacje
v
v
drill-down – rozwijanie – rozwinięcie danego wymiaru do
kolejnego elementu tego wymiaru – np. mając wyniki
sprzedaży w rozbiciu na lata, chcemy poznać wyniki
sprzedaży w rozbiciu na miesiące. Operacją odwrotną jest
roll-up - zwijanie od rozbicia na bardziej szczegółowe
elementy wymiaru do bardziej ogólnych.
slice-and-dice – wycinanie – operacja projekcji na wybrany
podzbiór wymiarów dla wybranych wartości innych
wymiarów np. dane o sprzedaży poszczególnych
produktów (projekcja na wymiar produktów) w ubiegłym
roku (selekcja na wymiarze czasu).
16
IMPLEMENTACJA HURTOWNI DANYCH
W ORACLE
Histogramy
Dystrybucja wartości w kolumnie obliczana za pomocą instrukcji ANYLYZE
TABLE:
ANALYZE TABLE Sprzedaż
COMPUTE STATISTICS FOR COLUMNS Wartość_sprzedaży
SIZE 10;
-- liczba przedziałów, na które dzieli się wartości sprzedaży.
Histogramy są używane przez optymalizator zapytań. Można je odczytać z
USER_HISTOGRAMS i USER_TAB_COLUMNS.
17
Równoległe obliczenia
Tworząc tabelę możemy określić jej stopień zrównoleglenia:
CREATE TABLE XXX(....)
PARALLEL (DEGREE 8);
-- stopień zrównoleglenia zapytań dla tej tabeli
• Proces przyjmujący w serwerze bazy danych zgłoszenie użytkownika stara
się przyporządkować do obsługi zapytania z tą tabelą liczbę procesów serwera
równą temu stopniowi.
• Zrównolegleniu może ulec ładowanie danych do tabeli.
18
Obiekty z partycjami
(partycjonowane)
Na różnych stacjach dyskowych - zrównoleglenie we/wy dyskowego
CREATE Klienci(
Id NUMBER(5) PRIMARY KEY,
Kraj CHAR(2), ......)
PARTITION BY RANGE (Kraj)
-- klucz partycji: najlepiej aby nie ulegał modyfikacji
(PARTITION p1 VALUES LESS THAN ('C')
TABLESPACE Data01;
PARTITION p2 VALUES LESS THAN ('I')
TABLESPACE Data02;
...
PARTITION p19 VALUES LESS THAN MAXVALUE
TABLESPACE Data19);
19
Indeksy bitmapowe
CREATE BITMAP INDEX Ind_P_Os ON Osoby(Płeć);
CREATE BITMAP INDEX Ind_P_Os ON Osoby(Stanowisko);
Nazwisko
Płeć
Stanowisko
Kruszewska
M
Dyrektor
Jankowski
M
Sprzedawca
Malinowski
M
Sprzedawca
Gazda
K
Kasjer
Wiśniewski
M
Kasjer
Bojanowska
K
Sprzedawca
Indeks na Płeć
Indeks na Stanowisko
Nr wiersza
M
K
D S K
----------------------------------------1
1
0
1 0 0
2
1
0
0 1 0
3
1
0
0 1 0
4
0
1
0 0 1
5
1
0
0 0 1
6
0
1
0 1 0
SELECT Nazwisko FROM Osoby WHERE
(Płeć = 'K' AND Stanowisko ='K') OR
(Płeć = 'M' AND
Stanowisko = 'D')
sprowadza się do wykonania trzech operacji na wektorach
bitowych:
([000101] AND [000110]) OR ([111010] AND [100000])
= [100100]
dając jako wynik zapytania pierwszą i czwartą osobę
(miejsca jedynek w wektorze)
20
Implementacja indeksu bitmapowego
Dwie pomocnicze struktury danych:
v jedna, która w oparciu o wartość atrybutu pozwala
szybko wyznaczyć przypisany jej wektor bitów
(np. struktura danych B+ drzewa);
v druga, która w oparciu o numer wiersza (pozycję
jedynki w wektorze bitów) pozwala szybko
wyznaczyć ten wiersz (np. tablica lub B+ drzewo).
21
Optymalizacja "star query"
Jedna duża tabela faktów i wiele drobnych tabeli wymiarów (zastosowanie indeksów
bitmapowych). Zapytanie:
SELECT * FROM Sprzedaż, Miejsce, Towar, Czas
WHERE Sprzedaż.Id_miejsca = Miejsce.Id
AND Sprzedaż.Id_towaru = Towar.Id
AND Sprzedaż.Id_czasu = Czas.Id
AND Miejsce.Miasto IN ('WAW','KRA','RAD')
AND Towar.Kategoria = 'OPROGRAMOWANIE'
AND Czas.Rok > 1996
jest transformowane na:
SELECT * FROM Sprzedaż
WHERE
Sprzedaż.Id_miejsca IN (SELECT Miasto.Id FROM Miasto
WHERE Miejsce.Miasto. IN ('WAW','KRA','RAD'))
AND Sprzedaż.Id_towaru IN (SELECT Towar.Id FROM Towar
WHERE Towar.Kategoria = 'OPROGRAMOWANIE')
AND Sprzedaż.Id_czasu IN (SELECT Czas.Id FROM Czas
WHERE Czas.Rok > 1996)
Najpierw:
• oblicza się podzapytania,
• następnie stosuje się indeksy bitmapowe i operacje na wektorach bitów aby znaleźć
fakty spełniające jednocześnie wszystkie trzy warunki IN.
22
Perspektywy zmaterializowane (migawki)
Po zaprojektowaniu tabel faktów i wymiarów następnie projektuje się
perspektywy zmaterializowane określające wymagane agregacje danych z
powyższych tabel. Na perspektywie zmaterializowanej można zakładać
indeksy, więc ma ona te same własności co tabela.
CREATE MATERIALIZED VIEW Sprzedaz_mv
ENABLE QUERY REWRITE
AS
SELECT s.Nazwa_sklepu, SUM(f.Wielkosc) AS Suma
FROM Sklep s, Sprzedaz f
WHERE f.IdSklepu = s.IdSklepu
GROUP BY s.Nazwa_sklepu;
ENABLE QUERY REWRITE - zapytania pisane w terminach tabel faktów i
wymiarów są przekształcane przez optymalizator do zapytań
korzystających z perspektyw zmaterializowanych.
23
Odświeżanie zawartości perspektywy
zmaterializowanej
• COMPLETE – przez powtórne wykonanie zapytania.
• FAST – skorzystanie z dzienników zapisujących zmiany w
tabelach bazowych (MATERIALIZED VIEW LOG).
• ON DEMAND – przez wykonanie explicite instrukcji
odświeżającej (DBMS_MVIEW.REFRESH).
• ON COMMIT – automatycznie po każdym COMMIT
dotyczącym tabel bazowych.
24
Grupowanie ROLLUP
v
v
SELECT Czas.rok, Miejsce.miasto, Towar.kategoria,
SUM(Sprzedaż.wartość)AS "W sumie"
FROM Sprzedaż NATURAL JOIN Miejsce NATURAL JOIN Towar
NATURAL JOIN Czas
GROUP BY ROLLUP(Czas.Rok, Miejsce.Miasto, Towar.Kategoria);
ROK MIASTO KATEGORIA W sumie
------ ------------- ------------------- ---------1996 Warszawa Komputery 100000
1996 Warszawa Telewizory 50000
....
1996 Warszawa
*
1000000
.....
1996
*
*
2000000
1997 Warszawa Komputery 1500000
....
*
*
*
10000000
25
Grupowanie CUBE
v
ROLLUP +
* Warszawa Komputery 1000000
...
* Warszawa
*
9000000
...
*
*
Komputery 8000000
....
1996 *
Komputery 500000
26
Funkcje analityczne
nazwa_funkcji_grupowej(argument,...) OVER
(klauzula_okna)
Dla każdego wynikowego wiersza zapytania
określamy zbiór powiązanych z nim wierszy nazywany oknem tego wiersza. Definiuje się go
za pomocą tzw. klauzuli okna.
27
Klauzula okna
v
v
v
v
PARTITION BY wyrażenie, ... określa podział całego zbioru
wynikowego wierszy na grupy; jeśli zostanie opuszczona,
cały zbiór wynikowych wierszy stanowi jedną grupę.
Wiersze wchodzące w skład okna są zawsze ograniczone
(zawarte) do jednej grupy.
ORDER BY wyrażenie, ...
określa porządek wierszy w
ramach podziału określonego w grupie przez podklauzulę
PARTITION BY.
ROWS specyfikacja_okna specyfikuje okno poprzez
określenie liczby wierszy;
RANGE specyfikacja_okna specyfikuje okno poprzez
określenie zakresu wierszy.
28
PARTITION BY
v
v
v
PARTITION jest podobne do GROUP BY z tym, że wartości
podsumowujące są załączane do każdego wiersza w grupie, a nie tylko
raz dla całej grupy. Oknem wiersza jest jego grupa.
SELECT e.Ename, e.Sal,
SUM(e.Sal) OVER (PARTITION BY e.Deptno) AS Suma,
Round(100*e.Sal/(SUM(e.Sal) OVER (PARTITION BY e.Deptno)))
AS Proc_zarob
FROM Emp e
ORDER BY e.Deptno, e.Ename;
ENAME SAL SUMA PROC_ZAROB
---------- ------- --------- -------------------CLARK 2450 8750 28
KING 5000 8750 57
MILLER 1300 8750 15
ADAMS 1100 10875 10
FORD 3000 10875 28
………………
29
RANGE
Dla każdego pracownika wyznacz liczbę
pracowników, których zarobki są co najwyżej 50zł
mniejsze i co najwyżej 150zł większe od zarobków
tego pracownika. W tym przypadku okno
obliczeniowe dla danego pracownika o zarobkach
e.Sal nie zależy od departamentu i obejmuje
wszystkich pracowników, których zarobki są w
przedziale [e.Sal-50, e.Sal+150]. (Jest określona tylko
jedna grupa - zbiór wszystkich wierszy.)
30
v
v
SELECT e.Ename, e.Sal,
COUNT(*) OVER (ORDER BY e.Sal RANGE BETWEEN
50 PRECEDING AND 150 FOLLOWING) AS Licznik
FROM Emp e;
Oto wyniki:
ENAME SAL LICZNIK
------------ ------ -----------SMITH
800
2
JAMES 950
2
ADAMS 1100
3
WARD 1250
3
MARTIN 1250
3
MILLER 1300
3
TURNER 1500
2
ALLEN 1600
1
………..
31
ROWS
Dla każdego pracownika podaj numer jego kierownika, datę
zatrudnienia, zarobki oraz średnią wartość zarobków
pracowników zatrudnionych bezpośrednio przed i po
zatrudnieniu tego pracownika (włącznie z tym
pracownikiem) wśród pracowników tego kierownika.
SELECT mgr, ename, hiredate, sal,
AVG(sal) OVER (PARTITION BY mgr ORDER BY hiredate
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS ave
FROM Emp;
32
ODM - Oracle Data Mining
Oracle rozpoczął w wersji 10g wprowadzanie pojęć i
algorytmów data-miningowych na razie głównie przy
pomocy pakietu DBMS_DATA_MINING (jeszcze nie na
poziomie SQL i specjalnych obiektów zapisywanych w bazie
danych). Centralne pojęcie to model określający parametry,
algorytm data-miningowy i wprowadzone dane uczące.
Dane do analizy – zbiór punktów w przestrzeni
wielowymiarowej - są dostarczane w jednej tabeli. Wiersze są
nazywane przypadkami (cases). Kolumna ID przypadku
dostarcza jednoznacznego identyfikatora np. CUSTOMER ID
w tabeli klientów. Kolumny są nazywane atrybutami.
Np. model regresji może przewidzieć poziom dochodów
klienta (atrybut typu target) w oparciu o datę urodzenia i płeć
(atrybuty typu predictors).
33
Parametry procedury CREATE_MODEL
model_name
-- nazwa modelu
mining_function -- stała reprezentująca rodzaj problemu
eksploracji danych np. klasyfikacja, clustering, regresja
data_table_name -- nazwa tabeli z danymi „uczącymi” model
case_id_column_name
target_column_name -- NULL dla modeli deskryptywnych,
nie NULL dla modeli predyktywnych
settings_table_name -- ustawienia dla funkcji i algorytmu (np.
nazwa algorytmu eksploracyjnego)
34
Procedura APPLY (model)
Stosuje podany model eksploracyjny do podanych danych i
generuje wyniki w tablicy APPLY. Operacja APPLY jest też
nazywana scoring. Dla modeli predyktywnych, operacja
APPLY generuje wynik w kolumnie docelowej. Dla modeli
deskryptywnych jak clustering, operacja APPLY
przyporządkowuje każdemu przypadkowi
prawdopodobieństwo należenia do klastra.
DBMS_DATA_MINING.APPLY (
model_name IN VARCHAR2,
data_table_name IN VARCHAR2,
case_id_column_name IN VARCHAR2,
result_table_name IN VARCHAR2, -- schemat tworzonej
przez APPLY tabeli zależy od algorytmu );
35