schema types data warehouse modeling star snowflake schema
W tym samouczku wyjaśniono różne typy schematów hurtowni danych. Dowiedz się, czym jest schemat gwiazdy i schemat płatka śniegu oraz różnica między schematem gwiazdy a schematem płatka śniegu:
W tym Samouczki dotyczące magazynu daty dla początkujących , przyjrzeliśmy się dogłębnie Wymiarowy model danych w hurtowni danych w naszym poprzednim samouczku.
W tym samouczku nauczymy się wszystkiego o schematach hurtowni danych, które są używane do strukturyzowania baz danych (lub) tabel hurtowni danych.
narzędzie Load Runner do testowania wydajności
Zaczynajmy!!
Docelowi odbiorcy
- Programiści i testerzy hurtowni danych / ETL.
- Specjaliści od baz danych z podstawową wiedzą na temat koncepcji baz danych.
- Administratorzy baz danych / eksperci Big Data, którzy chcą zrozumieć obszary hurtowni danych / ETL.
- Absolwenci / świeżo upieczeni studenci, którzy szukają pracy w hurtowni danych.
Czego się nauczysz:
Schemat hurtowni danych
W hurtowni danych schemat służy do definiowania sposobu organizacji systemu ze wszystkimi jednostkami bazy danych (tabele faktów, tabele wymiarów) oraz ich powiązania logiczne.
Oto różne typy schematów w DW:
- Harmonogram Gwiazda
- Schemat płatka śniegu
- Diagram galaktyki
- Schemat gromady gwiazd
# 1) Harmonogram gwiazd
To najprostszy i najbardziej efektywny schemat w hurtowni danych. Tabela faktów pośrodku otoczona tabelami wielu wymiarów przypomina gwiazdę w modelu schematu gwiazdy.
Tabela faktów zachowuje relacje jeden do wielu ze wszystkimi tabelami wymiarów. Każdy wiersz w tabeli faktów jest powiązany z wierszami tabeli wymiarów za pomocą odniesienia do klucza obcego.
Z tego powodu nawigacja między tabelami w tym modelu jest łatwa w przypadku zapytań o zagregowane dane. Użytkownik końcowy może łatwo zrozumieć tę strukturę. Dlatego wszystkie narzędzia Business Intelligence (BI) w dużym stopniu wspierają model schematu Star.
Podczas projektowania schematów gwiaździstych tabele wymiarów są celowo znormalizowane. Są szerokie i mają wiele atrybutów do przechowywania danych kontekstowych w celu lepszej analizy i raportowania.
Korzyści ze schematu gwiezdnego
- Zapytania używają bardzo prostych złączeń podczas pobierania danych, co zwiększa ich wydajność.
- Łatwo jest pobierać dane do raportowania w dowolnym momencie w dowolnym okresie.
Wady schematu gwiazdy
- Jeśli w wymaganiach jest wiele zmian, nie zaleca się modyfikowania i ponownego wykorzystywania istniejącego schematu gwiazdy na dłuższą metę.
- Nadmiarowość danych jest większa, ponieważ tabele nie są podzielone hierarchicznie.
Poniżej podano przykład schematu gwiezdnego.
Zapytanie o schemat gwiazdy
Użytkownik końcowy może zażądać raportu za pomocą narzędzi Business Intelligence. Wszystkie takie żądania będą przetwarzane przez wewnętrzne utworzenie łańcucha „zapytań SELECT”. Wydajność tych zapytań będzie miała wpływ na czas wykonania raportu.
Z powyższego przykładu schematu gwiazdy, jeśli użytkownik biznesowy chce wiedzieć, ile powieści i dysków DVD zostało sprzedanych w stanie Kerala w styczniu 2018 roku, można zastosować zapytanie w następujący sposób do tabel schematu gwiazdy:
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Product pdim, Sales sfact, Store sdim, Date ddim WHERE sfact.product_id = pdim.product_id AND sfact.store_id = sdim.store_id AND sfact.date_id = ddim.date_id AND sdim.state = 'Kerala' AND ddim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Wyniki:
| Nazwa produktu | Ilość sprzedana | |
|---|---|---|
| 7 | Każdy może łatwo zrozumieć i zaprojektować schemat. | Trudno jest zrozumieć i zaprojektować schemat. |
| Powieści | 12,702 | |
| DVD | 32,919 |
Mam nadzieję, że zrozumiałeś, jak łatwo jest przeszukiwać schemat gwiazdy.
# 2) Schemat płatka śniegu
Schemat gwiazdy działa jako dane wejściowe do projektowania schematu SnowFlake. Płatkowanie śniegu to proces, który całkowicie normalizuje wszystkie tabele wymiarów ze schematu gwiazdy.
Rozmieszczenie tabeli faktów pośrodku, otoczonej wieloma hierarchiami tabel wymiarów, wygląda jak SnowFlake w modelu schematu SnowFlake. Każdy wiersz tabeli faktów jest powiązany z wierszami tabeli wymiarów za pomocą odniesienia do klucza obcego.
Podczas projektowania schematów SnowFlake tabele wymiarów są celowo znormalizowane. Klucze obce zostaną dodane do każdego poziomu tabel wymiarów w celu połączenia z ich atrybutem nadrzędnym. Złożoność schematu SnowFlake jest wprost proporcjonalna do poziomów hierarchii tabel wymiarów.
Korzyści ze schematu SnowFlake:
- Nadmiarowość danych została całkowicie usunięta poprzez utworzenie nowych tabel wymiarów.
- W porównaniu ze schematem gwiaździstym, tabele wymiarów płatków śniegu zajmują mniej miejsca do przechowywania.
- Aktualizacja (lub) obsługa tabel płatków śniegu jest łatwa.
Wady schematu SnowFlake:
- Ze względu na znormalizowane tabele wymiarów system ETL musi załadować liczbę tabel.
- Ze względu na liczbę dodanych tabel do wykonania zapytania mogą być potrzebne złożone sprzężenia. W związku z tym wydajność zapytań zostanie obniżona.
Poniżej przedstawiono przykład schematu płatka śniegu.
Tabele wymiarów na powyższym diagramie płatka śniegu są znormalizowane, jak wyjaśniono poniżej:
- Wymiar daty jest normalizowany w tabelach kwartalnych, miesięcznych i tygodniowych przez pozostawienie identyfikatorów kluczy obcych w tabeli Date.
- Wymiar sklepu jest znormalizowany, aby obejmował tabelę stanu.
- Wymiar produktu jest znormalizowany do marki.
- W wymiarze Klient atrybuty powiązane z miastem są przenoszone do nowej tabeli Miasto przez pozostawienie identyfikatora klucza obcego w tabeli Klient.
W ten sam sposób pojedynczy wymiar może zachować wiele poziomów hierarchii.
Różne poziomy hierarchii z powyższego diagramu można odnieść w następujący sposób:
- Identyfikator kwartalny, Identyfikator miesięczny i Identyfikatory tygodniowe to nowe klucze zastępcze, które są tworzone dla hierarchii wymiarów daty i zostały dodane jako klucze obce w tabeli wymiarów Data.
- Identyfikator stanu jest nowym kluczem zastępczym utworzonym dla hierarchii wymiarów Sklepu i został dodany jako klucz obcy w tabeli wymiarów Sklep.
- Identyfikator marki jest nowym kluczem zastępczym utworzonym dla hierarchii wymiarów produktu i został dodany jako klucz obcy w tabeli wymiarów produktu.
- Identyfikator miasta jest nowym kluczem zastępczym utworzonym dla hierarchii wymiarów klienta i został dodany jako klucz obcy w tabeli wymiarów klienta.
Zapytanie o schemat płatka śniegu
Możemy generować takie same raporty dla użytkowników końcowych, jak struktury schematów gwiazd ze schematami SnowFlake. Ale pytania są tutaj nieco skomplikowane.
Na podstawie powyższego przykładu schematu SnowFlake wygenerujemy to samo zapytanie, które zaprojektowaliśmy w przykładzie zapytania ze schematem gwiazdy.
Oznacza to, że jeśli użytkownik biznesowy chce wiedzieć, ile powieści i dysków DVD zostało sprzedanych w stanie Kerala w styczniu 2018 r., Możesz zastosować zapytanie w następujący sposób do tabel schematu SnowFlake.
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Sales sfact INNER JOIN Product pdim ON sfact.product_id = pdim.product_id INNER JOIN Store sdim ON sfact.store_id = sdim.store_id INNER JOIN State stdim ON sdim.state_id = stdim.state_id INNER JOIN Date ddim ON sfact.date_id = ddim.date_id INNER JOIN Month mdim ON ddim.month_id = mdim.month_id WHERE stdim.state = 'Kerala' AND mdim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Wyniki:
| Nazwa produktu | Ilość sprzedana |
|---|---|
| Powieści | 12,702 |
| DVD | 32,919 |
Punkty do zapamiętania podczas wykonywania zapytań do tabel schematów gwiazdy (lub) SnowFlake
Każde zapytanie można zaprojektować z poniższą strukturą:
Klauzula SELECT:
- Atrybuty określone w klauzuli select są wyświetlane w wynikach zapytania.
- Instrukcja Select również używa grup do znalezienia zagregowanych wartości, dlatego musimy użyć klauzuli group by w warunku where.
Z punktu:
- Wszystkie istotne tabele faktów i tabele wymiarów należy wybrać zgodnie z kontekstem.
GDZIE Klauzula:
- Odpowiednie atrybuty wymiarów są wymienione w klauzuli where poprzez połączenie z atrybutami tabeli faktów. Klucze zastępcze z tabel wymiarów są łączone z odpowiednimi kluczami obcymi z tabel faktów w celu ustalenia zakresu danych do zapytania. Aby to zrozumieć, zapoznaj się z powyższym przykładem zapytania ze schematem gwiazdy. Możesz również filtrować dane w samej klauzuli from, jeśli używasz tam sprzężeń wewnętrznych / zewnętrznych, jak napisano w przykładzie schematu SnowFlake.
- Atrybuty wymiarów są również wymienione jako ograniczenia danych w klauzuli Where.
- Filtrując dane we wszystkich powyższych krokach, zwracane są odpowiednie dane do raportów.
Zgodnie z potrzebami biznesowymi możesz dodawać (lub) usuwać fakty, wymiary, atrybuty i ograniczenia do schematu gwiaździstego (lub) zapytania schematu SnowFlake, postępując zgodnie z powyższą strukturą. Możesz także dodać zapytania podrzędne (lub) scalić różne wyniki zapytań w celu wygenerowania danych dla dowolnych złożonych raportów.
# 3) Diagram galaktyki
Schemat galaktyki jest również znany jako Schemat konstelacji faktów. W tym schemacie wiele tabel faktów ma te same tabele wymiarów. Rozmieszczenie tabel faktów i tabel wymiarów wygląda jak zbiór gwiazdek w modelu schematu Galaxy.
Wymiary współdzielone w tym modelu nazywane są wymiarami zgodnymi.
Ten typ schematu jest używany w przypadku zaawansowanych wymagań i zagregowanych tabel faktów, które są bardziej złożone, aby były obsługiwane przez schemat Star (lub) schemat SnowFlake. Ten schemat jest trudny do utrzymania ze względu na jego złożoność.
Poniżej przedstawiono przykład schematu Galaxy.
# 4) Schemat gromady gwiazd
Schemat SnowFlake z wieloma tabelami wymiarów może wymagać bardziej złożonych połączeń podczas wykonywania zapytań. Schemat gwiazdy z mniejszą liczbą tabel wymiarów może mieć większą nadmiarowość. W związku z tym na obrazie pojawił się schemat gromady gwiazd, łączący cechy dwóch powyższych schematów.
Schemat gwiazdy jest podstawą do zaprojektowania schematu gromady gwiazd, a kilka podstawowych tabel wymiarów ze schematu gwiazdy jest pokrytych śniegiem, co z kolei tworzy bardziej stabilną strukturę schematu.
Poniżej podano przykład schematu gromady gwiazd.
Który jest lepszy schemat płatka śniegu czy schemat gwiazdy?
Platforma hurtowni danych i narzędzia BI używane w Twoim systemie DW będą odgrywać kluczową rolę w wyborze odpowiedniego schematu do zaprojektowania. Star i SnowFlake to najczęściej używane schematy w DW.
Schemat gwiaździsty jest preferowany, jeśli narzędzia BI umożliwiają użytkownikom biznesowym łatwą interakcję ze strukturami tabel za pomocą prostych zapytań. Schemat SnowFlake jest preferowany, jeśli narzędzia BI są bardziej skomplikowane dla użytkowników biznesowych do bezpośredniej interakcji ze strukturami tabel z powodu większej liczby sprzężeń i złożonych zapytań.
Możesz kontynuować ze schematem SnowFlake, jeśli chcesz zaoszczędzić trochę miejsca na dysku lub jeśli Twój system DW ma zoptymalizowane narzędzia do projektowania tego schematu.
Schemat gwiazdy kontra schemat płatka śniegu
Poniżej podano kluczowe różnice między schematem Star i schematem SnowFlake.
| S.Nr | Harmonogram Gwiazda | Schemat płatka śniegu |
|---|---|---|
| 1 | Nadmiarowość danych to więcej. | Nadmiarowość danych jest mniejsza. |
| dwa | Przestrzeń do przechowywania tabel wymiarów to więcej. | Przestrzeń do przechowywania tabel wymiarów jest stosunkowo mniejsza. |
| 3 | Zawiera zdenormalizowane tabele wymiarów. | Zawiera znormalizowane tabele wymiarów. |
| 4 | Pojedyncza tabela faktów jest otoczona tabelami wielu wymiarów. | Pojedyncza tabela faktów jest otoczona wieloma hierarchiami tabel wymiarów. |
| 5 | Zapytania używają bezpośrednich sprzężeń między faktami i wymiarami do pobierania danych. | Zapytania używają złożonych sprzężeń między faktami i wymiarami w celu pobrania danych. |
| 6 | Czas wykonania zapytania jest krótszy. | Czas wykonania zapytania to więcej. |
| 8 | Stosuje podejście odgórne. | Stosuje podejście oddolne. |
Wniosek
Mamy nadzieję, że dobrze rozumiesz różne typy schematów hurtowni danych, a także ich zalety i wady z tego samouczka.
Dowiedzieliśmy się również, w jaki sposób można przeszukiwać schemat gwiazdy i schemat płatka śniegu oraz który schemat ma wybierać między tymi dwoma, a także ich różnice.
Zaglądaj do naszego nadchodzącego samouczka, aby dowiedzieć się więcej o Data Mart w ETL !!
=> Obejrzyj serię szkoleń dotyczących prostego magazynowania danych tutaj.
rekomendowane lektury
- Typy danych w Pythonie
- Typy danych C ++
- Samouczek dotyczący testowania hurtowni danych z przykładami | Przewodnik po testach ETL
- 10 najpopularniejszych narzędzi hurtowni danych i technologii testowania
- Wymiarowy model danych w hurtowni danych - samouczek z przykładami
- Samouczek dotyczący testowania hurtowni danych ETL (kompletny przewodnik)
- Co to jest proces ETL (wyodrębnianie, przekształcanie, ładowanie) w hurtowni danych?
- Eksploracja danych: proces, techniki i główne problemy w analizie danych