Datenqualität sichern Wenn sich Controlling und Buchhaltung streiten Praxisseminar zu Datenqualitätsanalysen mit der Service GmbH als Fallbeispiel

<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

Operative Prozesse

Information Chain

KundeKunden-betreuer

Logistik- system

Stamm- daten

Marketing

Buch-haltung

Lager Spedition

Kunde

BedarfAdresseKredit-daten

Angebot Bestand

Bestell-daten

KD-Daten

Kredit OK Order

Adresse

Werbung

Verkaufs-daten

Rechnung

Bezahlung Reklamation

Mahnung

Liefer-schein

Ohne Daten kein Business Schlechte Daten sind wie Sand im Getriebe der Geschäftsprozesse

Operative Prozesse

Information Chain

KundeKunden-betreuer

Logistik- system

Stamm- daten

Marketing

Buch-haltung

Lager Spedition

Kunde

BedarfAdresseKredit-daten

Angebot Bestand

Bestell-daten

KD-Daten

Kredit OK Order

Adresse

Werbung

Verkaufs-daten

Rechnung

Bezahlung Reklamation

Mahnung

Liefer-schein

Der Schnittstellen-Aspekt

SCM

ERP

CRM

Bereitstellung DataWarehouse

Data Marts

Einheitliche Wandlung der Daten

Getrennte Aufbereitung

von Daten

Pot. Fehler

Unter-schiedliche Daten und

Fehlerquellen


Wo findet das Profiling / die Fehlersuche statt?

optimal

Akzeptabel Aber nicht

optimal

Potentiellfalsch

Nicht glaubhaft

Verlässlichkeit

SCM

ERP

CRM


BI Tool A

BI Tool B

BI Tool C

Data Marts


Getrennte Aufbereitung

von Daten

Pot. Fehler


Fehlerquellen


Wo findet das Profiling / die Fehlersuche statt?

KonsolidierterDatenbereich

Konsoli-dierung

SCM

ERP

CRM


BI Tool A

BI Tool B

BI Tool C

Data Marts


Fehlerquellen

Datenqualität bezogen auf den Warehousing – Prozess


Konsoli-dierung

• Heterogene Datenmodelle / Konsistenz / Homonyme / Synonyme• Kontinuität des Ladevorgangs / Vollständigkeit• Widerspruchsfreiheit zwischen den Quellen

SCM

ERP

CRM


BI Tool A

BI Tool B

BI Tool C

Data Marts


Fehlerquellen

Datenqualität bezogen auf den Warehousing – Prozess


Konsoli-dierung

• Heterogene Datenmodelle / Konsistenz / Homonyme / Synonyme• Kontinuität des Ladevorgangs / Vollständigkeit• Widerspruchsfreiheit zwischen den Quellen

Metadaten

•Eindeutige Datenobjekte Beschreibungen•Homonyme / Synonyme•Anwendungsneutral

++

+

-

--

Verlässlichkeit

Wo sollten Korrekturen stattfinden

Data WarehouseData Load

Correction

Operative Anwendung

Vorsysteme bzw. Fachabteilungen sind in der Pflicht!

Wo sollten Korrekturen stattfinden

Data Warehouse

Data Load

Correction

Operative Anwendung

Operative Anwendung

Operative Anwendung ?

InternetzugriffeInternetzugriffe

OperativesOperativesCRMCRM

MIS MIS ControllingControlling

BeschwerdenBeschwerden

AnalytischesAnalytischesCRMCRM

DiversifizierungDiversifizierungMarketing-MaterialMarketing-Material

ProduktProduktManagementManagement

CallCallCenterCenter

Informationsbasis Oracle Data Warehouse

Die Qualität von Data Warehouse daten wird immer wichtiger

Fehlende Praxis inDatenmanagement

Gewachsene Bedeutungdes Faktors Informationfür den Erfolg von Unternehmen.

Ausufernde Datenmengen Vermehrtes Inseltum

durch Fertig-Anwendungen

Daten-qualität

Immer häufigereProzessänderungen

Warum wächst die Herausforderung der Qualität der Daten

Was ist Datenqualität?Aspekte (Dimensionen) der Datenqualität

Brauchbarkeit der Daten!

1. Korrekt2. Stimmig3. Vollständig4. Dokumentiert5. Redundanzfrei6. Aktuell7. Verfügbar (Access)8. Nützlich (TCO)9. Handhabbar10.Vertrauenswürdig11.Harmonisch

Agenda











DieSERVICE GmbH

Fallbeispiel

• 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

Die SERVICE GmbH

SERVICE GmbH

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

Vertrieb

ControllingManagement

Marketing Buchhaltung

Vertrieb: wünscht leichtere AuswertungenWas sind wichtige Produkte?Was sind rentable Sparten?Hat sich der Servicebereich gelohnt?

Marketing: Absatzzahlen sind nicht aussagefähigWie viel Kunden gibt es?Lohnt die Kundekarte?Welche Segmentierung gibt es?

Buchhaltung: Es fehlen DatenWarum sind die Spediteursrechnungen so hoch?Sind alle Bestellungen korrekt bezahlt worden?Wie hoch sind die Versandkosten pro Lieferung?Was wurde storniert?

Controlling: Vergleichbarkeit fehlt Was kosten Produkte im Einkauf? Wie teuer wurden Produkte verkauft? Wie rentabel sind einzelne Produkte?

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








Die wichtigsten Analyse-/Verfahrenstechniken


„Induktives und deduktives“ Vorgehen

• Wir wissen, vermuten Dinge die nicht stimmen• Wir können sinnvolle Analysen

aufgrund bekannter Dinge ableiten

• 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

Vermutungen verifizieren

Neues entdecken

Vorgehensweisen / Methoden im Data Profiling

Unternehmensdaten

DataProfiling

Metadaten

.. .. ..

Data Quality Assessement

Erwartungen an die Datenqualität

Abgleich

DiscoveryAssertion Testing Metadata Verification

Neue Erkenntnisse(Überraschungen)

Bottom up

Methoden und Hilfsmittel

• Datenmodellierung• Datenqualitätsprüfmethoden• Data Profiling• Data Profiling Tool• Attribut-Klassifizierung (Namen)• Kategorisierung von Qualitätsregeln• ETL-Tool• Datenbank

Agenda











Zieldefinition

Bestandsaufnahme

Planen

Strukturanalysen

Regelanalysen

Umsetzung Ergebnisse

Erwartungen Geschäftsregeln

Owner User Ressourcen Kosten Modelle

