Consolidatie (dwh)

Met consolidatie bedoel ik het samenvoegen van data uit meerdere bronnen, waarbij deze bronnen min-of-meer dezelfde data bevatten, maar dverschillen qua kwantiteit en/of kwaliteit.

Wat is de beste manier om dit te doen? In de loop der jaren heb ik redelijk uiteenlopende methodes ontwikkeld. Het is een fascinerend vraagstuk rondom informatie en entropie.

Beginnetje april 2020

Ik denk dat deze uitleg niet accuraat is, maar het zet me wel aan het denken ;)

Eerst sleutels, dan de inhoud

• Defineer een tijdelijke doeltabel, met alleen het veld dat later de PK gaat worden, en velden waarover geaggregeerde functies moeten worden toegepast (bv. optellen)
• Voeg hierin alle gegevens
• Defineer een tweede tabel, nu wél met de actieve PK
• Kopiëer alle info uit de tmp-tabel, pas hierbij de aggregaties toe
• Voeg ontbrekende data in, vanuit de verschillende brontabellen.

Aanvullen, van goed naar slecht

• Defineer gelijk de doel-tabel
• Begin met de meest betrouwbare doeltabel om data te kopiëren naar de doeltabel
• Ga verder met de overige tabellen, van meest betrouwbaar naar minst betrouwbaar. Uiteraard wordt data hierbij niet overschreven, maar alleen aangevuld.

Eerst sleutels, dan veld-voor-veld

Variant op het eerste algoritme:

• Verzamel eerst de SKU's plus eventuele agreggatie-velden
• Vul daarna veld-voor-veld in vanuit de verschillende brontabellen, bv. mbv. coalesce

Deze aanpak is meer werk dan de eerste variant, en ik betwijfel of het eigenlijk beter is: Door individuele velden los van de bijbehorende records te kopiëren, krijg je misschien minder betrouwbare data - Hangt waarschijnlijk van de situatie af.

Overloaden van slecht naar goed

Ik weet niet goed hoe ik dit zou implementeren, maar dit is het idee:

• Begin met de minst betrouwbare data
• Overload het daarna, waarbij data wordt overschreven ipv. aangevuld.

Wat brengt de meeste informatie?

Waarschijnlijk de centrale vraag bij alles rondom consolidatie en/of datwarehousing: Wat brengt de meeste informatie?

Bijkomend probleem: Vaak is de betrouwbaarheid van de brondata niet vast te stellen.

Complete records vs. losse velden

Ik moet het nog een goede naam geven en het nog uitwerken, maar dit lijkt één van de meest basale principes te zijn:

Vind consolidatie plaats dmv. complete records die worden overgenomen, of door losse velden?

Dit lijkt iets weg te hebben van Mason's Rule (als ik de naam goed herinner) uit digitale techniek: Je kunt een expressie opbouwen met AND's of met OR's + NOT's - of zoiets :)

In de praktijk zal het vaak een combinatie zijn van complete records en losse velden. Als voorbeeld de vorkheftruck-casus uit juli 2020:

Vorkheftruck-casus juli 2020

• Complete records: In beginsel waren er drie tabellen met min-of-meer complete records: Al deze tabellen hadden zo'n acht velden, die als geheel overgenomen konden worden. Daarnaast hadden deze drie tabellen verschillende niveaus van betrouwbaarheid. Daarom zijn eerste alle acht velden uit de meest betrouwbare tabel (f08) overgenomen. Daarna - vermits de pk nog beschikbaar was - dezelde complete records uit de overige drie tabellen
• Losse velden: Er was één veld dat los stond van de 'complete records': De prijzen. Die stonden primair in tabel manomano (deze tabel was verder nergens geschikt voor, want onbetrouwbaar) met eventuele aanvullingen in één van de eerdere drie tabellen.

Wat brengt de meeste informatie?

Waarschijnlijk elke keer weer hetzelfde vraag: Wat brengt de meeste informatie?

• In de casus hiervoor was het duidelijk dat de complete records goede informatie bevatten tav. de betreffende acht velden
• Er zijn situaties waarin het moeilijk is in te schatten waar complete records vs. losse velden het beste werkt, omdat de kwaliteit van de achterliggende data moeilijk is in te schatten.

Casus juli 2020: Vier tabellen met forklifttruck-data

Dit is een relatief gemakkelijk en overzichtelijke cases.

Bronnen

Ik heb vier brontabellen, met de volgende karakteristieken:

                          #
  Tabel- & codenaam     Rec.  Betrouwheid
  -------------------   ----  ------------
* forklift_2008 - f08   799   1    
* forklift_2011 - f11   866   3
* forklift_2020 - f20   793   2
* manomano_2020 - m20   522   3: Secundair: Wrsch. alleen relevant voor prijsinformatie

Doel

Een geconsolideerde tabel f (of set geconsolideerde tabellen als dat handiger is) waarvoor geldt:

