De techniek biedt steeds meer mogelijkheden om verschillende databronnen te ontsluiten. In de meeste gevallen is de wens dat deze data ook aan elkaar gekoppeld kunnen worden. Als voorbeeld nemen we het koppelen van klantgegevens vanuit de webshop met de klantgegevens in het CRM systeem en klantgegevens in de financiële administratie. Dit blijkt echter niet altijd even eenvoudig. Elke applicatie slaat de gegevens weer op zijn eigen manier en in zijn eigen formaat op. Vaak wordt er geprobeerd om dezelfde nummering aan te houden in de verschillende applicaties. In de praktijk zijn er vaak vele uitzonderingen.
Daarnaast komen wij ook regelmatig applicaties tegen die geen goede ‘invoer-controles’ hebben ingericht, waardoor ook binnen één applicaties dubbele klantgegevens kunnen voorkomen met alle vervelende gevolgen van dien.
Bovenstaande laat zien dat het belangrijk is om regelmatig kritische bedrijfsgegevens te controleren op integriteit en betrouwbaarheid. Nu kun je natuurlijk alle gegevens dumpen naar Excel, en daar met behulp van draaitabellen en verticaal zoeken functies gaan zoeken naar de verschillen. Dit is echter erg bewerkelijk en ook nog eens foutgevoelig.
Gelukkig heeft Microsoft hier een schitterende applicatie voor ontwikkeld waarmee je dit kunt automatiseren: SQL Server Data Quality Services (DQS). Met DQS kun je de kwaliteit van de data onderhouden en zorgen dat de data geschikt is voor het gebruik in de business.
Met behulp van DQS kun je ‘business rules’ opstellen waaraan de data moet voldoen en heb je de mogelijkheid met behulp van ‘matching rules’ te zoeken naar dubbele records.
In deze blog zullen wij met behulp van DQS, in een tabel met circa 20.000 relaties, op zoek naar mogelijke dubbelingen.
Benodigdheden
Om deze blog te kunnen volgen is een installatie vereist van de Data Quality Server en Data Quality Client. Deze twee componenten moeten apart geïnstalleerd worden naast een SQL Server installatie. Via de volgende link is meer informatie over de installatie te vinden: https://msdn.microsoft.com/en-us/library/gg492277.aspx.
Aanmaken van een Knowledge Base
De eerste stap in DQS is het aanmaken van een Knowledge Base. In een knowledge base wordt de kennis van de data opgeslagen en worden de ‘business rules’ ingesteld op basis waarvan de data moet worden gecontroleerd.
Open SQL Server Data Quality Client en kies in het start scherm voor New Knowledge Base.
Geef een naam op voor de nieuwe Knowledge Base. In dit voorbeeld geven wij de naam Relatiegegevens.
In een Knowledge base kun je kiezen voor drie activiteiten:
- Domain Management
Hierbij worden velden vastgelegd van de data en kunnen de ‘rules’ worden opgenomen waaraan elk veld moet voldoen. - Knowledge discovery
Bij deze activiteit kan gekoppeld worden met een databron en kan de data onderzocht worden om te bepalen aan welke ‘rules’ de data voldoet. - Matching Policy
In dit onderdeel kunnen matching rules worden opgenomen op basis waarvan DQS moet onderzoeken of er dubbele records in de data voorkomen.
Selecteer in eerste instantie als Activiteit Domain Management.
In het volgende scherm gaan we eerst de verschillende Domains aanmaken. We kiezen in dit voorbeeld voor een simpele uitwerken en maken twee Domains aan: een voor de RelatieNaam en een voor de RelatieID.
Klik op het rondje met het sterretje om een nieuw Domain aan te maken.
Vul in het volgende scherm de details van het Domain in. Selecteer onder Language ‘Dutch’ en klik op OK.
Maak op dezelfde wijze een Domain aan voor het veld RelatieID
Business Rules
Nadat een Domain is aangemaakt kunnen er, in het volgende scherm, verschillende eigenschappen van het Domain worden gekoppeld.
Onder het tabje Domain Rules kun je bijvoorbeeld business rules aangeven waar de data aan moet voldoen. Als voorbeeld kan de lengte van de velden worden gecontroleerd of kan een controle plaatsvinden of de gegevens voldoen aan een specifieke opmaak.
In deze blog wordt hier niet verder op ingegaan.
Publiceren van de Knowledge Base
Klik rechts onderin op Finish nadat de twee Domains zijn aangemaakt. Je krijgt nu het volgende scherm te zien.
De Knowledge Base wordt gepubliceerd naar de aparte database, zodat deze door Data Quality Projecten gebruikt kunnen worden. Klik op Publish.
Matching Policy
In het start scherm van DQS vind je onder de Recent Knowledge Base de zojuist aangemaakte Knowledge Base. Klik op het pijltje rechts naast de Knowledge base en kies nu voor Matching Policy.
Connectie database
In het volgende scherm wordt de verbinding gemaakt met de SQL Server waar de brondata in is opgenomen die gecontroleerd moet worden. In dit geval is gekozen voor de controle van de tabel DimRelations.
Mapping
In de mapping tabel kan de koppeling gemaakt worden tussen de velden in de Brontabel en de Domains, die we in de eerdere stappen hebben aangemaakt.
Klik op vervolgens op Next.
Aanmaken Matching rule
Bij de volgende stap moet je een matching rule aanmaken op basis waarvan DQS naar dubbele records data zoeken. Klik op het plusje om een nieuwe Rule aan te maken.
Vul onder de Rule Details een naam voor de rule in en geef een waarde in voor de ‘Min. Matching score’. In dit voorbeeld laten we de score op 80% staan. Deze waarde geeft aan in hoeverre de data moet aansluiten om door DQS als gelijk te worden aangemerkt.
Maak onder het kopje Rule Editor een nieuwe rule aan door op het groene plusje te klikken.
We kiezen hier nu alleen voor het Domain Relatienaam.
Onder Similarity kan gekozen worden voor Similar of voor Exact. Als gekozen wordt voor Similar controleert DQS op basis van de Min. Matching score of de records overeenkomen.
Als er meerdere Domains toegevoegd worden, kan er op basis van de kolom Weight een weging worden meegegeven. Opgeteld moet de waarde altijd 100% zijn.
Klik op Next.
Matching results
Op het volgende scherm kunnen de resultaten bekeken worden van de matching rule. Klik op Start om DQS te starten.
Nadat DQS klaar is krijg je een detail overzicht te zien met alle gevonden resultaten. In het selectievak kan worden gekozen om de dubbele records of juist de unieke records te laten zien.
In dit voorbeeld zijn er van meerdere relaties dubbele records gevonden. Zo zien we dat er negen records zijn met RelationID 3LVR00017, waarbij de score op het matchingsresultaat 100% is, maar waarbij er toch negen aparte records voor zijn opgenomen.
Een ander voorbeeld van de effectiviteit van de Data Quality Services zien we in het volgende screenshot:
In dit overzicht zien we twee voorbeelden die door DQS gevonden zijn:
- Het eerste record heeft exact dezelfde relatienaam maar een ander relationID
- Het tweede record heeft een score van 83%, omdat in de relatienaam BV zonder puntjes is geschreven terwijl dat bij de andere records met puntjes is opgenomen. Gezien de naamgeving is dit ongetwijfeld dezelfde relatie.
Klik op Finish om vervolgens de Knowledge base opnieuw te publiceren.
Data Quality Project
De aangemaakte knowledge base kan nu worden gebruikt in één of meerdere Data Quality projecten.
Het aanmaken van een Data Quality project gaat nagenoeg op dezelfde wijze als het aanmaken van een knowledge base.
Als het project is aangemaakt en de verbinding met de databron is gemaakt, kunnen de resultaten vervolgens worden geëxporteerd naar een database tabel zodat deze verder gebruikt kunnen worden.
Afsluiting
Bovenstaande uitwerking heeft laten zien hoe je met behulp van SQL Data Quality Services een gegevensbron kunt onderzoeken op dubbele records.
De knowledge base en de data quality projecten kunnen met behulp van SQL Server Integration Services geautomatiseerd worden aangeroepen, zodat het ‘opschonen’ van de data in een continu proces van dataverwerking kan worden opgenomen.