Dimensionel modellering i Amazon Redshift | Amazon Web Services

Amazon rødforskydning er et fuldt administreret og petabyte-skala cloud data warehouse, der bruges af titusindvis af kunder til at behandle exabytes af data hver dag for at drive deres analytiske arbejdsbyrde. Du kan strukturere dine data, måle forretningsprocesser og hurtigt få værdifuld indsigt ved at bruge en dimensionel model. Amazon Redshift giver indbyggede funktioner til at accelerere processen med modellering, orkestrering og rapportering fra en dimensionel model.

I dette indlæg diskuterer vi, hvordan man implementerer en dimensionel model, specifikt Kimball metode. Vi diskuterer implementeringsdimensioner og fakta inden for Amazon Redshift. Vi viser, hvordan man udfører extract, transform and load (ELT), en integrationsproces fokuseret på at få de rå data fra en datasø ind i et iscenesættelseslag for at udføre modelleringen. Samlet set vil indlægget give dig en klar forståelse af, hvordan du bruger dimensionel modellering i Amazon Redshift.

Løsningsoversigt

Følgende diagram illustrerer løsningsarkitekturen.

I de følgende afsnit diskuterer og demonstrerer vi først de vigtigste aspekter af den dimensionelle model. Derefter opretter vi en datamart ved hjælp af Amazon Redshift med en dimensionel datamodel inklusive dimensions- og faktatabeller. Data indlæses og iscenesættes ved hjælp af COPY kommandoen indlæses dataene i dimensionerne ved hjælp af FUSIONERE udsagn, og fakta vil blive sammenføjet til de dimensioner, hvorfra indsigt er afledt. Vi planlægger indlæsningen af ​​dimensionerne og fakta ved hjælp af Amazon Redshift Query Editor V2. Til sidst bruger vi Amazon QuickSight at få indsigt i de modellerede data i form af et QuickSight dashboard.

Til denne løsning bruger vi et eksempeldatasæt (normaliseret) leveret af Amazon Redshift til billetsalg til begivenheder. Til dette indlæg har vi indsnævret datasættet for enkelheds- og demonstrationsformål. Følgende tabeller viser eksempler på data for billetsalg og spillesteder.

Ifølge Kimball dimensionel modellering metode, er der fire vigtige trin i design af en dimensionel model:

1. Identificer forretningsprocessen.
2. Erklære kernen af ​​dine data.
3. Identificer og implementer dimensionerne.
4. Identificer og implementer fakta.

Derudover tilføjer vi et femte trin til demonstrationsformål, som er at rapportere og analysere forretningsbegivenheder.

Forudsætninger

For denne gennemgang skal du have følgende forudsætninger:

Identificer forretningsprocessen

Kort sagt er identifikation af forretningsprocessen at identificere en målbar hændelse, der genererer data i en organisation. Normalt har virksomheder en form for operationelt kildesystem, der genererer deres data i dets råformat. Dette er et godt udgangspunkt for at identificere forskellige kilder til en forretningsproces.

Forretningsprocessen fortsættes derefter som en datamart i form af dimensioner og fakta. Ser vi på vores eksempeldatasæt nævnt tidligere, kan vi tydeligt se, at forretningsprocessen er salget for en given begivenhed.

En almindelig fejl er at bruge afdelinger i en virksomhed som forretningsproces. Dataene (forretningsprocessen) skal integreres på tværs af forskellige afdelinger, i dette tilfælde kan marketing få adgang til salgsdataene. Det er vigtigt at identificere den korrekte forretningsproces – hvis dette trin bliver forkert, kan det påvirke hele datamarkedet (det kan forårsage, at kornet bliver duplikeret og ukorrekte målinger på de endelige rapporter).

Erklære kernen af ​​dine data

At erklære kornet er handlingen med entydigt at identificere en post i din datakilde. Kornet bruges i faktatabellen til præcist at måle dataene og gøre dig i stand til at rulle yderligere op. I vores eksempel kunne dette være en linjepost i salgsforretningsprocessen.

