Microsoft PowerPoint - 02 Modelowanie danych

Fakty i miary

Fakty (ang. facts) – zdarzenia, o których informacja przechowywana
jest w hurtowni danych, podlegające analizie.

Rozmowa telefoniczna

Złożenie wniosku

Zakup polisy

Aktywacja kontraktu

Miara (ang. measures) - cecha ilościowa, opisująca zdarzenia lub
związana z faktem np.:

Czas trwania połączenia

Suma ubezpieczenia

marcin.mazurek@wat.edu.pl

Wymiary

Wymiary - atrybuty opisujące kontekst wystąpienia faktu, np.

Czas

Lokalizacja geograficzna

Klient

Produkt

Sprzedawca

Atrybuty – cechy opisujące wymiar, np.:

Nazwisko klienta

Nazwa kraju

Typ produktu

Poziom szczegółowości - wymiar może być opisywany przez
atrybuty na jednym lub więcej poziomie szczegółowości

Granulacja danych w hurtowni - największy możliwy poziom
szczegółowości wymiarów analizy dla miar związanych opisujących

marcin.mazurek@wat.edu.pl

Projektowanie modelu danych

Wybór procesu biznesowego

Określenie istotnych faktów i ich granulacji

Określenie wymiarów analizy

Określenie miar

Model logiczny danych

Diagram encji (ERD)

Model tabel

marcin.mazurek@wat.edu.pl

Model punktowy

Tabele faktów

Główne tabele hurtowni (wolumen
danych, przyrost) – stanowią około
90% wolumenu danych hurtowni

Model tabeli faktów powstaje w wyniku
odpowiedzi na pytanie:

„Co mierzymy ?”

Sprzedaz

«column»

Ilosc: numeric(10,2)

„Co mierzymy ?”

Kolumny tabeli faktów:

Klucze obce łączące z tabelami
wymiarów

Numeryczne kolumny zawierające miary
opisujące fakt

Kolumny wyliczane

Klucz sztuczny (ROWID)

Niepotrzebny, gdyż kombinacja wartości
kluczy wymiarów powinna być unikalna

Może być wymagany przez system
zarządzania bazą danych

marcin.mazurek@wat.edu.pl

Ilosc: numeric(10,2)

Wartosc_PLN: numeric(10,2)

Wartosc_EUR: numeric(10,2)

ID_Produkt (FK): bigint

ID_Sklep (FK): bigint

ID_Data (FK): bigint

Tabele faktów – wzorce
projektowe

Transakcje (ang. transactions)

Tabela, przechowująca informacje o zdarzeniach

Migawka okresowa (ang. periodic snapshot)

Tabela, przechowująca informację o stanie, np.: stan magazynu, stan
konta, wartość aktywów

konta, wartość aktywów

Stan jest zapisywany w regularnych odstępach czasu – np. codziennie,
na koniec miesiąca

Migawka akumulacyjna (ang. accumulating snaphsot)

Tabela przechowująca cykl życia produktu, zamówienia, usługi

Każdy wiersz odzwierciedla zmianę w czasie w postaci zbioru kluczy
wymiaru czasowego, odpowiadających chwilom zmiany statusu

Nietypowy wzorzec zakładający aktualizację rekordu.

marcin.mazurek@wat.edu.pl

Tabele wymiarów

Dostarczają kontekst analizy

Kluczowe dla możliwości wykorzystania
hurtowni danych w procesie analizy
danych

Duża liczba kolumn (może sięgać
nawet 100)

Produkty

«column»

*PK ID: bigint
*

Kod_produktu: varchar(50)

Opis_produktu: varchar(50)

nawet 100)

Stosunkowo niewielka liczba wierszy
(rzadko przekracza 1 milion wierszy)

Kolumny tabeli wymiarów :

Klucz sztuczny

Klucz naturalny (biznesowy)

Kolumny tekstowe zawierające
zrozumiałe wyjaśnienia (nie tylko
kody!)

Kolumny numeryczne (np. wielkość
powierzchni punktu sprzedaży)

marcin.mazurek@wat.edu.pl

Opis_produktu: varchar(50)

Model: varchar(50)

Marka: varchar(50)

Masa: varchar(50)

Rodzaj_opakowania: varchar(50)

Kategoria: varchar(50)

Wysokość : numeric(10,2)

Głębokość : timestamp

Szerokość : numeric(10,2)

Dlugosc_gwarancji: integer

Model wymiarowy

Kalendarz

«column»

*PK ID: bigint
*

Rok: bigint

Miesiac: varchar(50)

