dimensional data model data warehouse tutorial with examples
Denna handledning förklarar fördelarna och myterna med dimensionell datamodell i datalager. Lär dig också om dimensionstabeller och faktatabeller med exempel:
Datalagertestning förklarades i vår tidigare handledning, i detta Datalagerutbildningsserie för alla .
Enorma data är organiserade i Data Warehouse (DW) med dimensionella datamodelleringstekniker. Dessa dimensionella datamodelleringstekniker gör jobbet för slutanvändare mycket enkelt att fråga om affärsdata. Denna handledning förklarar allt om dimensionella datamodeller i DW.
Målgrupp
- Datalager / ETL-utvecklare och testare.
- Databasproffs med grundläggande kunskap om databaskoncept.
- Databasadministratörer / big data-experter som vill förstå datalager / ETL-koncept.
- Högskoleexaminerade / nybörjare som letar efter datalagerjobb.
Vad du kommer att lära dig:
Dimensionella datamodeller
Dimensionella datamodeller är de datastrukturer som är tillgängliga för slutanvändarna i ETL-flöde, för att fråga och analysera data. ETL-processen slutar med att ladda in data i måldimensionella datamodeller. Varje dimensionell datamodell är byggd med en faktatabell omgiven av flera dimensionstabeller.
Steg som ska följas vid utformning av en dimensionell datamodell:
Fördelar med dimensionell datamodellering
Nedan listas de olika fördelarna med dimensionell datamodellering.
- De är säkra för att använda de kontinuerligt föränderliga DW-miljöerna.
- Enorma data kan enkelt byggas med hjälp av dimensionella datamodeller.
- Data från dimensionella datamodeller är lätta att förstå och analysera.
- De är snabbt tillgängliga för slutanvändarna för frågor med hög prestanda.
- Dimensionella datamodeller tillåter oss att borra ner (eller) rulla upp data hierarkiskt.
ER-modellering mot dimensionell datamodellering
- ER-modellering är lämplig för operativa system medan dimensionell modellering är lämplig för datalagret.
- ER-modellering upprätthåller detaljerade aktuella transaktionsdata medan dimensionell modellering underhåller sammanfattningen av både aktuell och historisk transaktionsdata.
- ER-modellering har normaliserat data medan dimensionell modellering har av-normaliserat data.
- ER-modellering använder fler sammanfogningar under frågeanhämtning medan dimensionell modellering använder ett mindre antal sammanfogningar, så frågeprestanda är snabbare vid dimensionell modellering.
Dimensionella datamodelleringsmyter
Nedan följer några av de existerande dimensionella datamodelleringsmyterna.
- Dimensionsmodeller används endast för att representera sammanfattningen av data.
- De är avdelningsspecifika i en organisation.
- De stöder inte skalbarhet.
- De är utformade för att tjäna syftet med slutanvändarrapporter och frågor.
- Vi kan inte integrera dimensionella datamodeller.
Dimensionstabeller
Dimensionstabeller spelar en nyckelroll i DW-systemet genom att lagra alla analyserade mätvärden. Dessa värden lagras under lätt att välja dimensionella attribut (kolumner) i tabellen. Kvaliteten på ett DW-system beror mest på djupet på dimensionens attribut.
Därför bör vi försöka tillhandahålla många attribut tillsammans med deras respektive värden i dimensionstabellerna.
Låt oss utforska strukturen på dimensionstabeller !!
# 1) Dimensionstabellnyckel: Varje dimensionstabell kommer att ha någon av dess dimensionsattribut som en primär nyckel för att identifiera varje rad unikt. Därför kan de distinkta numeriska värdena för det attributet fungera som primära nycklar.
Om attributvärdena inte är unika i vilket fall som helst kan du betrakta sekventiellt genererade systemnummer som de primära nycklarna. Dessa kallas också som surrogatnycklar.
Dimensionella datamodeller måste ha referensintegritetsbegränsningen för varje nyckel mellan dimensioner och fakta. Således kommer faktatabeller att ha en främmande nyckelreferens för varje primär / surrogatnyckel i dimensionstabellen för att upprätthålla referensintegritet.
Om det misslyckas kan inte respektive faktatabeldata hämtas för den dimensionnyckeln.
# 2) Tabellen är bred: Vi kan säga att dimensionstabeller är breda eftersom vi kan lägga till valfritt antal attribut till en dimensionstabell när som helst i DW-cykeln. DW-arkitekt kommer att be ETL-teamet att lägga till respektive nya attribut i schemat.
I realtidsscenarier kan du se dimensionstabeller med 50 (eller) mer attribut.
# 3) Textattribut: Dimensionsattribut kan vara av vilken typ som helst (helst) text (eller) numeriskt. Textattribut har riktiga affärsord snarare än koder. Dimensionstabeller är inte avsedda för beräkningar och numeriska värden används sällan för dimensionella attribut.
# 4) Attribut kanske inte är direkt relaterade: Alla attribut i en dimensionstabell kanske inte är relaterade till varandra.
# 5) Ej normaliserad: Normalisering av en dimensionstabell ger fler mellanliggande tabeller in i bilden som inte är effektiva. Således normaliseras inte dimensionstabeller.
Dimensionsattribut kan fungera som källa för begränsningar i frågor och kan också visas som etiketter i rapporterna. Frågorna fungerar effektivt om du väljer ett attribut direkt från dimensionstabellen och hänvisar direkt till respektive faktatabell utan att röra vid några andra mellanliggande tabeller.
# 6) Borrning och rullning: Dimensionsattribut har förmågan att borra (eller) rulla upp data när det behövs.
# 7) Flera hierarkier: En enda dimensionstabell med flera hierarkier är mycket vanligt. En dimensionstabell har en enkel hierarki om det bara finns en sökväg från bottennivån till toppen. På samma sätt kommer det att ha flera hierarkier om det finns flera vägar att nå från bottennivån till toppen.
# 8) Få register: Dimensionstabeller har mindre antal poster (i hundratals) än faktatabellerna (i miljoner). Även om de är mindre än fakta, ger de alla inmatningar till faktatabellerna.
Här är ett exempel på en kunddimensionstabell:
Genom att förstå ovanstående begrepp kan du bestämma om ett datafält kan fungera som ett dimensionsattribut (eller) inte medan du extraherar data från själva källan.
Den grundläggande lastplanen för en dimension
Dimensioner kan skapas på två sätt, dvs. genom att extrahera måttdata från externa källsystem (eller) ETL-systemet kan bygga dimensionerna från iscenesättning utan att involvera externa källor. Ett ETL-system utan extern bearbetning är dock mer lämpligt för att skapa dimensionstabeller.
Nedan följer stegen i denna process:
bästa ställen att titta på anime gratis online
- Datarengöring: Data rengörs, valideras och affärsregler tillämpas innan de laddas i dimensionstabellen för att upprätthålla enhetlighet.
- Uppfyller data: Data från andra delar av datalagret ska sammanställas ordentligt som ett enda värde, med avseende på varje fält i dimensionstabellen.
- Dela samma domäner: När data har bekräftats lagras de igen i mellanläggstabeller.
- Dataleverans: Slutligen laddas alla dimensionella attributvärden med tilldelade primära / surrogatnycklar.
Typer av mått
De olika typerna av mått listas nedan för din referens.
Låt oss börja!!
# 1) Små dimensioner
Små dimensioner i datalager fungerar som uppslagstabeller med mindre antal rader och kolumner. Data i små dimensioner kan enkelt laddas från kalkylark. Vid behov kan små dimensioner kombineras som en superdimension.
# 2) Konformerad dimension
En anpassad dimension är en dimension som kan hänvisas på samma sätt med varje faktatabell som den är relaterad till.
Datumdimension är det bästa exemplet på en anpassad dimension eftersom attributen för datumdimension som år, månad, vecka, dagar etc. kommunicerar samma data på samma sätt över ett antal fakta.
Ett exempel på en anpassad dimension.
# 3) Skräpdimension
Få attribut i en faktatabell som flaggor och indikatorer kan flyttas till en separat skräpdimensionstabell. Dessa attribut tillhör inte några andra befintliga dimensionstabeller också. I allmänhet är värdena för dessa attribut helt enkelt ett “ja / nej” (eller) “sant / falskt”.
Att skapa en ny dimension för varje enskilt flaggattribut gör det komplicerat genom att skapa fler utländska nycklar till faktatabellen. Samtidigt ökar också att hålla alla dessa flaggor och indikatorinformation i faktatabeller mängden data som lagras i fakta vilket därmed försämrar prestanda.
Därför är den bästa lösningen för detta att skapa en enda skräpdimension, eftersom en skräpdimension kan innehålla valfritt antal “ja / nej” eller “sant / falskt” indikatorer. Skräppstorlekar lagrar dock beskrivande värden för dessa indikatorer (ja / nej (eller) sant / falskt) som aktivt och väntande etc.
Baserat på komplexiteten i en faktatabell och dess indikatorer kan en faktatabell ha en eller flera skräpdimensioner.
Ett exempel på skräpdimension.
# 4) Rollspelande dimension
En enda dimension som kan hänvisas för flera ändamål i en faktatabell kallas rollspelande dimension.
Det bästa exemplet för en rollspeldimension är återigen en datumdimensionstabell eftersom samma datumattribut i en dimension kan användas för olika ändamål i ett faktum som orderdatum, leveransdatum, transaktionsdatum, avbokningsdatum, etc.
Om det behövs kan du skapa fyra olika vyer i datumdimensionstabellen med avseende på fyra olika datumattribut för en faktatabell.
Ett exempel på en rollspelande dimension.
# 5) Degenererade dimensioner
Det kan finnas få attribut som varken kan vara dimensioner (mått) eller fakta (mått) men de behöver analyseras. Alla sådana attribut kan flyttas till degenererade dimensioner.
Till exempel, Du kan betrakta ordernummer, fakturanummer etc som degenererade dimensionsattribut.
Ett exempel på en degenererad dimension.
# 6) Långsamt förändrade dimensioner
En långsamt föränderlig dimension är ett slag där data kan förändras långsamt när som helst snarare än med jämna mellanrum. Modifierade data i dimensionstabeller kan hanteras på olika sätt enligt nedan.
Du kan välja SCD-typ för att svara på en förändring individuellt för varje attribut i en dimensionstabell.
(i) Typ 1 SCD
- I typ 1, när det sker en förändring av värdena för dimensionella attribut, skrivs de befintliga värdena över med de nyligen modifierade värdena, vilket bara är en uppdatering.
- Gamla data underhålls inte för historisk referens.
- Tidigare rapporter kan inte återskapas på grund av att det inte finns gamla data.
- Lätt att underhålla.
- Effekten på faktatabellerna är mer.
Exempel på typ 1 SCD:
(Ii) Typ 2 SCD
- I typ 2, när det sker en förändring av värdena för dimensionella attribut, kommer en ny rad att infogas med de modifierade värdena utan att de gamla raddata ändras.
- Om det finns någon främmande nyckelreferens som finns till den gamla posten i någon av faktatabellerna uppdateras den gamla surrogatnyckeln automatiskt överallt med en ny surrogatnyckel.
- Effekten på faktabordförändringarna är mycket mindre med ovanstående steg.
- Gamla data beaktas inte någonstans efter ändringarna.
- I typ 2 kan vi spåra alla förändringar som händer med dimensionella attribut.
- Det finns ingen gräns för lagring av historiska data.
- I typ 2 läggs några attribut till varje rad, till exempel ändrat datum, ikraftträdande datum, slutdatum, orsaken till ändringen och den aktuella flaggan är valfri. Men detta är viktigt om företaget vill veta antalet ändringar som gjorts under en viss tidsperiod.
Exempel på typ 2 SCD:
(Iii) Typ 3 SCD
- I typ 3 när det sker en förändring av värdena för dimensionella attribut uppdateras nya värden men de gamla värdena förblir giltiga som det andra alternativet.
- Istället för att lägga till en ny rad för varje ändring läggs en ny kolumn till om den inte existerar tidigare.
- Gamla värden placeras i de ovan tillagda attributen och det primära attributets data skrivs över med det ändrade värdet som i typ 1.
- Det finns en gräns för lagring av historisk data.
- Effekten på faktatabellerna är mer.
Exempel på typ 3 SCD:
(iv) Typ 4 SCD
- I typ 4 lagras aktuella data i en tabell.
- All historisk data finns i en annan tabell.
Exempel på typ 4 SCD:
(v) Typ 6 SCD
- En dimensionstabell kan också ha en kombination av alla tre SCD-typerna 1, 2 och 3 som är kända som en typ 6 (eller) hybrid som långsamt förändras.
Faktatabeller
Faktatabeller lagrar en uppsättning kvantitativt uppmätta värden som används för beräkningar. Faktatabellens värden visas i affärsrapporterna. Till skillnad från dimensionstabellens textdata är faktatabellens datatyp betydligt numerisk.
Faktatabellerna är djupa medan dimensionstabellerna är breda eftersom faktatabellerna kommer att ha ett högre antal rader och ett mindre antal kolumner. En primär nyckel definierad i faktatabellen är främst att identifiera varje rad separat. Den primära nyckeln kallas också en sammansatt nyckel i faktatabellen.
Om den sammansatta nyckeln saknas i en faktatabell och om två poster har samma data är det mycket svårt att skilja mellan data och hänvisa data till dimensionstabeller.
Följaktligen, om det finns en riktig unik nyckel som den sammansatta nyckeln, är det bra att generera ett sekvensnummer för varje faktatabellpost. Ett annat alternativ är att bilda en sammanfogad primärnyckel. Detta genereras genom att sammanfoga alla de refererade primära nycklarna i dimensionstabeller radvis.
En enda faktatabell kan omges av flera dimensionstabeller. Med hjälp av de främmande nycklar som finns i faktatabeller kan respektive sammanhang (detaljerade data) för de uppmätta värdena hänvisas till i dimensionstabellerna. Med hjälp av frågor kommer användarna att utföra borrning och rullning upp effektivt.
Den lägsta nivån på data som kan lagras i en faktatabell kallas Granularity. Antalet dimensionstabeller associerade med en faktatabell är omvänt proportionell mot granulariteten hos faktatabelldata. dvs. det minsta mätvärdet behöver fler dimensionstabeller för att hänvisas.
I en dimensionell modell upprätthåller faktatabellerna många-till-många-förhållande till dimensionstabeller.
Ett exempel på en försäljning faktatabell:
Lastplan för faktatabeller
Du kan ladda faktatabelldata effektivt genom att överväga följande pekare:
# 1) Släpp och återställ index
Faktabaser med index är bra prestationsförstärkare när du frågar efter data, men de förstör prestandan medan data laddas. Därför, innan du laddar enorma data i faktatabeller, släpper du först och främst alla index på den tabellen, laddar in data och återställer indexen.
# 2) Separata insatser från uppdateringar
Slå inte samman infoga och uppdatera poster medan du laddar in i en faktatabell. Om antalet uppdateringar är mindre, bearbeta insatser och uppdateringar separat. Om antalet uppdateringar är fler är det tillrådligt att trunka och ladda om faktatabellen för snabba resultat.
# 3) Partitionering
Gör partitioneringen fysiskt på ett faktatabell i minitabeller för bättre frågeprestanda på faktatabellens data. Förutom DBA: erna och ETL-teamet kommer ingen att vara medveten om partitionerna på fakta.
Som en exempel , kan du partitionera en tabell månadsvis, kvartalsvis, årsmässigt etc. Under frågan beaktas endast partitionerad data istället för att skanna hela tabellen.
# 4) Ladda parallellt
hur öppnar man en swf-fil
Vi har nu fått en idé om partitioner på faktabord. Partitioner på fakta är också fördelaktiga när man laddar enorma data i fakta. För att göra detta, först dela upp data logiskt i olika datafiler och kör ETL-jobb för att ladda alla dessa logiska delar av data parallellt.
# 5) Bulk Load Utility
Till skillnad från andra RDBMS-system behöver ETL-systemet inte upprätthålla återställningsloggar uttryckligen för mellantransaktionsfel. Här sker 'bulkbelastningar' till fakta istället för 'SQL-insatser' för att ladda enorma data. Om en enstaka belastning misslyckas kan hela informationen lätt laddas om (eller) den kan fortsätta från den plats där den slutar med bulkbelastningen.
# 6) Ta bort en faktapost
Att ta bort en faktatabellpost sker bara om företaget vill uttryckligen. Om det finns någon faktatabellinformation som inte längre finns i källsystemen kan respektive data raderas antingen fysiskt (eller) logiskt.
- Fysisk radering: Oönskade poster tas bort från faktatabellen permanent.
- Logisk radering: En ny kolumn läggs till i faktatabellen, till exempel ”borttagen” av bit (eller) boolesk typ. Detta fungerar som en flagga för att representera de raderade posterna. Du måste se till att du inte väljer de raderade posterna när du frågar faktatabelldata.
# 7) Sekvens för uppdateringar och raderingar i en faktatabell
När det finns data som ska uppdateras bör dimensionstabellerna uppdateras först följt av uppdatering av surrogattangenterna i uppslagstabellen om det behövs och därefter uppdateras respektive faktatabell. Radering sker i omvänd ordning eftersom det är enkelt att ta bort länkade oönskade data från dimensionstabellerna genom att ta bort all oönskad data från faktatabeller.
Vi bör följa ovanstående sekvens i båda fallen eftersom dimensionstabeller och faktatabeller upprätthåller referensintegritet hela tiden.
Typer av fakta
Baserat på beteendet hos faktatabelldata kategoriseras de som faktatabeller för transaktioner, faktatabeller för ögonblicksbild och faktatabeller för ögonblicksbild. Alla dessa tre typer följer olika funktioner med olika dataladdningsstrategier.
# 1) Transaktionstabeller
Som namnet indikerar lagras faktatabeller för transaktionsnivå för varje händelse som händer. Sådan typ av data är lätt att analysera på själva faktatabellnivån. Men för vidare analys kan du också hänvisa till tillhörande dimensioner.
Till exempel, varje försäljning (eller) köp som sker från en marknadsföringswebbplats bör laddas i en faktatabell för transaktioner.
Ett exempel på en transaktionstabell visas nedan.
# 2) Periodiska ögonblicksfaktatabeller
Som namnet indikerar lagras data i periodiska ögonblicksbilds faktatabeller i form av ögonblicksbilder (bilder) med jämna mellanrum som för varje dag, vecka, månad, kvartal etc beroende på affärsbehov.
Så det är uppenbart att detta är en sammanställning av data hela tiden. Därför är ögonblicksbildsfakta mer komplexa jämfört med faktatabeller för transaktioner. Till exempel, alla resultatintäktsrapporter kan lagras i ögonblicksbilds faktatabeller för enkel referens.
Ett exempel på en periodisk faktatabell för ögonblicksbild visas nedan.
# 3) Ackumulerande snapshot-faktatabeller
Med ackumulerande faktatabeller för ögonblicksbild kan du lagra data i tabeller under en produkts hela livstid. Detta fungerar som en kombination av ovanstående två typer där data kan infogas av valfri händelse när som helst som en ögonblicksbild.
I den här typen uppdateras ytterligare datumkolumner och data för varje rad med varje milstolpe för den produkten.
Ett exempel på en ackumulerande faktatabell för ögonblicksbild.
Förutom ovanstående tre typer, här är några andra typer av faktatabeller:
# 4) Faktiska faktatabeller: Ett faktum är en samling åtgärder medan faktum mindre fångar endast händelser (eller) förhållanden som inte innehåller några åtgärder. En fakta-mindre faktatabell används främst för att spåra ett system. Uppgifterna i dessa tabeller kan analyseras och användas för rapportering.
Till exempel, du kan leta efter detaljer om en anställd som har tagit ledighet och vilken typ av ledighet under ett år etc. Inklusive alla dessa oklara faktauppgifter i ett faktum kommer tabellen definitivt att öka faktornas storlek.
Ett exempel på en Factless Fact Table visas nedan.
# 5) Konformerade faktatabeller: Ett överensstämmande faktum är ett faktum som kan hänvisas på samma sätt med varje datamart det är relaterat till.
Specifikationer för en faktatabell
Nedan följer specifikationerna för en faktatabell.
- Fakta namn: Detta är en sträng som i korthet beskriver faktatabellens funktionalitet.
- Affärsprocess: Samtalen om verksamheten måste uppfyllas av faktatabellen.
- Frågor: Nämner en lista med företagsfrågor som kommer att besvaras av faktatabellen.
- Spannmål: Indikerar den lägsta detaljnivån associerad med faktatabelldata.
- Mått: Lista ut alla dimensionstabeller som hör till den faktatabellen.
- Åtgärder: De beräknade värdena lagrade i faktatabellen.
- Lastfrekvens Representerar tidsintervallen för att ladda in data i faktatabellen.
- Inledande rader: Se de ursprungliga uppgifterna i faktatabellen för första gången.
Exempel på dimensionell datamodellering
Du kan få en uppfattning om hur dimensionstabeller och faktatabeller kan utformas för ett system genom att titta på nedanstående dimensionella datamodelleringsdiagram för försäljning och order.
Slutsats
Nu borde du ha fått utmärkt kunskap om tekniker för dimensionering av datamodeller, deras fördelar, myter, dimensionstabeller, faktatabeller, tillsammans med deras typer och processer.
Kolla in vår kommande handledning för att lära dig mer om Data Warehouse Schemas !!
=> Besök här för att lära dig datalagring från repor.
Rekommenderad läsning
- Data Warehouse Testing Tutorial med exempel | ETL Testguide
- Data Mining Exempel: De vanligaste tillämpningarna av Data Mining 2021
- Python DateTime-handledning med exempel
- Grundläggande för datalagring: en ultimat guide med exempel
- Volymtesthandledning: Exempel och volymtestverktyg
- Topp 10 populära datalagerverktyg och testtekniker
- Data Mining: Process, Techniques & Major Issues In Data Analysis
- Hur man utför datadriven testning i SoapUI Pro - SoapUI-handledning nr 14