Dimenzionalno modeliranje v Amazon Redshift | Spletne storitve Amazon

Amazon RedShift je popolnoma upravljano skladišče podatkov v oblaku velikosti petabajtov, ki ga uporablja na desettisoče strank za obdelavo eksabajtov podatkov vsak dan za oskrbo s svojo analitično delovno obremenitvijo. Svoje podatke lahko strukturirate, merite poslovne procese in hitro pridobite dragocene vpoglede z uporabo dimenzijskega modela. Amazon Redshift ponuja vgrajene funkcije za pospešitev procesa modeliranja, orkestriranja in poročanja iz dimenzijskega modela.

V tej objavi razpravljamo o tem, kako implementirati dimenzijski model, natančneje o Metodologija Kimball. Razpravljamo o izvajanju razsežnosti in dejstev znotraj Amazon Redshift. Pokažemo, kako izvesti ekstrahiranje, preoblikovanje in nalaganje (ELT), proces integracije, ki je osredotočen na pridobivanje neobdelanih podatkov iz podatkovnega jezera v uprizoritveni sloj za izvedbo modeliranja. Na splošno vam bo objava dala jasno razumevanje uporabe dimenzijskega modeliranja v Amazon Redshift.

Pregled rešitev

Naslednji diagram prikazuje arhitekturo rešitev.

V naslednjih razdelkih najprej razpravljamo in prikazujemo ključne vidike dimenzijskega modela. Po tem ustvarimo podatkovno trgovino z uporabo Amazon Redshift z dimenzionalnim podatkovnim modelom, vključno s tabelami razsežnosti in dejstev. Podatki se naložijo in razporedijo z uporabo COPY se podatki v dimenzijah naložijo z ukazom MERGE izjava, dejstva pa bodo združena z dimenzijami, iz katerih izhajajo vpogledi. Nalaganje dimenzij in dejstev načrtujemo s pomočjo Amazon Redshift Query Editor V2. Nazadnje uporabljamo Amazon QuickSight pridobiti vpogled v modelirane podatke v obliki nadzorne plošče QuickSight.

Za to rešitev uporabljamo vzorčni nabor podatkov (normaliziran), ki ga zagotavlja Amazon Redshift za prodajo vstopnic za dogodke. Za to objavo smo zaradi enostavnosti in predstavitve zožili nabor podatkov. Naslednje tabele prikazujejo primere podatkov za prodajo vstopnic in prizorišča.

Glede na Metodologija dimenzijskega modeliranja Kimball, obstajajo štirje ključni koraki pri oblikovanju dimenzijskega modela:

1. Identificirajte poslovni proces.
2. Navedite zrno svojih podatkov.
3. Identificirajte in implementirajte dimenzije.
4. Ugotovite in implementirajte dejstva.

Dodatno dodajamo še peti korak za demonstracijo, to je poročanje in analiza poslovnih dogodkov.

Predpogoji

Za ta korak morate imeti naslednje predpogoje:

Identificirajte poslovni proces

Preprosto povedano, prepoznavanje poslovnega procesa je prepoznavanje merljivega dogodka, ki ustvarja podatke znotraj organizacije. Običajno imajo podjetja nekakšen operativni izvorni sistem, ki generira njihove podatke v neobdelani obliki. To je dobro izhodišče za prepoznavanje različnih virov za poslovni proces.

Poslovni proces se nato ohrani kot a podatkovni mart v obliki dimenzij in dejstev. Če pogledamo naš vzorčni nabor podatkov, omenjen prej, lahko jasno vidimo, da je poslovni proces prodaja za določen dogodek.

Pogosta napaka je uporaba oddelkov podjetja kot poslovnega procesa. Podatki (poslovni procesi) morajo biti integrirani po različnih oddelkih, v tem primeru marketing lahko dostopa do podatkov o prodaji. Prepoznavanje pravilnega poslovnega procesa je ključnega pomena – napačna izvedba tega koraka lahko vpliva na celotno podatkovno trgovino (lahko povzroči podvajanje zrna in nepravilne meritve v končnih poročilih).

Navedite zrno svojih podatkov

Deklaracija zrna je dejanje enolične identifikacije zapisa v vašem viru podatkov. Zrnatost je uporabljena v tabeli dejstev za natančno merjenje podatkov in omogočanje nadaljnjega združevanja. V našem primeru je to lahko vrstična postavka v prodajnem poslovnem procesu.

