Datenqualität mit den SQL Server Integration Services

www.datenfabrik.com www.datenfabrik.com

Datenqualität mit den SQL

Server Integration Services


datenfabrik GmbH & Co. KG

• Versandhandel und Direktmarketing

• Internationale Datenverarbeitung in über 10 europäischen Ländern – Listbroking – Datenbereinigung – „Daten-Hosting“ – Datenmigration

• Softwareentwicklung im Bereich Data Quality


Tillmann Eitelberg

- Prokurist datenfabrik GmbH & Co. KG

- Lehrbeauftragter an der Uni Bonn

- RGV Köln\Bonn\Düsseldorf

- Regional Director PASS Deutschland

- Regional Mentor PASS Deutschland

- Blog: www.ssis-components.net

- Codeplex Projekte - ShapeFileSource

- ImageSource

- SMSTask

- ReportGeneratorTask

- GoogleAnalyticsSource

http://www.ssis-components.net/




Agenda

• Was ist Datenqualität?

• Ursachen schlechter Datenqualität

• Auswirkungen schlechter Datenqualität

• Gesetzliche Anforderungen

• 5 DQ Prozesse

• Microsoft Boardmittel

• Community Components

• Third Party Components


Was ist Datenqualität?

„Ein (mehrdimensionales) Maß für

die Eignung von Daten, den an ihre

Erfassung/Generierung gebundenen

Zweck zu erfüllen.“

Dr. Volker Würthele



„Quality is when your customer

returns, not your product. “



• Datenqualität ist subjektiv

• Anwendungsbezogen

• Domänenspezifisch

• Mehrdimensional


Was ist Datenqualität (nicht)?

Frottee Stoffhase (blau)

49716 Unterschleißheim

Herr Max Mustermann

Bester Kunde 2010

3,0 m x 3,0 m

=


Ursachen schlechter Datenqualität

• Unterschiedliche Datenformate

• Datenverfall

• (Neue) Datenverwendung

• Inkonsistente Datendefinition



• Architektur

• Systemaktualisierungen

• (Prozess-) Automatisierungen

• Datenkonvertierungen

• Systemkonsolidierung

• Fehlende Änderungsverfolgung



• Bedienungs- und Benutzerfehler

• Manuelle Dateneingabe

• Verschiedene Datenverarbeitungsprozesse

• Mangelhaftes User Interface

• User Experience



• Verlust von Fachkenntnissen

• Fehlendes Problembewusstsein

• Falsche Motivationsmethodik



0

5

10

15

20

25

30

35

40


Auswirkungen schlechter Datenqualität

• Kosten!!! – Direkte Kosten

• Nachweiskosten

• Wiedereingabekosten

– Indirekte Kosten • Umsatzeinbußen

• Fehlentscheidungen

• Imageverlust

• Werbekosten

• Betrugsversuch



• Ausschuss und Nacharbeit durch falsch justierte Maschinen

• Rückrufaktionen aufgrund von Produktionsmängeln

• Projektmisstrauen

• Geldstrafen

• Sinkende Mitarbeitermotivation



500.000 Kunden

3% Dubletten

Zustellung

5% = 25.000 3% = 15.000

40.000

0,45 € = 18.000 € 0,55 € = 22.000 €

X2 = 80.000 €

40.000 €



40.000 Kunden

3% Dubletten

Zustellung

5% = 2.000 3% = 1.200

3.200

0,45 € = 1.440 € 0,55 € = 1.760 €

X2 = 6.400 €

3.200 €


Gesetzliche Anforderungen

• Gesetzliche und behördliche Anforderungen nehmen zu

• Nachweispflicht setzt eine einwandfreie Datenqualität voraus

• Einhaltung nationaler und internationaler Gesetze und

Richtlinien (Antiterrorlisten, Robinsonliste)

• Bekannte Compliance-Maßnahmen

– International Financial Reporting Standards (IFRS)

– Sarbanes-Oxley Act (SOX)

– Basel II

– REACH

– International Material Data Systems (IMDS)


Geschäftstreiber laut Gartner

0 10 20 30 40 50 60 70

Unterstützung von Compliance-Aktivitäten

Verbesserung der Anwenderakzeptanz der wichtigstenApplikationssysteme

Unterstützung von CRM-Initiativen

Unterstützung von Business.Intelligence oder Data-Warehouse-Initiativen

Stärkung des Vertrauens in die eigene Datenbasis

Antwort auf Datenqualitätsinitiativen bei Wettbewerbern

Folge eines signifikanten Schadenfalls durch schlechteDatenqualität


Datenqualität steigern

• Bei der Dateneingabe

• Fachkenntnisse aufbauen

• Problembewusstsein stärken

• Master Data Management

• Corporate Data Definition

