Struktura gwiazdy – Struktura płatka śniegu

Gdy mówi się o hurtowniach danych, najczęściej całość sprowadza się do odpowiedniej struktury. Są to stosunkowo proste układy tabel zwane – gwiazdą lub płatkiem śniegu. Ułożenie danych w ten sposób pozwala na znaczące ułatwienie tworzenia wszelkiej maści raportów. To wszystko bez wdrażania drogich systemów BI, które de facto działają na tego typu strukturach. W prostych przypadkach wystarczy nam struktura gwiazdy, co oznacza jedną tabelę faktów połączoną z wieloma wymiarami. We faktach odnotujemy zaistnienie jakiegoś zdarzenia np. sprzedaż (słoika ogórków). Aby to odnotowanie było sensowne musimy to powiązać z jakimiś wymiarami oraz przypisać do tego wydarzenia jakieś wielkości(ilość,cena sprzedaży,cena zakupu) itp. Powiązania w tabeli faktów to klucze obce do innych tabel nazywanych wymiarami. We wymiarach trzymamy pogrupowane tematycznie dane. Oczywiście tworzenie hurtowni danych to znacznie więcej teorii, jednakże warto rozpocząć od prostych modeli. Poniżej przedstawiam procedurę w T-SQL szykującą gotowy do przekształcenia w kostkę analityczną model gwiazdy.

Studenci przedmiotu hurtownie danych który prowadzę dostali ode mnie dane źródłowe, które pozwalały na wygenerowanie tabel opisanych poniżej. Jeśli drogi czytelniku nie jesteś moim studentem, nie zrażaj się całość jest opisana w taki sposób abyś łatwo mógł to przystosować do swoich potrzeb.

Najpierw zamieszczę kompletny kod t-sql później go konkretnie opiszę.

ALTER PROCEDURE [dbo].[data_loader]
AS
BEGIN
SET NOCOUNT ON;
begin try
drop table f_wypozyczenia
end try
begin catch
print 'cos'
end catch

begin try
drop table w_adresy
end try

begin catch
print 'cos'
end catch

--- zaladowanie adresow do tabeli wymiarow
select *
into w_adresy
from (
select kod_p,
(select top 1 miasto from etl_badresy b1 where b.kod_p = b1.kod_p ) miasto,
(select top 1 dzielnica from etl_badresy b1 where b.kod_p = b1.kod_p ) dzielnica,
(select top 1ulica from etl_badresy b1 where b.kod_p = b1.kod_p ) ulica

from etl_badresy b
) e
group by kod_p,miasto,dzielnica,ulica

begin try
drop table w_klienci
end try

begin catch
print 'cos'
end catch
--- zaladowanie klientow

select * ,case when len(pesel)=11 then dbo.get_wiek_from_pesel(pesel) else 0 end wiek,
dbo.pesel_plec(pesel) plec
into w_klienci
from etl_bosob

begin try
drop table w_ksiazki
end try

begin catch
print 'cos'
end catch

select * into w_ksiazki from etl_ksiazki

-- utworzenie indeksow
begin try
create index wypo_ean on etl_wypozyczenia(ean)
create index wypo_osob on etl_wypozyczenia(osob_id)
create index osob_id on etl_bosob (id)
end try
begin catch
print 'juz mam indeksy na etl'
end catch

-- stworzenie tabeli faktow
select osob_id,b.ean,o.kodp,cena,liczba_stron,data_od,data_do,datediff(day,data_od,data_do) ile_dni,
case when year(data_od)<2011 then 0.01 else 0.02 end zysk_per_strona, case when year(data_od)<2011 then 0.01 else 0.02 end * liczba_stron as zysk_wypozyczenie into f_wypozyczenia from etl_wypozyczenia w inner join etl_ksiazki b on b.ean = w.ean inner join etl_bosob o on o.id = osob_id --utworzenie wiezow integralnosci pk ALTER TABLE w_adresy ALTER COLUMN kod_p varchar(255) not null ALTER TABLE w_adresy ADD CONSTRAINT pk_kodp PRIMARY KEY (kod_p) ALTER TABLE w_klienci ALTER COLUMN id int not null ALTER TABLE w_klienci ADD CONSTRAINT pk_klientID PRIMARY KEY (id) -- zapewnienie unikalnosci ean w bazie ksiazek: delete from w_ksiazki where ean in ( select ean from w_ksiazki group by ean having count(*) >1
) and id not in (
select max(id ) from w_ksiazki where ean in (
select ean from w_ksiazki
group by ean
having count(*) >1
)
)