V našem primeru uporabe je mogoče prodajo enolično identificirati tako, da pogledamo čas transakcije, ko je bila prodaja izvedena; to bo najbolj atomska raven.

Identificirajte in implementirajte dimenzije

Vaša razsežna tabela opisuje vašo tabelo dejstev in njene atribute. Ko identificirate opisni kontekst vašega poslovnega procesa, shranite besedilo v ločeno tabelo, pri čemer upoštevate zrnatost tabele dejstev. Ko združujete tabelo dimenzij s tabelo dejstev, mora biti s tabelo dejstev povezana samo ena vrstica. V našem primeru uporabljamo naslednjo tabelo, ki jo ločimo v tabelo dimenzij; ta polja opisujejo dejstva, ki jih bomo merili.

Pri načrtovanju strukture dimenzijskega modela (sheme) lahko ustvarite a zvezda or snežinka shema. Struktura mora biti tesno usklajena s poslovnim procesom; zato je zvezdasta shema najbolj primerna za naš primer. Naslednja slika prikazuje naš diagram odnosov entitet (ERD).

V naslednjih razdelkih podrobno opisujemo korake za implementacijo dimenzij.

Uprizorite izvorne podatke

Preden lahko ustvarimo in naložimo tabelo dimenzij, potrebujemo izvorne podatke. Zato izvorne podatke postavimo v uprizoritveno ali začasno tabelo. To se pogosto imenuje uprizoritveni sloj, ki je neobdelana kopija izvornih podatkov. Za to v Amazon Redshift uporabimo ukaz COPY za nalaganje podatkov iz javnega vedra dimenzionalnega-modeliranja-in-amazon-redshift S3, ki se nahaja na us-east-1 Regija. Upoštevajte, da ukaz COPY uporablja AWS upravljanje identitete in dostopa (IAM) vloga z dostop do Amazon S3. Vloga mora biti povezana z grozdom. Izvedite naslednje korake za uprizoritev izvornih podatkov:

1. Ustvarite venue izvorna tabela:

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

2. Naložite podatke o prizorišču:

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. Ustvarite sales izvorna tabela:

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. Naloži podatke o izvoru prodaje:

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. Ustvarite calendar miza:

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. Naloži podatke koledarja:

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

Ustvarite tabelo dimenzij

Oblikovanje tabele dimenzij je lahko odvisno od vaših poslovnih zahtev – ali morate na primer slediti spremembam podatkov skozi čas? obstajajo sedem različnih vrst dimenzij. Za naš primer uporabljamo Tip 1 ker nam ni treba slediti zgodovinskim spremembam. Za več o tipu 2 glejte Poenostavite nalaganje podatkov v počasi spreminjajoče se dimenzije vrste 2 v Amazon Redshift. Tabela dimenzij bo denormalizirana s primarnim ključem, nadomestnim ključem in nekaj dodanimi polji za označevanje sprememb v tabeli. Oglejte si naslednjo kodo:

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;

Nekaj ​​opomb o ustvarjanju tabele dimenzij:

• Imena polj se spremenijo v poslovno prijazna imena
• Naš primarni ključ je VenueID, ki ga uporabljamo za enolično identifikacijo kraja, kjer je potekala prodaja
• Dodani bosta dve dodatni vrstici, ki označujeta, kdaj je bil zapis vstavljen in posodobljen (za sledenje spremembam)
• Uporabljamo an AUTO distribucijski slog da Amazonu Redshift podeli odgovornost za izbiro in prilagajanje sloga distribucije

Drug pomemben dejavnik, ki ga je treba upoštevati pri dimenzijskem modeliranju, je uporaba nadomestni ključi. Nadomestni ključi so umetni ključi, ki se uporabljajo pri dimenzijskem modeliranju za enolično identifikacijo vsakega zapisa v dimenzijski tabeli. Običajno so ustvarjeni kot zaporedno celo število in v poslovni domeni nimajo nobenega pomena. Ponujajo številne prednosti, na primer zagotavljanje edinstvenosti in izboljšanje zmogljivosti pri združevanjih, ker so običajno manjši od naravnih ključev in se kot nadomestni ključi sčasoma ne spreminjajo. To nam omogoča, da smo dosledni in lažje združujemo dejstva in dimenzije.