1. Zo compleet mogelijk
2. Zo accuraat mogelijk

Tav. compleetheid:

• De eerste drie tabellen bevatten verschillende hoeveelheden records. De meest betrouwbare bron bevat daarnaast niet eens het grootste aantal records
• Alle drie de primaire bronnen bevatten serialised data → Explode (MySQL)
• Vermoedelijk zullen er tegen de 1.000 records resulteren.

Aanpak - Principes

• De drie 'echte' brontabellen moeten opgeschoond worden. Dat doe ik voor het grootste gedeelde voordat ik ze ga samenvoegen. Dat betekent dat sommige werkzaamheden drie keer uitgevoerd moet worden. Het is aanlokkelijk om opschonen pas te doen na consolidatie, maar ik denk dat dat per saldo toch lastiger wordt, omdat ik dan minder overzicht heb. Ik denk dat ik het prettig vind om gelijk aan de basis te voorkomen dat ik met onnodig slechte data aan de slag ga. Daarnaast is het wellicht mogelijk om dit opschonen in een sproc onder te brengen, zodat het op alle drie de brontabellen toegepast kan worden zonder dat dit veel extra werk met zich meeneemt. Ik vereist trouwens wel dat de brontabellen min-of-meer gelijkvormig zijn. Dat kan tegenvallen
• In dit geval is er één bron die flink beter is dan andere bronnen. Daarom wordt de doeltabel eerst volledig gevuld met data uit deze bron
• Daarna worden eventuele additionele records toegevoegd uit bron2 en daarna bron3
• Data uit minder-betrouwbare bronnen, mag nooit data uit betrouwbaardere bronnen overschijven: Bron3 mag bron2- en bron1-data nooit overschrijven en bron2-data mag nooit bron1-data overschrijven
• Data uit minder-betrouwbare bronnen, mag data uit betrouwbare bronnen wel aanvullen - waarschijnlijk treedt dat hier niet op

Aanpak - Concreet

1. f8 opschonen
2. f20 opschonen
3. f11 opschonen
4. Vorm doeltabel f
5. Vul f zo volledig mogelijk met data uit f08
6. Voeg eventuele additionele records toe uit f20
7. Voeg eventuele additionele records toe uit f11
8. Voeg prijzen in vanuit m20
9. Voeg prijzen in vanuit f11
10. (Vul records aan vanuit f20)
11. (Vul records aan vanuit f11)
12. Geconsolideerde data opschonen

In dit geval spelen de laatste twee stappen geen rol - En da's bepaald een mazzeltje. Waarschijnlijk kom ik in de toekomst situaties tegen waarin de bronnen allemaal even onbetrouwbaar zijn, en dan kan dit een interessant puzzeltje zijn. Of situaties waarin sommige bronnen betrouwbaarder zijn mbt. bepaalde kolommen.

Principes achteraf - Begin met de meest betrouwbare data

Het klinkt als een open deur, om met de meest betrouwbare data te beginnen, maar dat is het toch niet: Sommige algorithmes met overloading werken juist andersom: Ik populate iets eerst met de slechtse data, en daarna overload ik dit met betere data. In dit geval was beginnen met de meest betrouwbare data hoe dan ook een gemakkelijk uitgangspunt.

Principes achteraf - Data overwegend vantevoren opschonen

Er waren aardig wat situaties waarbij ik de data toch ben gaan opschonen na samenvoegen. Dat bleek een paar keer onthutstend complex te zijn:

• PK-conflicten brontabel: De brontabel had een PK, zodat ik mbv. insert ignore op een gemakkelijke maniere unieke data overhield. Echter, als ik naderhand opschoonde, ontstonden er doublures. Om dit op te ruimen, zou ik waarschijnlijk moeten gaan stunten met verwijderen van de PK, of met een nieuwe doeltabel - Nogal ontluisterend
• Moeilijk te localiseren: Bij sommige fouten was het lastig te localiseren waar ik dat nou het beste naderhand had kunnen opschonen - Een hoop gedoe. Vantevoren opschonen is daarintegen zo klaar als een klontje
• Kip-en-ei-probleem: Ik had te maken met de velden brand, orgbrush en brand_orgbrush. Sommige brontabellen hadden alleen de eerste twee tabellen, en sommige alleen de laatste tabel. Na samenvoegen en opschonen, wordt er wat gegoocheld met die drie tabellen, zodat ze allemaal ok zijn. Als er een fout in brand_orgbrush zat, werd het heel ingewikkeld om dat rond te krijgen - Veel gemakkelijker om dat vooraf te doen.

Welke principes er op hun beurt hier misschien achter schuilen:

• Werk casus-gewijs, bottom-up
• KISS: Het is veel eenvoudiger om iets te manipuleren als het nog klein en niet-complex is
• GIGO: Garbage in-garbage out: Het is veel logischer om te zorgen dat er geen rommel in komt.

