datenqualität mit den sql server integration services
TRANSCRIPT
www.datenfabrik.com www.datenfabrik.com
Datenqualität mit den SQL
Server Integration Services
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Was ist Datenqualität?
„Quality is when your customer
returns, not your product. “
www.datenfabrik.com www.datenfabrik.com
Was ist Datenqualität?
www.datenfabrik.com www.datenfabrik.com
Was ist Datenqualität?
• Datenqualität ist subjektiv
• Anwendungsbezogen
• Domänenspezifisch
• Mehrdimensional
www.datenfabrik.com www.datenfabrik.com
Was ist Datenqualität (nicht)?
Frottee Stoffhase (blau)
49716 Unterschleißheim
Herr Max Mustermann
Bester Kunde 2010
3,0 m x 3,0 m
=
www.datenfabrik.com www.datenfabrik.com
Ursachen schlechter Datenqualität
• Unterschiedliche Datenformate
• Datenverfall
• (Neue) Datenverwendung
• Inkonsistente Datendefinition
www.datenfabrik.com www.datenfabrik.com
Ursachen schlechter Datenqualität
• Architektur
• Systemaktualisierungen
• (Prozess-) Automatisierungen
• Datenkonvertierungen
• Systemkonsolidierung
• Fehlende Änderungsverfolgung
www.datenfabrik.com www.datenfabrik.com
Ursachen schlechter Datenqualität
• Bedienungs- und Benutzerfehler
• Manuelle Dateneingabe
• Verschiedene Datenverarbeitungsprozesse
• Mangelhaftes User Interface
• User Experience
www.datenfabrik.com www.datenfabrik.com
Ursachen schlechter Datenqualität
• Verlust von Fachkenntnissen
• Fehlendes Problembewusstsein
• Falsche Motivationsmethodik
www.datenfabrik.com www.datenfabrik.com
Ursachen schlechter Datenqualität
0
5
10
15
20
25
30
35
40
www.datenfabrik.com www.datenfabrik.com
Auswirkungen schlechter Datenqualität
• Kosten!!! – Direkte Kosten
• Nachweiskosten
• Wiedereingabekosten
– Indirekte Kosten • Umsatzeinbußen
• Fehlentscheidungen
• Imageverlust
• Werbekosten
• Betrugsversuch
www.datenfabrik.com www.datenfabrik.com
Auswirkungen schlechter Datenqualität
• Ausschuss und Nacharbeit durch falsch justierte Maschinen
• Rückrufaktionen aufgrund von Produktionsmängeln
• Projektmisstrauen
• Geldstrafen
• Sinkende Mitarbeitermotivation
www.datenfabrik.com www.datenfabrik.com
Auswirkungen schlechter Datenqualität
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 €
www.datenfabrik.com www.datenfabrik.com
Auswirkungen schlechter Datenqualität
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 €
www.datenfabrik.com www.datenfabrik.com
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)
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Rufnummern
0228902990
Rufnummer inkl. Vorwahl
www.datenfabrik.com www.datenfabrik.com
Rufnummern
0228 902990
Ortsnetzkennzahl Teilnehmerrufnummer
www.datenfabrik.com www.datenfabrik.com
Rufnummern
0 228 902990
Ortsnetzkennzahl Teilnehmerrufnummer
Verkehrsausscheidungsziffer
www.datenfabrik.com www.datenfabrik.com
Rufnummern
0 228 90299 0
Ortsnetzkennzahl Teilnehmerrufnummer
Verkehrsausscheidungsziffer
Durchwahl
www.datenfabrik.com www.datenfabrik.com
Rufnummern
00 49 228 90299 0
Ortsnetzkennzahl
Teilnehmerrufnummer
Internationale Verkehrsausscheidungsziffer Durchwahl
Internationale Vorwahl
www.datenfabrik.com www.datenfabrik.com
Rufnummern
+ 49 228 90299 0
Ortsnetzkennzahl
Teilnehmerrufnummer
Internationale Verkehrsausscheidungsziffer Durchwahl
Internationale Vorwahl
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
5 DQ Prozesse
Profiling
Validation
Cleansing
Enrichment
Monitoring
www.datenfabrik.com www.datenfabrik.com
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?
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Cleansing
Profiling
Validation
Cleansing
Enrichment
Monitoring
• Normierung von Daten
(z.B. Telefonnummern,
Straße + Hausnummer)
• Bereinigung der Daten
• Verschmelzung von
Dubletten
www.datenfabrik.com www.datenfabrik.com
Enrichment
Profiling
Validation
Cleansing
Enrichment
Monitoring
• Ergänzen der
bestehenden Daten
mit zusätzlichen (meist
externen)
Informationen
– Geokoordinaten
– Soziodemographische
Daten
– Microsoft Dallas
www.datenfabrik.com www.datenfabrik.com
Monitoring
Profiling
Validation
Cleansing
Enrichment
Monitoring
• Durchgehende
Prüfung und Messung
relevanter Regeln und
Ergebnisse aus den
Prozessen Profiling und
Validation
www.datenfabrik.com www.datenfabrik.com
Microsoft Boardmittel
• Character Map
• Data Converter
• Data Profiling Task
• Lookup Task
• Derived Column
• Fuzzy Grouping
• Fuzzy Lookup
• Script Component
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Demo
Data Profiling
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Demo
Erkennen von Fehlern innerhalb von
Attributsabhängikeiten
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.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
www.datenfabrik.com www.datenfabrik.com
Fuzzy Suche
• Fuzzy Lookup
– Führt Suchvorgänge in einem Verweisdataset
durch
– Suche über Ähnlichkeiten
• Fuzzy Grouping
– Sucht innerhalb des Eingabedatenstroms
– Suche über Ähnlichkeiten
www.datenfabrik.com www.datenfabrik.com
Demo
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Demo
RegEx und Pattern mit der Script Komponente
www.datenfabrik.com www.datenfabrik.com
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)
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
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)
www.datenfabrik.com www.datenfabrik.com
Demo
Reguläre Ausdrücke mit den Integration Services
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Demo
Kreditkartenvalidierung
www.datenfabrik.com www.datenfabrik.com
Third-party Components
• datenfabrik GmbH & Co. KG
• Pragmatic Works
• CozyRoc
• Intelligent Search Technologies
• AMB Dataminers
• Melissa Data
• DQ Components
www.datenfabrik.com www.datenfabrik.com
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)
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Demo
Dubletten erkennen und verschmelzen
www.datenfabrik.com www.datenfabrik.com
datenfabrik.profiler
• Profiling direkt im Datenfluss Datenquellen „unabhängig“
• Alarmfunktion auf einzelnen Ergebnissen pro Spalte
• Umfangreiches Regelwerk
• Erweiterung der Statistiken mit SSRS
www.datenfabrik.com www.datenfabrik.com
Demo
Kundendaten bereinigen
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
Fragen?
www.datenfabrik.com www.datenfabrik.com
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
www.datenfabrik.com www.datenfabrik.com
DANKE!