Modelowanie wymiarowe w Amazon Redshift | Usługi sieciowe Amazona

Amazonka Przesunięcie ku czerwieni to w pełni zarządzana hurtownia danych w chmurze o wielkości petabajtów, która jest używana przez dziesiątki tysięcy klientów do codziennego przetwarzania eksabajtów danych w celu obsługi obciążeń analitycznych. Możesz ustrukturyzować swoje dane, mierzyć procesy biznesowe i szybko uzyskiwać cenne spostrzeżenia, używając modelu wymiarowego. Amazon Redshift zapewnia wbudowane funkcje przyspieszające proces modelowania, orkiestracji i raportowania z modelu wielowymiarowego.

W tym poście omawiamy, jak zaimplementować model wymiarowy, a konkretnie model Metodologia Kimballa. Omawiamy wymiary i fakty dotyczące wdrażania w Amazon Redshift. Pokazujemy, jak przeprowadzić ekstrakcję, transformację i ładowanie (ELT), proces integracji skoncentrowany na pobieraniu nieprzetworzonych danych z jeziora danych do warstwy pomostowej w celu wykonania modelowania. Ogólnie rzecz biorąc, post da ci jasne zrozumienie, jak korzystać z modelowania wymiarowego w Amazon Redshift.

Omówienie rozwiązania

Poniższy schemat ilustruje architekturę rozwiązania.

W poniższych sekcjach najpierw omówimy i zademonstrujemy kluczowe aspekty modelu wymiarowego. Następnie tworzymy hurtownię danych za pomocą Amazon Redshift z wymiarowym modelem danych, w tym tabelami wymiarów i faktów. Dane są ładowane i umieszczane przy użyciu KOPIA polecenie, dane w wymiarach są ładowane za pomocą MERGE stwierdzenie, a fakty zostaną połączone z wymiarami, z których pochodzą spostrzeżenia. Planujemy ładowanie wymiarów i faktów za pomocą Edytor zapytań Amazon Redshift V2. Na koniec używamy Amazon QuickSight aby uzyskać wgląd w modelowane dane w postaci pulpitu nawigacyjnego QuickSight.

W przypadku tego rozwiązania używamy przykładowego zestawu danych (znormalizowanego) dostarczonego przez Amazon Redshift do sprzedaży biletów na wydarzenia. W tym poście zawęziliśmy zestaw danych dla uproszczenia i celów demonstracyjnych. Poniższe tabele przedstawiają przykładowe dane dotyczące sprzedaży biletów i miejsc.

Według Metodologia modelowania wymiarowego Kimballa, istnieją cztery kluczowe etapy projektowania modelu wymiarowego:

1. Zidentyfikuj proces biznesowy.
2. Zadeklaruj ziarno swoich danych.
3. Zidentyfikuj i zaimplementuj wymiary.
4. Zidentyfikuj i zastosuj fakty.

Dodatkowo do celów demonstracyjnych dodajemy piąty krok, czyli raportowanie i analizowanie zdarzeń biznesowych.

Wymagania wstępne

W tej instrukcji należy spełnić następujące wymagania wstępne:

Zidentyfikuj proces biznesowy

Mówiąc prościej, identyfikacja procesu biznesowego to identyfikacja mierzalnego zdarzenia, które generuje dane w organizacji. Zwykle firmy mają jakiś operacyjny system źródłowy, który generuje ich dane w surowym formacie. Jest to dobry punkt wyjścia do zidentyfikowania różnych źródeł dla procesu biznesowego.

Proces biznesowy jest następnie utrwalany jako plik Data Mart w postaci wymiarów i faktów. Patrząc na nasz przykładowy zestaw danych wspomniany wcześniej, możemy wyraźnie zobaczyć, że proces biznesowy to sprzedaż dokonywana dla danego wydarzenia.

Częstym błędem jest wykorzystywanie działów firmy jako procesu biznesowego. Dane (proces biznesowy) muszą być zintegrowane w różnych działach, w takim przypadku dział marketingu może uzyskać dostęp do danych sprzedaży. Identyfikacja prawidłowego procesu biznesowego ma kluczowe znaczenie — błędne wykonanie tego kroku może mieć wpływ na całą hurtownię danych (może to spowodować zduplikowanie ziarna i nieprawidłowe metryki w raportach końcowych).

Zadeklaruj ziarno swoich danych

Deklarowanie ziarna to czynność polegająca na unikatowym identyfikowaniu rekordu w źródle danych. Ziarno jest używane w tabeli faktów do dokładnego pomiaru danych i umożliwia dalsze zestawienie. W naszym przykładzie może to być pozycja w procesie biznesowym sprzedaży.