ALTER TABLE w_ksiazki
ALTER COLUMN ean varchar(255) not null;

ALTER TABLE w_ksiazki
ADD CONSTRAINT pk_eanasid PRIMARY KEY (ean)

-- utworzenie fk

alter table f_wypozyczenia
alter column osob_id int

alter table f_wypozyczenia
alter column kodp varchar(255)

alter table f_wypozyczenia
add constraint fk_ean foreign key (ean) references w_ksiazki(ean)

alter table f_wypozyczenia
add constraint fk_osobid foreign key (osob_id) references w_klienci(id)

-- sprawdzenie wiezow integralnosci pomiedzy wypozyczeniami a adresami

--select count(distinct kodp) from f_wypozyczenia where kodp not in (select kod_p from w_adresy)
update f_wypozyczenia set kodp='66-666' where kodp not in (select kod_p from w_adresy)

insert into w_adresy (kod_p,miasto,dzielnica,ulica) values ('66-666','NIEZNANE','NIEZNANA','NIEZNANA');

alter table f_wypozyczenia
add constraint fk_kodpi foreign key (kodp) references w_adresy(kod_p);
end;

Całość to procedura która usuwa/tworzy tabele w odpowiedniej kolejności i kopiuje dane z jakiś innych struktur. Jest to model pokazowy, który nie spełnia wielu założeń komercyjnych modeli. Założeniem było maksymalne uproszczenie.

Każda hurtownia ma swoje tło, najczęściej biznesowe. Wytworzony model ma wspomagać pracę biblioteki. Biblioteka posiada listę książek(tytuł, autor, ean, cena, ilość stron), oraz listę klientów (nazwisko,pesel, kod_pocztowy). Biblioteka zakupiła słownik kodów pocztowych(kod pocztowy, ulica, dzielnica, miasto).
Dodatkowo biblioteka posiada system w którym są odnotowane: czas wypożyczenia, czas oddania, ean książki, id osoby wypożyczającej. Umówiliśmy się że za każdą przeczytaną stronę biblioteka dostaje dotację od Państwa w wysokości 1grosza(w 2010) lub 2 grosze(od 2011).

W modelach komercyjnych stosuje się różne podejścia w stosunku do zmienności danych (mniej lub bardziej restrykcyjnie – prowadzące do niezmienności, bądź niskiej zmienności danych w czasie), w przedstawionym modelu o to nie dbam. dlatego też usuwam wszystkie tabele do których ładuje dane.

begin try
drop table f_wypozyczenia
end try

begin catch
print 'cos'
end catch

Zaczynam od tabeli faktów, jak pisałem posiada ona same klucze obce i miary. Nie mogę usunąć tabel z kluczami podstawowymi do momentu usunięcia więzów integralności (drop table te więzy usuwa).

Powyższa składnia próbuje usunąć tabele, w przypadku gdy coś pójdzie nie tak procedura wydrukuje komunikat ‘cos’.

begin try
drop table w_adresy
end try

begin catch
print 'cos'
end catch

--- zaladowanie adresow do tabeli wymiarow
select *
into w_adresy
from (
select kod_p,
(select top 1 miasto from etl_badresy b1 where b.kod_p = b1.kod_p ) miasto,
(select top 1 dzielnica from etl_badresy b1 where b.kod_p = b1.kod_p ) dzielnica,
(select top 1 ulica from etl_badresy b1 where b.kod_p = b1.kod_p ) ulica

from etl_badresy b
) e
group by kod_p,miasto,dzielnica,ulica