Felder

Priorisieren Problemkomplexe

Objekte Beziehungen Hierarchien

Daten Werte Fach

Abgleich-Alt Neudefinition Monitoring

To

p D

ow

nB

otto

m U

pVorgehensmodell Datenqualitätsanalyse

6 Phasen, 95 Aktivitäten, 16 Ergebnis-Templates, 1 Metamodell, Klassifizierungen

Vorgehensmodell für Datenqualitätsprojekte

Erheben der Grunddaten(Ist-Daten, Wahrnehmungen, Ziele)

Beschreibung der Geschäftsprozesse(Ist-Daten, Wahrnehmungen, Ziele)

Daten-/Modell-Prüfungen

Detailanalyse

GeschäftsfelderData Owner / Daten-Interessenten / KonsumentenDQ-ErwartungenBekannte SchwachstellenKostenPrioritäten

ObjektmodellDatenflüsse und – SchnittstellenBekannte Geschäftsregeln

VollständigkeitsbetrachtungBetrachtung der VerständlichkeitSchlüsselanalysen / BeziehungsanalysenAnalyse von HierarchienSuche nach Redundanzen (z. B. Normalisierung)Mengenanalyse / Stammdatenabgleiche

Überprüfen der GeschäftsregelnAnalyse der erkannten SchwachstellenVerifizieren der DQ Erwartungen

Agenda











Agenda











Datenmodellierung

• Schlüssel• Identifizierung von Dingen

• Functional Dependencies• Versteckte Abhängigkeiten

• Beziehungen• Existenzabhängigkeit

• Orphans

• Childless

• Normalisierung• One Fact One Place

Ziel: Aufspüren und Minimierung von Redundanzen als eine der Hauptursachen von Datenfehlern

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

Primary Key Nichtschlüssel-Attribut

Nicht von einem SchlüsselAbhängige Attribute

3. Normalform

3. Normalform

Artikelgruppennummer ArtikelgruppeArtikelnummer (PK) BeschreibungArtikelname

Tabelle PRODUKTE_STAMM

Functional Dependencyüber Primary Key (PK)

Zusätzliche „verborgene“Functional Dependency

Funktionale Abhängigkeit

Artikelgruppennummer ArtikelgruppeArtikelnummer (PK) BeschreibungArtikelname

Artikelgruppennummer Artikelgruppe Beschreibung

Tabelle PRODUKTE_STAMM

Tabelle ARTIKEL_GRUPPE

Functional Dependencyüber Primary Key (PK)

Zusätzliche „verborgene“Functional Dependency

Redundante Daten mit der Gefahrvon fehlerhaftenEinträgen

Funktionale Abhängigkeit

Agenda











Kunden_Wohnart_Nr

Informationzu einem Kunden wird beschrieben

Die Art und Weise, wie ein Kundewohnt wirdbeschrieben

unter-schiedlicheWohnungs-arten sind durch-nummeriert

Hauptwort Eigenschafts-benennung

Basistyp

Bezugsobjekt BeschreibendeInformation

Charakter desAttributes

Wortstammanalyse hilft bei der Klassifizierung von Column-Namen

Basistypgruppe

Feldyp und Art des Wertes

Rolle in Ab-hängigkeits-be-ziehung

Sind NULLserlaubt

Muss Eindeutigkeitvorliegen

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 eher nicht, denn Zustände sollten für alle Sätze gelten,

nein

Zeiten Date / Time RHS ja nein

Sequenzen, Aufzählungen Zählwerte)

meist numerisch,oft versteckte Schlüsselkandidaten

LHS nein

ja

Mengen meist numerisch,einfache Zahlenwerte ohne weitere Angaben

RHS nein,wenn etwas gezählt wird, sollte es immer gezählt warden

ja

Operatoren und abgeleitete Größen


RHS nein ja

Werte

(brauchen i. d. R. eine relativierende Bezugsgröße z. B. Preis -> Währung)

meist numerisch,einfache Zahlenwerte ohne weitere Angaben(brauchen i. d. R. eine relativierende Bezugsgröße z. B. Preis -> Währung)

RHS nein ja

Maße, Bezugsgrößen, Einheiten


RHS nein ja

select substr(table_name,1,18) Tab,substr(column_name,1,28) Col,

substr(data_type,1,8) Typ, substr(data_length,1,3) Len,

'| ' Nul,'| ' Basis,'| ' Bus,'| ' Syn_zu,'| ' Hom_zu,'| ' Dom,'| ' Max,'| ' Min

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

select substr(table_name,1,18) Tab,substr(column_name,1,28) Col,

substr(data_type,1,8) Typ, substr(data_length,1,3) Len,

'| ' Nul,'| ' Basis,'| ' Bus,'| ' Syn_zu,'| ' Hom_zu,'| ' Dom,'| ' Max,'| ' Min

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

Feldliste

• Über alle Tabellenhinweg

• Alphabetisch sortiert nach Spaltennamen

• Hilft beim Erkennen von Homonymen und Synonymen

• Hilft bei der Bewertung der Tauglichkeit von Spaltennamen

• Erlaubt Vorahnungen von Schlüsselkandidaten

