Databronnen ontsluiten: Wat zijn moeilijke bronnen en waarom?

Het onsluiten van databronnen is essentieel voor succesvolle Business Intelligence (BI) en data-analyse. Maar niet elke databron is even eenvoudig te verwerken. Wat zijn nu eigenlijk moeilijke databronnen en wat maakt ze lastig? Hieronder leggen we dit uitgebreid uit, van eenvoudige tabellen tot complexe JSON-bestanden en API’s.

Laten we beginnen met makkelijke bronnen. Het makkelijkste is een enkele simpele lijst, een tabel met kolommen en rijen. Alle gegevens die je nodig hebt zitten in die ene tabel. Iedere rij is een item en in de kolommen staan de kenmerken van dat item. Je hoeft bij zo’n bron alleen maar die ene tabel in te lezen en je hebt alles wat je nodig hebt om visualisaties te maken. Zo’n tabel waar alles in zit wordt wel volledig gedenormaliseerd genoemd.

Genormaliseerde databronnen met meerdere tabellen

Iets ingewikkelder is als je met meerdere tabellen te maken hebt. Dan is de data in meer of mindere mate genormaliseerd. In veel administratieve systemen wordt normalisatie gebruikt om fouten bij de invoer te voorkomen. In plaats van iedere keer de adresgegevens van een persoon in te voeren bij het vastleggen van een aankoop sla je de persoonsgegevens maar één keer op in een klanten tabel. In de aankopen tabel leg je dan bijvoorbeeld het klantnummer vast. In de klanten tabel zijn dan bij het klantnummer de correcte adresgegevens te vinden. Daarmee voorkom je dat een klant tien keer voorkomt met net andere straatnamen door typfouten of andere afkortingen etc.

Zo’n genormaliseerde databron is op zich niet zo ingewikkeld. Maar als de database heel veel tabellen bevat en je er weinig (of geen) documentatie over hebt, kan het een hele puzzel zijn om alle benodigde gegevens bij elkaar te zoeken. Het laden van gegevens uit dat soort tabellen is echter heel eenvoudig. Dit soort tabellen is over het algemeen ook goed om te zetten in wat een sterschema wordt genoemd.

Een sterschema bevat een feiten tabel (of meerdere) die de dingen bevat die je wilt (op)tellen of gemiddelden van wilt berekenen (of andere berekeningen) en dimensie tabellen die de ‘dingen’ vertegenwoordigen waarvoor je de tellingen, gemiddelden etc. wilt weten. Bijvoorbeeld omzet per winkel of omzet per jaar per winkel. Omzet is dan een feit en winkel en jaar zijn dimensies. De meeste databronnen die we tegenkomen hebben dit soort genormaliseerde datamodellen.

Complexere databases met één object- en één relatietabel

Nog wat lastiger zijn databases waarbij alle soorten items, bijvoorbeeld klant, winkel, product, straat, leverancier, medewerker, kortingsactie, etc., in een enkele tabel zitten. Je hebt dan meestal één objecttabel waar alle items in zitten en één relatietabel waar de relaties tussen items in vastgelegd zijn.

Met zo’n database moet je die twee tabellen gebruiken om er dimensie- en feitentabellen van te maken. Je moet de tabel met de items uit elkaar trekken om er nieuwe tabellen van te maken en je hebt de tabel met de relaties nodig om te zorgen dat alle items goed aan elkaar gekoppeld blijven.

Een van onze klanten gebruikt een systeem waarbij de database een variant van dit model heeft. De itemstabel heeft een flink aantal standaardvelden met een standaardnaam, maar er is nog een extra tabel die per soort item aangeeft wat de naam van het veld is in het systeem en dus wat de inhoud van het veld betekent. Een veld dat in de database de naam datum1 heeft, kan dus voor het ene item de aankoopdatum zijn, maar voor een ander item de betaaldatum en voor weer een ander item de verzenddatum. Dat maakt het nog iets ingewikkelder om data in de tabellen goed uit elkaar te halen en weer in elkaar te zetten in een sterschema.

Key-value databases: één tabel met kenmerken per rij

Een ander databaseontwerp is een database met echt maar één tabel, waar iedere rij alleen maar aangeeft om welk item het gaat, welk kenmerk de rij betreft en wat de waarde van dat kenmerk is. Dit wordt een key-value database genoemd.

Om daar een sterschema van te maken moet je niet alleen alle items verdelen over tabellen, maar ook nog eens alle kenmerken per item verzamelen en als kolommen in de tabellen laten landen.

