Blog

Toepassing van code generatie op basis van metadata en de PowerDesigner Generation Templating Language

Binnen mijn werkveld is het voldoen aan / houden aan code-standaarden een grote uitdaging, vaak getracht door het opzetten van processen waar collegiale reviews en kennissessies binnen scrum teams centraal staan. Meestal wordt dit niet technisch afgedwongen en daarmee ontstaat het risico om gauw uit de pas te lopen over tijd. Niet alles valt te standaardiseren, elke ontwikkelaar werkt immers ook op zijn eigen manier. Wel heb ik ondervonden in mijn recente werkervaring hoe dit effectief af te dwingen is.

In deze post leg ik een toepassing uit bij een recente klant in de financiële dienstverlening door gebruik te maken van code templates in SAP Powerdesigner.

Een klein stuk context. Deze organisatie gebruikt een Teradata database i.c.m. eigen ontwikkelde software voor het orkestreren, uitvoeren en managen van ETL jobs voornamelijk bestaande uit SQL code in een repository, vaak aangestuurd op basis van metadata.

Mijn werkzaamheden

In de afgelopen zes maanden heb ik als data engineer gewerkt om een datamart conform een dimensioneel model op te zetten. Deze datamart gebruikt een interface database als bron, die wordt ingezet als uitleverpunt tussen het data-vault integratie model en diverse afnemers (diverse bedrijfsapplicaties en dus ook deze datamart).

Dit betekent ook dat alle business-rules en specifieke selecties geïsoleerd worden opgezet in deze interface database. Alle afnemers maken gebruik van dezelfde logica. De datamart dient er voor om historie (conform principe SCD2) op te bouwen t.b.v. historisch inzicht in de data voor rapportages. Ook betekent dit dat er geen business-rules (bijvoorbeeld afgeleide velden) worden gemaakt in de datamart, al dit soort zaken gebeuren in de interface database.

De opzet van de datamart is generiek, hetzelfde trucje wordt immers voor elke tabel zo toegepast. Hierdoor zag ik de toegevoegde waarde van automatisering op basis van templates al snel. Dit heb ik mogelijk gemaakt door de datamodelleringstool SAP Powerdesigner uit te breiden met eigen (custom) functionaliteit. Doorgaans wordt deze tool gebruikt voor het vastleggen van objectdefinities van een database.

Deze gegevens zijn beschikbaar in een uitvoerig gedocumenteerd metamodel en zijn dus op zichzelf als data ook op te vragen om bijvoorbeeld SQL scripts mee te maken.

Powerdesigner extentions

Powerdesigner is dus uit te breiden door gebruik te maken van zogeheten extensions. Deze extensions stellen je in staat om de vastgelegde metadata te bevragen en te gebruiken in scripts Hierdoor is het mogelijk code te genereren. Deze scripts zijn een combinatie van Visual Basic en een eigen taal, de Generation Templating Language (GTL).

Generation Templating language

De Generation Template Language (GTL) gebruik je om het metamodel te bevragen om zo object eigenschappen beschikbaar te hebben voor code in een template. De templates worden gegenereerd op basis van zogeheten generated files. Een specifieke alinea is gewijd aan uitleg over de generated files en hoe deze te configureren.

GTL templates bevatten tekst, macro’s en variabelen en kunnen het volgende opvragen:

  • Metamodel attributen zoals de naam van een object, type tabel (fact / dimension) of owner van een object
  • Lijstjes zoals een opsomming van kolommen van een tabel
  • Overige eigenschappen binnen het model zoals omgevingsvariabelen en zelfs eigen toegevoegde ‘Extrended Attributes’.

Vrijwel alle metadata die je over een object op kan geven is beschikbaar in GTL om op te vragen. Onderstand een voorbeeld van een CREATE TABLE statement op basis van GTL.

Afbeelding 1 – Voorbeeld template als CREATE TABLE statement

Opvragen object metadata

Object metadata wordt in templates opgevraagd als variabelen, de syntax is % <variabele naam> %, ingesloten in een procent teken dus. De variabele naam is hoofdletter gevoelig en als standaard wordt vaak CamelCase notatie gebruikt. De volgende datatypes worden teruggegeven

  • String.
  • Boolean.
  • Object (ObjectID)