FeldlisteTAB COL TYP LEN NUL BASIS BUS SYN_ZU HOM_ZU DOM MAX MIN------------------ ---------------------------- -------- --- ------ ------ ------ ------ ------ ------ ------ -----KUNDEN_STAMM ANREDE VARCHAR2 10 | | | | | | | |LIEFERANT ANSCHRIFT VARCHAR2 40 | | | | | | | |KUNDEN_STAMM ANZ_KINDER NUMBER 22 | | | | | | | |PRODUKTE_STAMM ANZ_STEUCK_PRO_VERPACKUNG NUMBER 22 | | | | | | | |PRODUKTE_STAMM ARTIKELGRUPPE VARCHAR2 50 | | | | | | | |ARTIKEL_GRUPPE ARTIKELGRUPPENNAME VARCHAR2 50 | | | | | | | |PRODUKTE_STAMM ARTIKELGRUPPENNR NUMBER 22 | | | | | | | |ARTIKEL_GRUPPE ARTIKELGRUPPENNR NUMBER 22 | | | | | | | |PRODUKTE_STAMM ARTIKELNAME VARCHAR2 30 | | | | | | | |PRODUKTE_STAMM ARTIKELNR NUMBER 22 | | | | | | | |BEST_POSITION ARTIKELNUMMER VARCHAR2 400 | | | | | | | |ARTIKELSPARTE ARTIKELSPARTENNAME VARCHAR2 18 | | | | | | | |ARTIKEL_GRUPPE ARTIKELSPARTENNR NUMBER 22 | | | | | | | |ARTIKELSPARTE ARTIKELSPARTENNR NUMBER 22 | | | | | | | |KUNDEN_STAMM BERUFSGRUPPE VARCHAR2 30 | | | | | | | |KUNDEN_STAMM BERUFSGRUPPEN_NR VARCHAR2 1 | | | | | | | |PRODUKTE_STAMM BESCHREIBUNG VARCHAR2 400 | | | | | | | |PRODUKTE_STAMM BESTAND NUMBER 22 | | | | | | | |BESTELLUNG BESTELLDATUM DATE 7 | | | | | | | |STORNIERUNG BESTELLDATUM DATE 7 | | | | | | | |LIEFERUNG BESTELLDATUM DATE 7 | | | | | | | |BEST_POSITION BESTELLDATUM DATE 7 | | | | | | | |BEST_POSITION BESTELLMENGE NUMBER 22 | | | | | | | |BESTELLUNG BESTELLNR NUMBER 22 | | | | | | | |BEST_POSITION BESTELLNR NUMBER 22 | | | | | | | |STORNIERUNG BESTELLNR NUMBER 22 | | | | | | | |LIEFERUNG BESTELLNR NUMBER 22 | | | | | | | |LIEFERUNG BESTELL_TOTAL NUMBER 22 | | | | | | | |BESTELLUNG BESTELL_TOTAL NUMBER 22 | | | | | | | |STORNIERUNG BESTELL_TOTAL NUMBER 22 | | | | | | | |LIEFERANT BEVORZUGUNG_KLASSE NUMBER 22 | | | | | | | |KUNDEN_STAMM BILDUNG VARCHAR2 30 | | | | | | | |KUNDEN_STAMM BILDUNGS_NR VARCHAR2 1 | | | | | | | |KUNDEN_STAMM BRANCHE VARCHAR2 30 | | | | | | | |

BEST_POSITION POSNUMMER NUMBER 22 | | | | | | | |

TAB COL TYP LEN NUL BASIS BUS SYN_ZU HOM_ZU DOM MAX MIN------------------ ---------------------------- -------- --- ------ ------ ------ ------ ------ ------ ------ -----KUNDEN_STAMM ANREDE VARCHAR2 10 | | | | | | | |LIEFERANT ANSCHRIFT VARCHAR2 40 | | | | | | | |KUNDEN_STAMM ANZ_KINDER NUMBER 22 | | | | | | | |PRODUKTE_STAMM ANZ_STEUCK_PRO_VERPACKUNG NUMBER 22 | | | | | | | |PRODUKTE_STAMM ARTIKELGRUPPE VARCHAR2 50 | | | | | | | |ARTIKEL_GRUPPE ARTIKELGRUPPENNAME VARCHAR2 50 | | | | | | | |PRODUKTE_STAMM ARTIKELGRUPPENNR NUMBER 22 | | | | | | | |ARTIKEL_GRUPPE ARTIKELGRUPPENNR NUMBER 22 | | | | | | | |PRODUKTE_STAMM ARTIKELNAME VARCHAR2 30 | | | | | | | |PRODUKTE_STAMM ARTIKELNR NUMBER 22 | | | | | | | |BEST_POSITION ARTIKELNUMMER VARCHAR2 400 | | | | | | | |ARTIKELSPARTE ARTIKELSPARTENNAME VARCHAR2 18 | | | | | | | |ARTIKEL_GRUPPE ARTIKELSPARTENNR NUMBER 22 | | | | | | | |ARTIKELSPARTE ARTIKELSPARTENNR NUMBER 22 | | | | | | | |KUNDEN_STAMM BERUFSGRUPPE VARCHAR2 30 | | | | | | | |KUNDEN_STAMM BERUFSGRUPPEN_NR VARCHAR2 1 | | | | | | | |PRODUKTE_STAMM BESCHREIBUNG VARCHAR2 400 | | | | | | | |PRODUKTE_STAMM BESTAND NUMBER 22 | | | | | | | |BESTELLUNG BESTELLDATUM DATE 7 | | | | | | | |STORNIERUNG BESTELLDATUM DATE 7 | | | | | | | |LIEFERUNG BESTELLDATUM DATE 7 | | | | | | | |BEST_POSITION BESTELLDATUM DATE 7 | | | | | | | |BEST_POSITION BESTELLMENGE NUMBER 22 | | | | | | | |BESTELLUNG BESTELLNR NUMBER 22 | | | | | | | |BEST_POSITION BESTELLNR NUMBER 22 | | | | | | | |STORNIERUNG BESTELLNR NUMBER 22 | | | | | | | |LIEFERUNG BESTELLNR NUMBER 22 | | | | | | | |LIEFERUNG BESTELL_TOTAL NUMBER 22 | | | | | | | |BESTELLUNG BESTELL_TOTAL NUMBER 22 | | | | | | | |STORNIERUNG BESTELL_TOTAL NUMBER 22 | | | | | | | |LIEFERANT BEVORZUGUNG_KLASSE NUMBER 22 | | | | | | | |KUNDEN_STAMM BILDUNG VARCHAR2 30 | | | | | | | |KUNDEN_STAMM BILDUNGS_NR VARCHAR2 1 | | | | | | | |KUNDEN_STAMM BRANCHE VARCHAR2 30 | | | | | | | |

BEST_POSITION POSNUMMER NUMBER 22 | | | | | | | |

Agenda











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 ToolMethoden

Feintuning zu

den Analyse-

methoden

Die

operativen

Daten

Proto-

kollierung

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

Auswahl und Ergebnisansicht Methoden

Chart-DarstellungTabellen-Darstellung

Drill-Werte

Operative Datensätze

Die Tabellen,

die zu dem Analyse-

fukus

gehören

Feintuning

zu den

Analyse-

methoden

Analyse-Job-

Protokolle Aktivierbare Business Rules

Starten einesProfiling-Laufs

Starten einer Correction-Mapping-Generierung

Generierung-Rule

SourceStage

Profiling Stage

SAP R/3

SAPIntegrator

non OracleGateway / ODBC/ FTP

Oracle 9i / 10g / 11gDB2, SQL ServerInformix, Teradata

LDAP

Meta DatenRepository

Direct PathDBLink

TransportableModules

DBMS_LDAP

Siebel CRM

OracleeBusiness

Text / XML

