1

Hurtownie danych

http://zajecia.jakubw.pl/hur

Projektowanie hurtowni:
modele wielowymiarowe.

Modelowanie punktowe.

Operacje OLAP na kostkach.

UZASADNIENIE BIZNESOWE

Po co nam hurtownia danych? Jakie mogą być efekty analiz?

- Wzrost sprzedaży, zwiększenie udziału w rynku itp.

- Środek: analiza zachowania klientów, lokalizacja słabych
punktów, śledzenie trendów.

- Narzędzie: analiza gromadzonych danych (rozproszonych,
niejednorodnych) - budowa hurtowni danych.

Jakimi danymi dysponujemy?
Które dane powinniśmy gromadzić w hurtowni?
Ze względów technicznych gromadzimy tylko to, co niezbędne.

2

Ekstrakcja,

czyszczenie,

integracja

DECYZJE PROJEKTOWE:

perspektywa pojęciowa

Hurtownia

Źródła danych

Model pojęciowy: związki między
pojęciami związanymi z działalnością
przedsiębiorstwa, np.:
- Kto to jest „klient” i co o nim wiemy?
- Jak opisywana jest sprzedaż w
różnych oddziałach firmy?
- Co pod pojęciem „fakt sprzedaży”
chciałby rozumieć analityk?

- Czy model wielowymiarowy danych
może nam pomóc w tworzeniu
raportów biznesowych?

MODEL WIELOWYMIAROWY

Baza zawiera fakty opisane przez wymiary i określające wartość miar.

Fakt - pojedyncze zdarzenie będące podstawą analiz (np. sprzedaż).
Fakty opisane są przez wymiary i miary.

Wymiar - cecha opisująca dany fakt, pozwalająca powiązać go z
innymi pojęciami modelu przedsiębiorstwa (np. klient, data, miejsce,
produkt). Wymiary są opisane atrybutami.

Atrybut - cecha wymiaru, przechowująca dodatkowe informacje na
temat faktu (np. wymiar data może mieć atrybuty: miesiąc, kwartał,
rok; wymiar klient może mieć atrybuty: nazwisko, region).

Miara - wartość liczbowa przyporządkowana do danego faktu
(np. wartość sprzedaży, liczba sztuk).

3

MODEL GWIAZDY

Fakty i wymiary są często w systemach relacyjnych modelowane
w postaci gwiazdy.

Sprzedaż

id_klienta
id_daty
id_produktu
id_sklepu

ilość
kwota

Klient

id_klienta
nazwisko
adres
grupa_kl

Data

id_daty
miesiąc
kwartał
rok

Produkt

id_produktu
grupa_prod
opis
data_wprowadzenia
marka
producent

Sklep

id_sklepu
miasto
region
kierownik
telefon

Miary

Uwaga: nie wszystkie informacje będą potrzebne - część można pominąć

Tablica faktów

Tablice
wymiarów

Atrybuty

MODEL PŁATKA ŚNIEGU

Normalizacja schematu gwiazdy poprzez modelowanie atrybutów
za pomocą kolejnych tablic prowadzi do modelu płatka śniegu.

Sprzedaż

id_klienta
id_daty
id_produktu
id_sklepu

ilość
kwota

Produkt

id_produktu
id_grupy_prod
id_marki
data_wprowadzenia

Sklep

id_sklepu
id_miasta

Uwaga: model płatka śniegu odtwarza hierarchię wymiarów.

Tablica faktów

Tablice
wymiarów

Miasto

id_miasta
id_regionu
nazwa

Region

id_regionu
nazwa

Producent

id_producenta
nazwa_producenta
kraj_producenta

Grupa_prod

id_grupy_prod
nazwa_grupy_prod

Marka

id_marki
id_producenta
nazwa_marki

4

AGREGACJE

Agregacje to operacje zamieniające zbiór wartości (przeważnie
liczbowych) miar opisujących fakty, na pojedynczą wartość.

Podstawowa operacja tworząca kostki danych.

Przykłady:
• Suma
• Liczba rekordów
• Średnia
• Minimum, maksimum, mediana
• Dodatkowy warunek (np. HAVING)
• Specjalne (stored procedures)

SELECT miesiąc, SUM(kwota) FROM tablica_faktów
GROUP BY miesiąc HAVING SUM(kwota)>500

KOSTKI DANYCH

230$

Dane (np. schemat gwiazdy)

Kostka danych: tabela, w której
krawędziami są wymiary, a
zawartością komórek – miary.

