DQM_Seminar_2010

<Insert Picture Here>
Datenqualität sichern
Wenn sich Controlling und Buchhaltung streiten
Praxisseminar zu Datenqualitätsanalysen mit
der Service GmbH als Fallbeispiel
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
So...
...oder so?
So...
...oder so?
Wer glaubt schon bunten Charts?
Die Kosten der schlechten Daten
Ohne Daten kein Business
Daten sind der Treibstoff der Prozesse
Information Chain
Marketing
Werbung
Adresse
KD-Daten
Bedarf
Adresse
Kreditdaten
Kunde
Angebot
Kredit OK
Kundenbetreuer
Bestelldaten
Bestand
Stammdaten
Order
Logistiksystem
Lager
Buchhaltung
Verkaufsdaten
Lieferschein
Spedition
Rechnung
Mahnung
Bezahlung
Reklamation
Kunde
Operative Prozesse
Ohne Daten kein Business
Schlechte Daten sind wie Sand im Getriebe der Geschäftsprozesse
Information Chain
Marketing
Werbung
Adresse
KD-Daten
Bedarf
Adresse
Kreditdaten
Kunde
Angebot
Kredit OK
Kundenbetreuer
Bestelldaten
Bestand
Stammdaten
Order
Logistiksystem
Lager
Buchhaltung
Verkaufsdaten
Lieferschein
Spedition
Rechnung
Mahnung
Bezahlung
Reklamation
Kunde
Operative Prozesse
Der Schnittstellen-Aspekt
Wo findet das Profiling / die
Fehlersuche statt?
Unterschiedliche
Daten und
Fehlerquellen
Einheitliche
Wandlung der Daten
Getrennte
Aufbereitung
von Daten
Einheitliche
Wandlung
der Daten
Pot. Fehler
optimal
CRM
Data Marts
SCM
Bereitstellung
Data
Warehouse
Akzeptabel
Aber nicht
optimal
Potentiell
falsch
ERP
Nicht
glaubhaft
Verlässlichkeit
Wo findet das Profiling / die
Fehlersuche statt?
Unterschiedliche
Daten und
Fehlerquellen
Einheitliche
Wandlung der Daten
Getrennte
Aufbereitung
von Daten
Einheitliche
Wandlung
der Daten
Pot. Fehler
CRM
Data Marts
SCM
Bereitstellung
BI Tool A
Data
Warehouse
BI Tool B
ERP
Konsolidierung
Konsolidierter
Datenbereich
BI Tool C
Datenqualität bezogen auf den
Warehousing – Prozess
Unterschiedliche
Daten und
Fehlerquellen
• Heterogene Datenmodelle / Konsistenz / Homonyme / Synonyme
• Kontinuität des Ladevorgangs / Vollständigkeit
• Widerspruchsfreiheit zwischen den Quellen
CRM
Data Marts
SCM
Bereitstellung
BI Tool A
Data
Warehouse
BI Tool B
ERP
Konsolidierung
Konsolidierter
Datenbereich
BI Tool C
Datenqualität bezogen auf den
Warehousing – Prozess
Unterschiedliche
Daten und
Fehlerquellen
• Heterogene Datenmodelle / Konsistenz / Homonyme / Synonyme
• Kontinuität des Ladevorgangs / Vollständigkeit
• Widerspruchsfreiheit zwischen den Quellen
++
CRM
Data Marts
SCM
ERP
Bereitstellung
Konsolidierung
Metadaten
BI Tool A
+
BI Tool B
-
Data
Warehouse
Konsolidierter
Datenbereich
•Eindeutige Datenobjekte
Beschreibungen
•Homonyme / Synonyme BI Tool C
•Anwendungsneutral
-Verlässlichkeit
Wo sollten Korrekturen stattfinden
Correction
Data Load
Data
Warehouse
Operative Anwendung
Vorsysteme bzw. Fachabteilungen sind in der Pflicht!
Wo sollten Korrekturen stattfinden
?
Operative Anwendung
Correction
Data
Warehouse
Operative Anwendung
Operative Anwendung
Data Load
Die Qualität von Data Warehouse daten
wird immer wichtiger
MIS
Controlling
Produkt
Management
Analytisches
CRM
Informationsbasis
Oracle Data Warehouse
Call
Center
Internetzugriffe
Beschwerden
Operatives
CRM
Diversifizierung
Marketing-Material
Warum wächst die Herausforderung
der Qualität der Daten
Gewachsene Bedeutung
des Faktors Information
für den Erfolg von
Unternehmen.
Fehlende Praxis in
Datenmanagement
Datenqualität
Immer häufigere
Prozessänderungen
Ausufernde
Datenmengen
Vermehrtes Inseltum
durch FertigAnwendungen
Was ist Datenqualität?
Aspekte (Dimensionen) der Datenqualität
Brauchbarkeit
der Daten!
1. Korrekt
2. Stimmig
3. Vollständig
4. Dokumentiert
5. Redundanzfrei
6. Aktuell
7. Verfügbar (Access)
8. Nützlich (TCO)
9. Handhabbar
10. Vertrauenswürdig
11. Harmonisch
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Die
SERVICE GmbH
Fallbeispiel
Die SERVICE GmbH
• Vermittlung von Dienstleistungen
für Endkunden rund um das Handwerk
• Handwerksleistung
• Darlehen
• Großhandel für Baumärkte und
Einzelhandel
• Haushaltswaren
• Heimwerker
• Gartenbedarf
• KFZ-Zubehoer
• Elektroartikel
•Bereich Internet-/Versandhandel
• Computerteile
SERVICE GmbH
Die SERVICE GmbH
• Unterscheidung
• Privatkunden
• Firmenkunden
• Kundenkarte
• Privatkunden
• Entstand aus Zusammenschluss
mehrerer Vertriebsgesellschaften
• Integration der Stammdaten
„mit Hindernissen“
SERVICE GmbH
Erwartungen aus dem Unternehmen
Buchhaltung: Es fehlen Daten
Warum sind die Spediteursrechnungen so hoch?
Sind alle Bestellungen korrekt bezahlt worden?
Wie hoch sind die Versandkosten pro Lieferung?
Was wurde storniert?
Controlling:
Vergleichbarkeit fehlt
Marketing: Absatzzahlen
sind nicht aussagefähig
 Was kosten Produkte im
Einkauf?
 Wie teuer wurden Produkte
verkauft?
 Wie rentabel sind einzelne
Produkte?
Wie viel Kunden gibt es?
Lohnt die Kundekarte?
Welche Segmentierung gibt es?
Vertrieb: wünscht leichtere
Auswertungen
Was sind wichtige Produkte?
Was sind rentable Sparten?
Hat sich der Servicebereich
gelohnt?
SERVICE GmbH
Vertrieb
Marketing
Management
Buchhaltung
Controlling
Management:
Kennzahlen fehlen
 Wie hoch sind die
liquiden Mittel?
 Wie hoch sind die
Außenstände?
Bekannte Probleme:
• Bestimmte Lieferungen erreichen nie den Adressaten
• Adressen falsch
• Die Lieferung wird auch nicht bezahlt
• Oft Privatkunden
• Von bestimmten Artikeln werden sehr viele Stückzahlen
verkauft
• In den Statistiken laufen diese Produkte jedoch unter Verlustbringern
• (Verpackungsmengen stimmen nicht mit denen bei den Lieferanten
bezahlten Mengen überein)
• Was geschieht mit den Retouren?
• Lieferantenname in Produkte_Stamm passt nicht auf die
Lieferantennummer in der Lieferantentabelle
• Es gibt auch keine passenden Felder
Strategische Fragestellungen
• Welches sind die wirklich profitablen Produkte/Services?
•
•
•
•
Wo wird am meisten Kapital gebunden?
Welche Produkte beschaffen am meisten Kapital?
Welche Produkte verursachen den höchsten Aufwand?
Wie sind die Trends?
• Auf welche Bereiche soll man sich künftig stärker
fokussieren
• Einzelhandel?
• Servicevermittlung?
• Großkundengeschäft?
• Kann die verkaufte Menge genau festgestellt werden?
• Welcher Vertriebsmitarbeiter macht welchen Umsatz?
• Wie hoch ist die Kapitalrückflussquote
• Ausstände?
• Kreditlimits?
• Liquide Mittel für Neuinvestitionen?
Das Analysemodell zeigt oft andere
(strategische) Fragestellungen auf,
die zunächst nicht auf der operativen
Ebene offensichtlich sind.
Die Controlling-Sicht
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-/Verfahrenstechniken
 Fallbeispiel Service GmbH