Analyse Datenbank

Analyseumgebung

Eindeutigkeitsanalysen (Unique Key)

Wertebereichsanalysen (Domain)

Funktionale Abhängigkeiten

Beziehungen (Relational)

Wertmustererkennung (Pattern)

Formate (Data Type)

Statistiken (Aggregation)

Korrekt, muß 0 sein

Korrekt:Es kann nur einWert gepflegt sein.


Korrekt, das sind richtige Werte


Korrekt: Zusammen 100%(Alle Fälle erfasst)

Problem:kein Schlüsselfeldist gepflegt

Korrekt, das sind die richtigen Werte


Korrekt

Korrekt:Zusammen100%.(Alle Fälle erfasst)

Problem


Individuelle Regeln (Data Rules)

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

DATA ViewerDATA Viewer

Native PL/SQL Profiling Routinen

Datenschema SYS Schema

DQ_CALL_FD

DQ_CALL_AGGR

DQ_CALL_TYPE

DQ_CALL_PATTERN

DQ_CALL_DOMAIN

DQ_CALL_UNIQUE

Kunde

DQ_Kunde_FD

DQ_Kunde_AGGR

DQ_Kunde_TYPE

DQ_Kunde_PATTERN

DQ_Kunde_DOMAIN

DQ_Kunde_UNIQUE

SQL DeveloperSQL Developer

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












1. Fragebögen / Templates

2. Vollständigkeitsanalyse

3. Schnittstellenanalyse

4. Wo und wie liegen Daten vor?

5. Synonymen / Homonymenanalyse

6. Prüfungen von Datenstrukturen• Felder, Tabellen, Beziehungen

• Originaldaten oder Kopien?

• Müssen alle Daten analysiert werden?

Techniken

Verfahren

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

• Programmier-aufwendig

• Kann performance-kritisch sein

• Erlaubt auch fachliche Prüfungen

Stage-Tabelle

Varchar2()

Kopieren

Prüfungen

Geprüfte Daten

DateNumberVarchar2()

Statistiken

z. B. mit Table Functions

Prüfkonzepte in der Datenbank

• Einfach implementierbar

• Bessere Performance• Nur bei aktivierten

Constraints• Fachliche Prüfungen

kaum möglich• Eventuell zusätzliche

Prüfungen nötig

Stage-Tabelle+ Geprüfte Daten

Kopieren

Statistiken


BadFile

FehlerhafteSätze

CheckConstraints

StatistikRoutine

DMLError Log

Error Logging

• Constraints• Unique Key / Primary Key• Foreign Key• Not Null• Check Constraint

Kunde

Kunde_err

KUNDENNRVORNAME NACHNAME ORTNR STRASSE TELEFON

KUNDENNRVORNAME NACHNAME ORTNR STRASSE TELEFON

ORA_ERR_NUMBER$ORA_ERR_MESG$ ORA_ERR_ROWID$ORA_ERR_OPTYP$ ORA_ERR_TAG$

Insert into KundeValues (......)

Error Logging: Beispiel 1

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)

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)

SQL> desc T3 Name Type --------------------------------- -------- -------- F1 NUMBER F2 NUMBER

SQL> desc T3 Name Type --------------------------------- -------- -------- F1 NUMBER F2 NUMBER

insert into t3 values(1,2) LOG ERRORS INTO err$_T3

insert into t3 values(1,2) LOG ERRORS INTO err$_T3

exec DBMS_ERRLOG.CREATE_ERROR_LOG ('T3') exec DBMS_ERRLOG.CREATE_ERROR_LOG ('T3')

1* select substr(ora_err_number$,1,10) Nr,substr(ora_err_mesg$,1,50) Err from ERR$_T3SQL> /NR ERR---------- --------------------------------------------------1 ORA-00001: unique constraint (DWH4.IDX_T3) violate

1* select substr(ora_err_number$,1,10) Nr,substr(ora_err_mesg$,1,50) Err from ERR$_T3SQL> /NR ERR---------- --------------------------------------------------1 ORA-00001: unique constraint (DWH4.IDX_T3) violate

2

3

4

5

1

Error Logging: Beispiel 2

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 0end ergebnisfrom dual;

select case 1 when 1 then 1 when 2 then 2 else 0end ergebnisfrom dual;

select case when isnumeric(999) = 1 then 'Numerisch‘‚ else 'nicht numerisch‘‚ end Ergebnisfrom dual;

select case when isnumeric(999) = 1 then 'Numerisch‘‚ else 'nicht numerisch‘‚ end Ergebnisfrom dual;

with tab as (select '123' col1 from dual union allselect '123' col1 from dual union allselect 'x23d' col1 from dual )

select case when regexp_like(col1,'^[[:digit:]]') then ‘numerisch'

else ‘Nicht numerisch' end from tab

with tab as (select '123' col1 from dual union allselect '123' col1 from dual union allselect 'x23d' col1 from dual )



Beispiel 1

Beispiel 2

Abarbeitungslogik mit CASEBeispiel mit temporärer Tabelle

• Temporäre Tabelle ist optional• Ist aber damit wesentlich übersichtlicher • Erlaubt Kombination von unterschiedlichen Prüfkriterien

Stage-Tabelle

Varchar2()

Kopieren

Gepruefte_Daten


Feld1Feld2Feld3

Temp-Tabelle

Varchar2()

Feld1Feld2Feld3Feld1_is_nullFeld1_is_numericFeld2_is_numeric

Insert into temp_tableSelect case ....FromStage_Table

Error_Daten


Insert ALL when Feld_1_is_null =1 into Error_Datenwhen Feld_1_is_null=0 into Gepruefte_Daten

Key

Feld1

Feld2

Feld3

Feld4

...

SRC_Table

TGT_Table

Key

Flag

Prueftabelle

Insert into TGT_TABLE.....Select ..,..,..,.. When SRC_TABLE.key = Prueftable.key and Prueftable.key = 0

Insert into PrueftabelleSelect 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 endFrom SRC_TABLE;

Err_Table

Insert into TGT_TABLE.....When SRC_TABLE.key = Prueftable.key and Prueftable.key != 0

FromSRC_TABLE,Prueftabelle

Key

Feld1

Feld2

Feld3

Feld4

...

Key

Feld1

Feld2

Feld3

Feld4

...

Abarbeitungslogik mit CASEAufbau einer Flag-Tabelle zur besseren Dokumentation

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 FROM SRC1 ) ;

INSERT 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_NUMERICFROM EL_KUNDE_TMP );