W naszym przypadku sprzedaż można jednoznacznie zidentyfikować, patrząc na czas transakcji, w której miała miejsce sprzedaż; będzie to poziom najbardziej atomowy.

Zidentyfikuj i zaimplementuj wymiary

Tabela wymiarów opisuje tabelę faktów i jej atrybuty. Podczas identyfikowania opisowego kontekstu procesu biznesowego zapisujesz tekst w osobnej tabeli, pamiętając o ziarnistości tabeli faktów. Podczas łączenia tabeli wymiarów z tabelą faktów z tabelą faktów powinien być powiązany tylko jeden wiersz. W naszym przykładzie używamy następującej tabeli, aby podzielić ją na tabelę wymiarów; pola te opisują fakty, które będziemy mierzyć.

Podczas projektowania struktury modelu wymiarowego (schematu) można albo utworzyć plik gwiazda or płatek śniegu schemat. Struktura powinna być ściśle powiązana z procesem biznesowym; dlatego schemat gwiazdy najlepiej pasuje do naszego przykładu. Poniższy rysunek przedstawia nasz Diagram relacji encji (ERD).

W poniższych sekcjach szczegółowo opisujemy kroki wdrażania wymiarów.

Przygotuj dane źródłowe

Zanim będziemy mogli utworzyć i załadować tabelę wymiarów, potrzebujemy danych źródłowych. Dlatego umieszczamy dane źródłowe w tabeli przejściowej lub tymczasowej. Jest to często określane jako warstwa inscenizacyjna, która jest nieprzetworzoną kopią danych źródłowych. Aby to zrobić w Amazon Redshift, używamy polecenie KOPIUJ aby załadować dane z publicznego zasobnika S3 modelowania wymiarowego w amazonce z przesunięciem ku czerwieni znajdującego się na us-east-1 Region. Zauważ, że polecenie COPY używa pliku AWS Zarządzanie tożsamością i dostępem (IAM) rola z dostęp do Amazona S3. Rola musi być związany z klastrem. Wykonaj następujące kroki, aby przygotować dane źródłowe:

1. utworzyć venue tabela źródłowa:

CREATE TABLE public.venue ( venueid bigint, venuename character varying(100), venuecity character varying(30), venuestate character(2), venueseats bigint
) DISTSTYLE AUTO SORTKEY (venueid);

2. Załaduj dane miejsca:

COPY public.venue
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/venue.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

3. utworzyć sales tabela źródłowa:

CREATE TABLE public.sales (
    salesid integer,
    venueid character varying(256),
    saletime timestamp without time zone,
    qtysold BIGINT,
    commission numeric(18,2),
    pricepaid numeric(18,2)
) DISTSTYLE AUTO;

4. Załaduj dane źródła sprzedaży:

COPY public.sales
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/sales.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

5. utworzyć calendar stół:

CREATE TABLE public.DimCalendar(
    dateid smallint,
        caldate date,
        day varchar(20),
        week smallint,
        month varchar(20),
        qtr varchar(20),
        year smallint,
        holiday boolean
) DISTSTYLE AUTO
SORTKEY
    (dateid);

6. Załaduj dane kalendarza:

COPY public.DimCalendar
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/date.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ',' 
REGION 'us-east-1'
IGNOREHEADER 1

Utwórz tabelę wymiarów

Projektowanie tabeli wymiarów może zależeć od wymagań biznesowych — na przykład czy chcesz śledzić zmiany danych w czasie? Tam są siedem różnych typów wymiarów. Dla naszego przykładu używamy wpisz 1 ponieważ nie musimy śledzić historycznych zmian. Aby uzyskać więcej informacji na temat typu 2, patrz Uprość ładowanie danych do powoli zmieniających się wymiarów typu 2 w Amazon Redshift. Tabela wymiarów zostanie zdenormalizowana za pomocą klucza podstawowego, klucza zastępczego i kilku dodanych pól wskazujących zmiany w tabeli. Zobacz następujący kod:

create schema SalesMart;

CREATE TABLE SalesMart.DimVenue( 
    "VenueSkey" int IDENTITY(1,1) primary key
    ,"VenueId" VARCHAR NOT NULL
    ,"VenueName" VARCHAR NULL
    ,"VenueCity" VARCHAR NULL
    ,"VenueState" VARCHAR NULL
    ,"VenueSeats" INT NULL
    ,"InsertedDate" DATETIME NOT NULL
    ,"UpdatedDate" DATETIME NOT NULL
) 
diststyle AUTO;

Kilka uwag na temat tworzenia tabeli wymiarów:

• Nazwy pól są przekształcane w nazwy przyjazne dla biznesu
• Naszym podstawowym kluczem jest VenueID, którego używamy do jednoznacznej identyfikacji miejsca, w którym miała miejsce sprzedaż
• Zostaną dodane dwa dodatkowe wiersze wskazujące, kiedy rekord został wstawiony i zaktualizowany (w celu śledzenia zmian)
• Używamy Styl dystrybucji AUTO powierzyć Amazonowi Redshift odpowiedzialność za wybór i dostosowanie stylu dystrybucji

Innym ważnym czynnikiem, który należy wziąć pod uwagę w modelowaniu wymiarowym, jest użycie klucze zastępcze. Klucze zastępcze to sztuczne klucze używane w modelowaniu wymiarowym do jednoznacznej identyfikacji każdego rekordu w tabeli wymiarów. Zazwyczaj są one generowane jako sekwencyjne liczby całkowite i nie mają żadnego znaczenia w domenie biznesowej. Oferują one kilka korzyści, takich jak zapewnienie wyjątkowości i poprawa wydajności połączeń, ponieważ są zazwyczaj mniejsze niż klucze naturalne i jako klucze zastępcze nie zmieniają się w czasie. To pozwala nam być konsekwentnym i łatwiej łączyć fakty i wymiary.

W Amazon Redshift klucze zastępcze są zwykle tworzone przy użyciu słowa kluczowego IDENTITY. Na przykład poprzednia instrukcja CREATE tworzy tabelę wymiarów z a VenueSkey Klucz zastępczy. The VenueSkey kolumna jest automatycznie wypełniana unikatowymi wartościami w miarę dodawania nowych wierszy do tabeli. Tej kolumny można następnie użyć do przyłączenia tabeli miejsc do FactSaleTransactions tabela.

Kilka wskazówek dotyczących projektowania kluczy zastępczych:

• Użyj małego typu danych o stałej szerokości dla klucza zastępczego. Poprawi to wydajność i zmniejszy przestrzeń dyskową.
• Użyj słowa kluczowego IDENTITY lub wygeneruj klucz zastępczy, używając wartości sekwencyjnej lub identyfikatora GUID. Dzięki temu klucz zastępczy będzie unikalny i nie będzie można go zmienić.

Załaduj tabelę dim za pomocą MERGE

Istnieje wiele sposobów ładowania tabeli dim. Należy wziąć pod uwagę pewne czynniki — na przykład wydajność, ilość danych i być może czasy ładowania umów SLA. z MERGE wykonujemy upsert bez konieczności określania wielu poleceń wstawiania i aktualizowania. Możesz ustawić MERGE oświadczenie w A procedura składowana aby wypełnić dane. Następnie zaplanujesz programowe uruchamianie procedury składowanej za pośrednictwem edytora zapytań, co zademonstrujemy w dalszej części wpisu. Poniższy kod tworzy procedurę składowaną o nazwie SalesMart.DimVenueLoad:

CREATE OR REPLACE PROCEDURE SalesMart.DimVenueLoad()
AS $$
BEGIN
MERGE INTO SalesMart.DimVenue USING public.venue as MergeSource
ON SalesMart.DimVenue.VenueId = MergeSource.VenueId
WHEN MATCHED
THEN
UPDATE
SET VenueName = ISNULL(MergeSource.VenueName, 'Unknown')
, VenueCity = ISNULL(MergeSource.VenueCity, 'Unknown')
, VenueState = ISNULL(MergeSource.VenueState, 'Unknown')
, VenueSeats = ISNULL(MergeSource.VenueSeats, -1)
, UpdatedDate = GETDATE()
WHEN NOT MATCHED
THEN
INSERT (
VenueId
, VenueName
, VenueCity
, VenueState
, VenueSeats
, UpdatedDate
, InsertedDate
)
VALUES (
ISNULL(MergeSource.VenueId, -1)
, ISNULL(MergeSource.VenueName, 'Unknown')
, ISNULL(MergeSource.VenueCity, 'Unknown')
, ISNULL(MergeSource.VenueState, 'Unknown')
, ISNULL(MergeSource.VenueSeats, -1)
, ISNULL(GETDATE() , '1900-01-01')
, ISNULL(GETDATE() , '1900-01-01')
);
END;
$$
LANGUAGE plpgsql;

Kilka uwag na temat ładowania wymiarów:

• Gdy rekord jest wstawiany po raz pierwszy, wstawiona data i zaktualizowana data zostaną wypełnione. W przypadku zmiany jakichkolwiek wartości dane są aktualizowane, a zaktualizowana data odzwierciedla datę zmiany. Wstawiona data pozostaje.
• Ponieważ dane będą używane przez użytkowników biznesowych, musimy zastąpić wartości NULL, jeśli takie istnieją, bardziej odpowiednimi wartościami biznesowymi.

