Endringstreg dimensjon
Innen dataforvaltning og datavarehus er en endringstreg dimensjon er en dimensjon som inneholder relativt statiske data, men som kan endre seg med uforutsigbare tidsintervaller.[1] Vanlige eksempler på endringstrege dimensjoner er entiteter som navn på geografiske lokasjoner, kundenavn eller produktdetaljer.
Noen scenarier kan føre til problemer med referanseintegritet.
Motiverende eksempel
[rediger | rediger kilde]For eksempel kan en database inneholde en faktatabell som holder på salgskolonner. Denne faktatabellen vil ofte være koblet til en dimensjon ved hjelp av fremmednøkler. En av disse dimensjonene kan inneholde data om selskapets selgere, som for eksempel det regionale kontoret hvor de jobber. Det er imidlertid ikke uvanlig at selgerne blir overført fra ett regionskontor til et annet. For historisk salgsrapportering kan det da være nødvendig å holde en oversikt over hvilke kontorer en bestemt selger har jobbet på til ulike tidspunkt.
Håndtering av slike spørsmål innebærer bruk av ulike typer endrigstrege dimensjoner. Det finnes da 7 ulike metoder som blir referert til som type 0 til 6, pluss noen hybrider av disse. Type 6 kalles også av og til for en hybrid type endringstreg dimensjon.
Type 0: behold originalen
[rediger | rediger kilde]Med en type 0 endringstreg dimensjon vil dimensjonsattributtene aldri forandre seg, og blir tilordnet attributter som har persistente (varige) verdier og kan beskrives som "originale". Noen eksempler kan være en fødselsdato eller opprinnelig kredittskår. Type 0 kan anvendes for de fleste dimensjonsattributter for datoer[2] ettersom satte datoer stort sett ikke behøver endres.[trenger referanse]
Type 1: overskriv
[rediger | rediger kilde]Med en type 1 endringstreg dimensjon overskrives gamle data med nye, og det blir ikke ført historikk på historiske data.
Et eksempel kan være en leverandør som bytter adresse, og man ikke har behov for å beholde historikk for den gamle adressen eller når adressen ble endret i systemet. Eksempel på en tabell leverandør:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State |
|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA |
I eksemplet ovenfor er Supplier_Code den naturlige nøkkelen og Supplier_Key er en surrogatnøkkel. (Teknisk sett er ikke surrogatnøkkelen nødvendig, siden raden vil være unik med den naturlige nøkkelen Supplier_Code.)
Dersom leverandøren flytter hovedkvarteret fra California (CA) til Illinois (IL) vil oppføringen bli overskrevet:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State |
|---|---|---|---|
| 123 | ABC | Acme Supply Co | IL |
En ulempe med Type 1-metoden er at databasen ikke inneholder historikk. En fordel er dog at den er enkel å vedlikeholde.
Dersom man har gjort utregninger som oppsummerer faktaene i tabellen etter Supplier_State må man sørge for at disse aggregerte faktaene beregnes på nytt når Supplier_State endres.[1]
Type 2: legg til en ny rad
[rediger | rediger kilde]Med en type 2 endringstreg dimensjon vil man ta vare på historiske data ved å opprette nye oppføringer for en gitt naturlig nøkkel i dimensjonstabellene, men med unike surrogatnøkler og/eller versjonsnumre. På denne måten bevarer man all historikk uten begrensning ved hver insert-kommando. Det finnes mange måter å implementere en type 2 endringstreg dimensjon på.
Eksempelvis kan et versjonsnummer øke sekvensielt når leverandøren flytter til Illinois:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Versjon |
|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA | 0 |
| 123 | ABC | Acme Supply Co | IL | 1 |
En annen metode er å legge til kolonner for ikrafttredelsesdato:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Start_Date | sluttdato |
|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA | 2000-01-01T00:00:00 | 2004-12-22T00:00:00 |
| 123 | ABC | Acme Supply Co | IL | 2004-12-22T00:00:00 | NULL |
Her er sluttdato/klokkeslett lik startdato/klokkeslett i påfølgende rad, mens NULL for sluttdato indikerer at den nåværende raden er siste element i tuppelen. Alternativt kan man velge en "standardisert" dato langt frem i tid som brukes som sluttdato i systemet (f.eks. 9999-12-31, som er ISO-notasjon for 31. desember i år 9999) slik at feltet kan inkluderes i en indeks, samtidig som at substitusjon av NULL-verdier ikke er nødvendig ved spørring.
En tredje variant er å bruke dato for ikrafttredelse og et flagg for hva som er gjeldende versjon.
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Effective_Date | Current_Flag |
|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA | 2000-01-01T00:00:00 | N |
| 123 | ABC | Acme Supply Co | IL | 2004-12-22T00:00:00 | Y |
Flaggverdien "Y" indikerer her elementet i i tuppelen som er gjeldende versjon.
Transaksjoner som refererer til en bestemt surrogatnøkkel (Supplier_Key) er dermed permanent bundet til tidsintervaller definert av den aktuelle raden i den endringstrege dimensjonstabellen. En aggregattabell som oppsummerer fakta basert på supplier state vil fortsette å gjenspeile den historiske staten (altså den staten som leverandøren holdt til i på den tiden transaksjonen ble utført) og dermed er ingen oppdatering nødvendig. For å referere til entiteten[klargjør] via naturlige nøkkelen er det nødvendig å fjerne unikhets-begrensninger, hvilket vil gjøre referanseintegritet av databasehåndteringssystemet umulig.
Dersom det gjøres tilbakevirkende endringer i innholdet i en dimensjon, eller dersom nye attributter legges til i dimensjonmen (for eksempel en Sales_Rep-kolonne) som har forskjelliger ikrafttredelsesdatoer enn de som allerede er definert kan dette føre til at de tidligere transaksjoner må oppdateres for å gjenspeile den nye situasjonen. Dette kan være en beregningsmessig dyr databaseoperasjon, og type 2 endringstrege dimensjoner er følgelig ikke et godt valg dersom dimensjonsmodellen er gjenstand for hyppige endringer.[1]
Type 3: legg til en ny attributt
[rediger | rediger kilde]Med en type 3 endringstreg dimensjon spores endringer ved hjelp av egne kolonner, og man beholder på denne måten begrenset historikk. Begrensningen ligger her på antall kolonner som er avsatt til lagring av historiske data. Tabellstrukturen kan ellers være ganske lik den som brukes for type 1 og type 2, men type 3 legger altså til en eller flere ekstra kolonner.
I eksempelet under har en ekstra kolonne blitt lagt til i tabellen for å ha historikk på staten hvor leverandøren opprinnelig hadde tilholdssted. Merk at dersom leverandøren flytter tilholdssted på nytt vil nåværende tilholdssted bli overskrevet, og har dermed ikke historikk over tidligere tilholdssteder med unntak av det første.
| Supplier_Key | Supplier_Code | Supplier_Name | Original_Supplier_State | Effective_Date | Current_Supplier_State |
|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA | 2004-12-22T00:00:00 | IL |
Historikken kan dermed ikke spores dersom leverandøren flytter tilholdssted en andre gang.
En variant av type 3 er å å opprette en kolonne for Previous_Supplier_State istedet for Original_Supplier_State. Dette vil medføre at man bare holder historikk over nåværende verdi og tilholdsstedet ved den forrige tilstandsendringen, og man kan altså bare spore nylige endringer.[1]
Type 4: legge til historikktabell
[rediger | rediger kilde]Med en type 4 endringstreg dimensjon benytter man "historikktabeller" hvor en tabell inneholder de nåværende versjonen av dataene, og en annen tabell holder oversikt av noen eller alle endringene som har blitt gjort. Begge surrogatnøklene blir refererte i faktatabellen for å hjelpe ytelsen under spørringer.
I eksempelet nedenfor er det opprinnelige tabellnavnet Supplier og historikktabellen heter Supplier_History:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State |
|---|---|---|---|
| 124 | ABC | Acme & Johnson Supply Co | IL |
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Create_Date |
|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | SERTIFISERINGSINSTANS | 2003-06-14T00:00:00 |
| 124 | ABC | Acme & Johnson Supply Co | IL | 2004-12-22T00:00:00 |
Denne metoden ligner på hvordan man går frem for å lage revisjonstabeller i databaser og fangst av dataendringer.
Type 5
[rediger | rediger kilde]Med en type 5 endringstreg dimensjon bygger man på en type 4 minidimensjon ved å bygge inn en minidimensjons-nøkkel for "nåværende profil" i basedimensjonen, som blir overskrevet som en type 1 attributt. Fremgangsmåten har fått navnet fordi det er en kombinasjon av type 1 og type 4, og 4+1 blir 5. Type 5 gjør det mulig at de nåværende tildelte attibuttverdiene for minidimensjonen å bli aksesserte sammen med basedimensjonens andre[klargjør] uten at man lenker disse via en faktatabell. Logisk kan man dermed representere basedimensjonen og den nåværende minidimensjons-profilutriggeren som én enkelt tabell i presentasjonslaget. Utrigger-attributtene bør ha distinkte kolonnenavn, som for eksempel "Current Income Level" for å differensiere dem fra attributter i minidimensjonen lenket til faktatabellen. ETL-teamet må sørge for å kontinuerlig oppdatering eller overskriving av type 1 minidimensjones-referansen når enn den nåværende minidimensjonen endrer seg. Dersom tilnærmingen med utriggere ikke tilfredsstiller behovet for ytelse under spørringer kan man gjøre minidimensjons-attributtene tiil en fysisk innebygd (og oppdatert) del av basedimensjonen.[3]
Type 6: kombinert tilnærming
[rediger | rediger kilde]Med en type 6 endringstreg dimensjon kombinerer man fremgangsmåtene for type 1, type 2 og type 3, og navnet kommer dermed fra at 1+2+3 = 6. Det er muligens Ralph Kimball som kom på begrepet under en samtale med Stephen Tempo fra Kalido.[trenger referanse] I boken Data Warehouse Toolkit kaller Ralph Kimball metoden for Unpredictable Changes with Single-Version Overlay, direkte oversatt "uforutsigbare endringer med enkelt-versjons overlegg".[1]
I følgende eksempel begynner leverandørtabellen med én oppføring for én leverandør:
| Supplier_Key | Row_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|---|---|
| 123 | 1 | ABC | Acme Supply Co | CA | CA | 2000-01-01T00:00:00 | 9999-12-31T23:59:59 | Y |
Det er her samme innhold i cellene Current_State og Historical_State. Den valgfrie attributten Current_Flag indikerer at dette er den gjeldende eller mest nylige oppføringen for denne leverandøren.
Når selskapet Acme Supply Co flytter til Illinois legges det til en ny oppføring på lignende måte som for type 2, men med forskjellen at en radnøkkel blir inkludert for å sikre at man har en unik nøkkel for hver rad.
| Supplier_Key | Row_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|---|---|
| 123 | 1 | ABC | Acme Supply Co | IL | CA | 2000-01-01T00:00:00 | 2004-12-22T00:00:00 | N |
| 123 | 2 | ABC | Acme Supply Co | IL | IL | 2004-12-22T00:00:00 | 9999-12-31T23:59:59 | Y |
Man overskriver så informasjonen i cellen Current_State for den første oppføringen (hvor Row_Key = 1) med den nye informasjonen, som ved type 1-prosessering. Deretter opprettes en ny oppføring slik at man kan spore endringer, som ved type 2-prosessering. Til slutt lagres historikken i en annen State-kolonne (her: Historical_State), hvilket innebærer type 3-prosessering.
Eksempelvis dersom leverandøren skulle flytte igjen kan man dermed legge til en annen oppføring i Supplier-dimensjonen, og man overskriver innholdet i Current_State-kolonnen:
| Supplier_Key | Row_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|---|---|
| 123 | 1 | ABC | Acme Supply Co | NY | CA | 2000-01-01T00:00:00 | 2004-12-22T00:00:00 | N |
| 123 | 2 | ABC | Acme Supply Co | NY | IL | 2004-12-22T00:00:00 | 2008-02-04T00:00:00 | N |
| 123 | 3 | ABC | Acme Supply Co | NY | NY | 2008-02-04T00:00:00 | 9999-12-31T23:59:59 | Y |
Type 2 / type 6 faktaimplementasjon
[rediger | rediger kilde]Type 2-surrogatnøkler med type 3-attributter
[rediger | rediger kilde]I mange implementasjoner av endringstrege dimensjoner med type 2 og type 6 blir surrogatnøkkelen fra dimensjonen puttet inn i faktatabellen istedenfor den naturlige nøkkelen når faktadataene blir lastet inn i data-oppbevaringsstedet (data repository).[1] Surrogatnøkkelen blir valgt for en gitt oppføring i faktatabellen basert på den effektive datoen, samt Start_Date og End_Date fra dimensjonstabelen. Dette gjør at faktadataene skal være lette å joine med de korrekte dimensjonsdataene for den korresponderende effektive datoen.
Under er Supplier-tabellen som ble opprettet ovenfor ved å bruke type 6 hybridmetoden:
| Supplier_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | NY | CA | 2000-01-01T00:00:00 | 2004-12-22T00:00:00 | N |
| 124 | ABC | Acme Supply Co | NY | IL | 2004-12-22T00:00:00 | 2008-02-04T00:00:00 | N |
| 125 | ABC | Acme Supply Co | NY | NY | 2008-02-04T00:00:00 | 9999-12-31T23:59:59 | Y |
Etterhvert når Delivery-tabellen inneholder den korrekte Supplier_Key kan den enkelt joines med Supplier-tabellen ved å bruke den nøkkelen. Den følgende SQL-koden henter ut for hver faktaoppføring både den nåværende staten og staten hvor leverandøren oppholdt seg ved tidspunktet for levering.
SELECT
delivery.delivery_cost,
supplier.supplier_name,
supplier.historical_state,
supplier.current_state
FROM delivery
INNER JOIN supplier
ON delivery.supplier_key = supplier.supplier_key;
Ren type 6-implementasjon
[rediger | rediger kilde]Det å ha en type 2-surrogatnøkkel for hvert tidssnitt (time slice) kan forårsake problemer dersom dimensjonen skulle endres.[1] En ren type 6-implementasjon benytter ikke dette, men bruker istedet en surrogatnøkkel for hvert grunndata-element (for eksempel kan hver unike leverandør ha én enkelt surrogatnøkkel). På denne måten unngår man at eventuelle endringer i grunndata har en innvirkning på eksisterende transaksjonsdata. Dette gir flere muligheter når man skal spørre transaksjonene.
Under følger en Supplier-tabell som bruker ren type 6-metodikk:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Start_Date | End_Date |
|---|---|---|---|---|---|
| 456 | ABC | Acme Supply Co | CA | 2000-01-01T00:00:00 | 2004-12-22T00:00:00 |
| 456 | ABC | Acme Supply Co | IL | 2004-12-22T00:00:00 | 2008-02-04T00:00:00 |
| 456 | ABC | Acme Supply Co | NY | 2008-02-04T00:00:00 | 9999-12-31T23:59:59 |
Følgende SQL-kode viser et eksempel på hvordan spørringen må utvides for å sikre at en enkelt Supplier-oppføring blir hentet for hver transaksjon:
SELECT
supplier.supplier_code,
supplier.supplier_state
FROM supplier
INNER JOIN delivery
ON supplier.supplier_key = delivery.supplier_key
AND delivery.delivery_date >= supplier.start_date AND delivery.delivery_date < supplier.end_date;
En faktaroppføring med en effektiv dato (Delivery_Date) på 2021-08-09 vil bli lenket til Supplier_Code "ABC" og Supplier_State "CA". Faktaoppføringen med en effektiv dato 2007-08-11 vil også bli lenket til den samme Supplier_Code "ABC", men med Supplier_State "IL".
Selv om denne tilnærmingen har en mer kompleks form har den også en rekke fordeler, inkludert:
- Referanseintegritet med databasehåndteringssystemet er nå mulig, men man kan ikke bruke Supplier_Code som fremmednøkkel i Product-tabellen, og bruk av Supplier_Key som fremmednøkkel i hvert av produktene er forbundet med et bestemt tidssnitt.[klargjør]
- Dersom det er mer enn en dato for et gitt faktum (for eksempel Order_Date, Delivery_Date, Invoice_Payment_Date) kan man velge hvilken dato som skal brukes i et søk.
- Man kan gjøre spørringer om hvordan tilstandene er nå, hvordan de var ved et transaksjonstidspunkt eller ved et annet tidspunkt ved å endre datafilter-logikken.
- Man trenger ikke å re-prosessere faktatabellen dersom det er en endring i dimensjonstabellen, eksempelvis dersom det blir lagt til felter retrospektivt hvilket endrer tidssnittene. Et annet eksempel er at man enkelt kan korrigere dersom man gjør en feil i datoene for dimensjonstabellene.
- Man kan innføre bitemporale datoer i dimensjonstabellen.
- Man kan joine faktaene med flere versjoner av dimensjonstabellen og dermed kan man lage rapporter på den samme informasjonen med ulike effektive datoer i én og samme spørring.
Følgende eksempel viser hvordan en bestemt dato, for eksempel '2012-01-01T00:00:00" (som kunne vært nåværende dato og tid) kan brukes:
SELECT
supplier.supplier_code,
supplier.supplier_state
FROM supplier
INNER JOIN delivery
ON supplier.supplier_key = delivery.supplier_key
AND supplier.start_date <= '2012-01-01T00:00:00' AND supplier.end_date > '2012-01-01T00:00:00';
Type 7: Hybrid med både surrogat- og naturlig nøkkel
[rediger | rediger kilde]En alternativ implementasjon er å plassere både surrogatnøkkelen og den naturlige nøkkelen i faktatabellen.[4] Dette muliggjør at brukeren kan velge riktige dimensjonsoppføringer basert på:
- den primære effektive datoen til faktaoppføringen (over),
- den mest nylige eller nåværende informasjonen, og
- alle andre datoer relaterte til faktaoppføringen.
Denne metoden muliggjør mer fleksible lenker til dimensjonen, selv om man har brukt en type 2-tilnærming istedet for type 6.
Under er Supplier-tabellen slik den kunne blitt laget med type 2-metodikk:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA | 2000-01-01T00:00:00 | 2004-12-22T00:00:00 | N |
| 124 | ABC | Acme Supply Co | IL | 2004-12-22T00:00:00 | 2008-02-04T00:00:00 | N |
| 125 | ABC | Acme Supply Co | NY | 2008-02-04T00:00:00 | 9999-12-31T23:59:59 | Y |
Den følgende SQL-koden henter den mest oppdaterte Supplier_Name og Supplier_State for hver av faktaoppføringene (fact records):
SELECT
delivery.delivery_cost,
supplier.supplier_name,
supplier.supplier_state
FROM delivery
INNER JOIN supplier
ON delivery.supplier_code = supplier.supplier_code
WHERE supplier.current_flag = 'Y';
Dersom det er flere datoer for en faktaoppføring kan man joine faktaene med dimensjonen ved å bruke en annen dato istedenfor den primære effektive datoen. For eksempel kan Delivery-tabellen ha en primær effektiv dato som heter Delivery_Date, men kan også ha Order_Date assosiert med hver oppføring. Følgende SQL-kode henter ut riktig Supplier_Name og Supplier_State for hver faktaoppføring basert på Order_Date:
SELECT
delivery.delivery_cost,
supplier.supplier_name,
supplier.supplier_state
FROM delivery
INNER JOIN supplier
ON delivery.supplier_code = supplier.supplier_code
AND delivery.order_date >= supplier.start_date AND delivery.order_date < supplier.end_date;
Noen forholdsregler:
- Referanseintegritet av databasehåndteringssystem er ikke mulig ettersom det er ikke er en unik nøkkel til å skape denne relasjonen.
- Dersom forholdet lages med en surrogatnøkkel for å løse problemet ovenfor ender man med entiteter som er knyttet til en bestemt tidperiode.
- Dersom join-spørringen ikke er skrevet korrekt kan man få returnert dupliserte rader og/eller feil svar.
- Dato-sammenligningen fungerer kanskje ikke like godt.
- Noen verktøy for forretningsinnsikt (business intelligence) håndterer generering av komplekse join-spørringer dårlig.
- ETL-prosessene som trengs for å skape dimensjonstabeller må være nøye utformet for å sikre at det ikke er overlapp mellom tidsperioder for hvert distinkte element av referansedata.[klargjør]
Kombinasjon av typer
[rediger | rediger kilde]
Ulike typer endringstrege dimensjoner kan brukes på forskjellige kolonner i en og samme tabell. For eksempel kan vi bruke type 1 til Supplier_Name-kolonnen og type 2 til Supplier_State-kolonnen i den samme tabellen.
Se også
[rediger | rediger kilde]- Fangst av dataendringer
- Temporal database, database som lagrer data knyttet til tidsforekomster
- Loggutløser, automatisk registrering av endringer som innsetting, oppdateringer eller sletting av rader i en databasetabell
- Vertikal entitet–attributt–verdi-modell, en type plasseffektiv datamodell
- Multitenancy (flerleie), en enkelt forekomst av en programvare som betjener flere leietakere
Referanser
[rediger | rediger kilde]- ^ a b c d e f g Kimball, Ralph; Ross, Margy. The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling.
- ^ http://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
- ^ https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
- ^ Ross, Margy; Kimball, Ralph (1. mars 2005). «Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3». Intelligent Enterprise.