De datastreams omvat veel data. Deze data kun je natuurlijk elke keer binnen halen als volledige dump waarin je de volledige histiorie aan data binnenhaalt. Maar het is ook mogelijk om alleen mutaties ten opzichte van de vorige dump te ontvangen. Dit laatste is natuurlijk veel efficienter, bespaart dataverkeer en zorgt ervoor dat je sneller je eigen rapportage omgeving kan bijwerken.

Toch merken we dat veel klanten liever een volledige dump ontvangen omdat ze aangeven dat een incrementele dump moeilijk is of dat ze niet goed weten hoe ze die moeten verwerken.

In dit artikel leggen we daarom graag uit hoe je een incrementele dump kunt verwerken en dat het helemaal niet ingewikkelder hoeft te zijn dan het verwerken van een volledige dump. Sterker dit patroon kun je ook gebruiken om een volledige dump te verwerken.

Allereerst een kleine toelichting op de technische velden van de datastreams.

Alle bestanden in de datastreams hebben technische sleutels. Vrijwel altijd te herkennen aan de prefix:  DIM of Fact.  

Deze sleutels hebben enkel betekenis binnen de datastreams om bestanden aan elkaar te koppelen.  Er kan dus geen enkele andere betekenis aan verbonden worden. Zie ons artikel Datamodel Datastreams.

Alle bestanden hebben daarnaast nog een Primarykey en een Recordkey. De PrimaryKey is een hash van de identificerende kolommen. Dat betekent dat een regel in een bestand over aanleveringen heen altijd herkend kan worden aan de PrimaryKey. De RecordKey is een Hash over de beschrijvende attributen daarmee kan herkend worden of een regel over aanleveringen heen is veranderd.

Met deze twee velden is het mogelijk om te herkennen of een record al bestaat en bijgewerkt moet worden (update) of nog niet bekend is en dus toegevoegd moet worden (insert). In dit artikel beschrijven we een patroon waarin we beide acties combineren en dat we ook wel een UpSert pattroon noemen. Een Upsert pattern is eenvoudig toe te passen om een datawarehouse bij te werken met een incrementele load of een full load. 

Het pattern is als volgt: Maak voor elk bestand een Target tabel. Dit is de tabel die je wil gebruiken om alle mutaties over tijd in te verzamelen. Voeg behalve alle kolommen uit het bestand een kolom FirstSeenDate  en een LastModifiedDate  kolom toe. Daarmee heb je iets meer informatie over wanneer een regel voor het eerst is gezien en wanneer deze is bijgewerkt.

Vervolgens lees je de bestanden in naar een stage tabel. Daarna kun je het volgende patroon gebruiken, in deze voorbeelden is TargetKolomN een kolom uit de Targettabel en SourceKolomN dezelfde kolom uit de sourcetabel. We hebben hier bewust abstracte namen gekozen zodat je het patroon eenvoudig kunt toepassen op alle bestanden.

Stap 1:    Bijwerken van gewijzigde records.

UPDATE Target

SET

LastModifiiedDate = getdate()

, TargetKolom = sourcekolom

, TargetKolom2 = sourcekolom2

, ....

from TargetTabel AS TARGET

inner join StageTabel AS SOURCE

ON target.PrimaryKey = source.PrimaryKey

where isnull(target.RecordKey, '') <> isnull(source.RecordKey, '')

INSERT into TargetTabel (FirstSeenDate ,LastModifiedDate , Targetkolom, Targetkolom2,..)

Select getdate(),getdate() , Sourcekolom, Sourcekolom2,...

from TargetTabel AS TARGET

right join StageTabel AS SOURCE

ON target.PrimaryKey = source.PrimaryKey

where target.PrimaryKey is null

Als je een Incrementele dump hebt ontvangen dan ben je nu klaar. In een incrementele dump worden Deletes, records die zijn verwijderd uit VX ook meegegeven in de dump. Deze hebben de Primarykey die ze altijd hebben gehad en een Recordkey met de waarde "deleted" en alle attributen zijn NULL. Als je bovenstaand patroon hebt toegepast is je Target tabel dus up to date. Misschien moet je de Delete records er nog uit filteren bij verdere verwerking (of verwijderen afhankelijk van hoe je ermee om wil gaan).

Als je een volledige dump hebt ontvangen dan heb je geen Deletes ontvangen en moet je de deletes nog zelf herkennen met de volgende code:

UPDATE target

  SET

           RecordKey = 'Deleted'

,           LastModifiiedDate = getdate()

,          TargetKolom = null

,          TargetKolom2 = null

,           ....

from  TargetTabel   AS TARGET

left join  StageTabel    AS SOURCE

    ON target.PrimaryKey = source.PrimaryKey

where target.PrimaryKey is null