Numer_dnia_w_miesiacu: integer

Sprzedaz

Produkty

«column»

*PK ID: bigint
*

Kod_produktu: varchar(50)

Opis_produktu: varchar(50)

Model: varchar(50)

Marka: varchar(50)

Masa: varchar(50)

Rodzaj_opakowania: varchar(50)

Kategoria: varchar(50)

Wysokość : numeric(10,2)

Głębokość : timestamp

Szerokość : numeric(10,2)

(ID_Produkt (FK) = ID)

«FK»

PK_Produkty

FK_Sprzedaz_Kalendarz

(ID_Data (FK) = ID)

«FK»

PK_Kalendarz

marcin.mazurek@wat.edu.pl

«column»

Ilosc: numeric(10,2)

Wartosc_PLN: numeric(10,2)

Wartosc_EUR: numeric(10,2)

*FK ID_Produkt (FK):  bigint
*FK ID_Sklep (FK):  bigint
*FK ID_Data (FK):  bigint

Szerokość : numeric(10,2)

Dlugosc_gwarancji: integer

Sklepy

«column»

*PK ID: bigint
*

Nazwa : varchar(50)

Miejscowosc: varchar(50)

Kod_pocztowy: char(6)

Ulica : varchar(50)

Poczta : varchar(50)

Telefon : char(10)

FK_Sprzedaz_Produkty

0..*

FK_Sprzedaz_Sklepy

0..*

(ID_Sklep (FK) = ID)

«FK»

PK_Sklepy

Schemat gwiazdy

Każdemu wymiarowi
odpowiada jedna tabela

Wszystkie atrybuty wymiaru,
niezależnie od poziomu
szczegółowości umieszczone

Sklepy

ID:  bigint
Nazwa :  varchar(50)
Kod_pocztowy:  char(6)
Ulica :  varchar(50)
Miejscowosc:  varchar(50)
Poczta :  varchar(50)
T elefon :  char(10)
Wojewodztwo:  varchar(50)

Sprzedaz

Ilosc:  numeric(10,2)
Wartosc_PLN:  numeric(10,2)
Wartosc_EUR:  numeric(10,2)
ID_Produkt (FK):  bigint
ID_Sklep (FK):  bigint

szczegółowości umieszczone
są w jednej tabeli

marcin.mazurek@wat.edu.pl

Kalendarz

ID:  bigint
Rok:  bigint
Miesiac:  varchar(50)
Numer_dnia_w_miesiacu:  integer

Produkty

ID:  bigint
Kod_produktu:  varchar(50)
Opis_produktu:  varchar(50)
Model:  varchar(50)
Marka:  varchar(50)
Masa:  varchar(50)
Rodzaj_opakowania:  varchar(50)
Kategoria:  varchar(50)
Wysokość :  numeric(10,2)
Głębokość:  timestamp
Szerokość:  numeric(10,2)
Dlugosc_gwarancji:  integer

ID_Sklep (FK): bigint
ID_Data (FK): bigint

Schemat płatka śniegu

Dane opisujące wymiary są
przechowywane w
znormalizowanym modelu (3NF)

Miejscow osci

ID:  bigint
Nazwa:  varchar(50)
ID_Wojewodztwo:  bigint

Sklepy

ID:  bigint
Nazwa :  varchar(50)
Kod_pocztowy:  char(6)

Wojew odztwa

ID: bigint
Nazwa: varchar(50)

marcin.mazurek@wat.edu.pl

Dni

ID:  bigint
Numer_dnia_w_miesiacu:  integer
ID_Miesiac:  bigint
ID_Tydzien:  bigint

Lata

ID: bigint
Rok: integer

Miesiace

ID:  bigint
Miesiac:  varchar(50)
ID_Rok:  bigint

Produkty

ID:  bigint
Kod_produktu:  varchar(50)
Opis_produktu:  varchar(50)
Model:  varchar(50)
Masa:  varchar(50)
Rodzaj_opakowania:  varchar(50)
Wysokość :  numeric(10,2)
Głębokość :  timestamp
Szerokość :  numeric(10,2)
Dlugosc_gwarancji:  integer
ID_Kategoria:  bigint
ID_Marka:  bigint

Kod_pocztowy:  char(6)
Ulica :  varchar(50)
Poczta :  varchar(50)
Telefon :  char(10)
Wojewodztwo:  varchar(50)
ID_Miejscowosc:  bigint

Sprzedaz

Ilosc:  numeric(10,2)
Wartosc_PLN:  numeric(10,2)
Wartosc_EUR:  numeric(10,2)
ID_Produkt (FK):  bigint
ID_Sklep (FK):  bigint
ID_Data (FK):  bigint