I vores use case kan et salg entydigt identificeres ved at se på transaktionstidspunktet, hvor salget fandt sted; dette vil være det mest atomare niveau.

Identificer og implementer dimensionerne

Din dimensionstabel beskriver din faktatabel og dens attributter. Når du identificerer den beskrivende kontekst af din forretningsproces, gemmer du teksten i en separat tabel, mens du holder faktatabellen i tankerne. Når du forbinder dimensionstabellen med faktatabellen, bør der kun være en enkelt række tilknyttet faktatabellen. I vores eksempel bruger vi følgende tabel til at blive adskilt i en dimensionstabel; disse felter beskriver de fakta, som vi vil måle.

Når du designer strukturen af ​​den dimensionelle model (skemaet), kan du enten oprette en stjerne or snefnug skema. Strukturen skal være tæt på linje med forretningsprocessen; derfor passer et stjerneskema bedst til vores eksempel. Følgende figur viser vores Entity Relationship Diagram (ERD).

I de følgende afsnit beskriver vi trinene til implementering af dimensionerne.

Iscenesætter kildedataene

Før vi kan oprette og indlæse dimensionstabellen, har vi brug for kildedata. Derfor iscenesætter vi kildedataene til en iscenesættelse eller midlertidig tabel. Dette omtales ofte som iscenesættelseslag, som er den rå kopi af kildedataene. For at gøre dette i Amazon Redshift bruger vi COPY kommando for at indlæse dataene fra dimensional-modeling-in-amazon-redshift offentlige S3-spand placeret på us-east-1 Område. Bemærk, at COPY-kommandoen bruger en AWS identitets- og adgangsstyring (IAM) rolle med adgang til Amazon S3. Rollen skal være knyttet til klyngen. Udfør følgende trin for at iscenesætte kildedataene:

1. Opret venue kildetabel:

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

2. Indlæs spillestedsdata:

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. Opret sales kildetabel:

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. Indlæs salgskildedata:

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. Opret calendar bord:

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. Indlæs kalenderdata:

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

Opret dimensionstabellen

Design af dimensionstabellen kan afhænge af dine forretningskrav – skal du for eksempel spore ændringer af dataene over tid? Der er syv forskellige dimensionstyper. Til vores eksempel bruger vi typen 1 fordi vi ikke behøver at spore historiske ændringer. For mere om type 2, se Forenkle dataindlæsning i Type 2 langsomt skiftende dimensioner i Amazon Redshift. Dimensionstabellen vil blive denormaliseret med en primær nøgle, surrogatnøgle og et par tilføjede felter for at angive ændringer i tabellen. Se følgende kode:

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;

Et par bemærkninger om oprettelse af dimensionstabellen:

• Feltnavnene omdannes til erhvervsvenlige navne
• Vores primære nøgle er VenueID, som vi bruger til entydigt at identificere et sted, hvor salget fandt sted
• To yderligere rækker vil blive tilføjet, som angiver, hvornår en post blev indsat og opdateret (for at spore ændringer)
• Vi bruger en AUTO distributionsstil at give Amazon Redshift ansvaret for at vælge og justere distributionsstilen

En anden vigtig faktor at overveje i dimensionel modellering er brugen af surrogatnøgler. Surrogatnøgler er kunstige nøgler, der bruges i dimensionsmodellering til entydigt at identificere hver post i en dimensionstabel. De genereres typisk som et sekventielt heltal, og de har ingen betydning i forretningsdomænet. De tilbyder flere fordele, såsom at sikre unikhed og forbedre ydeevnen i joinforbindelser, fordi de typisk er mindre end naturlige nøgler, og som surrogatnøgler ændres de ikke over tid. Dette giver os mulighed for at være konsekvente og nemmere forbinde fakta og dimensioner.

