dimensional data model data warehouse tutorial with examples
Denne opplæringen forklarer fordelene og mytene til dimensjonal datamodell i datavarehus. Lær også om dimensjonstabeller og faktatabeller med eksempler:
Testing av datalager ble forklart i vår forrige opplæring, i dette Data Warehouse Training Series for All .
Enorme data er organisert i Data Warehouse (DW) med dimensjonale datamodelleringsteknikker. Disse dimensjonale datamodelleringsteknikkene gjør jobben til sluttbrukere veldig enkle å spørre om forretningsdataene. Denne veiledningen forklarer alt om dimensjonale datamodeller i DW.
Målgruppe
- Datalager / ETL-utviklere og testere.
- Database fagpersoner med grunnleggende kunnskap om databasekonsepter.
- Databaseadministratorer / big data-eksperter som ønsker å forstå Data warehouse / ETL-konsepter.
- Høgskoleutdannede / Freshers som leter etter jobber med datalager.
Hva du vil lære:
Dimensjonale datamodeller
Dimensjonale datamodeller er datastrukturene som er tilgjengelige for sluttbrukerne i ETL-flyt, for å spørre og analysere dataene. ETL-prosessen ender med å laste inn data i målet Dimensjonale datamodeller. Hver dimensjonale datamodell er bygget med en faktatabell omgitt av flere dimensjonstabeller.
Fremgangsmåte som skal følges når du designer en dimensjonal datamodell:
Fordeler med dimensjonal datamodellering
Nedenfor er de forskjellige fordelene med dimensjonal datamodellering.
- De er sikret for å bruke kontinuerlig skiftende DW-miljøer.
- Enorme data kan enkelt bygges ved hjelp av dimensjonale datamodeller.
- Dataene fra dimensjonale datamodeller er enkle å forstå og analysere.
- De er raskt tilgjengelige for sluttbrukerne for spørsmål med høy ytelse.
- Dimensjonale datamodeller lar oss bore ned (eller) rulle opp dataene hierarkisk.
ER-modellering mot dimensjonal datamodellering
- ER-modellering er egnet for operasjonelle systemer, mens dimensjonsmodellering er egnet for datalageret.
- ER-modellering opprettholder detaljerte aktuelle transaksjonsdata mens dimensjonal modellering opprettholder sammendraget av både nåværende og historiske transaksjonsdata.
- ER-modellering har normalisert data, mens dimensjonsmodellering har de-normalisert data.
- ER-modellering bruker flere sammenføyninger under henting av spørringer, mens dimensjonsmodellering bruker et mindre antall sammenføyninger, og derfor er spørringsytelsen raskere i dimensjonsmodellering.
Dimensjonale datamodelleringsmyter
Nedenfor er noen av de eksisterende dimensjonale datamodelleringsmytene.
- Dimensjonale datamodeller brukes kun til å representere sammendraget av dataene.
- De er avdelingsspesifikke i en organisasjon.
- De støtter ikke skalerbarhet.
- De er designet for å tjene formålet med sluttbrukerrapporter og spørsmål.
- Vi kan ikke integrere dimensjonale datamodeller.
Dimensjonstabeller
Dimensjonstabeller spiller en nøkkelrolle i DW-systemet ved å lagre alle de analyserte beregningsverdiene. Disse verdiene er lagret under lett valgbare dimensjonsattributter (kolonner) i tabellen. Kvaliteten på et DW-system avhenger stort sett av dybden på dimensjonsattributtene.
Derfor bør vi prøve å gi mange attributter sammen med deres respektive verdier i dimensjonstabellene.
La oss utforske strukturen til dimensjonstabeller !!
# 1) Dimensjonstabellnøkkel: Hver dimensjonstabell vil ha en av dimensjonsattributtene som en primærnøkkel for å identifisere hver rad unikt. Derfor kan de forskjellige numeriske verdiene for dette attributtet fungere som primærnøkler.
Hvis attributtverdiene ikke er unike i alle fall, kan du vurdere sekvensielt genererte systemnumre som primærnøklene. Disse kalles også som surrogatnøkler.
Dimensjonale datamodeller må ha referanseintegritetsbegrensning for hver nøkkel mellom dimensjoner og fakta. Således vil faktatabeller ha en fremmed nøkkelreferanse for hver primær / surrogatnøkkel i dimensjonstabellen for å opprettholde referanseintegritet.
Hvis det mislykkes, kan ikke de respektive faktatabeldataene hentes for den dimensjonsnøkkelen.
# 2) Tabellen er bred: Vi kan si at dimensjonstabeller er brede, ettersom vi kan legge til et hvilket som helst antall attributter til en dimensjonstabell når som helst i DW-syklusen. DW-arkitekt vil be ETL-teamet om å legge til respektive nye attributter i skjemaet.
I sanntidsscenarier kan du se dimensjonstabeller med 50 (eller) flere attributter.
# 3) Tekstlige attributter: Dimensjonsattributter kan være av hvilken som helst type som helst tekst (eller) numerisk. Tekstlige attributter vil ha virkelige forretningsord i stedet for koder. Dimensjonstabeller er ikke ment for beregninger, og numeriske verdier brukes sjelden for dimensjonsattributter.
# 4) Attributter er kanskje ikke direkte relatert: Alle attributtene i en dimensjonstabell er kanskje ikke relatert til hverandre.
# 5) Ikke normalisert: Normalisering av en dimensjonstabell bringer flere mellomtabeller inn i bildet som ikke er effektive. Dermed er dimensjonstabeller ikke normalisert.
Dimensjonale attributter kan fungere som kilde for begrensninger i spørsmål, og kan også vises som etiketter i rapportene. Spørringene vil fungere effektivt hvis du velger et attributt direkte fra dimensjonstabellen og refererer direkte til den respektive faktatabellen uten å berøre noen andre mellomtabeller.
# 6) Boring og rulling: Dimensjonsattributter har muligheten til å bore ned (eller) rulle opp dataene når det er nødvendig.
# 7) Flere hierarkier: En enkeltdimensjonstabell med flere hierarkier er veldig vanlig. En dimensjonstabell vil ha et enkelt hierarki hvis bare en bane eksisterer fra bunnnivå til topp. Tilsvarende vil det ha flere hierarkier hvis det er flere stier til stede for å nå fra bunnen til toppen.
# 8) Få poster: Dimensjonstabeller vil ha færre antall poster (i hundrevis) enn faktatabellene (i millioner). Selv om de er mindre enn fakta, gir de alle innspillene til faktatabellene.
Her er et eksempel på en kundedimensjonstabell:
Ved å forstå de ovennevnte begrepene kan du bestemme om et datafelt kan fungere som et dimensjonsattributt (eller) ikke mens du trekker ut dataene fra selve kilden.
Den grunnleggende lasteplanen for en dimensjon
Dimensjoner kan opprettes på to måter, dvs. ved å trekke ut dimensjonsdataene fra eksterne kildesystemer (eller) ETL-systemet kan bygge dimensjonene fra iscenesettelse uten å involvere eksterne kilder. Imidlertid er et ETL-system uten ekstern behandling mer egnet for å lage dimensjonstabeller.
Nedenfor er trinnene involvert i denne prosessen:
hvordan åpne bin filer på android
- Datarengjøring: Data blir renset, validert og forretningsregler brukes før de lastes inn i dimensjonstabellen for å opprettholde konsistens.
- Data i samsvar: Data fra andre deler av datalageret bør samles riktig som en enkelt verdi, med hensyn til hvert felt i dimensjonstabellen.
- Del de samme domenene: Når dataene er bekreftet, lagres de igjen i iscenesettelsestabeller.
- Datalevering: Til slutt lastes alle dimensjonale attributtverdier med tilordnede primær- / surrogatnøkler.
Typer dimensjoner
De forskjellige dimensjonstypene er oppført nedenfor for din referanse.
La oss begynne!!
# 1) Små dimensjoner
Små dimensjoner i datalager fungerer som oppslagstabeller med mindre antall rader og kolonner. Data i små dimensjoner kan enkelt lastes inn fra regneark. Om nødvendig kan små dimensjoner kombineres som en superdimensjon.
# 2) Konformert dimensjon
En tilpasset dimensjon er en dimensjon som kan refereres på samme måte med hver faktatabell den er relatert til.
Datodimensjon er det beste eksemplet på en tilpasset dimensjon, da attributtene til datodimensjonen som år, måned, uke, dager osv. Kommuniserer de samme dataene på samme måte på tvers av et hvilket som helst antall fakta.
Et eksempel på en tilpasset dimensjon.
# 3) Søppeldimensjon
Få attributter i en faktatabell som flagg og indikatorer kan flyttes til en egen søppeldimensjonstabell. Disse attributtene tilhører heller ikke noen andre eksisterende dimensjonstabeller. Generelt er verdiene til disse attributtene ganske enkelt et “ja / nei” (eller) “sant / usant”.
Å lage en ny dimensjon for hvert enkelt flaggattributt gjør det komplisert ved å opprette flere antall utenlandske nøkler til faktatabellen. På samme tid øker det å holde alle disse flaggene og indikatorinformasjonen i faktatabeller også datamengden som er lagret i fakta, som derved forringer ytelsen.
Derfor er den beste løsningen for dette å lage en enkelt søppeldimensjon, da en søppeldimensjon er i stand til å inneholde et hvilket som helst antall 'ja / nei' eller 'sant / usant' indikatorer. Imidlertid lagrer søppeldimensjoner beskrivende verdier for disse indikatorene (ja / nei (eller) sant / usant) som aktiv og ventende osv.
Basert på kompleksiteten til en faktatabell og dens indikatorer, kan en faktatabell ha en eller flere søppeldimensjoner.
Et eksempel på søppeldimensjon.
# 4) Rollespilldimensjon
En enkelt dimensjon som kan henvises til flere formål i en faktatabell er kjent som rollespilldimensjon.
Det beste eksemplet for en rollespilldimensjon er igjen en datodimensjonstabell, siden samme datoattributt i en dimensjon kan brukes til forskjellige formål i et faktum som dato for ordre, leveringsdato, transaksjonsdato, dato for kansellering etc.
Om nødvendig kan du opprette fire forskjellige visninger på datodimensjonstabellen med hensyn til fire forskjellige datoattributter til en faktatabell.
Et eksempel på en rollespilldimensjon.
# 5) Degenerate Dimensions
Det kan være få attributter som verken kan være dimensjoner (beregninger) eller fakta (mål), men de trenger for analyse. Alle slike attributter kan flyttes til degenererte dimensjoner.
For eksempel, du kan betrakte ordrenummer, fakturanummer osv. som degenererte dimensjonsattributter.
Et eksempel på en degenerert dimensjon.
# 6) Endrer sakte dimensjoner
En sakte skiftende dimensjon er et slag der data kan endres sakte når som helst i stedet for med jevne mellomrom. Modifiserte data i dimensjonstabeller kan håndteres på forskjellige måter som forklart nedenfor.
Du kan velge SCD-typen for å svare på en endring individuelt for hvert attributt i en dimensjonstabell.
(i) Type 1 SCD
- I type 1 når det er en endring i verdiene til dimensjonsattributtene, blir de eksisterende verdiene overskrevet med de nylig modifiserte verdiene, noe som bare er en oppdatering.
- Gamle data opprettholdes ikke for historisk referanse.
- Tidligere rapporter kan ikke regenereres på grunn av at det ikke eksisterer gamle data.
- Lett å vedlikeholde.
- Virkningen på faktatabeller er mer.
Eksempel på type 1 SCD:
(Ii) Type 2 SCD
- I type 2, når det er en endring i verdiene til dimensjonsattributtene, vil en ny rad bli satt inn med de modifiserte verdiene uten å endre de gamle raddataene.
- Hvis det finnes noen fremmednøkkelhenvisning som eksisterer til den gamle posten i noen av faktatabellene, blir den gamle surrogatnøkkelen oppdatert overalt med en ny surrogatnøkkel automatisk.
- Virkningen på faktabordendringene er veldig mindre med trinnet ovenfor.
- Gamle data blir ikke vurdert hvor som helst etter endringene.
- I type 2 kan vi spore alle endringene som skjer med dimensjonsattributtene.
- Det er ingen grense for lagring av historiske data.
- I type 2 er det å legge til få attributter til hver rad som endret dato, effektiv dato, sluttdato, årsaken til endringen og det nåværende flagget er valgfritt. Men dette er viktig hvis virksomheten vil vite antall endringer som er gjort i løpet av en viss tidsperiode.
Eksempel på type 2 SCD:
(Iii) Type 3 SCD
- I type 3 når det er en endring i verdiene til dimensjonsattributtene, oppdateres nye verdier, men de gamle verdiene forblir fortsatt gyldige som det andre alternativet.
- I stedet for å legge til en ny rad for hver endring, vil en ny kolonne legges til hvis den ikke eksisterte tidligere.
- Gamle verdier plasseres i attributtene som er lagt til ovenfor, og dataene til det primære attributtet blir overskrevet med den endrede verdien som i type 1.
- Det er en grense for lagring av historiske data.
- Virkningen på faktatabeller er mer.
Eksempel på type 3 SCD:
(iv) Type 4 SCD
- I type 4 lagres de aktuelle dataene i en tabell.
- Alle historiske data er opprettholdt i en annen tabell.
Eksempel på type 4 SCD:
(v) Type 6 SCD
- Et dimensjonstabell kan også ha en kombinasjon av alle tre SCD-typene 1, 2 og 3, som er kjent som en Type 6 (eller) Hybrid som endrer sakte dimensjon.
Faktatabeller
Faktatabeller lagrer et sett med kvantitativt målte verdier som brukes til beregninger. Faktatabellens verdier vises i forretningsrapportene. I motsetning til dimensjonstabellens tekstlige datatype er faktatabellens datatype betydelig numerisk.
Faktatabeller er dype, mens dimensjonstabeller er brede, ettersom faktatabeller vil ha et høyere antall rader og et mindre antall kolonner. En primærnøkkel definert i faktatabellen er primært å identifisere hver rad separat. Primærnøkkelen kalles også en sammensatt nøkkel i faktatabellen.
Hvis den sammensatte nøkkelen mangler i en faktatabell, og hvis to poster har de samme dataene, er det veldig vanskelig å skille mellom dataene og henvise dataene i dimensjonstabeller.
Derfor, hvis en riktig unik nøkkel eksisterer som den sammensatte nøkkelen, er det bra å generere et sekvensnummer for hver faktatabellpost. Et annet alternativ er å danne en sammenkoblet primærnøkkel. Dette genereres ved å sammenkoble alle de henviste primærnøklene til dimensjonstabellene radvis.
En enkelt faktatabell kan være omgitt av flere dimensjonstabeller. Ved hjelp av de utenlandske nøklene som eksisterer i faktatabeller, kan den respektive konteksten (detaljerte data) for de målte verdiene refereres til i dimensjonstabellene. Ved hjelp av spørsmål vil brukerne utføre drill-down og roll-up effektivt.
Det laveste nivået av data som kan lagres i en faktatabell er kjent som Granularity. Antall dimensjonstabeller assosiert med en faktatabell er omvendt proporsjonal med granulariteten til faktatabelldataene. dvs. den minste måleverdien trenger flere dimensjonstabeller for å bli henvist.
I en dimensjonsmodell opprettholder faktatabellene mange-til-mange-forhold til dimensjonstabeller.
Et eksempel på en salgsfaktatabell:
Lastplan for faktatabeller
Du kan laste inn faktatabeldata effektivt ved å vurdere følgende pekere:
# 1) Slipp og gjenopprett indekser
Indekser er faktisk tabeller som er gode ytelsesforsterkere når du spør etter dataene, men de ødelegger ytelsen mens du laster inn dataene. Derfor, før du legger inn store data i faktatabeller, slipper du primært alle indeksene på den tabellen, laster inn dataene og gjenoppretter indeksene.
# 2) Separate innsatser fra oppdateringer
Ikke slå sammen innsett og oppdater poster mens de lastes inn i en faktatabell. Hvis antall oppdateringer er mindre, kan du behandle innsatser og oppdateringer hver for seg. Hvis antall oppdateringer er mer, er det tilrådelig å trunke og laste inn faktatabellen for raske resultater.
# 3) Partisjonering
Gjør partisjonering fysisk på et faktatabell i minitabeller for bedre spørringsytelse på data fra massetabeller. Bortsett fra DBA-er og ETL-teamet, vil ingen være klar over partisjonene på fakta.
Som en eksempel , kan du dele en tabell månedsvis, kvartvis, årsmessig, etc. Mens du spør, blir bare de partisjonerte dataene vurdert i stedet for å skanne hele tabellen.
# 4) Last inn parallelt
hva er de beste e-postleverandørene
Vi har nå fått en idé om partisjoner på faktabord. Partisjoner på fakta er også fordelaktige når du laster enorme data inn i fakta. For å gjøre dette, del først dataene logisk i forskjellige datafiler og kjør ETL-jobbene for å laste alle disse logiske delene av data parallelt.
# 5) Bulk Load Utility
I motsetning til andre RDBMS-systemer trenger ETL-systemet ikke å opprettholde tilbakeføringslogger eksplisitt for mellomtransaksjonsfeil. Her skjer 'bulkbelastninger' til fakta i stedet for 'SQL-innlegg' for å laste enorme data. Hvis en enkelt belastning mislykkes, kan hele dataene enkelt lastes inn på nytt (eller), de kan fortsette fra der de blir igjen med masselasten.
# 6) Slette en faktapost
Slette en faktatabelloppføring skjer bare hvis virksomheten ønsker det eksplisitt. Hvis det er noen faktatabeldata som ikke lenger eksisterer i kildesystemene, kan de respektive dataene slettes enten fysisk (eller) logisk.
- Fysisk sletting: Uønskede poster fjernes permanent fra faktatabellen.
- Logisk sletting: En ny kolonne vil bli lagt til i faktatabellen, for eksempel ‘slettet’ av bit (eller) boolsk type. Dette fungerer som et flagg for å representere de slettede postene. Du må forsikre deg om at du ikke velger de slettede postene mens du spør om faktatabelldataene.
# 7) Sekvens for oppdateringer og slettinger i en faktatabell
Når det er data som skal oppdateres, bør dimensjonstabellene oppdateres først etterfulgt av oppdatering av surrogatnøklene i oppslagstabellen om nødvendig, og deretter oppdateres de respektive faktatabellene. Sletting skjer i motsatt retning fordi det er enkelt å slette de koblede uønskede dataene fra dimensjonstabellene ved å slette alle uønskede data fra faktatabeller.
Vi bør følge sekvensen ovenfor i begge tilfeller fordi dimensjonstabeller og faktatabeller opprettholder referanseintegritet hele tiden.
Typer fakta
Basert på oppførselen til faktatabeldata blir de kategorisert som faktatabeller for transaksjoner, faktatabeller for øyeblikksbilde og faktatabeller for øyeblikksbilde. Alle disse tre typene følger forskjellige funksjoner med forskjellige datalastingsstrategier.
# 1) Transaksjonstabeller
Som navnet indikerer, lagrer faktatabeller på transaksjonsnivå data for hver hendelse som skjer. Slike typer data er enkle å analysere på selve faktabordnivået. Men for videre analyse kan du også referere til de tilknyttede dimensjonene.
For eksempel, hvert salg (eller) kjøp som skjer fra et markedsføringsnettsted, skal lastes inn i en faktatabell for transaksjoner.
Et eksempel på en transaksjonsfaktatabell er vist nedenfor.
# 2) Periodiske øyeblikksbilde faktatabeller
Som navnet indikerer, lagres data i periodisk øyeblikksbilde faktatabell i form av øyeblikksbilder (bilder) med jevne mellomrom som for hver dag, uke, måned, kvartal osv., Avhengig av forretningsbehovet.
Så det er klart at dette er en aggregering av data hele tiden. Derfor er øyeblikksbildefakta mer komplisert sammenlignet med faktatabeller for transaksjoner. For eksempel, data om resultatinntektsrapporter kan lagres i øyeblikksbildet faktatabeller for enkel referanse.
Et eksempel på en periodisk faktatabell for øyeblikksbilde er vist nedenfor.
# 3) Akkumulerende faktatabeller for øyeblikksbilde
Akkumulerende øyeblikksbilde faktatabeller lar deg lagre data i tabeller for hele produktets levetid. Dette fungerer som en kombinasjon av de to ovennevnte typene der data kan settes inn av en hvilken som helst hendelse når som helst som et øyeblikksbilde.
I denne typen blir ytterligere datakolonner og data for hver rad oppdatert med hver milepæl for det produktet.
Et eksempel på en akkumulerende faktatabell for øyeblikksbilde.
I tillegg til de tre ovennevnte typene, er det noen andre typer faktatabeller:
# 4) Faktale faktatabeller: Et faktum er en samling tiltak, mens faktum mindre bare fanger hendelser (eller) forhold som ikke inneholder noen tiltak. En faktaløs faktatabell brukes hovedsakelig til å spore et system. Dataene i disse tabellene kan analyseres og brukes til rapportering.
For eksempel, du kan se etter detaljer om en ansatt som har tatt permisjon og hvilken type permisjon det har gått i et år, etc. Inkludert alle disse ikke-klare faktaopplysningene i et faktum, vil tabellen definitivt øke størrelsen på fakta.
Et eksempel på en faktaløs faktatabell er vist nedenfor.
# 5) Tilpassede faktatabeller: Et tilpasset faktum er et faktum som kan henvises på samme måte med alle datamateriale det er relatert til.
Spesifikasjoner for en faktatabell
Nedenfor er spesifikasjonene til en faktatabell.
- Fakta navn: Dette er en streng som kort beskriver funksjonstabellen til faktatabellen.
- Forretningsprosess: Samtaler om virksomheten må oppfylles av faktatabellen.
- Spørsmål: Nevner en liste over forretningsspørsmål som vil bli besvart av faktatabellen.
- Korn: Angir det laveste detaljnivået knyttet til faktatabelldataene.
- Dimensjoner: Liste opp alle dimensjonstabellene som er knyttet til den faktatabellen.
- Målinger: De beregnede verdiene lagret i faktatabellen.
- Lastfrekvens Representerer tidsintervallene for å laste inn data i faktatabellen.
- Innledende rader: Se de første dataene som er fylt ut i faktatabellen for første gang.
Eksempel på dimensjonal datamodellering
Du kan få en ide om hvordan dimensjonstabeller og faktatabeller kan utformes for et system ved å se på nedenstående dimensjonale datamodelleringsdiagram for salg og ordrer.
Konklusjon
Nå burde du ha fått god kunnskap om teknikker for dimensjonal datamodellering, fordelene deres, myter, dimensjonstabeller, faktatabeller, sammen med deres typer og prosesser.
Ta en titt på den kommende veiledningen vår for å vite mer om Data Warehouse Schemas !!
=> Besøk her for å lære datalagring fra riper.
Anbefalt lesing
- Data Warehouse Testing Tutorial med eksempler | ETL Testing Guide
- Data Mining Eksempler: De vanligste applikasjonene av Data Mining 2021
- Python DateTime Tutorial med eksempler
- Grunnleggende om datalagring: En ultimate guide med eksempler
- Volumtestopplæring: Eksempler og volumtestverktøy
- Topp 10 populære datavareverktøy og testteknologier
- Data Mining: Prosess, teknikker og store problemer i dataanalyse
- Hvordan utføre datadrevet testing i SoapUI Pro - SoapUI Tutorial # 14