Dimensie Lookup Optimalisatie

Voor het bijwerken van de dimensie/feit moet er worden bepaald of een record al bestaat. Dit gebeurt normaal gesproken door te kijken naar de business key (natural key) in de dimensie. Als deze niet gevonden wordt betreft het een nieuw record en dient deze te worden ge-insert. Als deze wel wordt gevonden en is veranderd dient het record te worden geupdate (type 1) of dient er een nieuw record te worden aangemaakt (type 2). Ik ben wat aan het expirementeren geweest om het proces van het doen van een lookup te versnellen. Hieronder mijn bevindingen tot nu toe.

Een lookup doen op een integer waarde gaat sneller dan een varchar. Alle business keys behandel ik in mijn Datawarehouse als varchar velden, vaak ook nog een combinatie van twee velden. Dit om fouten in de toekomst te voorkomen als men bijvoorbeeld besluit een letter toe te voegen aan een productcode: Voorbeeld:
–    DimElement: companycode + elementcode
–    DimUitvoerCode: companycode + uitvoercode

We kunnen ervoor kiezen om de bronsleutel in de dimensie direct samen te stellen en als een veld op te slaan, bijvoorbeeld 100_101 (companycode, elementcode). We kunnen dit ook niet doen en de lookup doen op deze twee velden. Mijn voorkeur gaat uit naar het samenstellen van deze twee velden, gevolgd door een underscore zodat de sleutel terug te herleiden is vanuit de bron.

Een extra mogelijkheid is een checksum/hash genereren over de twee velden heen. Het voordeel hiervan is dat er een lookup gedaan wordt op slechts een veld en de naamgeving van de bronsleutel hierdoor generiek wordt. Iedere bronsleutel noemen we dan bijvoorbeeld BronHash en is altijd van hetzelfde type en dezelfde lengte.

Checksum T-SQL functie VS SSIS Component
Checksum is een 4-bytes integer veld en dus kan er snel een lookup worden gedaan. Checksum werkt niet goed om over bepaalde kolommen een checksum te genereren omdat er duplicates kunnen optreden (bewezen). Het genereren van een checksum over de businesskey is niet betrouwbaar.

Als ik een CRC32 genereer over de drie kolommen die de business key vormen krijg ik veel duplicates checksums (bijvoorbeeld op een tabel uit een boekhoudprogramma).

SELECT A, COUNT(*) AS Aantal
FROM
(SELECT BINARY_CHECKSUM(cmpcode,doccode,docnum) as A
FROM dbo.oas_dochead_REG as DH
) AS X
GROUP BY A
HAVING COUNT(*) > 1

SSIS Component
http://www.sqlis.com/post/Checksum-Transformation.aspx

Het SSIS checksum component uit de community ondersteunt verschillende typen checksums.

CRC32
Een daarvan is de CRC32. Deze heb ik getest maar zorgt voor duplicates en is dus niet betrouwbaar. Dit treedt zowel op bij het genereren van een Checksum over een kolom als over meerdere kolommen.

Frameworkchecksum
Ook de frameworkchecksum techniek zorgt voor duplicates. Ik heb dit met 200.000 records getest en daar waren twee duplicates gegenereerd.

Original
De laatste zorgt na testen ook voor acht duplicated checksums en is ook niet geschikt.

Conclusie
De MD5 hashfunctie vanuit T-SQL zou ik natuurlijk kunnen gebruiken, alleen is deze ongeveer 50% langzamer omdat dit een varchar veld is. De checksum is een integer veld. In dat geval kan ik net zo goed een lookup doen op de bronsleutel, deze is immers nog kleiner.

Update:
In ons datawarehouse gebruiken wij een Binary(20) veld als LookupHash in de dimensies. Het voordeel hiervan is dat er een generieke naamgeving is ontstaan voor de bronsleutel. De lookup om de surrogatkey te verkrijgen bij het vullen van het feit kun je nu doen op het veld LookupHash. Nog een voordeel is dat een binary veld goed te indexeren is.

Kortom: tijdens het aanmaken van je dimensie genereer je altijd een LookupHash kolom van het type binary(20). Vervolgens doe je bij de JOIN de sleutel uit het feit ook omzetten naar dezelfde binary(20) over dezelfde kolommen, de uitkomst is nu hetzelfde als in de dimensie. Vervolgens kun je snel de surrogatkey verkrijgen!