I Amazon Redshift oprettes surrogatnøgler typisk ved hjælp af søgeordet IDENTITY. For eksempel opretter den foregående CREATE-sætning en dimensionstabel med en VenueSkey surrogatnøgle. Det VenueSkey kolonne udfyldes automatisk med unikke værdier, når nye rækker tilføjes til tabellen. Denne kolonne kan derefter bruges til at slutte spillestedsbordet til FactSaleTransactions tabel.

Et par tips til at designe surrogatnøgler:

• Brug en lille datatype med fast bredde til surrogatnøglen. Dette vil forbedre ydeevnen og reducere lagerplads.
• Brug nøgleordet IDENTITY, eller generer surrogatnøglen ved hjælp af en sekventiel eller GUID-værdi. Dette vil sikre, at surrogatnøglen er unik og ikke kan ændres.

Indlæs dæmpningsbordet ved hjælp af MERGE

Der er mange måder at indlæse dit dæmpede bord på. Visse faktorer skal tages i betragtning - for eksempel ydeevne, datavolumen og måske SLA-indlæsningstider. Med FUSIONERE sætning, udfører vi en upsert uden at skulle angive flere indsættelses- og opdateringskommandoer. Du kan opsætte FUSIONERE udtalelse i en lagret procedure at udfylde dataene. Du planlægger derefter den lagrede procedure til at køre programmatisk via forespørgselseditoren, som vi demonstrerer senere i indlægget. Følgende kode opretter en lagret procedure kaldet 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;

Et par bemærkninger om dimensionsbelastningen:

• Når en post indsættes for første gang, vil den indsatte dato og den opdaterede dato blive udfyldt. Når nogen værdier ændres, opdateres dataene, og den opdaterede dato afspejler datoen, hvor den blev ændret. Den indsatte dato forbliver.
• Fordi dataene vil blive brugt af forretningsbrugere, er vi nødt til at erstatte NULL-værdier, hvis nogen, med mere forretningsegnede værdier.

Identificer og implementer fakta

Nu hvor vi har erklæret vores korn til at være begivenheden for et salg, der fandt sted på et bestemt tidspunkt, vil vores faktatabel gemme de numeriske fakta for vores forretningsproces.

Vi har identificeret følgende numeriske fakta at måle:

• Antal solgte billetter pr. salg
• Kommission for salget

Implementering af fakta

Der er tre typer faktatabeller (transaktionsfakta-tabel, periodisk snapshot-faktatabel og akkumulerende snapshot-faktatabel). Hver serverer et andet syn på forretningsprocessen. Til vores eksempel bruger vi en transaktionsfakta-tabel. Udfør følgende trin:

1. Lav faktatabellen

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;

En indsat dato med en standardværdi tilføjes, der angiver, om og hvornår en post blev indlæst. Du kan bruge dette, når du genindlæser faktatabellen for at fjerne de allerede indlæste data for at undgå dubletter.

Indlæsning af faktatabellen består af en simpel indsætningserklæring, der forbinder dine tilknyttede dimensioner. Vi tilslutter os fra DimVenue tabel, der blev oprettet, som beskriver vores fakta. Det er bedste praksis, men valgfrit at have kalender dato dimensioner, som giver slutbrugeren mulighed for at navigere i faktatabellen. Data kan enten indlæses, når der er et nyt salg, eller dagligt; det er her, den indsatte dato eller indlæsningsdato er praktisk.

Vi indlæser faktatabellen ved hjælp af en lagret procedure og bruger en datoparameter.

2. Opret den lagrede procedure med følgende kode. For at bevare den samme dataintegritet, som vi anvendte i dimensionsindlæsningen, erstatter vi NULL-værdier, hvis nogen, med mere forretningsegnede værdier:

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. Indlæs dataene ved at kalde proceduren med følgende kommando:

call SalesMart.FactSaleTransactionsLoad(getdate())

Planlæg dataindlæsningen