Tygodnie

ID:  bigint
Numer_tygodnia_w_roku:  integer
Etykieta:  varchar(10)

Kategorie

ID:  bigint
Nazwa:  varchar(50)
Nazwa_krotka:  varchar(50)
Opis:  varchar(50)

Marki

ID:  bigint
Nazwa:  varchar(50)
Wlasciciel:  varchar(50)

Schemat konstelacji

Współdzielone wymiary

Miej scowosci

ID:  bigint
Nazwa:  varchar(50)
ID_Wojewodztwo:  bigint

Sklepy

ID:  bigint
Nazwa :  varchar(50)
Kod_pocztowy:  char(6)
Uli ca :  varchar(50)
Poczta :  varchar(50)
Telefon :  char(10)
Wojewodztwo:  varchar(50)
ID_Miejscowosc:  bigi nt

Sprzedaz

Ilosc: numeric(10,2)
Wartosc_PLN: numeric(10,2)

Woj ew odztwa

ID: bigint
Nazwa: varchar(50)

marcin.mazurek@wat.edu.pl

Dni

ID:  bigint
Numer_dnia_w_miesiacu:  integer
ID_Miesiac:  bigint
ID_T ydzien:  bigint

Lata

ID: bigint
Rok: integer

Miesiace

ID:  bigint
Miesiac:  varchar(50)
ID_Rok:  bi gint

Produkty

ID:  bigint
Kod_produktu:  varchar(50)
Opis_produktu:  varchar(50)
Model :  varchar(50)
Masa:  varchar(50)
Rodzaj_opakowania:  varchar(50)
Wysokość:  numeric(10,2)
Głębokość :  timestamp
Szerokość:  numeric(10,2)
Dlugosc_gwarancj i:  integer
ID_Kategoria:  bigint
ID_Marka:  bigint

Wartosc_PLN:  numeric(10,2)
Wartosc_EUR:  numeric(10,2)
ID_Produkt (FK):  bigi nt
ID_Skl ep (FK):  bi gint
ID_Data (FK):  bigint

Tygodnie

ID:  bigi nt
Numer_tygodni a_w_roku:  i nteger
Etykieta:  varchar(10)

Kategorie

ID:  bigint
Nazwa:  varchar(50)
Nazwa_krotka:  varchar(50)
Opis:  varchar(50)

Marki

ID:  bi gint
Nazwa:  varchar(50)
Wlasciciel:  varchar(50)

Zamow ienia

ID:  bigint
Wartosc:  numeric(10,2)
ID_Data(FK):  bigi nt
ID_Produkt (FK):  bigint
ID_Klient(FK):  bigint
ID_DataRealizacji :  bi gint

Klienci

ID:  bi gint
Nazwi sko:  varchar(50)
Imie:  varchar(50)

Hierarchie

Atrybuty mogą opisywać
wymiar na różnych

poziomach

szczegółowości

, np.:

Nazwa kraju, nazwa gminy,
współrzędne geograficzne

współrzędne geograficzne

Rok kalendarzowy, miesiąc, dzień

Region, sklep, sprzedawca

Wartości atrybutów w
przypadku różnych poziomów
szczegółowości tworzą
hierarchię.

marcin.mazurek@wat.edu.pl

Rodzaje hierarchii

Kryterium semantyczne

Hierarchie oparte o poziomy
agregacji (ang. level –based,
standard)

Wyróżniona stała liczba nazwanych
poziomów hierarchii

Kryterium struktury

Hierarchie zrównoważone (ang.
balanced)

Hierarchie, które mogą być przedstawione w
postaci drzewa (drzew), w którym odległość
od korzenia do liści jest zawsze taka sama.

poziomów hierarchii

Hierarchia geograficzna, hierarchia stanowisk
w organizacji

Hierarchie oparte o wartości
(ang. value-based, recursive)

Jednorodne znaczeniowo elementy
powiązane relacją element nadrzędny-
elementy podrzędne (element podrzędny
może być elementem nadrzędnym dla
innych)

Hierarchia części urządzenie technicznego,
hierarchia przełożony –podwładny bez
wyodrębniania stanowisk

Hierarchie nierównomierne (ang.
unbalanced)

Hierarchie , w których nie wszystkie ścieżki
od korzenia drzewa do elementu najbardziej
szczegółowego dochodzą do tego samego
poziomu szczegółowości.

Hierarchie niewyrównane (ang.
ragged, noncovering)