„Induktives und deduktives“ Vorgehen
• Wir wissen, vermuten
Dinge die nicht stimmen
• Wir können sinnvolle Analysen
aufgrund bekannter Dinge ableiten
Vermutungen
verifizieren
• Wir lassen uns überraschen,
was da noch kommt
• Wir stöbern in den Daten und
• entdecken Auffälligkeiten
• beginnen zu kombinieren
• stellen Hypothesen auf
• versuchen Zusammenhänge
zu beweisen
Neues
entdecken
Vorgehensweisen / Methoden
im Data Profiling
..
..
..
Metadaten
Data Quality Assessement
Erwartungen an die
Datenqualität
Assertion Testing
Abgleich
Metadata Verification
Neue Erkenntnisse
(Überraschungen)
Discovery
Bottom up
Unternehmensdaten
Data
Profiling
Methoden und Hilfsmittel
•
•
•
•
•
•
•
•
Datenmodellierung
Datenqualitätsprüfmethoden
Data Profiling
Data Profiling Tool
Attribut-Klassifizierung (Namen)
Kategorisierung von Qualitätsregeln
ETL-Tool
Datenbank
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Vorgehensmodell Datenqualitätsanalyse
Zieldefinition
Geschäftsregeln
Erwartungen
Bestandsaufnahme
Owner
User
Ressourcen
Kosten
Modelle
Problemkomplexe
Priorisieren
Strukturanalysen
Felder
Objekte
Beziehungen
Hierarchien
Regelanalysen
Daten
Werte
Fach
Umsetzung Ergebnisse
Abgleich-Alt
Neudefinition
Monitoring
6 Phasen, 95 Aktivitäten, 16 Ergebnis-Templates, 1 Metamodell, Klassifizierungen
Bottom Up
Top Down
Planen
Vorgehensmodell für
Datenqualitätsprojekte
Erheben der Grunddaten
(Ist-Daten, Wahrnehmungen, Ziele)
Beschreibung der
Geschäftsprozesse
Geschäftsfelder
Data Owner / Daten-Interessenten / Konsumenten
DQ-Erwartungen
Bekannte Schwachstellen
Kosten
Prioritäten
Objektmodell
Datenflüsse und – Schnittstellen
Bekannte Geschäftsregeln
(Ist-Daten, Wahrnehmungen, Ziele)
Daten-/Modell-Prüfungen
Detailanalyse
Vollständigkeitsbetrachtung
Betrachtung der Verständlichkeit
Schlüsselanalysen / Beziehungsanalysen
Analyse von Hierarchien
Suche nach Redundanzen (z. B. Normalisierung)
Mengenanalyse / Stammdatenabgleiche
Überprüfen der Geschäftsregeln
Analyse der erkannten Schwachstellen
Verifizieren der DQ Erwartungen
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Datenmodellierung
Ziel:
Aufspüren und Minimierung von Redundanzen
als eine der Hauptursachen von Datenfehlern
• Schlüssel
• Identifizierung von Dingen
• Functional Dependencies
• Versteckte Abhängigkeiten
• Beziehungen
• Existenzabhängigkeit
• Orphans
• Childless
• Normalisierung
• One Fact One Place
Normalisierung
• 1. Normalform
• Eine Entity ist in der 1. Normalform, wenn jedes seiner Attribute
genau einen Wert in sich aufnimmt. Sammlungen von Werten in
Attributen oder unterschiedliche Verwendungen sind nicht erlaubt.
Die Werte sollten nicht weiter teilbar, sondern von granularer Natur
sein.
• 2. Normalform
• Eine Entität befindet sich in der 2. Normalform, wenn alle Attribute
von dem kompletten Schlüssel abhängig sind.
• 3. Normalform
• Eine Entität befindet sich in der 3. Normalform, wenn alle Attribute
von dem Primary Key abhängen und nicht von Nicht-Schlüssel
anderen Attributen mitbestimmt werden (funktionale Abhängigkeit).
1. Normalform
2. Normalform
3. Normalform
3. Normalform
3. Normalform
Primary Key
NichtschlüsselAttribut
Nicht von einem Schlüssel
Abhängige Attribute
3. Normalform
Funktionale Abhängigkeit
Tabelle PRODUKTE_STAMM
Artikelnummer (PK) Artikelname
Zusätzliche „verborgene“
Functional Dependency
Beschreibung Artikelgruppennummer Artikelgruppe
Functional Dependency
über Primary Key (PK)
Funktionale Abhängigkeit
Tabelle ARTIKEL_GRUPPE
Artikelgruppennummer Artikelgruppe Beschreibung
Tabelle PRODUKTE_STAMM
Artikelnummer (PK) Artikelname
Zusätzliche „verborgene“
Functional Dependency
Redundante
Daten mit der Gefahr
von fehlerhaften
Einträgen
Beschreibung Artikelgruppennummer Artikelgruppe
Functional Dependency
über Primary Key (PK)
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Wortstammanalyse hilft bei der
Klassifizierung von Column-Namen
Hauptwort
Eigenschaftsbenennung
Basistyp
Kunden_Wohnart_Nr
Information
zu einem
Kunden
wird
beschrieben
Die Art und
Weise, wie
ein Kunde
wohnt wird
beschrieben
unterschiedliche
Wohnungsarten sind
durchnummeriert
Bezugsobjekt
Beschreibende
Information
Charakter des
Attributes
Feldyp und
Art des Wertes
Rolle in
Ab-hängigkeitsbe-ziehung
Sind NULLs
erlaubt
Muss
Eindeutigkeit
vorliegen
Identifikatoren und
bezeichnende
Begriffe
meist numerisch
LHS
nein
ja
Beschreibungen,
Erzählungen, Texte
meist Text ,
beliebige Zeichen
RHS
ja
nein
Klassifikatoren
alphanumerisch,
in Bezug setzende
Begriffe,
oft wenige Werte
RHS
eher nicht, eine
Klassifizierung
sollte für alle Sätze
gelten
nein
Zustände
meist Text ,
beliebige Zeichen
RHS
nein
Zeiten
Date / Time
RHS
eher nicht, denn
Zustände sollten
für alle Sätze
gelten,
ja
Sequenzen,
Aufzählungen
Zählwerte)
meist numerisch,
oft versteckte
Schlüsselkandidaten
LHS
nein
ja
Mengen
meist numerisch,
einfache
Zahlenwerte ohne
weitere Angaben
RHS
ja
Operatoren und
abgeleitete Größen
meist Text ,
beliebige Zeichen
RHS
nein,
wenn etwas
gezählt wird, sollte
es immer gezählt
warden
nein
Werte
meist numerisch,
einfache
Zahlenwerte ohne
weitere Angaben
(brauchen i. d. R.
eine relativierende
Bezugsgröße z. B.
Preis -> Währung)
meist Text ,
beliebige Zeichen
RHS
nein
ja
RHS
nein
ja
Basistypgruppe
(brauchen i. d. R.
eine relativierende
Bezugsgröße z. B.
Preis -> Währung)
Maße,
Bezugsgrößen,
Einheiten
nein
ja
Feldliste
select
substr(table_name,1,18)
substr(column_name,1,28)
substr(data_type,1,8)
substr(data_length,1,3)
'|
'
'|
'
'|
'
'|
'
'|
'
'|
'
'|
'
'|
'
From dba_tab_columns
where table_name in
('PRODUKTE_STAMM',
'BESTELLUNG',
'LIEFERUNG',
'STORNIERUNG',
'BEST_POSITION',
'Zahlung',
'PRODUKTE_STAMM',
'ARTIKEL_GRUPPE',
'ARTIKELSPARTE',
'KUNDEN_STAMM',
'LIEFERANT',
'LAGER')
and owner = 'SG'
order by col
Tab,
Col,
Typ,
Len,
Nul,
Basis,
Bus,
Syn_zu,
Hom_zu,
Dom,
Max,
Min
• Über alle Tabellen
hinweg
• Alphabetisch sortiert nach
Spaltennamen
• Hilft beim Erkennen von
Homonymen und
Synonymen
• Hilft bei der Bewertung der
Tauglichkeit von
Spaltennamen
• Erlaubt Vorahnungen von
Schlüsselkandidaten
Feldliste
TAB
-----------------KUNDEN_STAMM
LIEFERANT
KUNDEN_STAMM
PRODUKTE_STAMM
PRODUKTE_STAMM
ARTIKEL_GRUPPE
PRODUKTE_STAMM
ARTIKEL_GRUPPE
PRODUKTE_STAMM
PRODUKTE_STAMM
BEST_POSITION
ARTIKELSPARTE
ARTIKEL_GRUPPE
ARTIKELSPARTE
KUNDEN_STAMM
KUNDEN_STAMM
PRODUKTE_STAMM
PRODUKTE_STAMM
BESTELLUNG
STORNIERUNG
LIEFERUNG
BEST_POSITION
BEST_POSITION
BESTELLUNG
BEST_POSITION
STORNIERUNG
LIEFERUNG
LIEFERUNG
BESTELLUNG
STORNIERUNG
LIEFERANT
KUNDEN_STAMM
KUNDEN_STAMM
KUNDEN_STAMM
COL
---------------------------ANREDE
ANSCHRIFT
ANZ_KINDER
ANZ_STEUCK_PRO_VERPACKUNG
ARTIKELGRUPPE
ARTIKELGRUPPENNAME
ARTIKELGRUPPENNR
ARTIKELGRUPPENNR
ARTIKELNAME
ARTIKELNR
ARTIKELNUMMER
ARTIKELSPARTENNAME
ARTIKELSPARTENNR
ARTIKELSPARTENNR
BERUFSGRUPPE
BERUFSGRUPPEN_NR
BESCHREIBUNG
BESTAND
BESTELLDATUM
BESTELLDATUM
BESTELLDATUM
BESTELLDATUM
BESTELLMENGE
BESTELLNR
BESTELLNR
BESTELLNR
BESTELLNR
BESTELL_TOTAL
BESTELL_TOTAL
BESTELL_TOTAL
BEVORZUGUNG_KLASSE
BILDUNG
BILDUNGS_NR
BRANCHE
TYP
-------VARCHAR2
VARCHAR2
NUMBER
NUMBER
VARCHAR2
VARCHAR2
NUMBER
NUMBER
VARCHAR2
NUMBER
VARCHAR2
VARCHAR2
NUMBER
NUMBER
VARCHAR2
VARCHAR2
VARCHAR2
NUMBER
DATE
DATE
DATE
DATE
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2
VARCHAR2
VARCHAR2
LEN
--10
40
22
22
50
50
22
22
30
22
400
18
22
22
30
1
400
22
7
7
7
7
22
22
22
22
22
22
22
22
22
30
1
30
NUL
-----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
BASIS
-----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
BUS
-----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SYN_ZU
-----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
HOM_ZU
-----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DOM
-----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
MAX
-----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
MIN
----|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
BEST_POSITION
POSNUMMER
NUMBER
22
|
|
|
|
|
|
|
|
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Metadaten-Dokumentation – Data Quality Plan
 Datenmodellierung – Die Grundlage
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Data Profiling Tool
• Standardanalysen
•
•
•
•
•
•
•
•
Unique Keys
Functional Dependencies
Relationships
Domains
Redundant Columns
Patterns, Types
Statistiken
Six Sigma
• Rules (Business-/ IT-Rules)
• Generierung von
Korrekturen
• Auditing
• Eingebettet in ein ETL-Tool
• hohe Flexibilität beim
Bereitstellen von Daten
• Direktes Anwenden erkannter
Regeln für eine spätere
Datenaufbereitung und
Minitoring
• Ablaufumgebung ist die
Datenbank
• Datennähe
Data Profiling Tool
Methoden
Die
operativen
Daten
Feintuning zu
den Analysemethoden
Protokollierung
laufende
Analysen
Drill Down zu den operativen Daten
ETL - Tool
• SQL-basiert
• wenig Lernaufwand
• Ablaufumgebung ist die Datenbank
• hohe Performance
• Wiederverwendung von DB-Funktionen und Infrastruktur
• Metadaten- / Modell-gesteuert
Profiling mit OWB
• Lizenz-Situation
• Arbeiten mit dem Tool
• Alternativen
Warum ist ein Tooleinsatz bei
Datenqualitätsanalysen sinnvoll?
• Das meiste geht auch ohne Tool, allerdings
mühsam
Functional Dependencies
Starten eines
GenerierungProfiling-Laufs Rule
Starten einer CorrectionMapping-Generierung
Auswahl und Ergebnisansicht Methoden
Die Tabellen,
die zu dem
Analysefukus
gehören
Tabellen-Darstellung
Chart-Darstellung
Feintuning
zu den
Analysemethoden
Drill-Werte
Operative Datensätze
AnalyseJobProtokolle
Aktivierbare Business Rules
Analyseumgebung
LDAP
DBMS_LDAP
non Oracle
DB2, SQL Server
Informix, Teradata
Meta Daten
Repository
Gateway
/ ODBC
/ FTP
Oracle 9i / 10g / 11g
SAP
Integrator
SAP R/3
Source
Stage
Siebel CRM
Oracle
eBusiness
Text / XML
Profiling
Stage
Analyse Datenbank
Direct Path
DBLink
Transportable
Modules
Eindeutigkeitsanalysen (Unique Key)
Wertebereichsanalysen (Domain)
Funktionale Abhängigkeiten
Beziehungen (Relational)
Wertmustererkennung (Pattern)
Formate (Data Type)
Statistiken (Aggregation)
Individuelle Regeln (Data Rules)
Korrekt: Zusammen 100%
(Alle Fälle erfasst)
Korrekt, muß 0 sein
Korrekt:
Es kann nur ein
Wert gepflegt sein.
Korrekt, muß 0 sein
Korrekt, muß 0 sein
Korrekt, das sind
die richtigen Werte
Korrekt, das sind
richtige Werte
Problem:
kein Schlüsselfeld
ist gepflegt
Korrekt, muß 0 sein
Korrekt:
Zusammen
100%.
(Alle Fälle erfasst)
Korrekt, muß 0 sein
Problem
Korrekt
Native PL/SQL Profiling Routinen
•
•
•
•
Können von jedem genutzt werden
Können verändert und weiterentwickelt werden
Laufen in der Datenbank
Decken die wichtigsten Profiling-Funktionen ab
• Aggregationen
• (Max/Min, Nulls, Selektivität, Numerisch, alphanumerisch, prozentual Anteile
• Domain-Analyse
• Liste der Domains, Anzahl Domain-Werte, prozentuale Anteile
• Pattern-Analyse
• Liste der Pattern, Anzahl Pattern-Werte, prozentuale Anteile
• Functional Dependency
• LHS-Key,RHS-Attribute, Anzahl DP-Treffer, prozentuale Anteile, Anzahl
Fehlerfälle, Orphan-Anzahl, Orphan-Prozent
• Data Type Analyse
• Numerisch, alphanumerisch, date, null, Anzahl Werte, prozentuale Werte
• Unique-Key-Analyse
• Anzahl Treffer, Anzahl Sätze, prozentualer Anteil
Native PL/SQL Profiling Routinen
DATA Viewer
SQL Developer
sys.dba_tab_columns
DQ_Kunde_UNIQUE
DQ_Kunde_DOMAIN
DQ_Kunde_PATTERN
Kunde
DQ_Kunde_TYPE
DQ_Kunde_AGGR
DQ_CALL_UNIQUE
DQ_CALL_DOMAIN
DQ_CALL_PATTERN
DQ_CALL_TYPE
DQ_CALL_AGGR
DQ_CALL_FD
DQ_Kunde_FD
Datenschema
SYS Schema
Hilfsscripte
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Alpha_Num_Check.txt
Domain_Analyse.txt
DQ_Datatype.txt
DQ_Datatype_Proc.txt
DQ_Domain.txt
DQ_Domain_Proc.txt
DQ_Feldliste_T300.txt
DQ_Statistik.txt
FD.txt
Feldabhaengigkeit_FD.txt
Feldabhaengigkeit_logisch.txt
Feldabhaengigkeit_logisch_Wertemengen.txt
Feldabhaengigkeit_logisch_Wertereihenfolge.txt
Feldstatistik.txt
•
•
•
•
•
•
•
•
•
•
•
•
•
isdate_2.txt
isnumber.txt
Kardinalitaet.txt
Maske.txt
Muster_Pattern.txt
Orphans_Childless.txt
Pattern.txt
Redundant.txt
Regular_Expression_Syntac.txt
strcnt.txt
Tabellenuebergreifende_Feldabgaengigkeit.txt
Unique_test.txt
WITH_Clause_Numeric-Check.txt
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Datenmodellierung – Die Grundlage
 Metadaten-Dokumentation – Data Quality Plan
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Die wichtigsten Analyse-Techniken
1.
2.
3.
4.
5.
Fragebögen / Templates
Vollständigkeitsanalyse
Schnittstellenanalyse
Wo und wie liegen Daten vor?
Synonymen / Homonymenanalyse
Verfahren
6. Prüfungen von Datenstrukturen
• Felder, Tabellen, Beziehungen
• Originaldaten oder Kopien?
• Müssen alle Daten analysiert werden?
Techniken
Prüfungen von
Datenstrukturen
Attribut – bezogen
• Not Null / Pflichtfelder
• Füllgrade
• Formatangaben
•
•
Typen, Längen
Pattern
• Check Constraint
• Wertbereiche (Domains)
•
•
Ober-/Untergrenzen / Wertelisten
Average
Satz – bezogen (Tupel)
• Abhängigkeiten von Werten in anderen
Attributen desselben Satzes
Satzübergreifend (Relationen)
• Primary Key / Eindeutigkeit
• Aggregat – Bedingungen
•
•
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
• Rekursive Zusammenhänge
•
Verweise auf andere Sätze derselben Tabelle
(Relation)
Tabellenübergreifende (Interrelational)
• Foreign Key
• Aggregat – Bedingungen
•
•
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
• Rekursive Zusammenhänge
•
Verweise auf Sätze einer anderen Tabelle (Relation)
Zeit – bezogen (Tupel)
• Zeitinvariante Inhalte
•
Umgang mit Ereignisse
• Zeitabhängige Veränderungen
• Über die Zeit mit anderen Daten
korrelierende Feldinhalte
Verteilungs – bezogen
• Arithmetische Mittel
• Varianz / Standardabweichungen
• Qualitätsmerkmale und Mengen
Prüfungen in der Datenbank
• Viele Prüfungen in der Datenbank
• An OLTP-Anfordertungen orientiert
• Oft nicht Massendaten-tauglich
• Prüfen großer Datenmengen mit mengenbasierter
Logik
•
•
•
•
CASE
Zwischentabellen
WITH
Sub-Select
Prüfkonzepte in der Datenbank
Statistiken
Prüfungen
z. B. mit Table Functions
Stage-Tabelle
Kopieren
Varchar2()
Geprüfte Daten
Date
Number
Varchar2()
• Programmieraufwendig
• Kann
performance-kritisch
sein
• Erlaubt auch
fachliche Prüfungen
Prüfkonzepte in der Datenbank
Stage-Tabelle
+ Geprüfte Daten
Kopieren
Statistik
Routine
Date
Number
Varchar2()
Check
Constraints
Bad
File
DML
Error Log
Fehlerhafte
Sätze
Statistiken
• Einfach
implementierbar
• Bessere Performance
• Nur bei aktivierten
Constraints
• Fachliche Prüfungen
kaum möglich
• Eventuell zusätzliche
Prüfungen nötig
Error Logging
Kunde
Insert into Kunde
Values (......)
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
Kunde_err
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$
• Constraints
•
•
•
•
Unique Key / Primary Key
Foreign Key
Not Null
Check Constraint
Error Logging: Beispiel 1
Error Logging: Beispiel 2
1
4
SQL> desc T3
Name
Type
--------------------------------- -------- -------F1
NUMBER
F2
NUMBER
insert into t3 values(1,2)
LOG ERRORS INTO err$_T3
5
2
exec DBMS_ERRLOG.CREATE_ERROR_LOG ('T3')
3
SQL> desc ERR$_T3;
Name
Type
----------------------------------------- ------------ORA_ERR_NUMBER$
NUMBER
ORA_ERR_MESG$
VARCHAR2(2000)
ORA_ERR_ROWID$
ROWID
ORA_ERR_OPTYP$
VARCHAR2(2)
ORA_ERR_TAG$
VARCHAR2(2000)
F1
VARCHAR2(4000)
F2
VARCHAR2(4000)
1* select substr(ora_err_number$,1,10) Nr,substr(ora_err_mesg$,1,50) Err
from ERR$_T3
SQL> /
NR
ERR
---------- -------------------------------------------------1
ORA-00001: unique constraint (DWH4.IDX_T3) violate
Einschränkungen + Ausnahmen
• Direct Path Insert mit Unique-Constraints
• Update (Merge) mit Unique-Constraints
• Error Logging verlangsamt den Ladeprozess
Arbeiten ohne Constraints
• Constraints stören bei Massenaktionen im DWH
• Ausschalten der Constraints
• Übernahme der Aufgaben von Constraints durch
ETL-Prozess
• Mengen-basierte Vorgehensweise
Feldprüfungen:
Was kann geprüft werden
• Formatprüfungen
• Feldtypen
• Stringformate, Ausdrücke
•
•
•
•
•
Not Null
Eindeutigkeit
Wertebereiche
Spaltenübergreifende Table_Checks
Inhaltliche Regeln
Wichtiges Hilfsmittel:
CASE-Anweisung
select case 1
when 1 then 1
when 2 then 2
else 0
end ergebnis
from dual;
Beispiel 1
select
case
when isnumeric(999) = 1 then 'Numerisch‘‚
else 'nicht numerisch‘‚
end Ergebnis
from dual;
Beispiel 2
with tab as (
select '123' col1 from dual union all
select '123' col1 from dual union all
select 'x23d' col1 from dual )
select
case
when regexp_like(col1,'^[[:digit:]]') then ‘numerisch'
else ‘Nicht numerisch'
end
from tab
Abarbeitungslogik mit CASE
Beispiel mit temporärer Tabelle
Gepruefte_Daten
Temp-Tabelle
Stage-Tabelle
Varchar2()
Kopieren
Feld1
Feld2
Feld3
Varchar2()
Insert into
temp_table
Select
case ....
From
Stage_Table
Feld1
Feld2
Feld3
Feld1_is_null
Feld1_is_numeric
Feld2_is_numeric
Insert ALL
when
Feld_1_is_null =1
into
Error_Daten
when
Feld_1_is_null=0
into
Gepruefte_Daten
Date
Number
Varchar2()
Error_Daten
Date
Number
Varchar2()
• Temporäre Tabelle ist optional
• Ist aber damit wesentlich übersichtlicher
• Erlaubt Kombination von unterschiedlichen Prüfkriterien
Abarbeitungslogik mit CASE
Aufbau einer Flag-Tabelle zur besseren Dokumentation
TGT_Table
Key
SRC_Table
Key
Prueftabelle
Key
Feld1
Flag
Insert into TGT_TABLE
.....
Select ..,..,..,..
When
SRC_TABLE.key = Prueftable.key and
Prueftable.key = 0
Feld1
Feld2
Feld3
Feld2
Feld3
Feld4
...
Insert into Prueftabelle
Select
Key,
case
when (Feld1 is NULL) then 1
when (Feld2 != 100) then 2
when (Feld3 < 300) then 3
when (Feld4 = 0) then 4
Else 0
end
From
SRC_TABLE;
Insert into TGT_TABLE
.....
When
SRC_TABLE.key = Prueftable.key and
Prueftable.key != 0
From
SRC_TABLE,Prueftabelle
Feld4
...
Err_Table
Key
Feld1
Feld2
Feld3
Feld4
...
Beispiel mit graphischer Modellierung:
Einsatz von CASE und Zwischentabelle
Beispiel mit manuellem SQL:
Einsatz von CASE und Zwischentabelle
INSERT INTO EL_KUNDE_TMP
(KUNDENNR,VORNAME,NACHNAME,
KUNDENNR_IS_NUMERIC)
(SELECT NUMMER , NAME , NACHNAME ,
case when is_number ( NUMMER ) = ‘ Y‘ then 1 else 0 end
51002 Zeilen wurden erstellt.
FROM
SRC1 ) ;
Abgelaufen: 00:00:01.45
51002 Zeilen wurden erstellt.
INSERT
Abgelaufen: 00:00:02.67
ALL
WHEN KUNDENNR_IS_NUMERIC = 1 THEN
INTO
EL_KUNDE
(KUNDENNR, VORNAME,NACHNAME)
VALUES
(KUNDENNR, VORNAME, NACHNAME)
WHEN KUNDENNR_IS_NUMERIC = 0 THEN
INTO
EL_KUNDE_FEHLER
(KUNDENNR,VORNAME, NACHNAME,KUNDENNR_IS_NUMERIC)
VALUES
(KUNDENNR, VORNAME, NACHNAME, KUNDENNR_IS_NUMERIC)
(SELECT
KUNDENNR,VORNAME ,NACHNAME,ORTNR ,STRASSE ,TELEFON ,KUNDENNR_IS_NUMERIC
FROM
EL_KUNDE_TMP );
Abarbeitungslogik mit CASE / WITH
Arbeiten mit WITH-Clause ohne Zwischentabelle
create table src (
schluessel varchar2(30),
Beschreibung varchar2(30),
Rechenfeld varchar2(30))
SCHLUESSEL
---------1
1
2
3
a
BESCHREIBUNG
--------------Wert
Wert
xxxx
yyyy
www
RECHENFELD
---------8*7
8*7
888
01
55
create table tgt (
schluessel number,
Beschreibung varchar2(30),
Rechenfeld number)
SCHLUESSEL
---------2
3
BESCHREIBUNG
RECHENFELD
--------------- ---------xxxx
888
yyyy
1
Insert into tgt
select * from (
with lc as
(
select
(case when is_number(schluessel) = 'Y' then schluessel else 'BUG' end) schluessel ,
Beschreibung,
(case when is_number(Rechenfeld) = 'Y' then Rechenfeld else 'BUG' end) Rechenfeld
from src)
select schluessel , Beschreibung, Rechenfeld from lc
where schluessel != 'BUG' and Rechenfeld != 'BUG‚)
Prüfungen auf numerisch
Hilfsfunktion Is_Number bzw. regexp_like
create or replace function is_number(in_var in varchar2)
return varchar2
is
v_number number;
begin
select to_number(in_var) into v_number from dual;
return 'Y'; -- No exception, so is a number
exception
when others then
return 'N'; -- is not a number
end;
select
case
Alternative mit REGEXP_LIKE
when regexp_like(col1,'^[[:digit:]]') then ‘numerisch'
else ‘Nicht numerisch'
end
from tab
BEGIN
Hilfsfunktion:
Date_Check
inDate:= trim(str);
if dateCheck(inDate, 'mm-dd-yyyy') = 'false'
AND dateCheck(inDate, 'mm-dd-yy') = 'false'
AND dateCheck(inDate, 'yyyy-mm-dd') = 'false'
AND dateCheck(inDate, 'yy-mm-dd') = 'false'
AND dateCheck(inDate, 'yyyy-mon-dd') = 'false‚
AND dateCheck(inDate, 'yy-mon-dd') = 'false‚
AND dateCheck(inDate, 'dd-mon-yyyy') = 'false‚
AND dateCheck(inDate, 'dd-mon-yy') = 'false‚
AND dateCheck(inDate, 'mmddyy') = 'false‚
AND dateCheck(inDate, 'mmddyyyy') = 'false‚
AND dateCheck(inDate, 'yyyymmdd') = 'false'
AND dateCheck(inDate, 'yymmdd') = 'false‚
AND dateCheck(inDate, 'yymmdd') = 'false'
AND dateCheck(inDate, 'yymondd') = 'false‚
AND dateCheck(inDate, 'yyyymondd') = 'false‚
AND dateCheck(inDate, 'mm/dd/yyyy') = 'false'
AND dateCheck(inDate, 'yyyy/mm/dd') = 'false‚
AND dateCheck(inDate, 'mm/dd/yy') = 'false'
AND dateCheck(inDate, 'yy/mm/dd') = 'false‚
AND dateCheck(inDate, 'mm.dd.yyyy') = 'false'
AND dateCheck(inDate, 'mm.dd.yy') = 'false'
AND dateCheck(inDate, 'yyyy.mm.dd') = 'false'
AND dateCheck(inDate, 'yy.mm.dd') = 'false'
then
return 'false';
else
return 'true';
end if;
--exception
--when others then return 'false';
create or replace function IsDate (str varchar2)
return varchar2 is
inDate varchar2(40);
FUNCTION dateCheck (inputDate varchar2,
inputMask varchar2)
RETURN varchar2
IS dateVar date;
BEGIN
dateVar:= to_date(inputDate,inputMask);
return 'true';
exception
when others then
return 'false';
END;
• In Verbindung mit der
Case-Anweisung
END;
Prüfungen auf Feldtypen
Variante mit TRANSLATE
select schluessel,
CASE
WHEN LENGTH(TRIM(TRANSLATE(schluessel, ' +-.0123456789',' '))) is null and
schluessel is not null
THEN 'numerisch'
WHEN LENGTH(TRIM(TRANSLATE(schluessel, ' +-.0123456789',' '))) is not null
THEN 'alphanumerisch‚
WHEN schluessel is NULL then 'NULL'
ELSE 'NULL‚
END Typ
from src
SCHLUESSEL
------------1
1
2
3
a
BESCHREIBUNG
--------------Wert
Wert
xxxx
yyyy
www
www
RECHENFELD
-------------8*7
8*7
888
01
55
55
SCHLUESSEL
------------1
1
2
3
a
TYP
-------------numerisch
numerisch
numerisch
numerisch
alphanumerisch
NULL
Satzübergreifende Prüfungen
pro Feld
• Eindeutigkeit
Satzübergreifende Prüfungen
Eindeutigkeit
• Finden von Schluessel-Kandidaten
• Erfüllung der 2. und 3. Normalform
• Prüfung ergibt nicht für alle Feldtypen einen Sinn
• i. d. R. LHS-Kandidaten (siehe Feldlisten-Analyse)
• Herausfinden von potentiellen ETL-JobAbbruchgründen
• Beim Schreiben in Tabellen mit Unique-Constraints
• Beim Aufbau von Parent-Child-Beziehungen in denen
eindeutige Parents gebraucht werden
Eindeutigkeit
Anzeigen aller Feldwerte mit ihrer jeweiligen Häufigkeit
SQL> select * from unique_test;
SCHLUESSEL
---------1
1
2
32
42
FELD
---------abc
abc
abc
abc
abc
select SCHLUESSEL, count(SCHLUESSEL) cnt from unique_test
group by SCHLUESSEL
SCHLUESSEL
CNT
---------- ---------1
2
42
1
2
1
32
1
Anzeigen des prozeduralen Grades der Eindeutigkeit
select round(nr_values*100/anz,2) Prozent, nr_values,anz from
(select count(*) nr_values from
(select SCHLUESSEL, count(SCHLUESSEL) cnt from unique_test group by SCHLUESSEL)) ,
(select count(*) anz from unique_test);
PROZENT NR_VALUES
ANZ
---------- ---------- ---------80
4
5
Anzeigen der Werte, die die Eindeutigkeit verletzen
select schluessel, n from
(select count(schluessel) n, schluessel from unique_test group by SCHLUESSEL )
where n > 1
SCHLUESSEL
N
---------- ---------1
2
Prüfung auf Eindeutigkeit:
Beispiel graphisch und manuell
insert into el_kunde (kundennr,vorname,nachname,ORTNR,strasse,TELEFON)
select src2.nummer,src2.name,src2.name,src2.nummer,src2.name,src2.nummer from SRC2,
(select nummer from
(select count(nummer) n, nummer from src2 group by nummer)
where n = 1) doppelte
where src2.nummer = doppelte.nummer;
Feldstatistik
NULL, MAX/MIN, Prozent, Count, Numeric, Alpanumeric…
with lc as (
select
SCHLUESSEL ,
(CASE WHEN SCHLUESSEL is null THEN 1 ELSE 0 END)
Ist_Null,
(CASE WHEN LENGTH(TRIM(TRANSLATE(SCHLUESSEL , ' +-.0123456789',' '))) is null and
SCHLUESSEL is not null THEN 1 ELSE 0 END) Ist_Numeric
from unique_test )
Select
MAX(SCHLUESSEL )
Max_Wert,
MIN(SCHLUESSEL )
Min_Wert,
count(distinct(SCHLUESSEL ))
Anz_Werte,
round(count(distinct SCHLUESSEL )*100 / count(*),2)
Selectivitaet,
sum(ist_null)
ist_null,
sum(ist_null)*100/count(*)
ist_null_prozent,
sum(Ist_Numeric)
ist_numeric,
sum(Ist_Numeric)*100/count(*)
ist_numeric_prozent,
count(*)-(sum(ist_null)+sum(Ist_Numeric))
ist_Alphanumeric,
(count(*)-(sum(ist_null)+sum(Ist_Numeric)))*100/count(*) ist_Alphanumeric_Prozent
from lc;
MAX_WERT MIN_WERT ANZ_WERTE SELECTIVITAET IST_NULL IST_NULL_PROZENT
-------- -------- --------- ------------- --------- ---------------42
1
4
80
0
0
IST_NUMERIC IST_NUMERIC_PROZENT IST_ALPHANUMERIC IST_ALPHANUMERIC_PROZENT
----------- ------------------- ---------------- --------------------5
100
0
0
Herausfiltern und Protokollieren Not
Null Feldern mit graphischen Mitteln
case
when F2 is NULL
then 1
else 0
end
Prüfen auf Eindeutigkeit der
Eingabesätze (Graphik)
Es dürfen nur Sätze geladen werden, die einmal im Quell-bestand vorkommen.
Select F1 from
(Select count(F1) n,F1 from s group by F1)
where n > 1;
Mustererkennung
(Translate)
create table Kunde
( KUNDENNR
NUMBER(10),
KUNDENNAME
VARCHAR2(20),
BERUFSGRUPPE VARCHAR2(20),
SEGMENT
NUMBER(10));
9
A
a
B
{n}
digit
upper letter
lower letter
blank
n-time repetition
SQL> select * from kunde where rownum < 10;
KUNDENNR
-------705
706
707
708
709
710
711
712
713
KUNDENNAME
----------Meister
Schmidt
Meister
Kraemer
Schneider
Schuster
Mueller
Schneider
Hartmann
BERUFSGRUPPE
SEGMENT
-------------- ------Angestellte
4
Rentner
34
Angestellte
16
Beamte
38
Freiberufler
16
Selbstaendige
47
Unbeschaeftigt
2
Angestellte
36
Rentner
46
column Anzahl format 9999999
column Maske format a30
select sum(nr_value) Anzahl, maske from
(with lc as (
select berufsgruppe,count(NVL(berufsgruppe,'NULL')) nr_value from
KUNDE group by berufsgruppe)
select berufsgruppe, nr_value,strcnt(TRANSLATE(berufsgruppe,
' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
'b9999999999AAAAAAAAAAAAAAAAAAAAAAAAAAaaaaaaaaaaaaaaaaaaaaaaaaaa'))
Maske
from lc order by NR_Value DESC)
group by maske
ANZAHL MASKE
------ -------4 Aa{11}
14 Aa{13}
2 Aa{11}b
4 Aa{10}
5 Aa{5}
13 Aa{6}
7 Aa{12}
Mustererkennung
(REGEXP_LIKE)
create table Kunde
( KUNDENNR
NUMBER(10),
KUNDENNAME
VARCHAR2(20),
BERUFSGRUPPE VARCHAR2(20),
SEGMENT
NUMBER(10));
SQL> select * from kunde where rownum < 10;
KUNDENNR
-------705
706
707
708
709
710
711
712
713
KUNDENNAME
----------Meister
Schmidt
Meister
Kraemer
Schneider
Schuster
Mueller
Schneider
Hartmann
BERUFSGRUPPE
SEGMENT
-------------- ------Angestellte
4
Rentner
34
Angestellte
16
Beamte
38
Freiberufler
16
Selbstaendige
47
Unbeschaeftigt
2
Angestellte
36
Rentner
46
Finde alle Sätze, deren Segment-Wert nur 1-stellig numerisch ist
SQL> select * from kunde where REGEXP_LIKE(segment,'^[[:digit:]]{1}$');
KUNDENNR
---------705
711
724
726
728
743
KUNDENNAME
-------------------Meister
Mueller
Schuster
Kraemer
Mueller
Bauer
BERUFSGRUPPE
SEGMENT
-------------------- ---------Angestellte
4
Unbeschaeftigt
2
Student
2
Rentner
5
Unbeschaeftigt
9
Freiberufler
3
Parameter
* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times
[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters
 http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/ap_posix001.htm
Mustererkennung
(REGEXP_LIKE)
create table P
(PNR
number,
pruef_wert varchar2(20));
SQL> select * from p;
PNR
---------1
2
3
4
5
7
6
8
PRUEF_WERT
------------8
89
A89
D-25436
Eumel
A89
D-1000
D-10003
select count(pattern) Anzahl, pattern Muster from
(select PNR,
case
when REGEXP_LIKE(pruef_wert,'^[[:digit:]]{1}$') then 'einstellig numerisch'
when REGEXP_LIKE(pruef_wert,'^[[:digit:]]{2}$') then 'zweistellig numerisch'
when REGEXP_LIKE(pruef_wert,'^[[:upper:]]{1}[[:digit:]]{2}$')
then '1 Großbuchstabe + 2 Zahlen'
when REGEXP_LIKE(pruef_wert,'^[[:upper:]]{1}-[[:digit:]]{5}$') then 'PLZ'
else
'falscher Wert'
ANZAHL MUSTER
-------------------------------end pattern
1
zweistellig numerisch
from P)
2 1 Großbuchstabe + 2 Zahlen
group by pattern;
2 falscher Wert
1 einstellig numerisch
2 PLZ
Check Constraint mit
Regular Expressions
CREATE TABLE Check_KUNDE (
KUNDENNR
NUMBER,
GESCHLECHT
NUMBER,
VORNAME
VARCHAR2(50),
NACHNAME
VARCHAR2(50),
ANREDE
VARCHAR2(10),
GEBDAT
DATE,
ORTNR
NUMBER,
STRASSE
VARCHAR2(50),
TELEFON
VARCHAR2(30)
);
Regel:
Im Kundennamen müssen Buchstaben
vorkommen und
keine reine Zahlenkolonne
Alter table check_kunde
add constraint Ch_KD_Name
check(REGEXP_LIKE(NACHNAME, '[^[:digit:]]'));
insert into check_kunde (kundennr,Geschlecht, Vorname, Nachname, Anrede,Gebdat,Ortnr,Strasse,Telefon)
Values (9,1,'Klaus','123','Herr','01.01.60',2,'Haupstr.',08923456);
FEHLER in Zeile 1:
ORA-02290: CHECK-Constraint (DWH.CH_KD_NAME) verletzt
Domain-Analysen
create table Kunde
( KUNDENNR
NUMBER(10),
KUNDENNAME
VARCHAR2(20),
BERUFSGRUPPE VARCHAR2(20),
SEGMENT
NUMBER(10));
SQL> select * from kunde where rownum < 10;
KUNDENNR
-------705
706
707
708
709
710
711
712
713
variable Anz_Records number;
begin
select count(*) into :Anz_Records
from Kunde;
end;
/
select * from (
select
BERUFSGRUPPE
Value_name,
count(BERUFSGRUPPE) Anzahl_Werte,
case
when count(BERUFSGRUPPE) = 0 then 0
else
round((count(BERUFSGRUPPE)*100)/:Anz_records,2)
end Prozent
from KUNDE group by BERUFSGRUPPE
order by Anzahl_Werte desc)
where rownum < 10;
KUNDENNAME
----------Meister
Schmidt
Meister
Kraemer
Schneider
Schuster
Mueller
Schneider
Hartmann
BERUFSGRUPPE
SEGMENT
-------------- ------Angestellte
4
Rentner
34
Angestellte
16
Beamte
38
Freiberufler
16
Selbstaendige
47
Unbeschaeftigt
2
Angestellte
36
Rentner
46
VALUE_NAME
ANZAHL_WERTE PROZENT
--------------- ------------ ------Unbeschaeftigt
14
28.57
Rentner
8
16.33
Selbstaendige
7
14.29
Student
5
10.2
Beamte
5
10.2
Angestellte
4
8.16
Freiberufler
4
8.16
Freiberufler
2
4.08
Spaltenübergreifende
Prüfungen
• Wertabhängigkeiten
• Synchrone Wertzuordnung
• Ableitung
• 1:1
• Formeln
• Kardinalität
• Functional Dependencies
Wertabhängigkeiten
(asynchron)
create table t
(F1 varchar2(5),
F2 varchar2(5),
F3 varchar2(5));
SQL> select * from t;
F1
----a
a
b
c
a
d
d
F2
----ab
am
bx
ck
al
dt
ab
gh
F3
----b
b
x
k
b
t
t
LHS (F1)
(1:3)
a
b
c
d
RHS(F2)
(1:2)
ab
am
bx
ck
al
dt
ab
gh
select count(distinct f1)
LHS_Anzahl, f2 RHS_Wert
from t group by f2;
LHS_ANZAHL
---------1
2
1
1
1
1
1
RHS_WERT
--------dt
ab
bx
gh
am
ck
al
with lc as
(select count(distinct f1) LHS_Anzahl,f2 from t group by f2)
(select LHS_Anzahl,count(LHS_Anzahl) Vorkommen from LC group by LHS_Anzahl)
LHS_ANZAHL VORKOMMEN
---------- ---------1
6
2
1
Bei 6 RHS-Werten gibt es nur exakt 1 LHS-Wert
Bei 1 RHS-Wert gibt es zwei LHS-Werte
Wertabhängigkeiten
(synchron)
create table t
(F1 varchar2(5),
F2 varchar2(5),
F3 varchar2(5));
SQL> select * from t;
F1
----a
a
b
c
a
d
d
F2
----ab
am
bx
ck
al
dt
ab
gh
F3
----b
b
x
k
b
t
t
LHS (F1)
(1:1)
a
a
b
c
a
d
d
RHS(F3)
(1:1)
b
b
x
k
b
t
t
select count(distinct f1)
LHS_Anzahl, f3 RHS_Wert
from t group by f3;
LHS_ANZAHL
---------1
1
1
1
1
RHS_WERT
-------k
b
t
x
with lc as
(select count(distinct f1) LHS_Anzahl,f3 from t group by f3)
(select LHS_Anzahl,count(LHS_Anzahl) Vorkommen from LC group by LHS_Anzahl)
LHS_ANZAHL VORKOMMEN
---------- ---------1
5
Bei 5 RHS-Werten gibt es nur exakt 1 LHS-Wert
Wertabhängigkeiten
Inhaltliche Abhängigkeit von zwei Feldern
• Variante 1: Wertereihenfolgen
• Variante 2: Wertemengen
Identifier
Zustandsbeschreibung
Zeitabhängige
Information
AKTION
STATUS AKTIONSDATUM
------ ---------- -----------1
10 01-AUG-10
1
20 01-MAY-10
1
20 01-SEP-10
1
30 01-OCT-10
2
10 01-SEP-10
2
20 02-SEP-10
3
10 05-SEP-10
4
10 05-SEP-10
4
20 06-SEP-10
Beispielregel zu Variante 1:
Die aufsteigenden Statuswerte 10, 20, 30
pro Aktion müssen in zeitlicher Reihenfolge
stattfinden .
Beispielregel zu Variante 2:
Für jede Aktion müssen immer 3
Statusmeldungen und die dazu passenden
Aktionsdatumsangaben folgen.
Wertabhängigkeiten
Inhaltliche Abhängigkeit von zwei Feldern
Lösung zu Beispielregel 1:
Bei korrekten Angaben zu den Aktionen muss eine Sortierung nach
Aktion, Status, Aktionsdatum
genauso aussehen wie eine Sortierung nach
Aktion, Aktionsdatum, Status.
Es werden zwei entsprechende Datenmengen mit einer zusätzlichen
laufenden Nummer erzeugt.
Bei einem Join der beiden Datenmengen und einem Join-Kriterium
bestehend aus der Nummer und den Feldern müssten sich alle Sätze
„paaren“ lassen, bzw. zu Fehlern führen, wenn die Werte auf ungleich
abgefragt werden.
(Lösung in Script, hier nur graphisch)
Join-Bedingung
Wertabhängigkeiten
Inhaltliche Abhängigkeit von zwei Felder, Prüfung auf Wertmengen
AKTION
STATUS AKTIONSDATUM
------ ---------- -----------1
10 01-AUG-10
1
20 01-MAY-10
1
20 01-SEP-10
1
30 01-OCT-10
2
10 01-SEP-10
2
20 02-SEP-10
3
10 05-SEP-10
4
10 05-SEP-10
4
20 06-SEP-10
Beispielregel zu Variante 2:
Für jede Aktion müssen immer 3
Statusmeldungen und die dazu passenden
Aktionsdatumsangaben folgen.
with lc as
(select aktion,
count(status) Anz_Status,
count(aktionsdatum) Anz_Datum
from Statusmeldung
group by aktion
order by aktion)
select aktion, Anz_Status,Anz_Datum
from lc
where Anz_Status < 3 or
Anz_Datum < 3;
AKTION ANZ_STATUS ANZ_DATUM
------- ---------- ---------2
2
2
3
1
1
4
2
2
Tabellenübergreifende
Prüfungen
• Referenzen
•
•
•
•
Kardinalität
Orphans
Childless
Redundant Columns
Tabellenübergreifende
Prüfungen – Kardinalität
select artikelgruppennr L_Key,' --> ',
count(artikelgruppennr) cnt1
from artikel_gruppe group by artikelgruppennr
order by artikelgruppennr;
L_KEY '-->'
CNT1
---------- ----- ---------1 -->
1
2 -->
1
3 -->
1
4 -->
1
5 -->
1
6 -->
1
7 -->
1
8 -->
1
9 -->
1
10 -->
1
11 -->
1
Prüfung innerhalb
einer Tabelle
select artikelgruppennr R_Key,' --> ',
count(artikelgruppennr) cnt2
from produkte_stamm group by artikelgruppennr
order by artikelgruppennr;
R_KEY '-->'
CNT2
---------- ----- ---------1 -->
11
2 -->
4
3 -->
14
4 -->
9
5 -->
3
6 -->
3
7 -->
3
10 -->
1
100 -->
6
-->
0
select min(l.cnt1), max(l.cnt1), min(R.cnt2), max(R.cnt2) from
(select artikelgruppennr L_Key,count(artikelgruppennr) cnt1 from artikel_gruppe
(select artikelgruppennr R_Key,count(artikelgruppennr) cnt2 from produkte_stamm
where
L.L_Key=R.R_Key;
MIN(L.CNT1) MAX(L.CNT1) MIN(R.CNT2) MAX(R.CNT2)
----------- ----------- ----------- ----------1
1
1
14
1:n - Beziehung
Prüfung über 2
Tabellen hinweg
group by artikelgruppennr) L,
group by artikelgruppennr) R
Tabellenübergreifende
Prüfungen – Orphans
Artikel_Gruppe
Produkte_Stamm
SQL> select
distinct artikelgruppennr
from Artikel_gruppe
order by artikelgruppennr;
SQL> select
distinct artikelgruppennr
from produkte_stamm
order by artikelgruppennr;
ARTIKELGRUPPENNR
---------------1
2
3
4
5
6
7
8
9
10
11
ARTIKELGRUPPENNR
---------------1
2
3
4
5
6
7
10
100
SQL> select count(artikelgruppennr) Anz_Orphans, artikelgruppennr Wert_Orphans from produkte_stamm
2
where artikelgruppennr not in (select artikelgruppennr from Artikel_gruppe)
3 group by artikelgruppennr ;
ANZ_ORPHANS WERT_ORPHANS
----------- -----------6
100
Tabellenübergreifende
Prüfungen – Childless
Artikel_Gruppe
Produkte_Stamm
SQL> select
distinct artikelgruppennr
from Artikel_gruppe
order by artikelgruppennr;
SQL> select
distinct artikelgruppennr
from produkte_stamm
order by artikelgruppennr;
ARTIKELGRUPPENNR
---------------1
2
3
4
5
6
7
8
9
10
11
ARTIKELGRUPPENNR
---------------1
2
3
4
5
6
7
10
100
select distinct artikelgruppennr from Artikel_gruppe MINUS
select distinct g.artikelgruppennr from Artikel_gruppe g, produkte_stamm p
where g.artikelgruppennr = p.artikelgruppennr;
ARTIKELGRUPPENNR
---------------8
9
11
Redundant Columns
• Redundanzen: eine der Hauptfehlerursachen
• Redundanzen innerhalb einer Tabelle
• Feststellbar mit
Select Count(*) from Tabelle where Feld_X = Feld_Y;
• Redundanzen in unterschiedlichen Tabellen
• Parent/Child-Beziehungen
• Child-redundante Information ist meist aus Parent ableitbar
• Feststellbar mit
select Count(*) from
Select Count(*) from T1,T2 where T1.PK = T2.PK
MINUS
Select Count(*) from T1,T2 where T1.PK = T2.PK and T1.Feld_Y = T2.Feld;
Redundant Columns
Parent- Child
create table parent (Parent_Key number, Wert_X varchar2(10));
insert
insert
insert
insert
insert
into
into
into
into
into
parent
parent
parent
parent
parent
values(1,'AUDI');
values(2,'VW');
values(3,'OPEL');
values(4,'BMW');
values(5,'Daimler');
create table child (Child_key number, Parent_Key number, Wert_Y varchar2(10));
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
into
into
into
into
child
child
child
child
child
child
child
child
child
child
child
child
values(1,1,'AUDI');
values(2,1,'AUDI');
values(1,2,'VW');
values(2,2,'VW');
values(1,3,'OPEL');
values(2,3,'OPEL');
values(1,'4','BMW');
values(2,'4','BMW');
values(3,'4','BMW');
values(4,'4','BMW');
values(1,5,'Daimler');
values(2,5,'Daler');
SQL> select Count(*) from
2 ((Select
Count(*) from parent,child
3
where parent.Parent_Key = child.Parent_Key) MINUS
4
(Select
Count(*) from parent,child
5
where
parent.Parent_Key = child.Parent_Key and
6
parent.Wert_X
= child.Wert_Y)) ;
COUNT(*)
---------1
Tabellenübergreifende
Prüfungen – Werte
Bestellung
Best_position
Bestellnr (PK)
Bestell_Total
Bestellnr (FK)
Gesamt_Pos_Preis
select count(*) from
(select bestellnr,BESTELL_TOTAL from bestellung) B,
(select BESTELLNR, sum(GESAMT_POS_PREIS) ges_Pos_Wert from
where B.bestellnr = P.BESTELLNR and
B.BESTELL_TOTAL != P.ges_Pos_Wert ;
=
best_position group by
BESTELLNR) P
Zugreifbarkeit auf Daten
Quell-Umgebung
Oracle
Oracle 10g / 11g
non Oracle
DB2, SQL Server
Informix, Teradata
SAP R/3
Siebel CRM
Gateway
/ ODBC
/ FTP
/ Golden Gate
SAP
Integrator
Direct Path
DBLink
Text / XML
Stage
Schema
Sampling
Analyse Datenbank
Analyse
Schema
Arbeiten mit Datenkopien
• Entlastung der operativen System
• Sicherstellen von nicht veränderlichen Daten
• Verhindern von Seiteneffekten
• Z. B. von Spacheinstellungen etc.
• Erleichtert länger dauerndes iteratives Arbeiten
• Überwinden von technischen Barrieren
• Z. B. bei SAP-Daten
• Veränderbarkeit von Daten zu
Simulationszwecken
• Bessere Performance
Sampling von Daten
• SAMPLE – Schlüsselwort
• select Kundenname from kunde sample (2);
• In Klammern Prozentwert
• select /*+ dynamic_sampling(Kunde 4)
*/Kundenname from kunde
• Macht nicht immer Sinn
• Z. B. nicht bei Eindeutigkeitsprüfungen
• Aber bei Stichproben auf der Suche nach NULL-Werten
• Immer nur bedingt, vollständige Sicherheit bringt nur eine
Komplettsuche
Wo macht Sampling Sinn?
Verfahren
Macht Sinn?
•
•
•
•
•
•
•
•
•
•
Bedingt
Bedingt
Bedingt
Bedingt
Nein
Nein
Nein
Nein
Nein
Nein
Patternanalyse
Domainanalyse
Not Null Messung
Typfeststellung
Max/Min/Aussreisser
Eindeutigkeitsanalyse
Functional Dependency
Orphan / Childless
Redundant Column
Kardinalität
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Datenmodellierung – Die Grundlage
 Metadaten-Dokumentation – Data Quality Plan
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Fragebögen und Templates
•
•
•
•
•
Geben Struktur vor
Dokumentieren
Verhindern, dass man etwas vergisst
Arbeitsgrundlage im Projekt
Werden ständig aktualisiert und in allen Phasen verwendet
Sinnvolle Listen und Templates für die
Projektarbeit
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
T1.10
T1.15
T1.20
T1.35
T1.45
T1.50
T2.25
T2.50
T3.00
T3.25
T3.50
T3.55
T3.60
T3.70
T3.75
Liste Geschäftsfelder (zur Fokussierung)
Datenbestandsliste (für die Vollständigkeitsanalyse)
Liste Metadatenbestände
Liste bekannten Schwachstellen und Erwartungen
Bekannte Business Rules
Geschätzte Kosten der bekannten Schwachstellen
Liste Datenbestände, Geschäftsobjekte und Fehldaten
Regelliste (Bekannte + abgeleitete Regeln)
Feldliste
Liste Missverständliche Objektnamen
Schlüsselliste
Funktionale Abhängigkeiten
Beziehungen
Normalform-Analysen
Redundante Felder
Die wichtigsten
Listen
Vollständigkeitsanalyse
Analysemodell
• Erstellen von
• Analysemodell ergibt
aus der Befragung der
Prozess-Kenner
• Objektmodell ergibt sich
aus dem Analysemodell
• Rückschlüsse auf
fehlende
Informationen
• Über Vergleiche des
Objektmodells mit der
Datenbestandsliste
Objektmodell
Schnittstellenanalyse
• Erstellen von
• Prozessmodell (Analyse-Modell)
• Objektmodell
• Datenbestandsliste
• Finden von
Übergabepunkten
als die
kritischen
Stellen
Prozessmodell
Synonymen / Homonymenanalyse
• Homonyme (leichter Fall)
1. Über Feldliste Kandidaten finden
2. Kandidaten mit Aggregation-, Domain-, Pattern-Analyse auf
fachliche Gleichheit überprüfen
• Synonyme (komplexer Fall)
1.
2.
3.
4.
Wortstammanalyse
Attributklassifizierung
Finden von Kandidaten über Basistypen
Kandidaten mit Aggregation-, Domain-, Pattern-Analyse auf
fachliche Gleichheit überprüfen
Finden von Anomalien
• 1. NF
• Domainanalyse und Sichtprüfung
• 2. NF
1. Suche nach Schlüsselkandidaten
1. Feldliste
2. Unique-Key-Prüfung
3. Fokussierung auf zusammengesetzte Schlüssel
2. Functional Dependency Analyse
• 3. NF
• Suche nach Schlüsselkandidaten
1. Feldliste
2. Unique-Key-Prüfung
3. Fokussierung auf nicht als Schlüssel genutzte Felder
Finden redundanter Spalten
• Über Feldliste
•
•
•
•
Kandidaten identifizieren, um den späteren Aufwand zu minimieren
Felder finden, die nicht geprüft werden müssen
Pools von ähnlichen Feldern bilden
Stichproben mit Sampling bzw. Domain-Analysen
• Innerhalb einer Tabelle
• Feststellen des Schlüsselfeldes
• Feldinhaltsvergleiche
(alle Spalten müssen mit allen anderen Spalten verglichen werden)
• Eventuell prozentuale Gleichheit feststellen
• Bei hohem Übereinstimmungsgrad, die „Nicht-Treffer“ mit Domain- bzw.
Patternanalyse genauer betrachten.
• Tabellenübergreifend
• Finden des Referenzkriteriums (PK / FK)
• Überprüfen auf Vollständigkeit der Referenz
• Feldinhaltsvergleiche mit Hilfe eines Joins
(wie oben beschrieben)
Agenda
 Teurer Datensumpf" oder
"Schlechte Daten kosten einfach nur viel Geld"
 Einweisung in das Planspiel „Service GmbH“
„Wenn Controlling auf die Buchhaltung schimpft“
Eine simulierte Firma mit (einigen) Problemen.
 Hilfsmittel für die systematische Vorgehensweisen bei
Datenqualitätsanalysen
 Vorgehensmodell – Der rote Faden
 Datenmodellierung – Die Grundlage
 Metadaten-Dokumentation – Data Quality Plan
 Feldliste – Das klassische Hilfe
 Sonst.: Profiling Tool / ETL Tool / Datenbank
 Die wichtigsten Analyse-Techniken
 Die wichtigsten Analyse-Verfahren
 Fallbeispiel Service GmbH
Zusammenfassen zu
Problemkomplexen
• Problemkomplex 1:
Dies sind Schwierigkeiten bei Auswertungen. Diese sind zwar machbar, aber es
fehlen offenbar einzelne Produkte und Produktgruppen. Die Daten müssen
umständlich zusammengesucht werden. Ob das Ergebnis stimmt ist unklar.
Produktgruppen und einzelne Produkte sind nicht richtig messbar. Von den
Produktsparten sind offensichtlich keine Auswertungen möglich. Zu untersuchen
sind die Produkte-Stamdatenhierarchien.
• Problemkomplex 2:
Die Zusammenhänge zwischen unterschiedlichen Größen (Einkaufsdaten und
Verkaufsdaten) sind nicht stimmig. Welche Waren werden zu welchem Preis
beschafft und zu welchem Preis und mit welchem Rabatt verkauft.
• Problemkomplex 3:
Verschiedene Kundengruppen können nicht voneinander abgegrenzt werden.
Auch das Thema Kundenkarte gehört dazu.
• Problemkomplex 4:
Spediteursrechnungen und Lieferungen. Wo gehen die Waren hin und wie
werden sie bezahlt? Gelingt die Kontrolle über Lieferungen und Zahlungen?
Problemkomplex
Stammdaten
(Produkte / Artikel)
Stammdaten
Hierarchien?
Einkaufspreise
(fehlerhaft?)
Stammdaten
(Kunden /
Identifizierung )
Fragestellungen
Benannte Probleme
Auswertbarkeit
Stimmen die
Einträge
Vergleichbarkeit von
Einkaufs- und
Verkaufspreisen
Welche Produkte
lohnen sich
Rabatte?
Messbarkeit für
Controlling
Korrekte Zahlen zur
Steuerung von
Marketingkampagnen
und für den Vertrieb
Wer hat wieviel
gekauft?
Kunden-Segmentierung
Wo bleibt die
gelieferte Ware?
Lieferungen
(Bestellungen
/Stornierungen)
Artikel-,
Gruppen-,
Spartenberichte
Ergebnis
Wie korrekt wird
gezahlt?
Spediteure?
Nachvollziehbarkeit
von Zahlungen für die
Buchhaltung
Analysemodell:
Was wissen wir über den Prozess?
Produkte
Handwerker
bietet an
beauftragt
Dienstleistungen
Kunden
bietet an
verkauft
Lieferanten
Service GmbH
holt ab
Privat
Kundenkarte
bestellt
storniert
beauftragt
Firmen
liefert aus
Spediteur
beliefert
Lager
holt
stornierte
Ware ab
liefert ab
Geschäftsprozess: Bestellungen
Bestellprozess
Status
Beschaffung
offene
Posten
Kundendaten
prüfen
MAX/MIN
Menge
Kreditlimit
prüfen
Kundenstamm
Verfügbarkeit
prüfen
Spediteur
beauftragen
Bestellung
anlegen
Lieferschein
Bestellsatz
updaten
Liefersatz
anlegen
Kundenstamm
Produktestamm
Bestellung
Best_Pos
Dienstleistung beauftragen
Vertrag
Bestellung
Best_Pos
Lieferung
Objektmodell:
Welche Geschäftsobjekte sind an dem Prozess beteiligt?
Bewegungs
daten
Stornierung
Lieferung
Spediteur
Stammdaten
Zahlung
Beauftragung /
Order
Partner
Lieferanten
Retouren
Bestellung
Dienstleister
Produkte
Artikel
Service
Kunde
FirmenKunde
Lager
KundenKarte
PrivatKunde
Vollständigkeitsanalyse
Wichtige Daten fehlen!
Identifizierung nur
über Bestellnummer
Keine Untergliederung
nach Positionen
möglich.
Bewegungs
daten
Stornierung
Zahlung
Lieferung
Spediteur
Stammdaten
Retouren
Bestellung
Beauftragung /
Order
Partner
Lieferanten
Liefernummer fehlt.
Identifizierung nur über
Bestellnummer
Dienstleister
Produkte
Artikel
Service
Kunde
FirmenKunde
Lager
KundenKarte
PrivatKunde
Zusammenfassung:
Vollständigkeitsanalyse
• Fehlen wichtiger Daten für Buchhaltung und Controlling
• Stornierungen können nicht korrekt erfasst werden, damit ist der
Rückfluss von Waren nicht messbar
-> sie verschwinden im Lager
-> Tabelle Stornierungen neu strukturieren
• Wg. Fehlender Liefernummer können Teillieferungen nicht genau
gemessen werden
-> Liefernummer einführen
• Unterscheidung zwischen Dienstleister und Waren-Lieferant wäre
sinnvoll. Damit wären Einkaufbedingungen leichter messbar
-> Feld Lieferantentyp einführen
• Es fehlt die Möglichkeit die eingekauften Services bzw. Lieferungen
genau zu messen
-> Tabelle Beauftragung / Order einführen
• u. a. m.
Verständlichkeit des Datenmodells
Synonym
Synonym
Synonyme
oder nicht?
Missverständliche
Begriffe
Homonym
Homonym
Synonym
Betrachtung von Form und Inhalt
einzelner Felder
• Nummern, bzw. Key-Felder sind alphanumerisch anstatt
numerisch wie es der Feldname vermuten lässt:
• KUNDEN_STAMM ->Firmenrabatt
der Grund sind wahrscheinlich einzelnen %-Zeichen
-> mit dem Feld kann man nicht mehr rechnen, bzw. muss es zunächst
aufbereiten
• PRODUKTE_STAMM -> Stueckpreis (varchar2)
• KUNDEN_STAMM -> Bildungsnr (varchar2)
• Felder enthalten z. T. nicht lesbare Zeichen
• BEST_POSITION -> Ausfuerhrung (@@@@)
• Viele Felder sind nicht gefüllt
• KUNDEN_STAMM -> Firmenrabatt / Kontaktperson / Kundenart
Wertähnlichkeitsprüfungen
• Begriffe, die dasselbe
meinen, sollten
standardisiert werden
• Standardisierungs-Glossar
einführen
• Lookup-Tabelle mit MappingBegriffen einführen
Standardisierte Werte
• Durchgängige Nomenklatur  Patternanalyse
• Das Bemühen sprechende Werte zu
nutzen, ist erkennbar, aber bei der
Umsetzung war man nicht
konsequent
• Falsche Werte grundsätzlich
ablehnen
• Standardisierungsregeln festlegen
Wechselseitige Fehler in den Daten
• Ein bestimmer Fehler taucht in
einem anderen Attribut in
abgewandelter Form wieder auf
• Wahrscheinlich maschinelle
Ursache
1. Domainanalyse
2. Direkter Vergleich mit SQL
SQL> select BERUFSGRUPPEN_NR,BERUFSGRUPPE
from kunden_stamm group by BERUFSGRUPPEN_NR,BERUFSGRUPPE
order by BERUFSGRUPPEN_NR;
KUNDEN_STAMM
B
1
2
3
4
5
6
7
8
A
B
BERUFSGRUPPE
-----------------------------Arbeiter
Angest_Oeff_
Schueler
Studenten
Arbeitslose
Renter
Selbststaendige
NA
ngestellter
eamter
1
3
Inhaltlich falsche Werte
• Domainanalyse
• Augenscheinlich falsche Werte
• Gleichmäßige Werteverteilung bei Feld für Anzahl Kinder
• Irritierender Umgang mit NULL bzw. fehelenden
Werten
KUNDEN_STAMM
Unique Key Analyse
• PRODUKTE_STAMM : Artikelnr / Produktnummer
•
Beide Felder werden für unterschiedliche Arten verwendet
 ein einheitlicher Schlüssel muesste entwickelt werden
• KUNDEN_STAMM: Kunden_ID / Kundennr
•
Es wird nur Kunden_ID verwendet
-> Kundennr muesste gelöscht werden
Funktionale Abhängigkeiten
• Funktionale Abhängigkeiten sollten nach Möglichkeit in einer 3 NF
aufgelöst sein
•
•
•
Hier gibt es diese Struktur aber schon und dennoch ist das Feld Artikelgruppe in der Tabelle
PRDUKTE_STAMM aufgenommen -> dieses Feld muesste entfernt werden
Allerdings offenbar es auch gleichzeitig einen Fehler. „Artikelgruppe“ ist offenbar nicht ganz
funktional abhängig -> hier muss eine weitere Analyse folgen
Ähnliches gilt für die Tabelle BEST_POSITION
Beziehungsanalyse
Artikel_
Sparte
Lieferung
Bestellnr
[6, (97%)]
Order_ID
Bestellnr
[6, (97%)]
Bestrellnr
Stornierung
Bestrellnr
[0, (100%)]
Bestrellnr
Bestellung
Kundencode
[0, (100%)]
Kunden_ID
Bestrellnr
[213, (90%)]
Best_
Position
KD_Nummer
[0, (100%)]
Kunden_ID
Kundencode
[0, (100%)]
Kunden_ID
Lager
Artikelgruppennr
[6, (92%)]
Produktnummer
[0, (100%)]
Artikelnr
[0, (100%)]
KD_Nummer
[1211, (46%)]
Kundennr
Kunden_
stamm
Artikel_
Gruppe
Order_ID
[0, (100%)]
Order_ID
Bestrellnr
[0, (100%)]
Order_ID
Zahlung
Artikelspartennnr
[1, (90%)]
Produkte_
stamm
Lieferant
Legende
Kundencode
[0, (100%)]
Kunden_ID
FK-Column
[Orphans, (%-korrekte Sätze) ]
UK-Column
Kreisbeziehungen
• Die Information Kundennummer kommt in
mehreren Tabellen vor
• Gefahr falscher Dateneintragungen
• Zudem mutiert der Feldname (Synonyme)
• Kunden_ID
• Kundencode
• KD_Nummer
• Meist Ergebnis bei dem
Zusammenführen von
Anwendungen, die
unterschiedliche Spaltennamen verwendet haben.
Beziehungsanalysen graphisch
Orphans und Childless
--- Orphans --------------------------select count(artikelgruppennr) Anz_Orphans, artikelgruppennr Wert_Orphans from produkte_stamm
where artikelgruppennr not in (select artikelgruppennr from Artikel_gruppe) group by
artikelgruppennr ;
ANZ_ORPHANS WERT_ORPHANS
----------- -----------6
100
--- Childless --------------------------select distinct artikelgruppennr from Artikel_gruppe MINUS
select distinct g.artikelgruppennr from Artikel_gruppe g, produkte_stamm p where
g.artikelgruppennr = p.artikelgruppennr;
ARTIKELGRUPPENNR
---------------8
9
11
• Die Artikelstruktur ist fehlerhaft
• Es gibt Artikelgruppen , die nicht unter einer
Sparte hängen
• Es gibt Produkte, die nicht Artikel_Gruppen
zugeordnet sind
Analyse von Hierarchien
Artikelsparte
ARTIKELSPARTENNR
1 , 2 ,3
Artikel_Gruppe
ARTIKELSPARTENNR
1,4,3
ARTIKELGRUPPENNR
1,2,3,4,5,6,11,10,9,8,7
Produkte_Stamm
ARTIKELGRUPPENNR
100,1,6,2,5,4,7,3,10
• Gerade BI-Werkzeuge haben DrillPfade, die auf einem sehr groben
Aggragations-Level einsteigen.
• Bei diesem Fehler wird man
Produkte mit einer Produktgruppe
100 nicht finden.
• Im Fall der Service GmbH sind
das ausgerechnet alle ServiceLeistungen.
•
Sie sind nachträglich in das Angebot hinzu
gekommen und man hat die Pflege der
Stammdaten vernachlässigt
Umsätze können nicht festgestellt
werden
Abfrage über die Hierarchie
Artikelgruppe -> Produkte_Stamm -> Best_Position
Abfrage über die Hierarchie
Produkte_Stamm -> Best_Position
?
Korrekte Business Intelligence
Auswertungen?
Korrekte
Werte für:
Umsatz pro Sparte?
Umsatz pro Gruppe?
Umsatz pro Produkt?
Werden korrekte
Rechnungen gestellt?
Umsatz pro Kunde?
Macht die Kundenkarte
Sinn?
Sparten
Gruppen
Produkte
Fehlerhafte
Spartenkennzeichnung
von Gruppen
Orphans
Falsche
Statuskennzeichnung
von Finanzprodukten
Fehlerhafte
Verschlüsselung
von Artikel- und
Produkten
Bestellung
Position
Doppelte
Produktnummern
KundenStamm
Doppelte Wertebelegung von
Statuskennzeichnung für
Privat- und Firmenkunden.
Fehlerhafte , nicht
rechenbare
Einzelpreisbezeichnung
Wer hat Recht
Controlling oder Buchhaltung?
Zahlen: Controlling
Zahlen: Buchhaltung
Data Quality Management Process
Data Quality
Reporting
Data Quality
Auditing
Data Profiling
Datenkorrektur
Datenqualitätsreporting
Verarbeitete Sätze pro Berichtzeitraum
(Anzahl Positionen pro Bestellungen)
Füllstandsanzeige einzelner Werte