Binnen ons Azure DWH Framework i.c.m. DevOps voor Automation hebben we de mogelijkheid toegevoegd om m.b.v. Point-in-Time (PIT) functies, historisch te kunnen query-en.
Nu denk je, dat is niet moeilijk toch? Klopt! Alleen wordt dit een stuk lastiger indien je bijvoorbeeld 4 tabellen zou willen combineren tot één resultaat (bijvoorbeeld voor een dimensie) en daarbij ook nog rekening te willen houden met tijd.
Hieronder geef ik een voorbeeld, maar allereerst, wat bedoelen we nu eigenlijk met ‘Point-in-Time‘?
Point in Time Analysis (Time Travel)
Unlike data audit, where the focus is typically on changes that occurred to an individual records, in time travel scenarios users want to see how entire data sets changed over time. Sometimes time travel includes several related temporal tables, each changing at independent pace, for which you want to analyze:
Trends for the important indicators in the historical and current data
Exact snapshot of the entire data “as of” any point in time in the past (yesterday, a month ago, etc.)
Differences in between two point in time of interest (a month ago vs. three months ago, for instance)
Als basis voor dit voorbeeld heb ik een aantal tabellen gebruikt uit de welbekende AdventureWorks database. Hieronder het gebruikte data model, inclusief enkele technische attributen uit ons Azure DWH Framework:
Om alle scenario’s te kunnen testen, heb ik handmatig delta’s aangemaakt binnen de tabellen. Zo zal ‘Customer’ nog geen data hebben, dit terwijl ‘SalesOrderHeader’ al wel aangeleverd en verwerkt is. Tevens wordt op een bepaald moment data verwijdert (in rood) binnen ‘SalesOrderDetail’ en ‘Customer’, gezien dit ook voor kan komen binnen een operationele database:
Allereest gaan we op zoek naar de drie regels binnen ‘SalesOrderHeader’, met daarbij de additionele attributen van de overige tabellen:
‘Customer’-data ontbreekt in het begin, maar de vraag is wat doen we met ‘Product’ nadat deze is verwijderd in het bronsysteem? Sales-rapportages zonder daarbij de productnamen zijn niet handig toch?:
Zowel ‘SalesOrderDetail’, ‘Customer’ als ‘Product’ hebben allen een paar wijzigingen gehad tussendoor, dit willen we wel kunnen aantonen in ons resultaat:
Maar wat doen we nu met ‘Product’ i.v.m. rapportages? Wel handig toch? In dit geval kiezen we voor ‘Latest’, omdat we geïnteresseerd zijn in de laatste stand van de data (ook al is deze inmiddels al verwijderd in het bronsysteem)
Toch is onze basis ‘SalesOrderHeader’, gezien we geïnteresseerd zijn in Sales-resultaten.
Het eindresultaat ziet er dan zo uit:
Hieronder een voorbeeld met leesbare data, een mooie basis om verder uit te bouwen.
Vergeet je niet om NULL-waardes te vervangen voor de cube? (zie ‘Territory’):
De query zelf? Leesbaar en zeer bruikbaar als template voor andere dimensies en queries:
Hieronder een voorbeeld van een historische SCD Type 6 dimensie:
Interesse in een live demo bij u op locatie? Neem dan contact met ons op: clint.huijbers@monkeyconsultancy.nl