V Amazon Redshift se nadomestni ključi običajno ustvarijo s ključno besedo IDENTITY. Na primer, prejšnji stavek CREATE ustvari tabelo dimenzij z a VenueSkey nadomestni ključ. The VenueSkey se samodejno zapolni z edinstvenimi vrednostmi, ko se v tabelo dodajo nove vrstice. Ta stolpec lahko nato uporabite za pridružitev tabele prizorišča v FactSaleTransactions miza.

Nekaj ​​nasvetov za oblikovanje nadomestnih ključev:

• Za nadomestni ključ uporabite majhen podatkovni tip s fiksno širino. To bo izboljšalo zmogljivost in zmanjšalo prostor za shranjevanje.
• Uporabite ključno besedo IDENTITY ali ustvarite nadomestni ključ z uporabo zaporedne vrednosti ali vrednosti GUID. To bo zagotovilo, da je nadomestni ključ edinstven in ga ni mogoče spremeniti.

Naloži tabelo dim z uporabo MERGE

Obstaja veliko načinov za nalaganje dimne mize. Upoštevati je treba nekatere dejavnike – na primer zmogljivost, količino podatkov in morda čas nalaganja SLA. z MERGE stavek izvedemo upsert, ne da bi morali podati več ukazov za vstavljanje in posodabljanje. Lahko nastavite MERGE izjava v a shranjen postopek za zapolnitev podatkov. Nato načrtujete programsko izvajanje shranjene procedure prek urejevalnika poizvedb, kar bomo prikazali kasneje v objavi. Naslednja koda ustvari shranjeno proceduro, imenovano 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;

Nekaj ​​opomb o nalaganju dimenzij:

• Ko je zapis vstavljen prvič, bosta zapolnjena vstavljeni datum in posodobljeni datum. Ko se katera koli vrednost spremeni, se podatki posodobijo in posodobljeni datum odraža datum, ko je bil spremenjen. Vstavljeni datum ostane.
• Ker bodo podatke uporabljali poslovni uporabniki, moramo vrednosti NULL, če obstajajo, nadomestiti z bolj poslovno primernimi vrednostmi.

Ugotovite in implementirajte dejstva

Zdaj, ko smo svoje žito razglasili za dogodek prodaje, ki se je zgodila ob določenem času, bo naša tabela dejstev shranila številčna dejstva za naš poslovni proces.

Ugotovili smo naslednja številčna dejstva za merjenje:

• Količina prodanih vstopnic na prodajo
• Provizija za prodajo

Izvajanje dejstva

obstajajo tri vrste tabel dejstev (tabela dejstev o transakcijah, periodična tabela dejstev posnetkov in kopična tabela dejstev posnetkov). Vsak služi drugačnemu pogledu na poslovni proces. Za naš primer uporabljamo tabelo dejstev o transakcijah. Izvedite naslednje korake:

1. Ustvarite tabelo dejstev

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;

Doda se vstavljeni datum s privzeto vrednostjo, ki označuje, ali in kdaj je bil zapis naložen. To lahko uporabite pri ponovnem nalaganju tabele dejstev, da odstranite že naložene podatke in se izognete dvojnikom.

Nalaganje tabele dejstev je sestavljeno iz preprostega vstavitvenega stavka, ki združuje vaše povezane dimenzije. Pridružujemo se iz DimVenue nastala tabela, ki opisuje naša dejstva. To je najboljša praksa, vendar izbirno koledarski datum dimenzije, ki končnemu uporabniku omogočajo krmarjenje po tabeli dejstev. Podatki se lahko naložijo ob novi prodaji ali dnevno; tu pride prav vstavljeni datum ali datum nalaganja.

Tabelo dejstev naložimo s pomočjo shranjene procedure in uporabimo datumski parameter.

2. Ustvarite shranjeno proceduro z naslednjo kodo. Da bi ohranili isto celovitost podatkov, kot smo jo uporabili pri nalaganju dimenzije, nadomestimo vrednosti NULL, če obstajajo, z bolj poslovno primernimi vrednostmi:

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. Naložite podatke tako, da pokličete proceduro z naslednjim ukazom:

call SalesMart.FactSaleTransactionsLoad(getdate())

Načrtujte nalaganje podatkov

Zdaj lahko avtomatiziramo postopek modeliranja z razporejanjem shranjenih procedur v Amazon Redshift Query Editor V2. Izvedite naslednje korake:

1. Najprej pokličemo nalaganje dimenzij in po uspešnem izvajanju nalaganja dimenzij se začne nalaganje dejstev:

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

Če nalaganje dimenzij ne uspe, se nalaganje dejstev ne bo izvajalo. To zagotavlja doslednost podatkov, ker ne želimo naložiti tabele dejstev z zastarelimi dimenzijami.

2. Če želite načrtovati obremenitev, izberite Urnik v urejevalniku poizvedb V2.

3. Poizvedbo načrtujemo vsak dan ob 5 zjutraj.
4. Po želji lahko dodate obvestila o napakah, tako da omogočite Amazon Simple notification Service (Amazon SNS) obvestila.

Poročanje in analiza podatkov v Amazon Quicksight

QuickSight je storitev poslovnega obveščanja, ki omogoča preprosto zagotavljanje vpogledov. Kot popolnoma upravljana storitev vam QuickSight omogoča preprosto ustvarjanje in objavo interaktivnih nadzornih plošč, do katerih lahko nato dostopate iz katere koli naprave in jih vdelate v svoje aplikacije, portale in spletna mesta.

Svojo podatkovno borzo uporabljamo za vizualno predstavitev dejstev v obliki nadzorne plošče. Če želite začeti in nastaviti QuickSight, glejte Ustvarjanje nabora podatkov z zbirko podatkov, ki ni samodejno odkrita.

Ko ustvarite vir podatkov v QuickSightu, združimo modelirane podatke (data mart) na podlagi našega nadomestnega ključa skey. Ta nabor podatkov uporabljamo za vizualizacijo podatkovne tržnice.

Naša končna nadzorna plošča bo vsebovala vpoglede v podatkovno trgovino in odgovarjala na kritična poslovna vprašanja, kot so skupna provizija na prizorišče in datumi z največjo prodajo. Naslednji posnetek zaslona prikazuje končni izdelek podatkovne tržnice.

Čiščenje

Da se izognete prihodnjim stroškom, izbrišite vse vire, ki ste jih ustvarili v okviru te objave.

zaključek

Zdaj smo uspešno implementirali podatkovni trg z našimi DimVenue, DimCalendarin FactSaleTransactions mize. Naše skladišče ni popolno; ker lahko razširimo podatkovno trgovino z več dejstvi in ​​implementiramo več tržnic, in ko poslovni proces in zahteve sčasoma rastejo, se bo povečalo tudi podatkovno skladišče. V tej objavi smo predstavili celovit pogled na razumevanje in implementacijo dimenzijskega modeliranja v Amazon Redshift.

Začnite s svojim Amazon RedShift dimenzionalni model danes.

O avtorjih

Bernard Verster je izkušen inženir v oblaku z dolgoletnimi izkušnjami pri ustvarjanju razširljivih in učinkovitih podatkovnih modelov, definiranju strategij integracije podatkov ter zagotavljanju upravljanja in varnosti podatkov. Navdušen je nad uporabo podatkov za pridobivanje vpogledov, hkrati pa se usklajuje s poslovnimi zahtevami in cilji.

Abhishek Pan je WWSO Specialist SA-Analytics, ki dela s strankami javnega sektorja AWS India. S strankami sodeluje pri definiranju strategije, ki temelji na podatkih, zagotavlja poglobljene seje o primerih uporabe analitike in oblikuje razširljive in zmogljive analitične aplikacije. Ima 12 let izkušenj in je navdušen nad podatkovnimi bazami, analitiko in AI/ML. Je navdušen popotnik in skuša ujeti svet skozi objektiv fotoaparata.

• Distribucija vsebine in PR s pomočjo SEO. Okrepite se še danes.
• PlatoData.Network Vertical Generative Ai. Opolnomočite se. Dostopite tukaj.
• PlatoAiStream. Web3 Intelligence. Razširjeno znanje. Dostopite tukaj.
• PlatoESG. Avtomobili/EV, Ogljik, CleanTech, Energija, Okolje, sončna energija, Ravnanje z odpadki. Dostopite tukaj.
• BlockOffsets. Posodobitev okoljskega offset lastništva. Dostopite tukaj.
• vir: https://aws.amazon.com/blogs/big-data/dimensional-modeling-in-amazon-redshift/