51002 Zeilen wurden erstellt.Abgelaufen: 00:00:01.45

51002 Zeilen wurden erstellt. Abgelaufen: 00:00:02.67

Abarbeitungslogik mit CASE / WITHArbeiten mit WITH-Clause ohne Zwischentabelle

create table src (schluessel varchar2(30), Beschreibung varchar2(30), Rechenfeld varchar2(30))

create table src (schluessel varchar2(30), Beschreibung varchar2(30), Rechenfeld varchar2(30))

create table tgt (schluessel number, Beschreibung varchar2(30), Rechenfeld number)

create table tgt (schluessel number, Beschreibung varchar2(30), Rechenfeld number)

SCHLUESSEL BESCHREIBUNG RECHENFELD---------- --------------- ----------1 Wert 8*71 Wert 8*72 xxxx 8883 yyyy 01a www 55

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‚)

SCHLUESSEL BESCHREIBUNG RECHENFELD---------- --------------- ---------- 2 xxxx 888 3 yyyy 1

Prüfungen auf numerischHilfsfunktion Is_Number bzw. regexp_like

create or replace function is_number(in_var in varchar2)return varchar2is v_number number;begin select to_number(in_var) into v_number from dual; return 'Y'; -- No exception, so is a numberexception when others then return 'N'; -- is not a numberend;

create or replace function is_number(in_var in varchar2)return varchar2is v_number number;begin select to_number(in_var) into v_number from dual; return 'Y'; -- No exception, so is a numberexception when others then return 'N'; -- is not a numberend;





Alternative mit REGEXP_LIKE

Hilfsfunktion:Date_Check

• In Verbindung mit der Case-Anweisung

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;

BEGIN 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'; END;

Prüfungen auf FeldtypenVariante 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 Typfrom src

SCHLUESSEL TYP------------- --------------1 numerisch1 numerisch2 numerisch3 numerischa alphanumerisch NULL

SCHLUESSEL BESCHREIBUNG RECHENFELD------------- --------------- --------------1 Wert 8*71 Wert 8*72 xxxx 8883 yyyy 01a www 55 www 55

Satzübergreifende Prüfungenpro Feld

• Eindeutigkeit

Satzübergreifende PrüfungenEindeutigkeit

• 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-Job-Abbruchgründen • Beim Schreiben in Tabellen mit Unique-Constraints

• Beim Aufbau von Parent-Child-Beziehungen in denen eindeutige Parents gebraucht werden

Eindeutigkeit

select SCHLUESSEL, count(SCHLUESSEL) cnt from unique_test group by SCHLUESSEL

SCHLUESSEL CNT---------- ---------- 1 2 42 1 2 1 32 1

select SCHLUESSEL, count(SCHLUESSEL) cnt from unique_test group by SCHLUESSEL

SCHLUESSEL CNT---------- ---------- 1 2 42 1 2 1 32 1

SQL> select * from unique_test;

SCHLUESSEL FELD---------- ---------- 1 abc 1 abc 2 abc 32 abc 42 abc

SQL> select * from unique_test;

SCHLUESSEL FELD---------- ---------- 1 abc 1 abc 2 abc 32 abc 42 abc

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

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 aller Feldwerte mit ihrer jeweiligen Häufigkeit

Anzeigen des prozeduralen Grades der Eindeutigkeit

select schluessel, n from (select count(schluessel) n, schluessel from unique_test group by SCHLUESSEL )where n > 1

SCHLUESSEL N---------- ---------- 1 2

select schluessel, n from (select count(schluessel) n, schluessel from unique_test group by SCHLUESSEL )where n > 1

SCHLUESSEL N---------- ---------- 1 2

Anzeigen der Werte, die die Eindeutigkeit verletzen

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;

FeldstatistikNULL, MAX/MIN, Prozent, Count, Numeric, Alpanumeric…

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

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;

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;

Herausfiltern und Protokollieren Not Null Feldern mit graphischen Mitteln

case when F2 is NULL then 1 else 0 end

Select F1 from (Select count(F1) n,F1 from s group by F1) where n > 1;

Prüfen auf Eindeutigkeit der Eingabesätze (Graphik)

Es dürfen nur Sätze geladen werden, die einmal im Quell-bestand vorkommen.

Mustererkennung (Translate)

create table Kunde ( KUNDENNR NUMBER(10), KUNDENNAME VARCHAR2(20), BERUFSGRUPPE VARCHAR2(20), SEGMENT NUMBER(10));


column Anzahl format 9999999column Maske format a30select 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')) Maskefrom lc order by NR_Value DESC)group by maske

column Anzahl format 9999999column Maske format a30select 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')) Maskefrom 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}

ANZAHL MASKE ------ -------- 4 Aa{11} 14 Aa{13} 2 Aa{11}b 4 Aa{10} 5 Aa{5} 13 Aa{6} 7 Aa{12}

SQL> select * from kunde where rownum < 10;

KUNDENNR KUNDENNAME BERUFSGRUPPE SEGMENT-------- ----------- -------------- ------- 705 Meister Angestellte 4 706 Schmidt Rentner 34 707 Meister Angestellte 16 708 Kraemer Beamte 38 709 Schneider Freiberufler 16 710 Schuster Selbstaendige 47 711 Mueller Unbeschaeftigt 2 712 Schneider Angestellte 36 713 Hartmann Rentner 46



9 digitA upper lettera lower letterB blank{n} n-time repetition

Mustererkennung (REGEXP_LIKE)







SQL> select * from kunde where REGEXP_LIKE(segment,'^[[:digit:]]{1}$');

KUNDENNR KUNDENNAME BERUFSGRUPPE SEGMENT---------- -------------------- -------------------- ---------- 705 Meister Angestellte 4 711 Mueller Unbeschaeftigt 2 724 Schuster Student 2 726 Kraemer Rentner 5 728 Mueller Unbeschaeftigt 9 743 Bauer Freiberufler 3

SQL> select * from kunde where REGEXP_LIKE(segment,'^[[:digit:]]{1}$');

KUNDENNR KUNDENNAME BERUFSGRUPPE SEGMENT---------- -------------------- -------------------- ---------- 705 Meister Angestellte 4 711 Mueller Unbeschaeftigt 2 724 Schuster Student 2 726 Kraemer Rentner 5 728 Mueller Unbeschaeftigt 9 743 Bauer Freiberufler 3

Finde alle Sätze, deren Segment-Wert nur 1-stellig numerisch ist

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

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' end patternfrom P) group by pattern;

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' end patternfrom P) group by pattern;

