Datenqualität_Panther - Robert Panther on SQL Server

Datenqualität konsequent umgesetzt
Robert Panther, CGI Deutschland Ltd. & Co. KG
Profil: Robert Panther
Ausbildung
Diplom-Informatiker (FH)
Rolle
Executive Consultant
Zertifizierungen
MCITP SQL Server 2008 Developer
MCTS SQL Server 2008 Implementation & Maintenance
MCTS SQL Server 2005 Business Intelligence
MCTS SQL Server 2005 Implementation & Maintenance
Überblick
• Regelmäßiger Speaker auf Konferenzen:
BASTA!, SQLCON, Frankfurter Datenbanktage, IT Tage, Deutsche SQL Server Konferenz
• Fachbuchautor:
entwickler.press: SQL Server Performance Ratgeber, SQL-Abfragen optimieren
Microsoft-Press: Datenbanken entwickeln mit SQL Server 2012
• Autor von Fachartikeln für diverse Publikationen:
z.B. Windows Developer, dot.net magazin, database pro
• Community:
aktives PASS-Mitglied, Leiter der SQL Server Expert Group bei CGI
Schwerpunkte
SQL Server
• Design
• Entwicklung
• Administration
• Performance
• SSIS (ETL)
Sonstige
• .NET (C# & VB)
• Windows Mobile
Agenda
1. Theoretischer Hintergrund
2. Datenqualität aus Entwicklersicht
3. Datenqualität aus Datenbanksicht
• Datenqualität mit MDS und DQS
• Alternative Möglichkeiten und Tools
4. Fazit
1. Theoretischer Hintergrund
Worum geht es beim Thema Datenqualität?
Datenqualität = Informationsqualität
Was sind qualitativ gute Daten?
• vollständig
• konsistent / korrekt
• zugreifbar / aktuell
• wiederverwendbar
Mythen zum Thema Datenqualität
1.
2.
3.
4.
5.
Datenqualität ist ein neues Thema!
Datenqualität ist ein reines BI-Thema!
Das Ziel der Bereinigungsmaßnahmen sind 100% korrekte Daten!
Mit den richtigen Tools erfolgt die Datenbereinigung vollautomatisch!
Wenn die Daten einmal bereinigt sind, ist alles gut!
Mögliche Folgen von schlechter Datenqualität
• falsche Adressdaten → Fehler beim Versand → Zusatzkosten & Kunde verärgert
• falscher Name und/oder Anrede → falsche Kundenansprache → Kunde verärgert
• offensichtliche Datenfehler, die geklärt werden müssen
→ Verzögerung, Zusatzaufwand für Klärung und Korrektur
→ Zusatzkosten, Mitarbeiter frustriert & Kunde verärgert
• unerkannte Datenfehler, die zu Batch-Abbrüchen führen
→ Verzögerung, Zusatzaufwand für Korrektur und erneuten Batch-Lauf
→ Zusatzkosten, Mitarbeiter frustriert & Kunde verärgert
→ Image- & Umsatzverlust!!
Ursachen für schlechte Datenqualität?
(… und wer ist schuld?)
Ist das wirklich so einfach?
Ursache:
Verantwortlich:
Fehleingaben
Anwender
Anwendungsfehler
Entwickler
Systemausfälle
Administratoren
Datenimporte/Migrationen
DB-Entwickler
Ursachen für schlechte Datenqualität?
(… und wer ist wirklich schuld?)
Typische Datenqualitäts-Probleme
Straße
Postleitzahl Ort
Hauptstr.__20
61462
Königstein
Hauptstrasse 20
61462 Königstein
Am Limespark 2 _65843
Sulzbach
• „Unsaubere“ Inhalte einzelner Felder
• unnötige Leerzeichen
(am Anfang, am Ende, mehrfach hintereinander)
• Sonderzeichen
• unterschiedliche Schreibweisen derselben Information
(typisches Beispiel: Straße, Strasse, Str.)
• Spaltenübergreifend: Informationen in falschen Feldern
• typische Beispiele: Vorname, Titel, Hausnummer, PLZ
• Datensatzübergreifend: Doubletten
Straße
Postleitzahl Ort
Hauptstr. 20
61462
Königstein
Hauptstr. 20
61462 Königstein
Am Limespark 2 65843
Sulzbach
Straße
Postleitzahl Ort
Hauptstr. 20
61462
Königstein
Hauptstr. 20
61462
Königstein
Am Limespark 2 65843
Sulzbach
Doubletten-Problematik
Doublettenerkennung
• Erkennung von doppelten Daten
• Problem: nicht-identische Schreibweisen
Doublettenabgleich
• welche Kopie wird gelöscht?
• Übernahme von Informationen aus zu löschender Kopie
• abhängige Daten in anderen Tabellen
Mögliche Maßnahmen (reaktiv)
1.
2.
3.
4.
Analyse der Daten
Korrektur der Daten
Entfernen von inhaltsleeren Datensätzen
Beseitigung von Doubletten
Mögliche Maßnahmen (proaktiv)
• Anpassung des Datenmodells:
referentielle Integrität, Datentypen & Constraints
• Anpassen der Anwendung:
Eingabevalidierung, Doublettensuche bei Neuanlage
• automatisierte Korrekturläufe für unkritische Korrekturen
• regelmäßige Analyse der Datenqualität, um weitere Schritte abzuleiten
2. Datenqualität aus
Entwicklersicht
Datenmodellierung
Korrekte Speicherung von Daten
• Unvollständige Informationen vermeiden
• NOT NULLable Spalten
• Default Values
• Richtiges Eingabeformat erzwingen
• Wahl des richtigen Datentyps
• Constraints auf einzelnen Spalten (z.B. Mindestlänge)
• Spaltenübergreifende Validierung
• Constraints zwischen zwei Spalten (z.B. Geburtsdatum vor Todesdatum)
• redundante Ablage von Informationen vermeiden
• UNIQUE Indizes
• Doubletten durch referentielle Integrität minimieren
Eingabevalidierung
Fehler direkt bei der Eingabe korrigieren
•
•
•
•
Entfernung von unnötigen Leer- und Sonderzeichen
Vereinheitlichung der Schreibweise
Vergleich mit Referenzdaten
letzte Entscheidung sollte immer beim Anwender liegen
Vermeidung von Doubletten
Prüfung bei Neuanlage
• keine Neuanlage ohne vorherige (implizite) Suche
Vermeidung von Doubletten
Abgleich von Doubletten
• Erkennung evtl. im Vorfeld durch Batch-Analysen
• Gegenüberstellung beider Varianten
• Auswahl pro Feld, welche Variante führend ist
• Auswahl pro abhängigem Objekt, welche beibehalten werden
3. Datenqualität aus
Datenbanksicht
Tools zur Datenqualität
Tools zur Datenqualität
•
•
•
•
•
•
Data Quality Services - Analyse und Korrektur von Daten
Datenprofilerstellungstask in SSIS & Datenprofil-Viewer – Analyse von Daten
Master Data Services - Verwaltung von Master Daten
T-SQL - Analyse und Korrektur von Daten
SSIS - Analyse von Daten über selbst definierte Datenflüsse
Data Mining - Analyse von Daten zur Erkennung von Zusammenhängen
Verlassen Sie sich nicht zu sehr auf die Tools!
Data Quality ist ein schmutziges Geschäft!
Data Quality Services
Überblick
•
•
•
•
mit SQL Server 2012 eingeführt
nur für Business Intelligence Edition und Enterprise Edition (bzw. Developer Edition)
bestehend aus einer Server- und einer Client-Komponente
automatisierte und interaktive Verbesserung der Datenqualität
Data Quality Services
Komponenten
• Data Quality Server (3 Datenbanken)
• DQS_MAIN: DQS Engine & SPs sowie veröffentlichte Wissensdatenbanken
• DQS_PROJECTS: KB Management und DQS Project Activities
• DQS_STAGING_DATA: Staging DB für Kopien der Quelldaten
• Data Quality Client
• separate Anwendung
• Wizard-unterstützt
• deckt verschiedene Aufgaben ab: Knowledge Management, Data Quality Projects & Administration
• SSIS DQS Cleansing Component
Data Quality Services
Features
• Data Profiling: Analyse der Datenqualität einer Datenquelle
• Data Cleansing: toolunterstützte interaktive Korrektur
von falschen oder unvollständigen Daten
• Data Matching: regelbasierte Erkennung und Bereinigung von Doubletten
• Reference Data Services: Referenzdaten aus dem Windows Azure Marketplace DataMarket
• Monitoring: Überwachung der Data Quality-Aktivitäten
• Knowledge Base: Regelwerk, das zur kontinuierlichen Verbesserung stets erweitert wird
• Out-of-the-box Regeln
• benutzerdefinierte Regeln
• DQS-generierte Regeln
Data Quality Services - Bewertung
Pro
Contra
• Definition von Regeln und Korrektur von Daten
kann ohne SQL-Kenntnisse erfolgen
• Quelle zur Analyse können Live-Daten sein, Ziel ist
immer separate Tabelle,
Übernahme der Änderungen in Live-Daten muss
explizit implementiert werden
• Vorgeschlagene Änderungen können in Frontend
akzeptiert, angepasst oder abgelehnt werden
• Gute Erkennung von Doubletten
• bisher keine Unterstützung für DB-Schemata
• werden bei Quellenauswahl nicht angezeigt
• beim Speichern mit Schemaname ins dbo-Schema
gezwängt
• irreführende Übersetzungen bei der deutschen
Version:
• "Korrigieren" = "Correct" = "korrekt"
Datenprofilerstellungs-Task & Datenprofil-Viewer
Überblick
•
•
•
•
ab SQL Server 2008 Standard Edition verfügbar
Allgemeine Datenanalyse (Häufigkeit von Inhalten, Längenverteilung etc.)
Datenprofilerstellung über Task in SSIS mit Output in XML-Datei
Analyse des Datenprofils mit separatem Datenprofil-Viewer
Datenprofilerstellungs-Task & Datenprofil-Viewer Bewertung
Pro
Contra
• Führt sehr schnell zu ersten Ergebnissen
• Gut für einen ersten Blick in die Daten, um
zu sehen, wo weitere Analysen Sinn
machen
• Nur für einfache vorgefertigte Analysen
geeignet (8 vordefinierte Profiltypen)
• Nicht „aus einem Guss“: Profilerstellung in
SSIS, Profilanalyse in separatem Tool
Master Data Services
Überblick
• mit SQL Server 2008 R2 eingeführt, mit SQL Server 2012 und 2016 überarbeitet
• nur für Business Intelligence Edition und Enterprise Edition (bzw. Developer Edition)
• Verwaltung, Korrektur und Anreicherung von Masterdaten
Master Data Services
Komponenten
• Master Data Services Configuration Manager
• Erstellung und Konfiguration von MDS Datenbanken und Web Applications
• Master Data Manager (Web-Anwendung)
• administrative Aufgaben: Erstellung von Modellen und Business Rules
• Aktualisierung und Korrektur von Daten
• MDSModelDeploy.exe
• Erstellung und Verteilung von Paketen mit Modellen und Daten
• MDS Web Service
• zur Entwicklung von individuellen MDS-Lösungen
• MDS Add-in for Excel
• Verwaltung von Daten sowie Anlegen von neuen Entitäten und Attributen
Master Data Services
Master Data Manager – Administrative Aufgaben
• Systemverwaltung
• Definition eines Master Daten Modells
• Erstellung von Modellen, Entitäten, Attributen,
Hierarchien und Business Rules
• Integrationsmanagement
• Batch-Import von Daten in MDS
• Erstellung von SQL-Ansichten für Abonnenten
• Versionsverwaltung
• Einfrieren eines Standes der Masterdaten als Version
• Benutzer- und Gruppenberechtigungen
• Verwaltung von Berechtigungen um auf Masterdaten zuzugreifen und diese zu ändern
Master Data Services
Master Data Manager - Datenaufgaben
• Explorer
• Validierung von Daten anhand der definierten Business Rules
• Änderungen an den Daten selbst
• Master Data Services in Excel installieren
• Installation des Excel Add-Ins
• Benutzer können Daten laden und Massenbearbeitung ausführen
• Administratoren können Daten laden, Entitäten erstellen und Attribute aktualisieren
Master Data Services
Neuerungen von SQL Server 2016
• neue Web-Oberfläche
• übersichtlicher, weniger Silverlight
• Allgemein/Verwaltung
•
•
•
•
Attribut-Namen nicht mehr auf 50-Zeichen beschränkt
m:n-Relationen in abgeleiteten Hierarchien
Entity Sync Relationships um Entity Daten in verschiedenen Modellen zu nutzen
Business Rules nun mit NOT und OR-Operatoren sowie benutzerdefinierter SQL Logik möglich
• bessere Performance
• Compression (Database: Row Level Data Compression, IIS/Web: Dynamic Content Compression)
• Indexing (Custom Non-Clustered Indizes, SQL Server Agent Job für Index- und Log-Wartung)
• Excel Add-in 4-15x schneller (je nach Excel Version)
Master Data Services - Bewertung
Pro
Contra
• Zentrale Verwaltung von Masterdaten
• Versionen von Masterdaten können
eingefroren werden
• Masterdaten können von verschiedenen
Anwendungen und Umgebungen genutzt
werden
• Sehr komfortabel im Zusammenspiel mit
Excel 2016 und PowerQuery
• hoher Aufwand für Modellerstellung
• nur für Referenzdaten / Masterdaten
sinnvoll einsetzbar
• korrigierte Daten müssen explizit in
Zielumgebung eingespielt werden
• Datenbankarchitektur muss gravierend
modifiziert werden
• Installation bereitet oft Probleme (wg.
Windows Komponenten, Silverlight etc.)
Datenbereinigung mit T-SQL
Überblick
•
•
•
•
für alle Editionen und Versionen verfügbar
manuelle Erstellung von SQL-Skripten zur Datenbereinigung
unverändert auf verschiedenen Umgebungen verwendbar (Entwicklung, Test, Produktion)
bewährte Skripte können regelmäßig über SQL Server Agent Jobs ausgeführt werden
Datenbereinigung mit T-SQL
Beispiel - Vereinheitlichung von Daten einer Domain (z.B. Telefonnummern)
1.
2.
3.
Über Systemview INFORMATION_SCHEMA.Columns alle Tabellen finden,
in denen Telefonnummern gespeichert werden
Skalarfunktion zur Bereinigung von Telefonnummern erstellen
Analyse/Aktualisierung der Tabelle mit Hilfe der Funktion
SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE COLUMN_NAME LIKE 'PhoneNumber' AND TABLE_NAME NOT LIKE 'v%'
SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE DOMAIN_NAME = 'Phone' AND TABLE_NAME NOT LIKE 'v%'
Datenbereinigung mit T-SQL
CREATE FUNCTION DQ.UFN_CleansePhoneNumber(@PhoneNumber dbo.Phone)
RETURNS dbo.Phone
AS
BEGIN
DECLARE @PhoneNumberNew dbo.Phone
SET @PhoneNumberNew = REPLACE(REPLACE(@PhoneNumber, '(', ''), ')', '')
RETURN @PhoneNumberNew
END
;WITH CTE AS
(SELECT *, Test.UFN_CleansePhoneNumber(PhoneNumber)
AS PhoneNumberNew FROM Test.PersonPhone)
UPDATE PP SET PhoneNumber = PhoneNumberNew
WHERE PhoneNumber <> PhoneNumberNew
Datenbereinigung mit T-SQL
Beispiel - allgemeine Bereinigung: Entfernung von unnötigen Leerzeichen in Strings
1. Über Systemview INFORMATION_SCHEMA.Columns alle Spalten finden,
in denen Strings gespeichert werden
2. Skalarfunktion zur Bereinigung von Strings erstellen
3. Analyse/Aktualisierung der Tabelle mit Hilfe der Funktion
SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE DATA_TYPE LIKE '%char'
AND TABLE_NAME NOT LIKE 'v%‘
Datenbereinigung mit T-SQL
CREATE FUNCTION DQ.UFN_CleanseString(@String nvarchar(255))
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @StringNew nvarchar(255)
SET @StringNew = @String
SET @StringNew = LTRIM(RTRIM(@StringNew))
WHILE @StringNew LIKE '%
%'
SET @StringNew = REPLACE(@StringNew, '
RETURN @StringNew
END
', ' ')
Datenbereinigung mit T-SQL
UPDATE Test.Address
SET City=DQ.UFN_CleanseString(City)
WHERE City<>DQ.UFN_CleanseString(City)
WHERE LEN(City)<>LEN(DQ.UFN_CleanseString(City))
WHERE DATALENGTH(City)<>DATALENGTH(DQ.UFN_CleanseString(City))
Datenbereinigung mit T-SQL
Doublettenerkennung
• Scharfer Vergleich (relevante Spalten identisch) problemlos implementierbar
• Unscharfer Vergleich (relevante Spalten ähnlich) problematisch
• Über LIKE-Operator explizite Vergleiche realisierbar
• Über LEFT-Funktion Vergleich der ersten n Zeichen realisierbar
• Über SOUNDEX und DIFFERENCE bedingt Ähnlichkeitsvergleiche möglich
Problematische Beispiele
•
•
•
•
•
SELECT
SELECT
SELECT
SELECT
SELECT
SOUNDEX('Nieß'), SOUNDEX('Niess')
SOUNDEX('Müller'), SOUNDEX('Mueller')
SOUNDEX('Mau'), SOUNDEX('Mieß')
SOUNDEX('Phone'), SOUNDEX('Penny')
SOUNDEX('Phonecall'), SOUNDEX('Penicilin')
Datenbereinigung mit T-SQL
Pro
Contra
• Korrektur kann direkt in Zielumgebung
erfolgen (Locking berücksichtigen!)
• ideal zur Bereinigung von Massendaten, sofern
keine zusätzliche Prüfung erforderlich
• durch Verwendung von Systemviews
generische Skripts realisierbar
• über .NET CLR erweiterbar (z.B. RegEx,
fuzzystring.codeplex.com)
• über SQL Server Agent Jobs leicht
automatisierbar
• auch etappenweise (TOP) in
Produktivumgebungen ausführbar
• SQL-Kenntnisse sowie direkter Serverzugriff
erforderlich
• hoher Aufwand in der Vorbereitung
(Skripterstellung)
• für Doublettenabgleich nur bedingt geeignet
Datenbereinigung mit SSIS
Überblick
•
•
•
•
•
ab SQL Server 2005 verfügbar
Einfache Transformationen ab Standard Edition, Fuzzy Transformationen ab Enterprise
Fuzzy Lookup / Fuzzy Grouping für ungenaue Suchen mit definierbarem Schwellwert
individuelle Regeln über Abgeleitete Spalte und Bedingtes Teilen realisierbar
kombinierbar mit DQS Cleansing Component
Datenbereinigung mit SSIS
Datenbereinigung mit SSIS
Datenbereinigung mit SSIS
Pro
Contra
• individuelle (beliebig komplexe) Workflows
realisierbar
• mit SQL Skripts, .NET Code (z.B. für RegEx)
etc. kombinierbar
• über zusätzliche Komponenten erweiterbar
(z.B. ssisdqsmatching.codeplex.com)
• über SQL Server Agent Jobs leicht
automatisierbar
• SSIS-Kenntnisse sowie direkter Zugriff auf
Daten erforderlich
• relativ hoher Aufwand in der Vorbereitung
(Paketerstellung)
• Quelle zur Analyse können Live-Daten sein,
Ziel ist immer separate Tabelle,
Übernahme der Änderungen in Live-Daten
muss explizit implementiert werden
4. Fazit
Checkliste für bestmögliche Datenqualität
bei Planung alle Bereiche (Daten, Anwendung und User) berücksichtigen
bei Umsetzung klein beginnen (Konzentration auf das Wesentliche)
stets Verhältnis von Aufwand zu Nutzen im Auge behalten
regelmäßige Datenpflege (Analyse und Korrektur)
Zuständigkeiten klar definieren
keine Schuldigen sondern Lösungen suchen
Welches Tool für was?
• Datenanalyse: DQS & Datenprofilerstellungs-Task
• Tiefgehende Analysen von Datenzusammenhängen: SSAS Data Mining
• Manuelle Korrektur von Daten: DQS & MDS
• Verwaltung von Master Daten: MDS
• Wiederholt auszuführende triviale Korrekturen: T-SQL-Skripts
• Doublettenanalyse: DQS & SSIS (Fuzzy Lookup & Fuzzy Grouping)
Finden Sie den passenden Mix für Ihre Umgebung!
Weitere Infos - Literatur
Implementing a Data Warehouse with Microsoft SQL Server 2012
Training Kit (Exam 70-463)
Dejan Sarka, Matija Lah, Grega Jerkic
Microsoft Press, 2012
ISBN: 978-0-7356-6609-2
Data Quality and Master Data Management with MS SQL Server 2008 R2
Dejan Sarka, Davide Mauri
SolidQ
http://www.solidq.com/product/data-quality-and-master-data-management-with-microsoft-sql-server-2008-r2
DQS step-by-step mit SQL-Server: SQL-Server Data Quality Services
Alexander Karl
http://www.net-cde.de/publications
Weitere Infos - Links
Books Online
•
Data Quality Services: https://msdn.microsoft.com/en-us/library/ff877925.aspx
•
Master Data Services: https://msdn.microsoft.com/en-us/library/ee633763.aspx
•
Microsoft TechNet - SQL Server Video Archive: https://technet.microsoft.com/de-de/dn912438
SQL Server 2016 – Master Data Services
•
What's New in Master Data Services (MDS): https://msdn.microsoft.com/en-us/library/ff929136.aspx
•
Master Data Services Team Blog: http://sqlblog.com/blogs/mds_team/archive/2015/05/27/what-s-new-in-sql2016-ctp2-release.aspx
•
Master Data Services 2016 CTP 2.2 - Explorer functionality quick fix:
http://www.sqlservercentral.com/blogs/sql-bi/2015/08/28/master-data-services-2016-ctp-22-explorer-functionality-quick-fix/
Allgemeine Links zum Thema Datenqualität
•
20th Annual MIT International Conference on Information Quality: http://iciq.global/2015/index.html
•
Whitepaper - In 5 Schritten zu besseren Unternehmensdaten: http://www.sas.com/de_de/whitepapers/ba-wp-fuenf-schritte-zu-besseren-daten-1998531.html
•
Sieben Regeln für bessere Datenqualität: http://www.computerwoche.de/a/sieben-regeln-fuer-bessere-datenqualitaet%2C2516239
Links des Referenten
•
SQL Server Blog: http://pantheronsql.wordpress.com
•
CGI: http://www.de.cgi.com / http://www.cgi.com
Vielen Dank!
eMail: [email protected]