Projektowanie schematu magazynu danych
Podobnie jak wszystkie relacyjne bazy danych, magazyn danych zawiera tabele, w których są przechowywane dane, które chcesz analizować. Najczęściej te tabele są zorganizowane w schemacie zoptymalizowanym pod kątem modelowania wielowymiarowego, w którym miary liczbowe skojarzone ze zdarzeniami znanymi jako fakty mogą być agregowane przez atrybuty skojarzonych jednostek w wielu wymiarach. Na przykład miary skojarzone z zamówieniem sprzedaży (takie jak kwota zapłacona lub ilość uporządkowanych przedmiotów) mogą być agregowane według atrybutów daty, w której nastąpiła sprzedaż, klient, sklep itd.
Tabele w magazynie danych
Typowym wzorcem dla relacyjnych magazynów danych jest zdefiniowanie schematu zawierającego dwa rodzaje tabel: tabel wymiarów i tabel faktów .
Tabele wymiarów
Tabele wymiarów opisują jednostki biznesowe, takie jak produkty, osoby, miejsca i daty. Tabele wymiarów zawierają kolumny atrybutów jednostki. Na przykład jednostka klienta może mieć imię, nazwisko, adres e-mail i adres pocztowy (który może składać się z adresu ulicznego, miasta, kodu pocztowego i kraju lub regionu). Oprócz kolumn atrybutów tabela wymiarów zawiera unikatową kolumnę klucza, która unikatowo identyfikuje każdy wiersz w tabeli. W rzeczywistości często tabela wymiarów zawiera dwie kluczowe kolumny:
- klucz zastępczy specyficzny dla magazynu danych i jednoznacznie identyfikuje każdy wiersz w tabeli wymiarów w magazynie danych — zwykle zwiększa liczbę całkowitą.
- Klucz alternatywny, często klucz naturalny lub biznesowy używany do identyfikowania określonego wystąpienia jednostki w transakcyjnym systemie źródłowym, z którego pochodzi rekord jednostki , na przykład kod produktu lub identyfikator klienta.
Uwaga
Dlaczego mają dwa klucze? Istnieje kilka dobrych powodów:
- Magazyn danych może zostać wypełniony danymi z wielu systemów źródłowych, co może prowadzić do ryzyka zduplikowania lub niezgodnych kluczy biznesowych.
- Proste klucze liczbowe zwykle działają lepiej w zapytaniach, które łączą wiele tabel — typowy wzorzec w magazynach danych.
- Atrybuty jednostek mogą ulec zmianie w czasie — na przykład klient może zmienić swój adres. Ponieważ magazyn danych jest używany do obsługi raportowania historycznego, warto zachować rekord dla każdego wystąpienia jednostki w wielu punktach w czasie; tak, aby na przykład zamówienia sprzedaży dla określonego klienta były liczone dla miasta, w którym mieszkały w momencie złożenia zamówienia. W takim przypadku wiele rekordów klientów będzie mieć ten sam klucz biznesowy skojarzony z klientem, ale różne klucze zastępcze dla każdego dyskretnego adresu, w którym klient mieszkał w różnym czasie.
Przykład tabeli wymiarów dla klienta może zawierać następujące dane:
| CustomerKey | CustomerAltKey | Nazwisko | Ulica | City | PostalCode | CountryRegion | |
|---|---|---|---|---|---|---|---|
| 123 | I-543 | Navin Jones | navin1@contoso.com | 1 Główny St. | Seattle | 90000 | Stany Zjednoczone |
| 124 | R-589 | Mary Smith | mary2@contoso.com | 234 190th Ave | Buffalo | 50001 | Stany Zjednoczone |
| 125 | I-321 | Antoine Dubois | antoine1@contoso.com | 2 Rue Jolie | Paryż | 20098 | Francja |
| 126 | I-543 | Navin Jones | navin1@contoso.com | 24 125th Ave. | Nowy Jork | 50000 | Stany Zjednoczone |
| ... | ... | ... | ... | ... | ... | ... | ... |
Uwaga
Zwróć uwagę, że tabela zawiera dwa rekordy dla Navina Jonesa. Oba rekordy używają tego samego klucza alternatywnego, aby zidentyfikować tę osobę (I-543), ale każdy rekord ma inny klucz zastępczy. W tym celu można przypuszczać, że klient przeniósł się z Seattle do Nowego Jorku. Sprzedaż dokonana klientowi podczas pobytu w Seattle jest skojarzona z kluczem 123, podczas gdy zakupy dokonane po przeniesieniu do Nowego Jorku są rejestrowane w stosunku do rekordu 126.
Oprócz tabel wymiarów reprezentujących jednostki biznesowe często magazyn danych zawiera tabelę wymiarów reprezentującą czas. Ta tabela umożliwia analitykom danych agregowanie danych w odstępach czasu. W zależności od typu danych, które należy przeanalizować, najniższy stopień szczegółowości (określany jako ziarno) wymiaru czasu może reprezentować czasy (do godziny, sekundy, milisekund, nanosekundy, a nawet niższej) lub dat.
Przykład tabeli wymiarów czasu z ziarnem na poziomie daty może zawierać następujące dane:
| DateKey | DateAltKey | DayOfWeek | DayOfMonth | Weekday | Month | Nazwa miesiąca | Kwartał | Rok |
|---|---|---|---|---|---|---|---|---|
| 19990101 | 01-01-1999 | 6 | 1 | Piątek | 1 | Styczeń | 1 | 1999 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20220101 | 01-01-2022 | 7 | 1 | Sobota | 1 | Styczeń | 1 | 2022 |
| 20220102 | 02-01-2022 | 1 | 2 | Niedziela | 1 | Styczeń | 1 | 2022 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20301231 | 31-12-2030 | 3 | 31 | Wtorek | 12 | grudzień | 100 | 2030 |
Przedział czasu objęty rekordami w tabeli musi zawierać najwcześniejsze i najnowsze punkty w czasie dla wszystkich skojarzonych zdarzeń zarejestrowanych w powiązanej tabeli faktów. Zazwyczaj istnieje rekord dla każdego interwału z odpowiednim ziarnem między.
Tabele faktów
Tabele faktów przechowują szczegóły obserwacji lub zdarzeń; na przykład zamówienia sprzedaży, salda zapasów, kursy wymiany lub zarejestrowane temperatury. Tabela faktów zawiera kolumny dla wartości liczbowych, które mogą być agregowane według wymiarów. Oprócz kolumn liczbowych tabela faktów zawiera kolumny kluczy odwołujące się do unikatowych kluczy w powiązanych tabelach wymiarów.
Na przykład tabela faktów zawierająca szczegóły zamówień sprzedaży może zawierać następujące dane:
| OrderDateKey | CustomerKey | StoreKey | ProductKey (Klucz produktu) | OrderNo | LineItemNo | Ilość | UnitPrice | Podatek | ItemTotal |
|---|---|---|---|---|---|---|---|---|---|
| 20220101 | 123 | 5 | 701 | 1001 | 1 | 2 | 2,50 | 0.50 | 5.50 |
| 20220101 | 123 | 5 | 765 | 1001 | 2 | 1 | 2.00 | 0,20 | 2,20 |
| 20220102 | 125 | 2 | 723 | 1002 | 1 | 1 | 4,99 | 0,49 | 5.48 |
| 20220103 | 126 | 1 | 823 | 1003 | 1 | 1 | 7.99 | 0.80 | 8.79 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
Kolumny klucza wymiaru tabeli faktów określają jego ziarno. Na przykład tabela faktów zamówień sprzedaży zawiera klucze dat, klientów, sklepów i produktów. Zamówienie może zawierać wiele produktów, więc ziarno reprezentuje pozycje liniowe dla poszczególnych produktów sprzedawanych w sklepach klientom w określonych dniach.
Projekty schematów magazynu danych
W większości transakcyjnych baz danych używanych w aplikacjach biznesowych dane są znormalizowane w celu zmniejszenia duplikacji. Jednak w magazynie danych dane wymiarów są zwykle deznormalizowane , aby zmniejszyć liczbę sprzężeń wymaganych do wykonywania zapytań dotyczących danych.
Często magazyn danych jest zorganizowany jako schemat gwiazdy , w którym tabela faktów jest bezpośrednio powiązana z tabelami wymiarów, jak pokazano w tym przykładzie:
Atrybuty jednostki mogą służyć do agregowania miar w tabelach faktów na wielu poziomach hierarchicznych — na przykład w celu znalezienia całkowitego przychodu ze sprzedaży według kraju lub regionu, miasta, kodu pocztowego lub pojedynczego klienta. Atrybuty dla każdego poziomu mogą być przechowywane w tej samej tabeli wymiarów. Jeśli jednak jednostka ma dużą liczbę poziomów atrybutów hierarchicznych lub gdy niektóre atrybuty mogą być współużytkowane przez wiele wymiarów (na przykład zarówno klienci, jak i magazyny mają adres geograficzny), warto zastosować pewną normalizację do tabel wymiarów i utworzyć schemat płatka śniegu, jak pokazano w poniższym przykładzie:
W tym przypadku tabela DimProduct została znormalizowana w celu utworzenia oddzielnych tabel wymiarów dla kategorii produktów i dostawców, a tabela DimGeography została dodana w celu reprezentowania atrybutów geograficznych zarówno dla klientów, jak i sklepów. Każdy wiersz w tabeli DimProduct zawiera wartości klucza dla odpowiednich wierszy w tabelach DimCategory i DimSupplier, a każdy wiersz w tabelach DimCustomer i DimStore zawiera wartość klucza dla odpowiedniego wiersza w tabeli DimGeography.