Voorbeeld

Resultaat

Tabel %Code% is onderdeel van database %Owner.Code% en is van type %SetOrMultiset%.

Tabel FEIT_HYPOTHEEKDEEL is onderdeel van database DM_BOM en is van type MULTISET.

Extended attributes

Extended attributes zijn extra metadata objecten, dit ter aanvulling op de meta attributen die out-of-the box beschikbaar zijn (zoals kolomnaam, datatype, lengte etc).

In onderstaand voorbeeld zie je een toepassing; %SetOrMultiset%. Dit is een eigen gemaakt metadata attribuut waarin bijgehouden wordt of een tabel als MULTISET of SET aangemaakt moet worden. Dit betekent eigenlijk of de tabel duplicates (multiset) of slechts unieke records (set) mag bevatten. Doorgaans zijn inserts op een multiset tabel sneller.

.

Afbeelding 2 – Voorbeeld extended attribute voor tabel FEIT_HYPOTHEEKDEEL

Generated files

In onderstaand voorbeeld zie je de eigenschappen van een extension. Een view / tabel modelleer je altijd in een model, het model kent ook zijn eigen specifieke metadata. Je begint met het maken van een generated file binnen het type object. Op deze manier ‘koppel’ je de template aan een view of tabel.

Afbeelding 3 – Voorbeeld opzet generated file

De generated files zorgen ervoor dat er .sql bestanden gemaakt worden voor de objecten waar je deze voor gekoppeld hebt. De inhoud van de generated file (binnen de extensie) is slechts een verwijzing naar de naam van het template (zie afbeelding 4).

Daarnaast kan de output van een generated file een fysiek .sql bestand opleveren wat door een CI/CD pipeline opgepakt kan worden voor toepassing (deployment) op een target omgeving.

Afbeelding 4 – Generated Files setup met verwijzing naar template ‘create_etl_view’

Het resultaat van deze koppeling en verwijzing naar een template is te zien bij een preview van de gegenereerde code in het object. Onderstaand een voorbeeld voor een bepaalde view.

Afbeelding 5 – Generatie resultaat binnen Preview tab

De toepassing binnen het project

Zoals eerder aangegeven gebeurt er in de datamart binnen dit project steeds hetzelfde, namelijk het toepassen van SCD2 voor alle tabellen. Daarnaast worden extra velden gemaakt zoals sequence nummers, row hashes, timestamps, allemaal attributen die een specifieke load / record identificeren. Alle custom selecties, aggregaties en de toepassing van business rules zit in de voorliggende interface database.

In feite bestaat de datamart uit een viertal lagen, elk met een eigen specifiek standaard doel.

Voor elke laag is dit doel ook hetzelfde. Deze lagen worden opgezet in bovenstaande volgorde en dienen als input voor elkaar, bijvoorbeeld de ETL-view heeft als bron de source-view. De source-view heeft de interface database als bron.

Source views

De source views worden gebruikt als ontkoppelpunt met de interface database, zodat de objecten die in scope zijn voor de datamart ook as-is bekend zijn op die database. De data wordt niet verplaatst naar de datamart, gezien het in een view is toegepast. Nogmaals, er wordt geen logica in deze views gestopt in deze use case.

Het template en het uiteindelijke gegenereerde resultaat voor een SOURCE_VIEW ziet er als volgt uit;

Afbeelding 6 – Template voor de source view

Afbeelding 7 – Generatie resultaat vanuit template

ETL views

De tweede laag die gemaakt wordt zijn de ETL views. In deze views worden nieuwe datamart specifieke metadata attributen geïntroduceerd. Dit zijn;

  • DM_SEQ ( uniek sequence nummer voor een record in een groep)
  • HASH_VALUE (unieke samenstelling record in SHA1 hash functie)
  • RUN_ID_INSERT (welke load heeft dit record toegevoegd)
  • RUN_ID_UPDATE (welke load heeft dit record aangepast)
  • FROM_DATE (CURRENT_TIMESTAMPAS FROM_DATE)
  • UNTO_DATE ( CAST('99991231' AS DATE FORMAT 'y4mmdd') AS UNTO_DATE)
  • DM_BOM_TASK_ID (identificatie van een etl proces in de scheduler)