Zawartość komórki:
zagregowana miara

klient

czas

produkt

Kostka wielowymiarowa danych

5

KOSTKI DANYCH

sprzedaż

wartość

czas

produkt

sklep

miasto

skl_1

skl_2

skl_3

suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1

miasto_2

skl_1

skl_2

skl_3

suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1

miasto_2

skl_1

skl_2

skl_3

suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1

miasto_2

skl_1

skl_2

skl_3

suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1

miasto_2

skl_1

skl_2

skl_3

suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1

miasto_2

Komórki zawierają zagregowane (po

wymiarach) wartości miar

Nagłówki wierszy i kolumn

kostki zawierają wartości

wymiarów (atrybutów)

cza

s

W kostkach bywają też przechowywane
pomocnicze podsumowania

MODEL PUNKTOWY

Przed stworzeniem logicznego modelu danych (gwiazda, płatek
śniegu) należy uzgodnić model pojęciowy.
Przykładowa technika modelowania: model punktowy.

Sprzedaż

- Fakty reprezentowane są punktami

Region

Czas

Towar

- Wymiary reprezentowane są
przez nazwy

Kwartał

Grupa towarów

- Podobnie reprezentujemy kolejne
poziomy hierarchii

Dostawa do

magazynu

Magazyn

Transport

- Model może obejmować wiele
(konstelację) faktów,
korzystających częściowo ze
wspólnej hierarchii wymiarów

6

MODEL PUNKTOWY

W modelu punktowym zapisujemy ponadto informacje na temat:

- Nazw atrybutów
- Typów danych
- Więzów integralności
- Retrospekcji (zmienności wartości atrybutów w czasie)
- Częstości odświeżania
- Pochodzenia danych (źródło, transformacje)
- Metadanych biznesowych

Retrospekcja może być:

-„prawdziwa” – zapisujemy wszelkie zmiany wartości wraz z
dokładnym czasem
- „fałszywa” – nowe wartości zastępują stare
- „trwała” – nie przewidujemy zmiany wartości.

POZIOMY AGREGACJI DANYCH

Nie zawsze potrzebujemy danych
opisanych z pełną dostępną
dokładnością.

Agregacja wartości może dotyczyć pomijania pewnych wymiarów
lub atrybutów w hierarchii.
Hurtownia danych (lub hurtownie tematyczne) mogą przechowywać
zmaterializowane podkostki danych i korzystać z nich podczas analiz.

7

PRZYKŁAD: POZIOMY AGREGACJI

Dane: fakty sprzedaży (50 mln. rekordów, miara: wartość), klienci
identyfikowani kodem pocztowym (3000 różnych kodów), towary (60 grup, 800
nazw indywidualnych), czas: 3 lata (1000 dni), sklepy (18 sztuk).

- Model gwiazdy: tablice z (łącznie) trochę ponad 50 mln. rekordów.

- Kostka danych: 3000*800*1000*18 = 43 200 000 000 komórek.

Wersja zagregowana: ignorujemy wymiar klientów, towary
rozpatrujemy tylko w grupach, czas rozpatrujemy w skali miesięcy.

- Kostka danych: 60*36*18 = 38 880 komórek (sumy wartości).

Powyższa agregacja nie pozwala na wygenerowanie wszystkich tych raportów,
które mogły by tworzone oryginalnie, ale za to dla pozostałych raportów może
działać o 3 rzędy wielkości szybciej.

OPTYMALIZACJA AGREGACJI

Problem: Które wymiary możemy pominąć? Jak agregować dane
(sum, min, max, avg, count)? Które atrybuty hierarchii agregujemy,
a które pozostawiamy? Ile powinno powstać zmaterializowanych
kostek pomocniczych? Jak optymalnie obliczać konkretne
zapytanie?
Cel: np. minimalizacja średniego czasu przetwarzania zadawanych
w praktyce zapytań OLAP.

czas, klient, towar, sklep

czas, klient, towar

czas, klient, sklep

czas, towar, sklep

klient, towar, sklep

czas, klient

towar, sklep

klient, towar

czas, sklep

czas

klient, sklep

sklep

czas, towar

towar

klient

Zwykle nie możemy zmaterializować wszystkich podkostek, musimy wybrać takie, z
których później najłatwiej będzie generować raporty.

gwiazda

kostka

8

NAWIGATOR PO AGREGACJACH

Skąd wiadomo, które zapytania są często wykonywane?

Nawigator po
agregacjach