• Verwendung von Referenzdaten

• Regelmäßige Prüfung mit „externen“ Programmen

• Beim Beladen des Data Warehouse


Rufnummern

0228902990

Rufnummer inkl. Vorwahl


Rufnummern

0228 902990

Ortsnetzkennzahl Teilnehmerrufnummer


Rufnummern

0 228 902990


Verkehrsausscheidungsziffer


Rufnummern

0 228 90299 0


Verkehrsausscheidungsziffer

Durchwahl


Rufnummern

00 49 228 90299 0

Ortsnetzkennzahl

Teilnehmerrufnummer

Internationale Verkehrsausscheidungsziffer Durchwahl

Internationale Vorwahl


Rufnummern

+ 49 228 90299 0

Ortsnetzkennzahl

Teilnehmerrufnummer

Internationale Verkehrsausscheidungsziffer Durchwahl

Internationale Vorwahl


Rufnummern

- Max. Rufnummernlänge 15 Stellen im Internationalen Verkehr

- 5 verschiedene Schreibweisen in der DACH-Region - +49 30 12345-67

- +49 30 1234567

- +49 (30) 1234567

- +49-30-1234567

- +49 (0)30 12345-67

- Zuständigkeit in Deutschland liegt bei der Bundesnetzagentur

- Ortsnetzkennzahlen werden im Nummerierungsplan festgehalten

- Private Nummerierungspläne, Sonderrufnummern, ITU


5 DQ Prozesse

Profiling

Validation

Cleansing

Enrichment

Monitoring


Profiling

Profiling

Validation

Cleansing

Enrichment

Monitoring

• Wie sehen meine

Daten aus?

• Welche Datentypen

sind enthalten?

• Welches Format haben

die Daten?

• Gibt es NULL-Werte?

• Existieren Abhängig-

keiten untereinander?


Validation

Profiling

Validation

Cleansing

Enrichment

Monitoring

• Prüfung von Datentypen und Formaten

• Syntaktische und semantische Prüfung aller relevanten Daten

• Prüfung auf Vollständigkeit (ggf. auch mehrdimensional)

• Prüfung auf Dubletten


Cleansing

Profiling

Validation

Cleansing

Enrichment

Monitoring

• Normierung von Daten

(z.B. Telefonnummern,

Straße + Hausnummer)

• Bereinigung der Daten

• Verschmelzung von

Dubletten


Enrichment

Profiling

Validation

Cleansing

Enrichment

Monitoring

• Ergänzen der

bestehenden Daten

mit zusätzlichen (meist

externen)

Informationen

– Geokoordinaten

– Soziodemographische

Daten

– Microsoft Dallas


Monitoring

Profiling

Validation

Cleansing

Enrichment

Monitoring

• Durchgehende

Prüfung und Messung

relevanter Regeln und

Ergebnisse aus den

Prozessen Profiling und

Validation


Microsoft Boardmittel

• Character Map

• Data Converter

• Data Profiling Task

• Lookup Task

• Derived Column

• Fuzzy Grouping

• Fuzzy Lookup

• Script Component


Data Profiling Task

• Hilft Entwicklern, sich mit Datenquellen vertraut zu machen

• Erstellt verschiedene Profile pro Spalte

• Daten müssen im SQL Server vorliegen

• Profil wird als XML Datei gespeichert

• ProfileToSQL Styleheets http://www.tf-informatik.dk/FreeStuff/ProfileToSQL/index.php


Demo

Data Profiling


Lookup Task

• Führt Suchvorgänge in einem Verweisdataset durch

• Setzt Referenzen

• Anreicherung von Daten

• Ermöglicht auch das erstellen von Business Regeln (Attributabhängigkeiten) WENN Hersteller = „Vita Kraft“ UND Produkt = „Stroh“ DANN Verpackungseinheit = „Liter“ WENN Kategorie = „Fernseher“ (UND Unterkategorie = „Plasma“) DANN ProduktPreis > 100


Demo

Erkennen von Fehlern innerhalb von

Attributsabhängikeiten


Data Conversion

• Konvertiert Daten in einen anderen Datentyp

• Konvertierte Daten werden in eine Ausgabespalte kopiert

• Mehrere Konvertierungen für eine Spalte

Replacing Data Conversion Component for SSIS

Todd McDermid - Codeplex

http://rdc.codeplex.com/

http://rdc.codeplex.com/


Derived Column

• Ermöglicht das Erstellen von Regeln

• Hilft bei der Bereinigung von Daten

• SSIS Expressions

– Bedingungen (BOOL ? True : False)

– String-Funktionen

– Datums-Funktionen

– Mathematische Operatoren


Fuzzy Suche

• Fuzzy Lookup

– Führt Suchvorgänge in einem Verweisdataset

