Surrogaatsleutel (surrogate key)

Wat is een surrogaatsleutel?

Een surrogaatsleutel is een kunstmatig getal dat je warehouse zelf toekent aan elke rij in een dimensietabel. Het heeft geen enkele betekenis, je kan er niets aan aflezen. Het is gewoon een oplopend nummer (1, 2, 3, ...) dat dient als primaire sleutel van de dimensie en als de sleutel waarmee de feittabel naar die rij verwijst.

Het alternatief is de natuurlijke sleutel of businesskey: de identificatie die het bronsysteem al gebruikt, zoals een klantnummer, een productcode of een BTW-nummer. Ralph Kimball omschrijft een surrogaatsleutel dan ook als een kunstmatige sleutel die je in de plaats stelt van die natuurlijke sleutel. In een ster-schema krijgt elke dimensie een surrogaatsleutel als primaire sleutel, en verwijst elke feitrij naar de dimensie via dat getal, nooit via het klantnummer zelf.

Je kan het vergelijken met een volgnummer dat je krijgt bij binnenkomst. Het zegt niets over wie je bent, het houdt enkel de koppeling met je dossier bij. Twee mensen met dezelfde naam krijgen toch een verschillend nummer.

Surrogaatsleutel versus natuurlijke sleutel

De verleiding is groot om gewoon het klantnummer uit het bronsysteem als primaire sleutel te nemen. Dat werkt, tot het bronsysteem iets doet waar jij geen controle over hebt. Kimball noemt drie klassieke problemen: de bron kan een oude code opruimen en later opnieuw uitdelen aan iets anders, de bron kan per ongeluk een verkeerde code toekennen, en de bron kan het formaat van zijn sleutels wijzigen om een nieuwe situatie op te vangen.

Neem een ERP dat productcode P-100 schrapt zodra een artikel uit het gamma verdwijnt, en die code een jaar later hergebruikt voor een ander product. Gebruik je P-100 als primaire sleutel, dan smelten twee verschillende producten samen tot één rij en tellen hun verkopen door elkaar. Met een surrogaatsleutel krijgt elk product zijn eigen getal, en blijft P-100 een gewone kolom die twee keer mag voorkomen.

De surrogaatsleutel ontkoppelt je warehouse dus van de grillen van de bron. De natuurlijke sleutel blijft bestaan, maar zakt naar een gewoon attribuut op de dimensierij: je bewaart hem om nieuwe brondata terug te vinden, niet om er de tabel op te bouwen.

Waarom een warehouse surrogaatsleutels gebruikt

Naast de ontkoppeling van de bron zijn er nog een paar concrete redenen waarom dit de standaard is in dimensioneel modelleren.

• Ze maken SCD Type 2 mogelijk. Zodra je historiek bijhoudt met Slowly Changing Dimensions Type 2, bestaat er van dezelfde klant meer dan één rij: een versie per periode. Die versies delen hetzelfde klantnummer, dus dat kan onmogelijk de primaire sleutel zijn. Elke versie heeft een eigen surrogaatsleutel nodig om uit elkaar gehouden te worden.

• Kleinere en snellere joins. Een geheel getal van vier bytes dekt al meer dan twee miljard waarden, veel compacter dan een lange alfanumerieke code of een samengestelde sleutel. Omdat die sleutel in elke feitrij meegaat en een feittabel al snel miljoenen rijen telt, scheelt dat merkbaar in opslag en in de snelheid van de joins.

• Een plaats voor speciale rijen. Soms ken je de dimensie nog niet, of is ze niet van toepassing. Met surrogaatsleutels reserveer je vaste rijen voor "Onbekend", bijvoorbeeld op sleutel -1, zodat elke feitrij naar een geldige dimensierij verwijst. Dat is precies wat een late arriving dimension nodig heeft: een tijdelijke rij met een echte surrogaatsleutel terwijl de echte gegevens nog moeten toekomen.

• Integratie van meerdere bronnen. Fuseren twee bedrijven, dan komt dezelfde klant uit twee systemen met twee onverenigbare klantnummers. De surrogaatsleutel geeft je één neutrale sleutel om beide onder te brengen.

Een voorbeeld met SCD Type 2

Klant met klantnummer K-4471 woont in Hasselt. In je klantdimensie staat één rij: surrogaatsleutel 8801, natuurlijke sleutel K-4471, stad Hasselt. Alle verkopen van die klant verwijzen in de feittabel naar 8801.

Op 1 maart verhuist de klant naar Gent. Omdat stad een Type 2 attribuut is, overschrijf je de oude rij niet. Je sluit rij 8801 af en maakt een nieuwe rij: surrogaatsleutel 8802, opnieuw natuurlijke sleutel K-4471, stad Gent. Dezelfde klant, twee rijen, twee surrogaatsleutels.

Vanaf dat moment krijgen nieuwe verkopen surrogaatsleutel 8802 mee. De oude verkopen blijven onaangeroerd naar 8801 wijzen. Vraag je later een rapport op van vorig jaar, dan hangt die omzet nog steeds aan de Hasselt-versie, want die was toen actief. Het klantnummer K-4471 kon dit niet, dat is voor beide versies identiek. De surrogaatsleutel scheidt de twee momentopnames van dezelfde klant.

Waar moet je op letten bij het gebruik van surrogaatsleutels

Genereer ze in het warehouse, niet in de bron. De hele bedoeling is dat de sleutel losstaat van het bronsysteem. Laat je laadproces het getal toekennen, bijvoorbeeld via een identity-kolom of een index in Power Query, en laat de bron er nooit invloed op hebben.

Bewaar altijd de natuurlijke sleutel als kolom. Je hebt de businesskey nog nodig om binnenkomende brondata aan de juiste dimensierij te koppelen. Gooi hem niet weg, hij verhuist enkel van primaire sleutel naar gewoon attribuut.

Stop geen betekenis in het getal. Een surrogaatsleutel waar je regio of jaartal in probeert te verwerken, is geen surrogaatsleutel meer. Op het moment dat die logica verandert, zit je vast. Houd het een leeg, oplopend nummer.

Voorzie je speciale rijen vooraf. Een rij voor "Onbekend" op een vaste sleutel bespaart je lege verwijzingen in de feittabel. Leg ze aan bij het opzetten van de dimensie, niet pas wanneer het misloopt.