Informacje o zadawanych zapytaniach OLAP są gromadzone jako metadane w
specjalnej bazie (repozytorium). Mogą być wykorzystane do optymalizacji zapytań
(np. materializacji niektórych agregacji). Metadane hurtowni zawierają też wiele
innych informacji, np. opis pojęciowy i logiczny danych, informacje o źródłach i
ich integracji, dziennik aktualizacji itd.

NAWIGATOR PO AGREGACJACH

Nawigator po agregacjach wspomaga też przeglądanie danych i
przygotowywanie raportów. Programy z tej rodziny zapewniają
wygodny wgląd w wielowymiarowy model danych.

Nawigator po
agregacjach

skl_1 skl_2 skl_3 suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

Raport

9

NAWIGACJA PO AGREGACJACH

Najczęściej wykonywane operacje OLAP - terminologia:

Agregacja, konsolidacja, zwijanie

(aggregation, consolidation, roll-up)

dni

miesiące

- przejście na bardziej ogólny poziom

wymiaru,

- wybór operacji agregującej (np. suma,

średnia, zestawienie procentowe).

Rozwijanie

(roll-down, drill-down, drill through)

- operacja odwrotna do agregacji:

przeglądanie szczegółowych danych
dotyczących danego podsumowania

sprzedaż w regionach

poszczególne sklepy

NAWIGACJA PO AGREGACJACH

Najczęściej wykonywane operacje OLAP - terminologia:

Selekcja

(slicing)

- wycinanie fragmentu danych poprzez określenie warunków na

wartościach wymiarów,

- wynikiem jest podkostka, np. dwuwymiarowy plaster (slice),

odpowiadająca danym spełniającym warunki.

skl_1 skl_2 skl_3 suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

skl_1 skl_2 skl_3 suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

skl_1 skl_2 skl_3 suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

skl_1 skl_2 skl_3 suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

skl_1 skl_2 skl_3 suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

skl_1 skl_2 skl_3 suma

...

...

...

...

...

...

3

12

70

0

9

23

7

2

12

0

15

1

0

8

5

...

...

...

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

warunek:
data = ....

10

NAWIGACJA PO AGREGACJACH

Najczęściej wykonywane operacje OLAP - terminologia:

Filtrowanie

(screening, selection, filtering)

- filtrowanie danych wchodzących w skład agregacji, np. poprzez

ograniczenie wartości miar lub atrybutów wymiarów

skl_1 skl_2 skl_3 suma

89

23

395

122

13

642

27

12

50

0

0

23

146

237

12

76

45

1

0

8

5

249

302

91

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

skl_1

-

skl_3 suma

...

...

...

...

...

...

3

-

70

0

-

23

7

-

12

0

-

1

0

-

5

...

-

...

prod_1

prod_2

prod_3

prod_4

prod_5

suma

miasto_1 miasto_2

warunek:
tylko sklepy mające
obrót powyżej 4 mln.
oraz
sumujemy tylko fakty
sprzedaży o wartości
powyżej 20.

NAWIGACJA PO AGREGACJACH

Najczęściej wykonywane operacje OLAP - terminologia:

Zawężanie

(scoping)

- ograniczenie danych branych pod uwagę w dalszej analizie,
- podobne do filtrowania, jednak realizowane na wcześniejszym

poziomie operacyjnym,

- działa jak automatyczne dodanie warunków do wszystkich

dalszych operacji na kostkach danych.

warunek:
tylko sklepy mające obrót powyżej 4 mln.
oraz
tylko pierwsze dwa kwartały 2000 roku

11

NAWIGACJA PO AGREGACJACH

Najczęściej wykonywane operacje OLAP - terminologia:

Obracanie

(pivot, rotate)

- zamiana miejscami kolumn i wierszy tabeli,
- przeniesienie pewnego wymiaru z kolumn do wierszy

(lub odwrotnie),

- zamiana pewnego wymiaru (lub atrybutu) na inny,

nieużywany.

skl_1

skl_2

skl_1

27

12

92

0

0

23

146

237

12

76

45

7

0

8

5

...

...

...

prod_1

prod_2

prod_3

prod_4

prod_5

suma

2002

2003

skl_1

50

21

12

1

0

...

skl_1

skl_2

27

12

0

0

146

237

76

45

0

8

...

...

prod_1

prod_2

prod_3

prod_4

prod_5

suma

92

50

23

21

12

12

7

1

5

0

prod_1

prod_2

prod_3

prod_4

prod_5

2002

2003