Usunięcie wymiaru z słownikiem adresu, powyżej przykład (może niezbyt optymalny) ale pewny zapewnienia unikalności kodu pocztowego. Podczas tworzenia hurtowni danych nie raz spotkamy się z potrzebą stworzenia klucza unikalnego. Wtedy trzeba pójść na kompromis – zaprezentowałem jedno z podejść pt. świadoma utrata danych mało znaczących biznesowo.

W poprzednich moich wpisach na blogu opisałem metody ekstrakcji danych z numeru pesel, poniżej z nich skorzystałem:
begin try
drop table w_klienci
end try

begin catch
print 'cos'
end catch
--- zaladowanie klientow

select * ,case when len(pesel)=11 then dbo.get_wiek_from_pesel(pesel) else 0 end wiek,
dbo.pesel_plec(pesel) plec
into w_klienci
from etl_bosob

funkcja dbo.get_wiek_from_pesel(pesel) oblicza wiek na podstawie numeru pesel, a dbo.pesel_plec(pesel)
wyznacza płeć.

begin try
drop table w_ksiazki
end try

begin catch
print 'cos'
end catch

select * into w_ksiazki from etl_ksiazki

Niektóre wymiary uda się przekopiować żywcem…

Tabele faktów często są największe, dlatego też przed rozpoczęciem tworzenia warto przemyśleć kwestię założenia indeksów:

-- utworzenie indeksow
begin try
create index wypo_ean on etl_wypozyczenia(ean)
create index wypo_osob on etl_wypozyczenia(osob_id)
create index osob_id on etl_bosob (id)
end try
begin catch
print 'juz mam indeksy na etl'
end catch

W moim przypadku przed stworzeniem tabeli faktów musiałem ją połączyć z innymi tabelami by wyznaczyć sensowne klucze obce:


select osob_id,b.ean,o.kodp,cena,liczba_stron,data_od,data_do,datediff(day,data_od,data_do) ile_dni,
case when year(data_od)<2011 then 0.01 else 0.02 end zysk_per_strona, case when year(data_od)<2011 then 0.01 else 0.02 end * liczba_stron as zysk_wypozyczenie into f_wypozyczenia from etl_wypozyczenia w inner join etl_ksiazki b on b.ean = w.ean inner join etl_bosob o on o.id = osob_id

gdy dane są już w bazie zakładamy więzy integralności:


--utworzenie wiezow integralnosci pk

ALTER TABLE w_adresy
ALTER COLUMN kod_p varchar(255) not null

ALTER TABLE w_adresy
ADD CONSTRAINT pk_kodp PRIMARY KEY (kod_p)

ALTER TABLE w_klienci
ALTER COLUMN id int not null

ALTER TABLE w_klienci
ADD CONSTRAINT pk_klientID PRIMARY KEY (id)

Może się okazać że jakość danych nie pozwoli nam na takie zabiegi (nulle, powtarzające się rekordy wynikające z jakiś błędów). Poniżej obsługa błędu powtarzającego się ean książki:

-- zapewnienie unikalnosci ean w bazie ksiazek:
delete from w_ksiazki where ean in (
select ean from w_ksiazki
group by ean
having count(*) >1
) and id not in (
select max(id ) from w_ksiazki where ean in (
select ean from w_ksiazki
group by ean
having count(*) >1
)
)

Powyższe w przypadku wykrycia powielonego ean zostawi najnowszą wersję rekordu.

Jak mamy zapewnione utworzenie klucza podstawowego, możemy dokończyć kwestię integralności:

ALTER TABLE w_ksiazki
ALTER COLUMN ean varchar(255) not null;

ALTER TABLE w_ksiazki
ADD CONSTRAINT pk_eanasid PRIMARY KEY (ean)

-- utworzenie fk

alter table f_wypozyczenia
alter column osob_id int

alter table f_wypozyczenia
alter column kodp varchar(255)

alter table f_wypozyczenia
add constraint fk_ean foreign key (ean) references w_ksiazki(ean)

alter table f_wypozyczenia
add constraint fk_osobid foreign key (osob_id) references w_klienci(id)