Overigens: Dit is nogal altijd niet zwart-wit. Er zullen beslist situaties zijn, waarin het veel handiger is om pas naderhand data op te ruimen.

Principes achteraf - Brontabellen aanpassen?

In dit geval ware er vier brontabellen, afkomstig uit het datawarehouse (dwh): f08, f11, f20 en manomano (deze laatste alleen voor de prijzen - Er waren dus drie 'echte' brontabellen). Deze drie brontabellen waren niet congruent met elkaar. Eén tabel hadden waardes voor alleen brand en orgbrush, en de twee andere tabellen hadden waardes voor brand_orgbrush, maar hadden verschillende namen voor die tabel (dus drie verschillende namen in totaal).

Had dat niet handiger gekund?

1. Tabellen in dwh aanpassen: Het is altijd aanlokkelijk om deze tabellen in het datawarehouse aan te passen. Dat is niet aanlokkelijk: (1) Het is nogal ver-van-m'n-bed. Ik wil iets doen op het gebied van consolidatie, maar in plaats daarvan ga ik sleutelen aan het datawarehouse. (2) Dit kan invloed hebben op andere processen. Ik zou dus met unit tests moeten verifiëren of dit ok is. Goede kans dat ik die unit tests niet paraat heb, omdat ik die meestal pas aan het eind van een project oplever (3) Waan van de dag: Data in het datawarehouse moet langer meegaan dan vandaag. Daarom tricky om nav. één project, dit gelijk te verbouwen
2. Locale brontabellen aanpassen: Dit is het logische alternatief: Deze tabellen expliciet defineren en aanpassen. Dat is in dit project niet gedaan: Ik koos voor de volgende optie
3. Niets doen: Dat was in dit geval een prima oplossing. Zo'n ramp is het niet.

Principes achteraf - Brontabellen locaal aanpassen - Hoe dan?

Mocht ik in een bepaalde situatie er wel voor kiezen om bronbestanden locaal aan te passen: Een paar ideeën:

• Analyse: Splits samengestelde velden gelijk op. In dit geval: brand_orgbrush opsplitsen in brand en orgbrush. Da's gemakkelijker te bewerken en creëert congruente locale brontabellen. Uiteraard heeft dit een nadeel: Elke keer als je data verwijdert, zal er (statistisch gezien) iets van de informatie verloren gaan
• Nomenclatura: Mooie gelegenheid om de namen van tabellen en velden eenvormig te maken. Die namen moet ik ergens opschrijven.

Principes achteraf - NULL ipv. lege velden

Dat wist ik al wel, maar het bleek weer: Gebruik NULL als een veld geen waarde heeft!

Principes achteraf - Behoud uniformiteit - Doe één ding tegelijkertijd en doe het goed

Op het moment dat ik code schrijf om één specifiek record te fixen (bv. een spelfout in brand, dan is het aantrekkelijk om gelijk dat hele record te fixen - Niet doen: Het gebrek aan uniformiteit dat daardoor ontstaat, maakt het daarna ingewikkelder om de data te verwerken.

Andere manieren om hier naar te kijken:

• Unix-filosofie: Tools die één ding goed doen en niet 100 dingen half
• KISS: M'n grote openbaring uit ca. 2018: Maak programmeercode echt heel simpel. En waarschijnlijk is dit daar een heel goed voorbeeld van. Het wordt opeens een heel stuk dommer, en dat is goed.

Vraagstukken

Data-cleaning: Per brontabel of pas na samenvoegen? - Beantwoord

Vraag

De casus hiervoor deed opschonen vooral aan het begin. En toch is dat niet altijd aantrekkelijk. Wat is wijsheid?

• Cleaning-naderhand: Kan soms heel complex worden, wat je kunt PK-violations krijgen
• Cleaning-naderhand: Als samengestelden velden fouten bevatten, kreeg ik soms een kip-en-ei-probleem
• Cleaning-vooraf: Ga ik dan de kopieën van de brontabellen aanpassen?
• Wat in ieder geval geen wijsheid is: Eerste brontabel importeren in eindtabel, en dan dingen aanpassen: Dat vind ik nogal onlogisch.

Antwoord

Casus Casus juli 2020: Vier tabellen met forklifttruck-data maakte duidelijk dat het vrijwel altijd beter is om data vantevoren op te schonen. Hier is geen apart hoofdstuk aan geweid in dit artikel, maar zo'n beetje alle principes rondom programmeren enzo, wijzen in deze richting.

Opgeschoonde tabellen pushen naar dwh?

Vraag

Tijdens Casus juli 2020: Vier tabellen met forklifttruck-data heb ik verbazend veel tijd besteed aan het opschonen van de brontabellen. Tegelijkertijd had ik bedacht, dat het weinig zin heeft om opgeschoonde tabellen terug te pushen naar het dwh. Weet ik zat zeker?

Keywords

• Consolidate
• Consolideren