dimensional data model data warehouse tutorial with examples
Denne vejledning forklarer fordelene og myterne ved dimensionel datamodel i datalager. Lær også om dimensionstabeller og faktaborde med eksempler:
Test af datalager blev forklaret i vores tidligere tutorial, i dette Data Warehouse Training Series for All .
Enorme data er organiseret i Data Warehouse (DW) med dimensionelle datamodelleringsteknikker. Disse dimensionelle datamodelleringsteknikker gør slutbrugernes job meget let at forhøre sig om forretningsdataene. Denne vejledning forklarer alt om dimensionelle datamodeller i DW.
Målgruppe
- Datalager / ETL-udviklere og testere.
- Databaseprofessionelle med grundlæggende viden om databasekoncepter.
- Databaseadministratorer / big data-eksperter, der ønsker at forstå datalager / ETL-koncepter.
- College kandidater / Freshers, der er på udkig efter datalagerjob.
Hvad du vil lære:
Dimensionelle datamodeller
Dimensionelle datamodeller er de datastrukturer, der er tilgængelige for slutbrugerne i ETL-flow, til forespørgsel og analyse af dataene. ETL-processen ender med at indlæse data i måldimensionale datamodeller. Hver dimensionelle datamodel er bygget med en faktatabel omgivet af flere dimensionstabeller.
Trin, der skal følges under design af en dimensionel datamodel:
Fordele ved dimensionel datamodellering
Nedenfor er de forskellige fordele ved dimensioneret datamodellering.
- De er sikret til at bruge de konstant skiftende DW-miljøer.
- Enorme data kan let bygges ved hjælp af dimensionelle datamodeller.
- Dataene fra de dimensionelle datamodeller er lette at forstå og analysere.
- De er hurtigt tilgængelige for slutbrugerne til forespørgsler med høj ydeevne.
- Dimensionelle datamodeller giver os mulighed for at bore (eller) rulle dataene hierarkisk op.
ER-modellering mod dimensionelle datamodellering
- ER-modellering er velegnet til operationelle systemer, mens dimensionel modellering er velegnet til datalageret.
- ER-modellering vedligeholder detaljerede aktuelle transaktionsdata, mens dimensionel modellering opretholder resuméet af både aktuelle og historiske transaktionsdata.
- ER-modellering har normaliserede data, mens dimensionel modellering har de-normaliserede data.
- ER-modellering bruger flere sammenføjninger under hentning af forespørgsler, mens dimensionel modellering bruger et mindre antal sammenføjninger, hvorfor forespørgselsydeevne er hurtigere i dimensionel modellering.
Dimensionelle datamodelleringsmyter
Nedenfor er nogle af de eksisterende dimensionelle datamodelleringsmyter.
- Dimensionelle datamodeller bruges kun til at repræsentere resuméet af dataene.
- De er afdelingsspecifikke i en organisation.
- De understøtter ikke skalerbarhed.
- De er designet til at tjene formålet med slutbrugerrapporter og forespørgsler.
- Vi kan ikke integrere dimensionelle datamodeller.
Dimensionstabeller
Dimensionstabeller spiller en nøglerolle i DW-systemet ved at gemme alle de analyserede metriske værdier. Disse værdier gemmes under let valgbare dimensionelle attributter (kolonner) i tabellen. Kvaliteten af et DW-system afhænger for det meste af dybden af dimensionsattributter.
Derfor bør vi forsøge at give mange attributter sammen med deres respektive værdier i dimensionstabellerne.
Lad os undersøge strukturen på dimensionstabeller !!
# 1) Dimensionstabelnøgle: Hver dimensionstabel vil have en hvilken som helst af dimensionens attributter som en primær nøgle til entydigt at identificere hver række. Derfor kan de forskellige numeriske værdier for denne attribut fungere som primære nøgler.
Hvis attributværdierne under ingen omstændigheder er unikke, kan du overveje sekventielt genererede systemnumre som de primære nøgler. Disse kaldes også som surrogatnøgler.
Dimensionelle datamodeller skal have den referencemæssige integritetsbegrænsning for hver nøgle mellem dimensioner og fakta. Således vil faktatabeller have en fremmed nøglehenvisning for hver primær / surrogatnøgle i dimensionstabellen for at opretholde henvisningens integritet.
Hvis det mislykkes, kan de respektive faktatabeldata ikke hentes for den dimensionnøgle.
# 2) Tabellen er bred: Vi kan sige, at dimensionstabeller er brede, da vi kan tilføje et vilkårligt antal attributter til en dimensionstabel på ethvert tidspunkt i DW-cyklussen. DW-arkitekt vil bede ETL-teamet om at tilføje respektive nye attributter til skemaet.
I realtidsscenarier kan du se dimensionstabeller med 50 (eller) flere attributter.
# 3) Tekstlige attributter: Dimensionelle attributter kan være af enhver type som fortrinsvis tekst (eller) numerisk. Tekstlige attributter vil have ægte forretningsord snarere end koder. Dimensionstabeller er ikke beregnet til beregninger, derfor bruges numeriske værdier sjældent til dimensionelle attributter.
# 4) Attributter er muligvis ikke direkte relateret: Alle attributter i en dimensionstabel er muligvis ikke relateret til hinanden.
# 5) Ikke normaliseret: Normalisering af en dimensionstabel bringer flere mellemliggende tabeller ind i billedet, hvilket ikke er effektivt. Dimensionstabeller er således ikke normaliserede.
Dimensionelle attributter kan fungere som kilde til begrænsninger i forespørgsler og kan også vises som etiketter i rapporterne. Forespørgslerne fungerer effektivt, hvis du direkte vælger en attribut fra dimensionstabellen og henviser direkte til den respektive faktatabel uden at røre ved andre mellemliggende tabeller.
# 6) Boring ned og oprulning: Dimensionsattributter har evnen til at bore (eller) rulle dataene op, når det er nødvendigt.
# 7) Flere hierarkier: En enkelt dimensionstabel med flere hierarkier er meget almindelig. En dimensionstabel vil have et simpelt hierarki, hvis der kun findes en sti fra bundniveau til top. På samme måde vil det have flere hierarkier, hvis der er flere stier til stede fra bundniveau til top.
# 8) Få optegnelser: Dimensionstabeller vil have færre antal poster (i hundreder) end faktatabellerne (i millioner). Selvom de er mindre end fakta, leverer de alle input til faktatabellerne.
Her er et eksempel på en kundedimensionstabel:
Ved at forstå ovenstående begreber kan du beslutte, om et datafelt kan fungere som en dimensionsattribut (eller) ikke, mens du ekstraherer dataene fra selve kilden.
Den grundlæggende belastningsplan for en dimension
Dimensioner kan oprettes på to måder, dvs. ved at udtrække dimensionens data fra eksterne kildesystemer (eller) ETL-systemet kan bygge dimensionerne fra iscenesættelse uden at involvere nogen eksterne kilder. Imidlertid er et ETL-system uden ekstern behandling mere egnet til at oprette dimensionstabeller.
Nedenfor er trinene involveret i denne proces:
test datastyringsværktøjer open source
- Datarengøring: Data renses, valideres, og forretningsregler anvendes inden indlæsning i dimensionstabellen for at opretholde konsistens.
- Data, der overholder: Data fra andre dele af datalageret skal aggregeres korrekt som en enkelt værdi i forhold til hvert felt i dimensionstabellen.
- Del de samme domæner: Når dataene er bekræftet, gemmes de igen i iscenesættelsestabeller.
- Data levering: Endelig indlæses alle dimensionelle attributværdier med tildelte primære / surrogatnøgler.
Typer af dimensioner
De forskellige typer dimensioner er angivet nedenfor til din reference.
Lad os begynde!!
# 1) Små dimensioner
Små dimensioner i datalager fungerer som opslagstabeller med færre antal rækker og kolonner. Data i små dimensioner kan let indlæses fra regneark. Om nødvendigt kan små dimensioner kombineres som en superdimension.
# 2) Konformeret dimension
En tilpasset dimension er en dimension, der kan henvises på samme måde med hver faktatabel, den er relateret til.
Datodimension er det bedste eksempel på en tilpasset dimension, da attributterne for datodimension som år, måned, uge, dage osv. Kommunikerer de samme data på samme måde på tværs af et hvilket som helst antal fakta.
Et eksempel på en tilpasset dimension.
# 3) Junk Dimension
Få attributter i en faktatabel, såsom flag og indikatorer, kan flyttes til en separat junkdimensionstabel. Disse attributter hører heller ikke til andre eksisterende dimensionstabeller. Generelt er værdierne for disse attributter simpelthen et “ja / nej” (eller) “sandt / falsk”.
Oprettelse af en ny dimension for hver enkelt flagattribut gør det kompliceret ved at oprette flere antal udenlandske nøgler til faktatabellen. Samtidig øger opbevaring af alle disse flag og indikatoroplysninger faktisk tabeller også mængden af data, der er lagret i fakta, hvilket derved forringer ydelsen.
Derfor er den bedste løsning til dette at skabe en enkelt uønsket dimension, da en uønsket dimension er i stand til at indeholde et vilkårligt antal 'ja / nej' eller 'sand / falsk' indikatorer. Imidlertid gemmer uønskede dimensioner beskrivende værdier for disse indikatorer (ja / nej (eller) sand / falsk) såsom aktiv og afventende osv.
Baseret på kompleksiteten af en faktatabel og dens indikatorer kan en faktatabel have en eller flere uønskede dimensioner.
Et eksempel på uønsket dimension.
# 4) Rollespil-dimension
En enkelt dimension, der kan henvises til flere formål i en faktatabel, er kendt som rollespil-dimension.
Det bedste eksempel på en rollespildimension er igen en dato-dimensionstabel, da den samme datoattribut i en dimension kan bruges til forskellige formål i en kendsgerning, såsom dato for ordre, leveringsdato, transaktionsdato, dato for annullering etc.
Om nødvendigt kan du oprette fire forskellige visninger i datodimensionstabellen med hensyn til fire forskellige datoattributter i en faktatabel.
Et eksempel på en rolle-spiller dimension.
# 5) Degenererede dimensioner
Der kan være få attributter, der hverken kan være dimensioner (metrics) eller fakta (målinger), men de er nødvendige for analyse. Alle sådanne attributter kan flyttes til degenererede dimensioner.
For eksempel, du kan betragte ordrenummer, fakturanummer osv. som degenererede dimensionse attributter.
Et eksempel på en degenereret dimension.
# 6) Langsomt ændrer dimensioner
En langsomt skiftende dimension er en slags, hvor data kan ændre sig langsomt når som helst i stedet for med jævne mellemrum. Modificerede data i dimensionstabeller kan håndteres på forskellige måder som forklaret nedenfor.
Du kan vælge SCD-typen for at svare individuelt på en ændring for hver attribut i en dimensionstabel.
(i) Type 1 SCD
- I type 1, når der er en ændring i værdierne for de dimensionelle attributter, overskrives de eksisterende værdier med de nyligt modificerede værdier, hvilket kun er en opdatering.
- Gamle data vedligeholdes ikke til historisk reference.
- Tidligere rapporter kan ikke regenereres på grund af manglende eksistens af gamle data.
- Let at vedligeholde.
- Virkningen på faktaborde er mere.
Eksempel på type 1 SCD:
(Ii) Type 2 SCD
- Når der er en ændring i værdierne for dimensionelle attributter i type 2, indsættes en ny række med de ændrede værdier uden at ændre de gamle ræddata.
- Hvis der findes nogen fremmed nøglehenvisning til den gamle post i nogen af faktatabellerne, opdateres den gamle surrogatnøgle automatisk overalt med en ny surrogatnøgle.
- Virkningen på faktabordets ændringer er meget mindre med ovenstående trin.
- Gamle data betragtes ikke nogen steder efter ændringerne.
- I type 2 kan vi spore alle de ændringer, der sker med de dimensionelle attributter.
- Der er ingen begrænsning for lagring af historiske data.
- I type 2 tilføjes få attributter til hver række, såsom ændret dato, effektiv dato-tid, slutdato-tid, årsagen til ændringen og det aktuelle flag er valgfri. Men dette er vigtigt, hvis virksomheden ønsker at kende antallet af ændringer, der er foretaget i en bestemt periode.
Eksempel på type 2 SCD:
(Iii) Type 3 SCD
- I type 3, når der er en ændring i værdierne for dimensionelle attributter, opdateres nye værdier, men de gamle værdier forbliver gyldige som den anden mulighed.
- I stedet for at tilføje en ny række for hver ændring tilføjes en ny kolonne, hvis den ikke tidligere eksisterede.
- Gamle værdier placeres i de ovennævnte attributter, og den primære attributs data overskrives med den ændrede værdi som i type 1.
- Der er en grænse for lagring af historiske data.
- Virkningen på faktaborde er mere.
Eksempel på type 3 SCD:
(iv) Type 4 SCD
- I type 4 gemmes de aktuelle data i en tabel.
- Alle historiske data bevares i en anden tabel.
Eksempel på type 4 SCD:
(v) Type 6 SCD
- En dimensionstabel kan også have en kombination af alle tre SCD-typer 1, 2 og 3, der er kendt som en type 6 (eller) hybrid, der langsomt skifter dimension.
Faktatabeller
Faktatabeller gemmer et sæt kvantitativt målte værdier, der bruges til beregninger. Faktatabellens værdier vises i forretningsrapporterne. I modsætning til dimensionstabellenes tekstdata, er datatypen faktatabeller betydeligt numerisk.
Faktatabeller er dybe, mens dimensionstabeller er brede, da faktatabeller vil have et højere antal rækker og et mindre antal kolonner. En primær nøgle defineret i faktatabellen er primært at identificere hver række separat. Den primære nøgle kaldes også en sammensat nøgle i faktatabellen.
Hvis den sammensatte nøgle mangler i en faktatabel, og hvis to poster har de samme data, er det meget svært at skelne mellem dataene og henvise dataene i dimensionstabeller.
Derfor, hvis der findes en ordentlig unik nøgle som den sammensatte nøgle, er det godt at generere et sekvensnummer for hver faktatabellepost. Et andet alternativ er at danne en sammenkædet primærnøgle. Dette genereres ved sammenkædning af alle de henviste primære nøgler til dimensionstabeller rækkevist.
En enkelt faktatabel kan være omgivet af flere dimensionstabeller. Ved hjælp af de fremmednøgler, der findes i faktisk tabeller, kan der henvises til den respektive kontekst (detaljerede data) for de målte værdier i dimensionstabellerne. Ved hjælp af forespørgsler vil brugerne udføre drill down og roll up effektivt.
Det laveste niveau af data, der kan lagres i en faktatabel, er kendt som Granularity. Antallet af dimensionstabeller, der er knyttet til en faktatabel, er omvendt proportional med granulariteten af disse faktatabeldata. dvs. den mindste måleværdi kræver, at flere dimensionstabeller skal henvises.
I en dimensionel model opretholder faktatabellerne mange-til-mange-forhold til dimensionstabellerne.
Et eksempel på en salgsfaktatabel:
Loadplan for faktaborde
Du kan indlæse faktatabeldata effektivt ved at overveje følgende markører:
# 1) Slip og gendan indekser
Indekser i virkeligheden er tabeller gode ydeevne boostere under forespørgsel på data, men de nedbryder ydeevnen, mens data indlæses. Derfor skal du inden indlæsning af enorme data i faktatabeller primært slippe alle indekserne på denne tabel, indlæse dataene og gendanne indekserne.
# 2) Separate indsatser fra opdateringer
Flet ikke indsæt og opdater opdateringer, mens de indlæses i en faktatabel. Hvis antallet af opdateringer er mindre, skal du behandle indsatser og opdateringer separat. Hvis antallet af opdateringer er mere, anbefales det at afkorte og genindlæse faktatabellen for hurtige resultater.
# 3) Partitionering
Gør partitioneringen fysisk på et faktatabel i minitabeller for bedre forespørgsel på de store faktatabells data. Bortset fra DBA'erne og ETL-teamet vil ingen være opmærksomme på partitionerne på fakta.
Som en eksempel , kan du opdele en tabel månedvis, kvartvis, årvis osv. Under forespørgsel betragtes kun de partitionerede data i stedet for at scanne hele tabellen.
# 4) Indlæs parallelt
selen-interviewspørgsmål til 4 års erfaring
Vi har nu fået en idé om partitioner på faktaborde. Partitioner på fakta er også gavnlige, når enorme data indlæses i fakta. For at gøre dette skal du først opdele dataene logisk i forskellige datafiler og køre ETL-jobene for at indlæse alle disse logiske dele af data parallelt.
# 5) Bulk Load Utility
I modsætning til andre RDBMS-systemer behøver ETL-systemet ikke at opretholde rollback-logfiler eksplicit for mellemtransaktionsfejl. Her sker 'bulkbelastninger' i fakta i stedet for 'SQL-indsatser' for at indlæse enorme data. Hvis en enkelt belastning mislykkes, kan hele dataene let genindlæses (eller) de kan fortsættes fra det sted, hvor de bliver tilbage med bulkbelastningen.
# 6) Sletning af en faktapost
Sletning af en faktatabelregistrering sker kun, hvis virksomheden udtrykkeligt ønsker det. Hvis der er nogen faktatabeldata, der ikke længere findes i kildesystemerne, kan de pågældende data slettes enten fysisk (eller) logisk.
- Fysisk sletning: Uønskede poster fjernes permanent fra faktatabellen.
- Logisk sletning: En ny kolonne tilføjes til faktatabellen, f.eks. 'Slettet' af bit (eller) boolsk type. Dette fungerer som et flag, der repræsenterer de slettede poster. Du skal sikre dig, at du ikke vælger de slettede poster, mens du spørger til faktatabeldataene.
# 7) Sekvens for opdateringer og sletninger i en faktatabel
Når der er data, der skal opdateres, skal dimensionstabellerne først opdateres efterfulgt af opdatering af surrogattasterne i opslagstabellen, hvis det er nødvendigt, og derefter opdateres de respektive faktatabeller. Sletning sker i omvendt retning, fordi sletning af alle uønskede data fra faktatabeller gør det let at slette de sammenkædede uønskede data fra dimensionstabellerne.
Vi bør følge ovenstående rækkefølge i begge tilfælde, fordi dimensionstabeller og faktatabeller opretholder referentiel integritet hele tiden.
Typer af fakta
Baseret på opførslen af faktatabeldata kategoriseres de som faktatabeller for transaktion, øjebliksbillede faktatabeller og akkumulerede øjebliksbillede faktatabeller. Alle disse tre typer følger forskellige funktioner med forskellige datalastningsstrategier.
# 1) Transaktionstabeller
Som navnet indikerer, opbevares transaktionsfaktatabeller på transaktionsniveau for hver begivenhed, der sker. En sådan form for data er let at analysere på selve faktatabelniveauet. Men til yderligere analyse kan du også henvise til de tilknyttede dimensioner.
For eksempel, ethvert salg (eller) køb, der sker fra et marketingwebsted, skal indlæses i en faktatabel for transaktioner.
Et eksempel på en tabel med transaktionsfakta er vist nedenfor.
# 2) Periodiske øjebliksbillede faktatabeller
Som navnet indikerer, lagres data i periodisk snapshot-faktatabel i form af snapshots (billeder) med periodiske intervaller, såsom for hver dag, uge, måned, kvartal osv. Afhængigt af forretningsbehovet.
Så det er klart, at dette er en sammenlægning af data hele tiden. Derfor er øjebliksbillede fakta mere komplekse sammenlignet med transaktion fakta tabeller. For eksempel, alle data om resultatindtægtsrapporter kan gemmes i snapshot-faktatabeller for nem reference.
Et eksempel på en periodisk snapshot-faktatabel er vist nedenfor.
# 3) Akkumulerende øjebliksbillede faktatabeller
Akkumulerende snapshot-faktatabeller giver dig mulighed for at gemme data i tabeller i hele et produkts levetid. Dette fungerer som en kombination af de to ovennævnte typer, hvor data til enhver tid kan indsættes som et øjebliksbillede.
I denne type opdateres yderligere datakolonner og data for hver række med hver milepæl i det produkt.
Et eksempel på en akkumulerende øjebliksbillede faktatabel.
Ud over de ovennævnte tre typer er her et par andre typer faktatabeller:
# 4) Faktiske faktatabeller: En kendsgerning er en samling af foranstaltninger, mens faktisk kun fanger kun begivenheder (eller) forhold, der ikke indeholder nogen foranstaltninger. En fakta-mindre faktatabel bruges hovedsageligt til at spore et system. Dataene i disse tabeller kan analyseres og bruges til rapportering.
For eksempel, du kan se efter detaljer om en medarbejder, der har taget orlov og typen af orlov i et år osv. Hvis alle disse ikke-klare faktaoplysninger inkluderes i en kendsgerning, vil tabellen helt sikkert øge fakta størrelse.
Et eksempel på en faktaløs faktatabel er vist nedenfor.
# 5) Overensstemmede faktatabeller: En tilpasset kendsgerning er en kendsgerning, der kan henvises til på samme måde med hver datamat, den er relateret til.
Specifikationer for en faktatabel
Nedenfor er specifikationerne for en faktatabel.
- Fakta navn: Dette er en streng, der kort beskriver funktionstabellen.
- Forretningsproces: Samtaler om virksomheden skal opfyldes af faktatabellen.
- Spørgsmål: Nævner en liste over forretningsspørgsmål, der vil blive besvaret af faktatabellen.
- Korn: Angiver det laveste detaljeringsniveau, der er knyttet til faktatabeldataene.
- Dimensioner: Skriv en liste over alle dimensionstabeller, der er knyttet til den faktatabel.
- Foranstaltninger: De beregnede værdier gemt i faktatabellen.
- Belastningsfrekvens Repræsenterer tidsintervallerne for at indlæse data i faktatabellen.
- Indledende rækker: Se de første data, der er udfyldt i faktatabellen for første gang.
Eksempel på dimensioneringsdatamodellering
Du kan få en ide om, hvordan dimensionstabeller og faktatabeller kan designes til et system ved at se på nedenstående dimensionelle datamodelleringsdiagram for salg og ordrer.
Konklusion
Nu skulle du have fået fremragende viden om dimensionelle datamodelleringsteknikker, deres fordele, myter, dimensionstabeller, faktatabeller sammen med deres typer og processer.
Se vores kommende tutorial for at vide mere om Data Warehouse Schemas !!
=> Besøg her for at lære datalagring fra bunden.
Anbefalet læsning
- Vejledning til test af datavarehus med eksempler ETL testguide
- Eksempler på data minedrift: De mest almindelige anvendelser af Data Mining 2021
- Python DateTime-tutorial med eksempler
- Grundlag for datalagring: En ultimativ guide med eksempler
- Volume Testing Tutorial: Eksempler og Volume Testing Tools
- Top 10 populære datalagerværktøjer og testteknologier
- Data Mining: Process, teknikker og større problemer i dataanalyse
- Sådan udføres datadrevet test i SoapUI Pro - SoapUI Tutorial # 14