Schematy Gwiazdowe: Projektowanie Bazy Danych Komercyjnej Na Około To, Jak Przedsiębiorstwa Zadawają Pytania

Dlaczego normalizować dla transakcji, ale denormalizować dla analizy

Bazę danych transakcyjną (OLTP) znormalizowujemy na dobre powody: gdy klient zmienia swoje adresy, chcesz aktualizować dokładnie jedno pole w dokładnie jednej tabeli, a nie szukać tego samego faktu po wszystkich podwójnie skopiowanych kopii. Ale ta sama znormalizowanie staje się zobowiązaniem, jak tylko próbujesz uruchomić analitykę, ponieważ odpowiedź na pytanie 'wysokość przychodu wraz z regionem klienta, kategorią produktu i miesiącem za ostatni dwa lata' na pełnoznormalizowanym schemacie oznacza łączenie się po dziesiątkach tabel, a to dla każdego zapytania w panelu jest zarówno wolne, jak i psychicznie natarczywe dla kogoś, kto ma napisać SQL.

Schemat gwiazdowy, popularizowany przez Ralpha Kimballa, rozwiązuje to poprzez świadomą denormalizację do celów odczytu i proste zapytania. Na centrum znajduje się tabela faktów — jedna linia dla każdego wydarzenia biznesowego, takiego jak pojedyncza pozycja na zamówieniu — otoczona tabelami wymiarowymi opisującymi kogo, co, gdzie i kiedy to wydarzenie: wymiarem klienta, wymiarem produktu, wymiarem daty, wymiarem sklepu. Rozrzucone wzdłuż osi, tabela faktów w centrum z wymiarami rozprzestrzenionymi wokół niej wygląda dokładnie jak gwiazda, co jest źródłem nazwy, a zapytanie do niej zwykle wymaga tylko kilku prostej łączeń z tabeli faktów do tych wymiarów, które dotyka pytanie.

Grain: najważniejsza decyzja projektowa

Przed nadaniem jednego kolumny wymaga dimensional modelu wyboru grain tabeli faktycznej — precyzyjnego, niezawodnego zdefiniowania tego, co reprezentuje pojedyncze pole. 'Jedno pole na zamówienie' i 'jedno pole na pozycję w zamówieniu' brzmiają podobnie, ale tworzą bardzo różne tabele: przy grainie zamówienia, $150 zamówienie z trzema pozycjami jest jednym polem z sumą; przy grainie pozycji zamówienia to trzy linie, każda dla osobnej produktu, każda ze swoim własnym ilością i ceną. Wybierając za duży grain (poziom zamówienia), stwarza się niemożliwość późniejszego pytania 'jakie produkty sprzedają najlepiej', ponieważ ta informacja została zignorowana podczas ładowania; wybierając odpowiednio precyzyjny grain (poziom pozycji zamówienia) zachowuje się możliwość późniejszej reagregacji na dowolne poziomy, co kosztuje większą tabelę faktyczną.

Zasada w dimensional modelling polega na budowie tabel faktycznych przy najniższym grain, który biznes mógłby kiedykolwiek potrzebować, ponieważ zawsze można zgrupować dane precyzyjne do wyższych poziomów, ale nigdy nie można rozdzielić danych zaawansowanych powrotem na niższe poziomy. Łańcuch handlowy przechowujący tylko sumy sprzedaży dla każdego sklepu dziennego nigdy nie będzie mógł odpowiedzieć 'jaka była najlepsza godzina sprzedania w dniach czarnych', niezależnie od tego, jak sofistyczna zostanie later zespół analtyczny, ponieważ ta rozdzieloność została utracona podczas przetwarzania.

Tabele faktowe: dodatnie miary i klucze obce

Dobrze zaprojektowana tabela faktowa zawiera głównie klucze obce wskazujące na tabele wymiarowe, oraz małą liczbę mierzonej numerycznej danych — rzeczy, które naprawdę chcesz sumować, średniczyć lub liczyć: ilość sprzedanych towarów, przychód, kwota rabatu, koszt. Najlepsze miary są całkowicie dodatnie, co oznacza, że można je sumować po dowolnym wymiarze bez produkcji sensu niestosownego — sumowanie przychodów po produktach, klientach i czasie daje rozsądne wyniki. Niektóre miary są tylko półdodatnie: salda kont mogą być sumowane w momencie danego okresu, ale sumowanie ich przez czas nie ma sensu (nie dodajesz salda poniedziałkowego do sobotniego, aby otrzymać kwartalne saldo) — te wymagają specjalnej obsługi, zazwyczaj podjęcia obrazu stanu lub średniej zamiast prostej sumy.

Tabele faktowe w dojrzałych magazynach są często ogromne — miliardy wierszy dla historii pozycji sprzedaży dużego sklepu — co jest dokładnie przyczyną ich małoformatowania: nie ma opisowej tekstury, ani nadmiernych atrybutów, tylko klucze i liczby. Wszystkie opisowe, wolno zmieniające się konteksty — imię klienta, kategoria produktu, region sklepu — żywe są w tabele wymiarowe zamiast tego, zachowując je małymi i bogatymi w atrybuty, które analiści filtrowują i grupują.

Mammercy zmieniające się wymiary: utrzymanie historii czystej

Atrybuty wymiarów zmieniają się w czasie — klient przenosi się do innej miejscowości, produkt jest przesunięty do nowej kategorii — a sposób obsługi tej zmiany znacząco wpływa na raportowanie historyczne. To problem, który rozwiązuje mamymercy zmieniające się wymiary (SCDs). Typ 1 prosto nadpisuje stare wartości nowymi, co jest stosowne wtedy, gdy historia naprawdę nie ma znaczenia (poprawianie pisma w imieniu klienta), ale oznacza to, że dowolny ponowiony raport historyczny po zmianie będzie pokazywał nową wartość nawet dla przeszłych okresów, cichym sposobem na zmodyfikowanie historii.

Typ 2 jest bardziej popularnym i mocniejszym podejściem do rzeczy, które naprawdę chcesz śledzić historycznie: zamiast nadpisywać, dodajesz nową wiersz klientowi z nową wartością atrybutu, kończysz poprzedni wiersz datą zakończenia i oznaczasz który wiersz jest obecny. Raport sprzedaży trzy lata temu prawidłowo pokazuje region klienta jakim był na chwilę sprzedania, ponieważ klucz obcy w tabeli faktów odnosi się do konkretnego wersji wymiaru aktywnej w danym czasie, a nie do tego, gdzie jest obecnie lokalizowany region klienta. Typ 3 to średnio miejsce używanym mniej często: dodaje kolumnę z poprzednią wartością obok kolumny z obecną wartością w tym samym wierszu, co działa tylko gdy musisz porównać dokładnie jedno wcześniejsze stanie z bieżącym — przydatne do śledzenia jednego wcześniejszego przypisania terenu sprzedaży, ale nie skali się na atrybuty zmieniające się wielokrotnie.

Często zadawane pytania