Hierarchie, w których nie wszystkie
poziomy szczegółowości są wypełnione.

marcin.mazurek@wat.edu.pl

Hierarchie oparte o poziomy
szczegółowości

Rok

Miesiąc

Dzień

2009

Grudzień

2010

Styczeń

Poziomy szczegółowości

hierarchii

2009

Styczeń

marcin.mazurek@wat.edu.pl

Kalendarz

«column»

*PK ID: bigint
*

Rok: bigint

Miesiac: varchar(50)

Numer_dnia_w_miesiacu: integer

Lata

«column»

*PK ID: bigint
*

Rok: integer

Miesiace

«column»

*PK ID: bigint
*

Miesiac: varchar(50)

*FK ID_Rok: bigint

Dni

«column»

*PK ID: bigint
*

Numer_dnia_w_miesiacu: integer

*FK ID_Miesiac: bigint

FK_Miesiace_Lata

0..*

(ID_Rok = ID)

«FK»

PK_Rok

FK_Dni_Miesiace

0..*

(ID_Miesiac = ID)

«FK»

PK_Miesiace

Hierarchie oparte o wartości

Pakiet

javax

javax.

servlet

javax.servlet.

jsp

javax.servlet.jsp.

javax

javax.

servlet

javax.servlet.

jsp

Javax.servlet.jsp.

jstl.core

javax

javax.

servlet

javax.servlet.

jsp

Javax.servlet.jsp.

jstl.fmt

javax

javax.

servlet

javax.servlet.

http

javax

javax.

servlet

javax.servlet.

http

javax

javax.

ejb

javax.ejb.spi

marcin.mazurek@wat.edu.pl

Pakiety

«column»

*PK ID: bigint
*

Pakiet : varchar(50)

FK ID_PakietNadrzedny: bigint

FK_Pakiety _Pakiety

0..*

«FK»

PK_Table1

Hierarchia niewyrównana

Wydział

Kierunek

Specjalność

Grupa
studencka

WCY

Informatyka

ISZ

I8E1S1

WCY

Informatyka

I8B1S1

WCY

Informatyka

I8B1S1

WCY

Zarządzanie

Wcy

Informatyka

I0X1S1

WCY

Informatyka

I0X1S2

marcin.mazurek@wat.edu.pl

Hierarchia niezrównoważona

Województwo Powiat

Gmina

Miejscowość

Mazowieckie

m.st.Warszawa

Warszawa

Mazowieckie

legionowski

Serock

Zegrze

Mazowieckie

legionowski

Legionowo

Mazowieckie

Warszawa-
zachód

Kampinos

Granica

Łódzkie

Pabianice

Lutomiersk

Kazimierz

marcin.mazurek@wat.edu.pl

Hierarchie - inne zagadnienie
projektowe

Czy fakty są związane wyłącznie z liśćmi hierarchii ?

Jak hierarchia może zmieniać się w czasie?

Nowa wersja hierarchii

Jednolity Rzeczowy Wykaz Akt (JRWA)

Podział administracyjny kraju

Zmiana przynależności elementu

zmiana granicy jednostki administracyjnej

Zmiana departamentu przez pracownika

Zmiana atrybutów elementu

marcin.mazurek@wat.edu.pl

Modelowanie zmienności
wymiarów

Wolno zmienne wymiary - ang. Slowly Changing Dimensions (SCD)

Typ 1 - nadpisanie wartości

nadpisanie wartości

Stosowane najczęściej w przypadku zmiany nazwy, nazwiska.

Typ 2 - dodanie rekordu

dodanie rekordu zawierającego nowe wartości

Najczęściej stosowany typ obsługi zmienności wymiaru

Typ 3 – dodanie kolumny

Zmiany zapisywane są w oddzielnych kolumnach (dla każdej wersji jest
oddzielna kolumna przechowująca wartość).

Stosowany w przypadku okazjonalnych zmian, w których istotna jest możliwość
łatwego odwołania do poprzedniej wartości, np. Województwo według starego i
nowego podziału administracyjnego

marcin.mazurek@wat.edu.pl

SCD – Typ 1

Nazwisko Imię

Data
urodzenia

Miejsce
zamieszkania

Województwo

10256

Kowalska

Janina 1990-01-10

Skierniewice

łódzkie

UPDATE

marcin.mazurek@wat.edu.pl

UPDATE

Nazwisko

Imię

Data
urodzenia

Miejsce
zamieszkania

Województwo

10256

Malinowska

Janina 1990-01-10 Skierniewice

łódzkie

SCD – Typ 2

INSERT