durch

– Suche über Ähnlichkeiten

• Fuzzy Grouping

– Sucht innerhalb des Eingabedatenstroms

– Suche über Ähnlichkeiten


Demo


Script Component

• Ausführung von VB.NET/C# Code

• Verwendung als Quelle, Transformation oder Ziel

• Verwendung bei

– erweiterten .NET Funktionen, z.B. regulären

Ausdrücken

– komplexen Algorithmen


Demo

RegEx und Pattern mit der Script Komponente


Community Components

• Data Validation Transform

• RegexClean

• Regular Expression Transform

• RegExtractor SSIS Component

• CCNValidator

• MapPoint Batch Geocoder (SQL Server CLR Function for Address Correction and Geocoding)


RegexClean

• Community Component von Darren Green (SQLIS.com)

• Match Expression - extrahiert Daten anhand eines regulären Ausdrucks

• Replace Expression - überführt Daten mit Hilfe eines regulären Ausdrucks


Regular Expression Transform

• Community Component von Darren Green (SQLIS.com)

• Validiert Daten anhand eines regulären Ausdrucks

• Komponente enthält zwei Ausgaben (Matched/Non-Matched)


Demo

Reguläre Ausdrücke mit den Integration Services


CCNValidator

• Codeplex-Projekt

• Komponente zum Prüfen von Kreditkartennummern

• Verwendung des Luhn-Algorithmus

• Ausgabedatenstrom wird automatisch um eine Validierung ergänzt

• Prüfung sagt nur aus, ob die Nummer richtig sein könnte


Demo

Kreditkartenvalidierung


Third-party Components

• datenfabrik GmbH & Co. KG

• Pragmatic Works

• CozyRoc

• Intelligent Search Technologies

• AMB Dataminers

• Melissa Data

• DQ Components


datenfabrik.dedupe

• Dublettensuche für die Integration Services

• Sehr granulare Einstellungen möglich

• Prüfung auf vertauschte Spalten möglich

• Prüfung auf vertauschte Doppelnamen möglich

• Normalisierung/Standardisierung spezieller Werte

• Sehr hohe Performance (ca. 15 Mio. Datensätze pro Stunde)


datenfabrik.address

• Komponente zur postalischen Korrektur

• Referenzdaten für über 240 Länder

• Normierung von Adressdaten

• Zerlegung von Adressdaten

• Umbenennung anhand historischer Informationen

• Geokodierung von Adressdaten


Demo

Dubletten erkennen und verschmelzen


datenfabrik.profiler

• Profiling direkt im Datenfluss Datenquellen „unabhängig“

• Alarmfunktion auf einzelnen Ergebnissen pro Spalte

• Umfangreiches Regelwerk

• Erweiterung der Statistiken mit SSRS


Demo

Kundendaten bereinigen


Weitere Komponenten

• datenfabrik.merge Zusammenführen doppelter Datensätze auf einen Golden Record

• datenfabrik.email Korrektur von Email-Adressen

• datenfabrik.deletix Löscht Dubletten nach verschiedenen Prioritätsinformationen und erstellt Löschprotokolle

• datenfabirk.gender Ermittelt die korrekte Anrede anhand des Vornamens für unterschiedliche Länder

• datenfabrik.currency Rechnet Beträge in verschiedene Währungen mit aktuellen und historischen Daten um.

• datenfabrik.phone Korrektur und Anreicherung von Telefondaten


Fragen?


Links

• Deutsche Gesellschaft für Informations- und Datenqualität http://www.dgiq.de

• Data Profiling im SQL Server 2008, Martin Kopp http://www.sqlpass.de/Mitgliedsbereich/Repository/tabid/73/DMXModule/696/Command/Core_Download/Default.aspx?EntryId=116

• SQLIS.com – RegexClean, Regular Expression Transform http://www.sqlis.com

• CCNValidator http://ccnv05.codeplex.com/

• Microsoft SQL Server Integration Services Product Samples http://msftisprodsamples.codeplex.com/

• SSIS Community Tasks and Components http://ssisctc.codeplex.com/

• SSIS-Components.net http://www.ssis-components.net

http://www.dgiq.de/

http://www.sqlpass.de/Mitgliedsbereich/Repository/tabid/73/DMXModule/696/Command/Core_Download/Default.aspx?EntryId=116



http://www.sqlis.com/

http://ccnv05.codeplex.com/

http://ccnv05.codeplex.com/

http://msftisprodsamples.codeplex.com/

http://msftisprodsamples.codeplex.com/

http://ssisctc.codeplex.com/

http://ssisctc.codeplex.com/





DANKE!

Datenqualität mit den SQL Server Integration Services

Documents

Transcript of Datenqualität mit den SQL Server Integration Services