Deze velden worden reeds in de ETL views gemaakt ter voorbereiding op het inserten in de tabel in de volgende laag van de DataMart. In de template ziet dit er als volgt uit (screenshot onvolledig i.v.m. lengte van script) ;

Afbeelding 8 – Snippet uit template ETL_VIEW

Uiteindelijk zoals onderstaand gegenereerd;

Afbeelding 9 – Snippet uit generatie resultaat ETL_VIEW

Merge tables

Deze laag bevat MERGE statements, die er voor zorgen dat records geïnsert danwel geüpdatet kunnen worden conform een vastgestelde conditie. Het MERGE script gebruikt de voorgaande laag (ETL-view) als SOURCE en de fysieke tabel als TARGET. Met andere woorden, deze laag dient er voor om te controleren of een record reeds aanwezig is. Deze check wordt gedaan op basis van een match op kolom HASH_VALUE (de unieke instance van een record), aangevuld met verplichte kolommen zoals de Primairy Index kolom (DATUM_RAPPORTAGE_OID) en de PARTITION kolom.

Onderstaand een screenshot van het MERGE statement wat gebruikt wordt.

Afbeelding 10 – Snippet uit template MERGE TABLE

Het veld DM_SEQ (een sequence nummer gegeven aan een record) wordt gebruikt om op basis van de GROUP BY op basis van de business-key te weten welk record de nieuwste is. Dit is nodig om het juiste record te kunnen UPDATEN voor het toepassen van een juiste einddatum in veld UNTO_DATE. Onderstaand een screenshot van de uitwerking, zie bijvoorbeeld de WHERE clausule voor DM_SEQ.

Afbeelding 11 – Snippet uit UPDATE gedeelte MERGE_TABLE template

Het uiteindelijke resultaat voor een tabel ziet er als volgt uit;

Afbeelding 12 – Generatie resultaat MERGE_TABLE

Data delivery

Deze laatste laag is een template die een eenvoudige SELECT op een tabel uitvoert. Hier worden tevens Engelse vertalingen vastgelegd conform de requirements van gebruikers.

Afbeelding 13 – Data delivery view (ook APPL_VIEW genoemd in de datamart)

Afbeelding 14 – Gegenereerd resultaat

Opvragen templates voor alle objecten binnen een domein

Uiteindelijk wil je alle objecten binnen een model laten genereren. In Model is het overkoepelende object waar views en tabellen onderdeel van zijn. Dit is altijd een 1 op 1 verhouding. Een model kent ook eigen templates, wat bijvoorbeeld een loop bevat op de child-objecten. Onderstaand een voorbeeld hoe dit is toegepast voor de APPL_VIEW laag:

Afbeelding 15 – For each loop voor APPL_VIEW’s

Dit resulteert in een gegenereerd script met alle APPL_VIEW’s onder elkaar. Dit komt door de newline character in de foreach loop ( \n, \n ).

Afbeelding 16 – Gegenereerd resultaat APPL_VIEW’s op model

De toepassing van templates binnen Powerdesigner t.b.v. het genereren van code kan heel ver gaan. Ik realiseer me goed dat niet alles in templates te vatten is, maar deze werkwijze heeft mij wel aan het denken gezet. Voor elke vaak voorkomende repetitieve operatie ben ik van mening dat de effort beter kan worden gestoken in automatisering zoals het gebruiken van templates in plaats van alle scripts handmatig uit te werken.

Initieel is dit wellicht wat meer effort, maar op termijn pluk je daar al gauw de vruchten van. Op deze manier zijn code standaarden, commentaar in code, algemene syntax, naamgeving etc allemaal technisch afgedwongen. Een direct gevolg van deze methode is ook de verplichting om alle metadata vast te leggen, het werkt immers niet zonder.

Door: Joerie Brugts

Data Engineer / BI Consultant @ Conspect Consulting & ICT