może się okazać że udało się utworzyć PK, ale nie można stworzyć FK ponieważ, występują wartośći których nie ma w tabeli wymiarów. Wtedy takie wartości należy oznaczyć w jakiś sposób:

update f_wypozyczenia set kodp='66-666' where kodp not in (select kod_p from w_adresy)

insert into w_adresy (kod_p,miasto,dzielnica,ulica) values ('66-666','NIEZNANE','NIEZNANA','NIEZNANA');

Na koniec dodanie ostatniego klucza:
alter table f_wypozyczenia
add constraint fk_kodpi foreign key (kodp) references w_adresy(kod_p);

I mamy całość struktury gwiazdy, na tej podstawie z łatwością utworzy się kostkę analityczną.

SSIS – czyli ETL w kilka chwil

Jak utworzyć bazę danych łączącą dane z różnych źródeł?

Jeśli mamy pod ręką pakiet Visual Studio BI, nazwane w najnowszej wersji SQL SErver (2012 RC0) SQL Server Data tools, możemy dość szybko załadować dane do dowolnej bazy z różnego rodzaju źródeł danych.

Aby tego dokonać należy otworzyć VS BI, wybrać nowy projekt:

obrazek1

Następnie należy wybrać Integration Servics Project, w przypadku posiadania starszej wersji VS BI, interfejs użytkownika może wyglądać nieco inaczej, nie mniej wszystkie wykorzystywane funkcje powinny być dostępne.

obrazek2

Kolejno z ToolBox należy wybrać kontrolkę DataFlow:

Kontrolkę należy kliknąć dwukrotnie, wtedy wejdziemy w tak zwane data flow.

obrazek3

Po lewej stronie w toolboxie mamy kilka kategorii kontrolek do wyboru, pierwszą interesującą grupą jest Data source(other sources). W przypadku od źródła danych wybieramy odpowiednią kontrolkę na screenie poniżej wybrałem źródło flat source file – czyli zwykły plik tekstowy – w tym wypadku najzwyklejszy CSV.
Po kliknięciu kontrolki należy utworzyć ‘link’ do pliku i odpowiednio skonfigurować go jako źródło.

obrazek4

Całość jest dość intuicyjna(Jeśli ktoś już miał do czynienia z plikiem CSV). Po skonfigurowaniu źródła,
należy jeszcze mieć miejsce docelowe, ja utworzyłem tabelkę:

create table etl_badresy (
kod_p varchar(255),
miasto varchar(255),
dzielnica varchar(255),
ulica varchar(255)
)

Jeśli mamy miejsce docelowe należy dorzucić do dataflow kontrolkę z sekcji other destinations. Ja wrzucam dane na SQL Server więc wybrałem kontrolkę ADO.NET Destination. Z destinations należy uważać, lubią się wysypywać(zwłaszcza Oraclowe). W przypadku pracy na maszynie z procesorem 64BIT i systemem kompilowanym pod X64 niektóre sterowniki(connectory) baz nie działają najlepiej, zalecam przełączenie projektu na x86. Po skonfigurowaniu miejsca docelowego łączymy strzałką obie kontrolki (źródło i miejsce docelowe).

obrazek5
Po wybraniu odpowiedniej instancji i bazy danych wybieramy odpowiednią tabelę z listy(tą do której chcemy ładować dane).

obrazek6

Kolejno musimy wykonać mapowanie kolumn, w konfiguracji miejsca docelowego wybieramy mappings(po lewej).
i przeciągamy kolumny ze źródła do odpowiedniego miejsca docelowego. Jeśli nazwy kolumn są tożsame zostaną zmapowane automatycznie.

obrazek7

Na tym etapie możemy przeprowadzić test projektu. Wciskając F5 (kompilacja projektu w tradycyjnym VS). Powinniśmy zobaczyć plus minus coś takiego:

obrazek8

Jeśli wszystko się powiodło po chwili (chwila może być dłuższa w przypadku większych plików) powinniśmy zobaczyć coś takiego:

obrazek9

W ten sposób zakończyliśmy najprostszy projekt ETL 🙂