Vi kan nu automatisere modelleringsprocessen ved at planlægge de lagrede procedurer i Amazon Redshift Query Editor V2. Udfør følgende trin:

1. Vi kalder først dimensionsbelastningen, og efter at dimensionsbelastningen kører med succes, begynder faktabelastningen:

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

Hvis dimensionsbelastningen fejler, vil faktabelastningen ikke køre. Dette sikrer ensartethed i dataene, fordi vi ikke ønsker at indlæse faktatabellen med forældede dimensioner.

2. Vælg for at planlægge belastningen Planlæg i Query Editor V2.

3. Vi planlægger, at forespørgslen skal køre hver dag kl. 5.
4. Du kan eventuelt tilføje fejlmeddelelser ved at aktivere Amazon Simple Notification Service (Amazon SNS) meddelelser.

Rapporter og analyser dataene i Amazon Quicksight

QuickSight er en business intelligence-tjeneste, der gør det nemt at levere indsigt. Som en fuldt administreret tjeneste lader QuickSight dig nemt oprette og udgive interaktive dashboards, som derefter kan tilgås fra enhver enhed og integreres i dine applikationer, portaler og websteder.

Vi bruger vores datamart til visuelt at præsentere fakta i form af et dashboard. For at komme i gang og konfigurere QuickSight, se Oprettelse af et datasæt ved hjælp af en database, der ikke er autoopdaget.

Når du har oprettet din datakilde i QuickSight, samler vi de modellerede data (datamart) baseret på vores surrogatnøgle skey. Vi bruger dette datasæt til at visualisere datamarkedet.

Vores slut-dashboard vil indeholde indsigten fra datamarkedet og besvare kritiske forretningsspørgsmål, såsom samlet kommission pr. spillested og datoer med det højeste salg. Følgende skærmbillede viser det endelige produkt af datamart.

Ryd op

For at undgå fremtidige gebyrer skal du slette alle ressourcer, du har oprettet som en del af dette indlæg.

Konklusion

Vi har nu med succes implementeret en datamart ved hjælp af vores DimVenue, DimCalendarog FactSaleTransactions borde. Vores lager er ikke komplet; da vi kan udvide datamarkedet med flere fakta og implementere flere marts, og efterhånden som forretningsprocessen og kravene vokser over tid, vil datavarehuset også vokse. I dette indlæg gav vi et ende-til-ende syn på forståelse og implementering af dimensionel modellering i Amazon Redshift.

Kom i gang med din Amazon rødforskydning dimensionel model i dag.

Om forfatterne

Bernard Verster er en erfaren cloud-ingeniør med mange års eksponering i at skabe skalerbare og effektive datamodeller, definere dataintegrationsstrategier og sikre datastyring og sikkerhed. Han brænder for at bruge data til at skabe indsigt, samtidig med at han er i overensstemmelse med forretningskrav og mål.

Abhishek Pan er en WWSO Specialist SA-Analytics, der arbejder med AWS Indiens kunder i den offentlige sektor. Han engagerer sig med kunder for at definere datadrevet strategi, give dybe dykkesessioner om analytics use cases og designe skalerbare og effektive analytiske applikationer. Han har 12 års erfaring og brænder for databaser, analyser og AI/ML. Han er en ivrig rejsende og forsøger at fange verden gennem sit kameraobjektiv.

• SEO Powered Content & PR Distribution. Bliv forstærket i dag.
• PlatoData.Network Vertical Generative Ai. Styrk dig selv. Adgang her.
• PlatoAiStream. Web3 intelligens. Viden forstærket. Adgang her.
• PlatoESG. Automotive/elbiler, Kulstof, CleanTech, Energi, Miljø, Solenergi, Affaldshåndtering. Adgang her.
• BlockOffsets. Modernisering af miljømæssig offset-ejerskab. Adgang her.
• Kilde: https://aws.amazon.com/blogs/big-data/dimensional-modeling-in-amazon-redshift/