Hurtownie danych

(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

89 23 395 122 13 642

27 12 50

0 0 23

146 237 12

76 45 1

0 8 5

249 302 91

89 23 395 122 13 642

27 12 50

0 0 23

146 237 12

76 45 1

0 8 5

249 302 91

89 23 395 122 13 642

27 12 50

0 0 23

146 237 12

76 45 1

0 8 5

249 302 91

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)

czas

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 czas, sklep towar, sklep klient, towar

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

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

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

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

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

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

...

3 12 70

0 9 23

7 2 12

0 15 1

0 8 5

... ... ...

miasto_1 miasto_2 warunek:

data = ....

(10)

NAWIGACJA PO AGREGACJACH

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

miasto_1 miasto_2

skl_1 - skl_3 suma ...

...

3 - 70

0 - 23

7 - 12

0 - 1

0 - 5

... - ...

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

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

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

... ... ...

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