Zmiana adresu zamieszkania
Skierniewice -> Warszawa

PESEL

Nazwisko

Imię

Data
urodzenia

Miejsce
zamieszkania

Województwo

Data od

Data do

321

01234567890

Kowalska

Janina

1990-01-10

Skierniewice

łódzkie

1995-06-01

9999-12-01

marcin.mazurek@wat.edu.pl

INSERT
UPDATE (data do)

PESEL

Nazwisko

Imię

Data
urodzenia

Miejsce
zamieszkania

Województwo

Data od

Data do

321

01234567890

Kowalska

Janina

1990-01-10

Skierniewice

łódzkie

1995-06-01

2011-03-15

322

01234567890

Kowalska

Janina

1999-01-10

Warszawa

Mazowieckie

2011-03-16

9999-12-01

Skierniewice -> Warszawa
Od dnia 2011-03-15

SCD – Typ 3

Nazwisko

Imię

Data
urodzenia

Miejsce
zamieszkania

Województwo

10256

Kowalska

Janina

1990-01-10

Skierniewice

skierniewickie

ALTER TABLE …

Zmiana województwa wynikająca
z reformy podziału

marcin.mazurek@wat.edu.pl

ALTER TABLE …
UPDATE ….

Nazwisko

Imię

Data
urodzenia

Miejsce
zamieszkania

Województwo
stare

Województwo

10256

Malinowska

Janin
a

1990-01-10

Skierniewice

skierniewickie

łódzkie

z reformy podziału
administarcyjnego kraju

SCD - podsumowanie

Typ 1

Typ 2

Typ 3

Zastąpienie wartości
poprzedniej nową w tym
samym rekordzie

Dodanie nowego wiersza w
tabeli wymiarów z nowymi
wartościami atrybutów

Dodanie nowej kolumny
przechowujące poprzednią
wartość atrybutu

Najłatwiejszy do implementacji

•Nie ma konieczności
aktualizacji istniejących

•Nie ma konieczności
aktualizacji raportów czy też

aktualizacji istniejących
agregatów
•Nowy wiersz automatycznie
partycjonuje historię w tabeli
faktów

aktualizacji raportów czy też
agregatów
•Umożliwia połączenie faktów
zarówno ze starymi jak i nowymi
wartościami atrybutów

Brak możliwości odczytania
poprzednich wartości

•Dwie (lub więcej) wartości
klucza sztucznego
•Wzrost wolumentu tabeli
wymiarów
•Nie pozwala połączyć nowych
wartości atrybutów ze starymi
faktami

Wymaga zmiany struktury bazy
danych

marcin.mazurek@wat.edu.pl

Modelowanie zmienności
wymiarów – inne rozwiązania

Oddzielne tabele przechowujące historię zmian

Podstawowa tabela przechowuje stan bieżący

Dodatkowa tabela przechowuje wszystkie zmiany rekordu z tabeli podstawowej
w postaci kolejnych rekordów opatrzonych datami wyznaczającymi zakres
ważności wersji rekordu.

ważności wersji rekordu.

Podejście hybrydowe (Ralph Kimball) - „ Unpredictable changes
with single-version ovelay”

Kombinacja typu 2 i 3

Dodanie nowego wiersza w tabeli wymiarów

Dodanie nowej kolumny przechowującej poprzednią wartość wymiaru

marcin.mazurek@wat.edu.pl

Tabela faktów bez faktów

ang. Factless fact table

Tabele faktów pozbawione miar, składające się
wyłącznie z kluczy obcych

Pozwalają rejestrować zdarzenia

Zapisanie się studenta na temat pracy dyplomowej

Pozwalają analizować zdarzenia, które nie miały
miejsca, np. w której promocji nie sprzedano ani
jednego produktu.

marcin.mazurek@wat.edu.pl

Agregacja w hurtowni danych

Miary:

Addytywne – takie, które mogą być agregowane w każdym z wymiarów.
Dla obliczenia podsumowania wymagane są wartości na dowolnym
poziomie granulacji, nie mniejszym niż podsumowanie.

Semiaddytywne - takie, które mogą być agregowane z wyłączeniem

Semiaddytywne - takie, które mogą być agregowane z wyłączeniem
niektórych wymiarów (najczęściej wymiar czasu)

Stan środków na koncie

Liczba klientów

Nieaddytywne – takie, które nie mogą być agregowane -
podsumowania muszą być obliczane w oparciu o dane najbardziej
szczegółowe

Liczba klientów, którzy skorzystali z usługi

distinct count()

marcin.mazurek@wat.edu.pl