Zidentyfikuj i zastosuj fakty

Teraz, gdy zadeklarowaliśmy, że nasze zboże jest wydarzeniem sprzedaży, która miała miejsce w określonym czasie, nasza tabela faktów będzie przechowywać fakty numeryczne dla naszego procesu biznesowego.

Zidentyfikowaliśmy następujące fakty liczbowe do zmierzenia:

• Liczba sprzedanych biletów na sprzedaż
• Prowizja od sprzedaży

Wdrażanie Faktu

Tam są trzy rodzaje tabel faktów (tabela faktów transakcji, tabela faktów migawek okresowych i tabela faktów kumulujących się migawek). Każdy z nich przedstawia inny pogląd na proces biznesowy. W naszym przykładzie używamy tabeli faktów transakcji. Wykonaj następujące kroki:

1. Utwórz tabelę faktów

CREATE TABLE SalesMart.FactSaleTransactions( 
    CalendarDate date NOT NULL
    ,SaleTransactionTime DATETIME NOT NULL
    ,VenueSkey INT NOT NULL
    ,QuantitySold BIGINT NOT NULL
    ,SaleComission NUMERIC NOT NULL
    ,InsertedDate DATETIME DEFAULT GETDATE()
) diststyle AUTO;

Dodawana jest wstawiona data z wartością domyślną, wskazująca, czy i kiedy rekord został załadowany. Możesz użyć tego podczas ponownego ładowania tabeli faktów, aby usunąć już załadowane dane, aby uniknąć duplikatów.

Ładowanie tabeli faktów składa się z prostej instrukcji wstawiania łączącej powiązane wymiary. Dołączamy od godz DimVenue tabela, która została utworzona, która opisuje nasze fakty. To najlepsza praktyka, ale opcjonalna data kalendarzowa wymiary, które umożliwiają użytkownikowi końcowemu poruszanie się po tabeli faktów. Dane mogą być ładowane, gdy pojawia się nowa sprzedaż lub codziennie; w tym miejscu przydaje się wstawiona data lub data załadunku.

Ładujemy tabelę faktów za pomocą procedury składowanej i używamy parametru daty.

2. Utwórz procedurę składowaną za pomocą następującego kodu. Aby zachować taką samą integralność danych, jaką zastosowaliśmy przy ładowaniu wymiarów, zastępujemy wartości NULL, jeśli takie istnieją, bardziej odpowiednimi wartościami biznesowymi:

create or replace procedure SalesMart.FactSaleTransactionsLoad(loadate datetime)
language plpgsql
as
    $$
begin
--------------------------------------------------------------------
/*** Delete records loaded for the day, should there be any ***/
--------------------------------------------------------------------
Delete from SalesMart.FactSaleTransactions
where cast(InsertedDate as date) = CAST(loadate as date);
RAISE INFO 'Deleted rows for load date: %', loadate;
--------------------------------------------------------------------
/*** Insert records ***/
--------------------------------------------------------------------
INSERT INTO SalesMart.FactSaleTransactions (
CalendarDate    
,SaleTransactionTime    
,VenueSkey  
,QuantitySold  
,Salecomission
)
SELECT DISTINCT
    ISNULL(c.caldate, '1900-01-01') as CalendarDate
    ,ISNULL(a.saletime, '1900-01-01') as SaleTransactionTime
    ,ISNULL(b.VenueSkey, -1) as VenueSkey
    ,ISNULL(a.qtysold, 0) as QuantitySold
    ,ISNULL(a.commission, 0) as SaleComission
FROM
    public.sales as a
 
LEFT JOIN SalesMart.DimVenue as b
on a.venueid = b.venueid
 
LEFT JOIN public.DimCalendar as c
on to_char(a.saletime,'YYYYMMDD') = to_char(c.caldate,'YYYYMMDD');
--Optional filter, should you want to load only the latest data from source
--where cast(a.saletime as date) = cast(loadate as date);
  
end;
$$;

3. Załaduj dane, wywołując procedurę za pomocą następującego polecenia:

call SalesMart.FactSaleTransactionsLoad(getdate())

Zaplanuj ładowanie danych

Możemy teraz zautomatyzować proces modelowania, planując procedury składowane w Amazon Redshift Query Editor V2. Wykonaj następujące kroki:

1. Najpierw wywołujemy ładowanie wymiarów, a po pomyślnym uruchomieniu ładowania wymiarów rozpoczyna się ładowanie faktów:

BEGIN;
----Insert Dim Loads
call SalesMart.DimVenueLoad(); ----Insert Fact Loads. They will only run if the DimLoad is successful
call SalesMart.FactSaleTransactionsLoad(getdate());
END;

Jeśli ładowanie wymiarów nie powiedzie się, ładowanie faktów nie zostanie uruchomione. Zapewnia to spójność danych, ponieważ nie chcemy ładować tabeli faktów z nieaktualnymi wymiarami.

2. Aby zaplanować ładowanie, wybierz Plan w edytorze zapytań V2.

3. Zaplanowaliśmy uruchamianie zapytania codziennie o 5:00.
4. Opcjonalnie możesz dodać powiadomienia o błędach, włączając Usługa prostego powiadomienia Amazon (Amazon SNS) powiadomienia.

Raportuj i analizuj dane w Amazon Quicksight

QuickSight to usługa analizy biznesowej, która ułatwia dostarczanie szczegółowych informacji. Jako w pełni zarządzana usługa, QuickSight umożliwia łatwe tworzenie i publikowanie interaktywnych pulpitów nawigacyjnych, do których można uzyskać dostęp z dowolnego urządzenia i osadzać je w aplikacjach, portalach i witrynach internetowych.

Wykorzystujemy naszą hurtownię danych do wizualnej prezentacji faktów w postaci dashboardu. Aby rozpocząć i skonfigurować QuickSight, patrz Tworzenie zestawu danych przy użyciu bazy danych, która nie jest wykrywana automatycznie.

Po utworzeniu źródła danych w QuickSight łączymy modelowane dane (data mart) w oparciu o nasz klucz zastępczy skey. Używamy tego zestawu danych do wizualizacji składnicy danych.

Nasz końcowy pulpit nawigacyjny będzie zawierał spostrzeżenia z hurtowni danych i odpowiadał na krytyczne pytania biznesowe, takie jak łączna prowizja na miejsce i daty z najwyższą sprzedażą. Poniższy zrzut ekranu przedstawia produkt końcowy składnicy danych.

Sprzątać

Aby uniknąć naliczania przyszłych opłat, usuń wszystkie zasoby utworzone w ramach tego wpisu.

Wnioski

Z powodzeniem wdrożyliśmy hurtownię danych za pomocą naszego DimVenue, DimCalendar, FactSaleTransactions stoły. Nasz magazyn nie jest kompletny; ponieważ możemy rozszerzyć hurtownię danych o więcej faktów i wdrożyć więcej hurtowni danych, a wraz z upływem czasu rosną procesy biznesowe i wymagania, tak samo będzie rosła hurtownia danych. W tym poście przedstawiliśmy kompleksowe spojrzenie na zrozumienie i wdrożenie modelowania wymiarowego w Amazon Redshift.

Zacznij od swojego Amazonka Przesunięcie ku czerwieni model wymiarowy dzisiaj.

O autorach

Bernarda Verstera jest doświadczonym inżynierem chmury z wieloletnim doświadczeniem w tworzeniu skalowalnych i wydajnych modeli danych, definiowaniu strategii integracji danych oraz zapewnianiu nadzoru i bezpieczeństwa danych. Pasjonuje go wykorzystywanie danych do uzyskiwania wglądu, przy jednoczesnym dostosowaniu do wymagań i celów biznesowych.

Abhiszek Pan jest specjalistą WWSO SA-Analytics pracującym z klientami AWS India z sektora publicznego. Współpracuje z klientami w celu zdefiniowania strategii opartej na danych, prowadzenia szczegółowych sesji dotyczących analitycznych przypadków użycia oraz projektowania skalowalnych i wydajnych aplikacji analitycznych. Ma 12 lat doświadczenia i jest pasjonatem baz danych, analityki i AI/ML. Jest zapalonym podróżnikiem i próbuje uchwycić świat przez obiektyw aparatu.

• Dystrybucja treści i PR oparta na SEO. Uzyskaj wzmocnienie już dziś.
• PlatoData.Network Pionowe generatywne AI. Wzmocnij się. Dostęp tutaj.
• PlatoAiStream. Inteligencja Web3. Wiedza wzmocniona. Dostęp tutaj.
• PlatonESG. Motoryzacja / pojazdy elektryczne, Węgiel Czysta technologia, Energia, Środowisko, Słoneczny, Gospodarowanie odpadami. Dostęp tutaj.
• Przesunięcia bloków. Modernizacja własności offsetu środowiskowego. Dostęp tutaj.
• Źródło: https://aws.amazon.com/blogs/big-data/dimensional-modeling-in-amazon-redshift/