Waarom bestaan dat soort databases? Ze maken het makkelijker om nieuwe soorten items aan te maken of extra kenmerken aan een item toe te voegen. Zeker bij een key-value database is een nieuw kenmerk toevoegen aan een item gewoon een nieuwe rij toevoegen in de tabel. Er is geen aanpassing van het databasemodel nodig. Er hoeven geen nieuwe tabellen of velden aangemaakt te worden en ook geen definities van tabellen of velden aangepast. Nieuwe items en kenmerken worden gewoon als data toegevoegd in de database. Dat maakt ze super flexibel. Gebruikers van deze systemen zijn dan veel minder afhankelijk van de bouwer of leverancier om dit soort wijzigingen door te voeren. Er hoeft geen database- of softwareontwikkelaar meer aan te pas te komen.

Alleen is het wel veel lastiger voor een BI-ontwikkelaar om er een ideaal sterschema voor BI-toepassingen van te maken.

Andere moeilijke bronnen: JSON-bestanden

Zijn er nog andere bronnen die moeilijk zijn? Ja, bijvoorbeeld JSON-bestanden. JSON-bestanden zijn tekstbestanden met gestructureerde data, vaak in een hiërarchische structuur. Dit soort bestanden zijn voor computers goed te verwerken.

Voor Qlik Sense zijn JSON-bestanden echter lastig, simpelweg omdat Qlik Sense alleen de meest eenvoudige JSON-bestanden kan inlezen. Bij REST API’s, waarbij Qlik Sense data via een webservice opvraagt, gaat het prima, maar bestanden zelf inlezen lukt niet altijd.

Voor een klant van ons die maandelijks zo’n 500.000 JSON-bestanden aanlevert, is daarom een trucje nodig. We converteren deze JSON-bestanden met een Python-script naar XML-bestanden, omdat Qlik Sense wel goed overweg kan met XML.

API’s: complexer dan databases ophalen

API’s zijn dus niet per definitie moeilijk? Op zich niet, maar het is vaak wel complexer dan gegevens uit een database ophalen. Dat komt bijvoorbeeld door de beveiliging van de webservice.

Een veelgebruikte manier van authenticeren is via OAuth2. Je moet je aanmelden om daarna data via de webservice te mogen ophalen. Hierbij zijn verschillende soorten tokens betrokken die beperkt houdbaar zijn. Je moet dus zorgen dat je op tijd een nieuw token ophaalt.

Behalve dat de authenticatie niet mag verlopen, kun je de data meestal alleen in setjes van beperkte grootte ophalen, bijvoorbeeld 1.000 resultaten per keer.

Er zijn verschillende manieren om het volgende setje resultaten te krijgen:

• Sommige webservices gebruiken parameters als skip en take: je geeft aan hoeveel je al hebt opgehaald en hoeveel je nu wilt ophalen.
• Andere webservices geven zelf een URL terug voor het volgende setje data, die je dan gebruikt om door te gaan.
• Sommige webservices geven aan hoeveel resultaten je in totaal kunt ophalen, terwijl je bij andere dat zelf moet berekenen of na elke aanroep moet controleren of er nog data is.

Je blijft doorgaan tot je een leeg setje terugkrijgt, dan weet je dat je alles hebt opgehaald.

Optionele elementen in JSON- en XML-documenten

JSON- en XML-documenten voldoen wel aan een structuur, maar elementen zijn vaak optioneel. Dat betekent dat ze niet in alle documenten voorkomen, in sommige wel en in andere niet.

Dit kan het lastig maken om in Qlik Sense een laadscript te schrijven dat alle documenten goed kan inlezen, zodat je alle elementen opvangt.

Om zo’n script te maken, heb je een JSON- of XML-document nodig dat elk mogelijk element bevat — het meest uitgebreide mogelijke document.

Gelukkig zijn API’s meestal goed gedocumenteerd en is er vaak een JSON-document beschikbaar dat de volledige structuur beschrijft. Voor XML-bestanden is vaak een schema-definitie beschikbaar om de XML-bestanden te valideren.

Met zo’n XML-schema-definitie kun je een voorbeeld XML-bestand genereren dat alle mogelijke elementen met geldige waarden bevat.

Maar soms moet je wel door wat hoepels springen voordat je zo’n JSON- of XML-bestand hebt.

Loop je vast op een specifieke databron?

Het ontsluiten van databronnen varieert van eenvoudig tot complex, afhankelijk van het type databron en de structuur ervan. Van volledig gedenormaliseerde tabellen tot key-value databases en complexe JSON-API’s: elke databron vraagt om een andere aanpak. Goed inzicht in deze verschillen helpt BI-professionals en data engineers om data efficiënt en betrouwbaar te ontsluiten voor analyse en visualisatie.

Veel van de databronnen die in dit blog worden genoemd, ontsluiten we in de praktijk dagelijks. Op onze pagina met koppelingen zie je bovendien welke systemen en bronnen we al succesvol hebben aangesloten. Meer weten of eens verder praten, neem gerust contact op. Wij helpen je graag verder!

Veelgestelde vragen over moeilijke databronnen