0 comments
Business Intelligence 
Onlangs heb ik eens wat geëxperimenteerd met de verschillende manieren om een slowly changing dimension in SSIS te implementeren. De verschillende methoden die ik heb bekeken zijn:
· SCD wizard
· Lookup transformatie
· Merge Join transformatie
Tevens heb ik gekeken naar het verschil in performance bij het opbouwen van tijdelijke sets voor de updates tov. gebruik van het OLE DB command.
SCD wizard
De SCD wizard heeft duidelijk een groot performance probleem. Ik was daar in het verleden in de praktijk al eens tegenaan gelopen, maar had nooit echt gekeken waar het in zat. Mijn aanname was toen het gebruik van de OLE DB commands, maar dat blijkt niet zo te zijn (weer die aannames J). De SCD wizard genereert een deel van je SSIS dataflow, maar er blijft een klein deel ‘black box’ over. En voor zover ik nu kan zien, is dat de boosdoener. Het initieel laden van 100.000 records (bij een lege destination dimensie tabel), duurde bij mij bijna 6 minuten (tegen 6 seconden bij een zelf gefabriceerde dataflow!). Bracht ik daarna 1000 type 1 changes aan, 1000 type 2 changes en 1000 nieuwe records, dan had de SCD wizard na 10 minuten slechts een derde van die wijzigingen verwerkt (tegen 5 seconden voor mijn eigen brouwsel).
Conclusie: NOOIT de SCD wizard gebruiken in een productie omgeving!!!
Lookup
Gebruik van een lookup transformatie om te bepalen of records in de bron nieuw zijn of changes betreffen, blijkt de meest gebruikte methode in het veld (gebaseerd op reacties in blogs en forums op internet). De oplossing is ook iets eenvoudiger dan de methode met de merge join transformatie. En de performance is in dezelfde testomgeving als die ik eerder bij de SCD wizard beschreven heb uitstekend te noemen. In combinatie met het aanmaken van tijdelijke sets voor type 1 en 2 changes die in de control flow worden verwerkt, worden de 100.000 initiele records in 3 seconden verwerkt. In datzelfde tempo worden ook de 1000 type 1, 1000 type 2 en 1000 nieuwe records verwerkt.
Aandachtspunt bij lookups: de lookup transformatie laadt default alle records uit de referentie tabel in memory. Toen ik mijn testset vergrootte naar 1 miljoen records, liep mijn VPC dan ook snel uit virtueel geheugen. Als dat gebeurt, moet je bij de lookup over naar de niet gecachte variant, en dan lever je weer flink in op de performance. Zoveel dat, toen ik dat probeerde, mijn VPC zich helemaal ophing… (waardoor ik overigens helaas al mijn gefabriceerde testscriptjes kwijt ben geraakt L).
Merge Join transformatie
De merge join transformatie is wat ingewikkelder te configureren, maar heeft als voornaamste voordeel dat er geen extreem beslag op het geheugen wordt gelegd, zoals dat wel bij de lookup transformatie het geval is. Je merkt dat ook wel iets in de performance: de 100.000 initiele records worden in 6 seconden verwerkt, de 1000 type 1, 1000 type 2 en 1000 nieuwe records in 5 seconden. Ook dit weer bij gebruik van tijdelijke sets die in de control flow worden verwerkt. Een belangrijke eis van de merge join transformatie is gesorteerde input. Voldoe niet aan deze eis door er een sort transformatie voor te zetten, want dan heb je opnieuw een memory (en een gigantisch performance) probleem. De oplossing is te sorteren in je data source.
Ook de merge join transformatie liep bij mij vast toen ik er 1 miljoen rijtjes doorheen liet wandelen gevolgd door 10.000 type 1, 10.000 type 2 en 10.000 nieuwe rijtjes. Maar wel minder vast dan bij de lookup; ik kon nu het package nog normaal stoppen. Ik heb niet gekeken waar dit probleem door veroorzaakt wordt en helaas kan ik dat nu ook niet meer zonder weer een hele testomgeving op te tuigen (zie mijn opmerking bij de lookup die mijn VPC ophing). Op de weblog van Jamie Thomson wordt nog wel iets gezegd over locks waar je tegenaan kunt lopen bij het gebruik van de merge join transformatie en hoe je dat kunt omzeilen. Ook Jamie geeft wel aan dat de merge join transformatie de enige is die je kunt gebruiken wanneer je tegen memory problemen bij de lookup aan loopt.
OLE DB command
Gebruik van het OLE DB command om updates direct in de dataflow te doen ipv. in tijdelijke sets, blijkt veel minder performance impact te hebben dan ik tot nu toe dacht. Nadat ik mijn merge variant had omgebouwd naar gebruik van twee OLE DB commands voor de updates van de type 1 en 2 changes, werden de 2000 updates in 6 seconden verwerkt (tegen 5 seconden bij gebruik van sets). Uiteraard kan dit performance verschil groter worden bij grotere aantallen updates.
Algemene conclusies
1. Wil je maximale performance, ga dan voor een SCD traject dat gebruik maakt van een lookup transformatie in combinatie met het gebruik van tijdelijke sets.
2. Loop je bij het gebruik van de lookup transformatie tegen geheugenproblemen aan, ga dan over op de merge join transformatie.
3. Blijft het aantal updates in een dimensie beperkt tot enkele duizenden, verdient gebruik van het OLE DB command voorkeur boven het werken met tijdelijke sets, wat complexer is in het opzetten van het package.
Kun je hier een algemene best practice uithalen? Ik zou zelf kiezen voor de combinatie van 1 en 3: een lookup met OLE DB commands. Bij grote dimensies met veel wijzigingen, zou je dan als alternatief 2 kunnen gebruiken (merge + sets). In mijn opinie is het hierbij niet mogelijk ‘grote’ en ‘veel’ hard te maken. Dat hangt sterk af van de omgeving (vooral beschikbaar geheugen).
Na aanleiding van deze test en de uitslagen ervan, heb ik mijn SCD voorbeeld aangepast. Deze bevat nu drie packages. Eén met een merge transformatie in combinatie met tijdelijke sets, één met een lookup transformatie en tijdelijke sets en één met een lookup transformatie en OLE DB commands. Het voorbeeld vind je hier.