Mustererkennung (REGEXP_LIKE)

create table P (PNR number, pruef_wert varchar2(20));

create table P (PNR number, pruef_wert varchar2(20));

SQL> select * from p;

PNR PRUEF_WERT---------- ------------- 1 8 2 89 3 A89 4 D-25436 5 Eumel 7 A89 6 D-1000 8 D-10003

SQL> select * from p;

PNR PRUEF_WERT---------- ------------- 1 8 2 89 3 A89 4 D-25436 5 Eumel 7 A89 6 D-1000 8 D-10003

ANZAHL MUSTER------- -------------------------- 1 zweistellig numerisch 2 1 Großbuchstabe + 2 Zahlen 2 falscher Wert 1 einstellig numerisch 2 PLZ

ANZAHL MUSTER------- -------------------------- 1 zweistellig numerisch 2 1 Großbuchstabe + 2 Zahlen 2 falscher Wert 1 einstellig numerisch 2 PLZ

Check Constraint mit Regular ExpressionsCREATE 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));

Alter table check_kunde add constraint Ch_KD_Name check(REGEXP_LIKE(NACHNAME, '[^[:digit:]]'));

Regel: Im Kundennamen müssen Buchstaben vorkommen und keine reine Zahlenkolonne

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

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;

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;

Domain-Analysen







VALUE_NAME ANZAHL_WERTE PROZENT--------------- ------------ -------Unbeschaeftigt 14 28.57Rentner 8 16.33Selbstaendige 7 14.29Student 5 10.2Beamte 5 10.2Angestellte 4 8.16Freiberufler 4 8.16Freiberufler 2 4.08

VALUE_NAME ANZAHL_WERTE PROZENT--------------- ------------ -------Unbeschaeftigt 14 28.57Rentner 8 16.33Selbstaendige 7 14.29Student 5 10.2Beamte 5 10.2Angestellte 4 8.16Freiberufler 4 8.16Freiberufler 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 F2 F3----- ----- -----a ab ba am bb bx xc ck ka al bd dt td ab t gh



LHS (F1) RHS(F2)

abcd

abambxckaldtabgh

(1:3) (1:2)

select count(distinct f1) LHS_Anzahl, f2 RHS_Wert from t group by f2; LHS_ANZAHL RHS_WERT---------- --------- 1 dt 2 ab 1 bx 1 gh 1 am 1 ck 1 al

select count(distinct f1) LHS_Anzahl, f2 RHS_Wert from t group by f2; LHS_ANZAHL RHS_WERT---------- --------- 1 dt 2 ab 1 bx 1 gh 1 am 1 ck 1 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


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)







LHS (F1) RHS(F3)

aabcadd

bbxkbtt

(1:1) (1:1)

select count(distinct f1) LHS_Anzahl, f3 RHS_Wert from t group by f3; LHS_ANZAHL RHS_WERT---------- -------- 1 k 1 b 1 t 1 x 1

select count(distinct f1) LHS_Anzahl, f3 RHS_Wert from t group by f3; LHS_ANZAHL RHS_WERT---------- -------- 1 k 1 b 1 t 1 x 1


LHS_ANZAHL VORKOMMEN---------- ---------- 1 5


LHS_ANZAHL VORKOMMEN---------- ---------- 1 5

Bei 5 RHS-Werten gibt es nur exakt 1 LHS-Wert

WertabhängigkeitenInhaltliche Abhängigkeit von zwei Feldern

• Variante 1: Wertereihenfolgen• Variante 2: Wertemengen

Identifier Zustands-beschreibung

ZeitabhängigeInformation

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, 30pro Aktion müssen in zeitlicher Reihenfolgestattfinden .

Beispielregel zu Variante 2:Für jede Aktion müssen immer 3 Statusmeldungen und die dazu passendenAktionsdatumsangaben folgen.

WertabhängigkeitenInhaltliche 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 nachAktion, 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 ungleichabgefragt werden.(Lösung in Script, hier nur graphisch)

Join-Bedingung

WertabhängigkeitenInhaltliche Abhängigkeit von zwei Felder, Prüfung auf Wertmengen

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

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



Beispielregel zu Variante 2:Für jede Aktion müssen immer 3 Statusmeldungen und die dazu passendenAktionsdatumsangaben folgen.

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

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

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 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 group by artikelgruppennr) L,(select artikelgruppennr R_Key,count(artikelgruppennr) cnt2 from produkte_stamm group by artikelgruppennr) RwhereL.L_Key=R.R_Key;

MIN(L.CNT1) MAX(L.CNT1) MIN(R.CNT2) MAX(R.CNT2)----------- ----------- ----------- ----------- 1 1 1 14

select min(l.cnt1), max(l.cnt1), min(R.cnt2), max(R.cnt2) from(select artikelgruppennr L_Key,count(artikelgruppennr) cnt1 from artikel_gruppe group by artikelgruppennr) L,(select artikelgruppennr R_Key,count(artikelgruppennr) cnt2 from produkte_stamm group by artikelgruppennr) RwhereL.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 innerhalb einer Tabelle

Prüfung über 2 Tabellen hinweg

Tabellenübergreifende Prüfungen – Orphans

SQL> select distinct artikelgruppennr from produkte_stamm order by artikelgruppennr;

ARTIKELGRUPPENNR---------------- 1 2 3 4 5 6 7 10 100



SQL> select distinct artikelgruppennr from Artikel_gruppe order by artikelgruppennr;

ARTIKELGRUPPENNR---------------- 1 2 3 4 5 6 7 8 9 10 11



Artikel_Gruppe Produkte_Stamm

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

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 ;


Tabellenübergreifende Prüfungen – Childless









Artikel_Gruppe Produkte_Stamm

select distinct artikelgruppennr from Artikel_gruppe MINUSselect distinct g.artikelgruppennr from Artikel_gruppe g, produkte_stamm p where g.artikelgruppennr = p.artikelgruppennr;

ARTIKELGRUPPENNR---------------- 8 9 11

select distinct artikelgruppennr from Artikel_gruppe MINUSselect distinct g.artikelgruppennr from Artikel_gruppe g, produkte_stamm p where g.artikelgruppennr = p.artikelgruppennr;


Redundant Columns

• Redundanzen: eine der Hauptfehlerursachen• Redundanzen innerhalb einer Tabelle

• Feststellbar mitSelect 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(*) fromSelect Count(*) from T1,T2 where T1.PK = T2.PK MINUSSelect Count(*) from T1,T2 where T1.PK = T2.PK and T1.Feld_Y = T2.Feld;

