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

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

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

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

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

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

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
Supplier_Key	Supplier_Code	Supplier_Name	Supplier_State
124	ABC	Acme & Johnson Supply Co	IL

Supplier_History
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

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

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

Type 2-surrogatnøkler med type 3-attributter

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

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

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

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å

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

^ ^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.

Eksterne lenker

Forstå stjerneskjema og viktigheten for Power BI - Power BI | Microsoft Docs

Autoritetsdata

[KimballToolkit-1] ^ ^a ^b ^c ^d ^e ^f ^g Kimball, Ralph; Ross, Margy. The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling.

[2] ttp://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/

[3] ttps://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/

[SCDnot123-4] Ross, Margy; Kimball, Ralph (1. mars 2005). «Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3». Intelligent Enterprise.

[1]

[2]

[3]

[4]