Redundant ColumnsParent- Child

create table parent (Parent_Key number, Wert_X varchar2(10));

insert into parent values(1,'AUDI');insert into parent values(2,'VW');insert into parent values(3,'OPEL');insert into parent values(4,'BMW');insert into parent values(5,'Daimler');

create table parent (Parent_Key number, Wert_X varchar2(10));

insert into parent values(1,'AUDI');insert into parent values(2,'VW');insert into parent values(3,'OPEL');insert into parent values(4,'BMW');insert into parent values(5,'Daimler');

create table child (Child_key number, Parent_Key number, Wert_Y varchar2(10));

insert into child values(1,1,'AUDI');insert into child values(2,1,'AUDI');insert into child values(1,2,'VW');insert into child values(2,2,'VW');insert into child values(1,3,'OPEL');insert into child values(2,3,'OPEL');insert into child values(1,'4','BMW');insert into child values(2,'4','BMW');insert into child values(3,'4','BMW');insert into child values(4,'4','BMW');insert into child values(1,5,'Daimler');insert into child values(2,5,'Daler');

create table child (Child_key number, Parent_Key number, Wert_Y varchar2(10));

insert into child values(1,1,'AUDI');insert into child values(2,1,'AUDI');insert into child values(1,2,'VW');insert into child values(2,2,'VW');insert into child values(1,3,'OPEL');insert into child values(2,3,'OPEL');insert into child values(1,'4','BMW');insert into child values(2,'4','BMW');insert into child values(3,'4','BMW');insert into child values(4,'4','BMW');insert into child values(1,5,'Daimler');insert into child 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

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 best_position group by BESTELLNR) Pwhere B.bestellnr = P.BESTELLNR and

B.BESTELL_TOTAL != P.ges_Pos_Wert ;

select count(*) from(select bestellnr,BESTELL_TOTAL from bestellung) B, (select BESTELLNR, sum(GESAMT_POS_PREIS) ges_Pos_Wert from best_position group by BESTELLNR) Pwhere B.bestellnr = P.BESTELLNR and

B.BESTELL_TOTAL != P.ges_Pos_Wert ;

StageSchema

AnalyseSchema

SAP R/3SAPIntegrator

non Oracle Gateway / ODBC/ FTP/ Golden Gate

Oracle 10g / 11g

DB2, SQL ServerInformix, Teradata

Direct PathDBLinkSiebel CRM

Text / XML

Analyse Datenbank

Oracle

Quell-Umgebung

Sampling

Zugreifbarkeit auf Daten

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• Patternanalyse

• Domainanalyse

• Not Null Messung

• Typfeststellung

• Max/Min/Aussreisser

• Eindeutigkeitsanalyse

• Functional Dependency

• Orphan / Childless

• Redundant Column

• Kardinalität

Macht Sinn?Bedingt

Bedingt

Bedingt

Bedingt

Nein

Nein

Nein

Nein

Nein

Nein

Agenda











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 Liste Geschäftsfelder (zur Fokussierung)

• T1.15 Datenbestandsliste (für die Vollständigkeitsanalyse)

• T1.20 Liste Metadatenbestände

• T1.35 Liste bekannten Schwachstellen und Erwartungen

• T1.45 Bekannte Business Rules

• T1.50 Geschätzte Kosten der bekannten Schwachstellen

• T2.25 Liste Datenbestände, Geschäftsobjekte und Fehldaten

• T2.50 Regelliste (Bekannte + abgeleitete Regeln)

• T3.00 Feldliste

• T3.25 Liste Missverständliche Objektnamen

• T3.50 Schlüsselliste

• T3.55 Funktionale Abhängigkeiten

• T3.60 Beziehungen

• T3.70 Normalform-Analysen

• T3.75 Redundante Felder

Die wichtigstenListen

Vollständigkeitsanalyse

• Erstellen von • Analysemodell ergibt

aus der Befragung derProzess-Kenner

• Objektmodell ergibt sichaus dem Analysemodell

• Rückschlüsse auf fehlende Informationen• Über Vergleiche des

Objektmodells mit derDatenbestandsliste

Analysemodell

Objektmodell

Schnittstellenanalyse

• Erstellen von • Prozessmodell (Analyse-Modell)

• Objektmodell

• Datenbestandsliste

• Finden von Übergabepunkten als diekritischen 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. Wortstammanalyse

2. Attributklassifizierung

3. Finden von Kandidaten über Basistypen

4. Kandidaten mit Aggregation-, Domain-, Pattern-Analyse auf fachliche Gleichheit überprüfen

Finden von Anomalien

• 1. NF• Domainanalyse und Sichtprüfung

• 2. NF1. 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











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?

Stammdaten(Produkte / Artikel)

StammdatenHierarchien?

Einkaufspreise(fehlerhaft?)

Stammdaten(Kunden / Identifizierung )

Lieferungen(Bestellungen/Stornierungen)

Wo bleibt die gelieferte Ware?

Wie korrekt wirdgezahlt?

Artikel-,Gruppen-,Spartenberichte

Vergleichbarkeit vonEinkaufs- und Verkaufspreisen

Rabatte?

Wer hat wieviel gekauft?

Kunden-Segmentierung

Welche Produkte lohnen sich

Spediteure?

Nachvollziehbarkeit von Zahlungen für dieBuchhaltung

Korrekte Zahlen zurSteuerung vonMarketingkampagnenund für den Vertrieb

Messbarkeit fürControlling

Auswertbarkeit

Stimmen die Einträge

Problem-komplex

FragestellungenBenannte Probleme

Ergebnis

Analysemodell: Was wissen wir über den Prozess?

Service GmbH

Produkte

Kunden

verkauft

Lieferanten

Dienst-leistungen

Privat

Firmen

Kunden-karte

Lager

Handwerker

Spediteur

bestellt

beauftragt

liefert aus

holt ab

bietet an

beauftragt bietet an

beliefert

storniert

holtstornierteWare ab

liefert ab

Geschäftsprozess: Bestellungen

Kundendatenprüfen

Kreditlimitprüfen

Verfüg-barkeitprüfen

Dienstleist-ung be-

auftragen

MAX/MINMenge Spediteur

beauftragen

Liefersatzanlegen

Bestellsatzupdaten

Kunden-stamm

Produkte-stamm

Liefer-schein

Vertrag

Kunden-stamm

Status

Bestellunganlegen

offene Posten

BestellungBest_Pos

BestellungBest_Pos

Lieferung

Beschaffung

Bestellprozess

Objektmodell: Welche Geschäftsobjekte sind an dem Prozess beteiligt?

Kunde

Lieferanten

Zahlung

Stornierung

Produkte

Privat-Kunde

Firmen-Kunde

Kunden-Karte

Artikel Service

LagerBestellung

Lieferung

Retouren

Dienst-leister

Partner

Spediteur

Beauf-tragung /

Order

Bewegungsdaten

Stamm-daten

Vollständigkeitsanalyse Wichtige Daten fehlen!

Kunde

Lieferanten

Zahlung

Stornierung

Produkte

Privat-Kunde

Firmen-Kunde

Kunden-Karte

Artikel Service

LagerBestellung

Lieferung

Retouren

Dienst-leister

Partner

Spediteur

Beauf-tragung /

Order

Bewegungsdaten

Stamm-daten

Liefernummer fehlt.Identifizierung nur über BestellnummerIdentifizierung nur

über BestellnummerKeine Untergliederung nach Positionen möglich.

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

Homonym

Homonym

Synonym

Synonym

Missver-ständlicheBegriffe

Synonyme oder nicht?

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 ->Firmenrabattder 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 Mapping-Begriffen 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;

B BERUFSGRUPPE- ------------------------------1 Arbeiter2 Angest_Oeff_3 Schueler4 Studenten5 Arbeitslose6 Renter7 Selbststaendige8 NAA ngestellterB eamter 1 3

SQL> select BERUFSGRUPPEN_NR,BERUFSGRUPPE from kunden_stamm group by BERUFSGRUPPEN_NR,BERUFSGRUPPE order by BERUFSGRUPPEN_NR;

B BERUFSGRUPPE- ------------------------------1 Arbeiter2 Angest_Oeff_3 Schueler4 Studenten5 Arbeitslose6 Renter7 Selbststaendige8 NAA ngestellterB eamter 1 3

KUNDEN_STAMM

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

Kunden_stamm

Lieferant

Zahlung

Stornierung

Lager

Artikel_Sparte

Artikelspartennnr [1, (90%)]

Artikelgruppennr [6, (92%)]

Beziehungsanalyse

Produktnummer [0, (100%)]

Artikelnr [0, (100%)]

Order_ID [0, (100%)]Order_ID

Bestrellnr [0, (100%)]Order_ID

Best_Position

Bestrellnr [213, (90%)]

KD_Nummer[1211, (46%)]Kundennr

KD_Nummer[0, (100%)]Kunden_ID

Bestrellnr [0, (100%)]Bestrellnr

Kundencode[0, (100%)]Kunden_ID


BestellungArtikel_Gruppe

Bestellnr [6, (97%)]Order_ID

Bestellnr [6, (97%)]Bestrellnr

Lieferung


Produkte_stamm

FK-Column[Orphans, (%-korrekte Sätze) ]UK-Column

Legende

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 vonAnwendungen, die unterschiedliche Spalten-namen verwendet haben.

Beziehungsanalysen graphisch

Orphans und Childless

• Die Artikelstruktur ist fehlerhaft• Es gibt Artikelgruppen , die nicht unter einer

Sparte hängen

• Es gibt Produkte, die nicht Artikel_Gruppen zugeordnet sind

--- Orphans ---------------------------select count(artikelgruppennr) Anz_Orphans, artikelgruppennr Wert_Orphans from produkte_stamm where artikelgruppennr not in (select artikelgruppennr from Artikel_gruppe) group by artikelgruppennr ;


--- Childless ---------------------------select distinct artikelgruppennr from Artikel_gruppe MINUSselect distinct g.artikelgruppennr from Artikel_gruppe g, produkte_stamm p where g.artikelgruppennr = p.artikelgruppennr;


--- Orphans ---------------------------select count(artikelgruppennr) Anz_Orphans, artikelgruppennr Wert_Orphans from produkte_stamm where artikelgruppennr not in (select artikelgruppennr from Artikel_gruppe) group by artikelgruppennr ;


--- Childless ---------------------------select distinct artikelgruppennr from Artikel_gruppe MINUSselect distinct g.artikelgruppennr from Artikel_gruppe g, produkte_stamm p where g.artikelgruppennr = p.artikelgruppennr;


Analyse von Hierarchien

• Gerade BI-Werkzeuge haben Drill-Pfade, 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 Service-Leistungen.

• Sie sind nachträglich in das Angebot hinzu gekommen und man hat die Pflege der Stammdaten vernachlässigt

Produkte_Stamm

Artikel_Gruppe

Artikelsparte ARTIKELSPARTENNR1 , 2 ,3

ARTIKELSPARTENNR1,4,3ARTIKELGRUPPENNR1,2,3,4,5,6,11,10,9,8,7

ARTIKELGRUPPENNR100,1,6,2,5,4,7,3,10

Umsätze können nicht festgestellt werden

?

Abfrage über die HierarchieArtikelgruppe -> Produkte_Stamm -> Best_Position

Abfrage über die HierarchieProdukte_Stamm -> Best_Position

Position

Produkte

Gruppen

Sparten

Kunden-Stamm

Bestellung

Falsche Statuskennzeichnungvon Finanzprodukten

Orphans

Fehlerhafte Spartenkennzeichnungvon Gruppen

Doppelte Wertebelegung von Statuskennzeichnung für Privat- und Firmenkunden.

Fehlerhafte Verschlüsselungvon Artikel- und Produkten

DoppelteProduktnummern

Fehlerhafte , nicht rechenbareEinzelpreisbezeichnung

Korrekte Werte für:Umsatz pro Sparte?Umsatz pro Gruppe?Umsatz pro Produkt?Werden korrekte

Rechnungen gestellt?Umsatz pro Kunde?Macht die Kundenkarte

Sinn?

Korrekte Business Intelligence Auswertungen?

Wer hat Recht Controlling oder Buchhaltung?

Zahlen: Controlling Zahlen: Buchhaltung

Data Quality Management Process

Data Profiling

DatenkorrekturData QualityAuditing

Data QualityReporting

DatenqualitätsreportingVerarbeitete Sätze pro Berichtzeitraum(Anzahl Positionen pro Bestellungen)

Füllstandsanzeige einzelner Werte

Documents

Datenqualität sichern Wenn sich Controlling und Buchhaltung streiten Praxisseminar zu Datenqualitätsanalysen mit der Service GmbH als Fallbeispiel