Generiertes OWB Staging inklusive Data Quality Checks - DOAG

Carsten Herbemetafinanz - Informationssysteme GmbH

Generiertes OWB Staginginklusive Data Quality Checks

Generiertes OWB Staging inklusive Data Quality Checks Seite 2November 2011©

Wir fokussieren mit unseren Services die Herausforderungen des Marktes und verbinden Mensch und IT

Facing the futureDie Virtualisierung und Digitalisierung der Welt verändert Wertschöpfungsketten und generiert neue Geschäftsmodelle. Ganzheitliche Strategien, das Zusammenspiel zwischen Business und IT sind dabei der Schlüssel zum wertorientierten Wachstumskurs. metafinanz gehört seit über 20 Jahren zu den erfahrensten Software-und Beratungshäusern am Markt. Wir entwickeln intelligente zukunftsorientierte Lösungen für komplexe Herausforderungen.

Wir sind Microsoft Solution Provider sowie Oracle-, Informatica- und SAS-Partner.

Die metafinanz ist ein Unternehmen der Allianz Gruppe.

Data Warehousing

Schulungen

Reporting� Customer Intelligence � Controlling

� BI-Portale

� DWH-Architekturen & -Systeme

� Datenqualität� Oracel Warehouse Builder

� OWB-Schulungen � Oracle DWH-Schulungen

Customer & Channel Mgmt.Risk, Compliance & Finance Mgmt.

Insurance OperationsSupplier Management

IT TransformationIT Innovations


Einführung - Generiertes OWB Staging inklusive Data Quality Checks

Anforderung� Einheitliches Vorgehen für alle Schnittstellen � Identifizierung von Datensätzen die korrigiert werden müssen� Einfache Korrekturmöglichkeit für Fachanwender

Idee� Zentrale Definition der Schnittstellen� Generierung der Mappings und Process Flows� Generische Applikation für Korrekturen

Nutzen

� Keine wiederkehrende Tätigkeiten� Bessere Wartbarkeit: neues Generieren bei Änderungen� Einheitliches Vorgehen� Geringere Fehleranfälligkeit

Vortrag� Erfahrungsbericht: Best Practices und Benefits� Anleitung zur Generierung� Inspiration für eigene Generatoren


Agenda Generiertes OWB Staging inklusive Data Quality Checks

Staging Prozess

Data Quality Checks

Data Cleansing Application

1

3

2

Generierung4

Fazit5



Staging Prozess

Data Quality Checks


1

3

2

Generierung4

Fazit5


Der Staging Prozess umfasst die Extraktion der Daten aus den Quellen, Standardisierung, Überprüfung der Data Quality Rules und optionale Korrekturen.

Data Extraction� Extraktion aus Quellsystem oder Laden aus Datei� Laden in Staging Area� File Handling (Archivierung Daten, Log-Files etc.)

Data Quality Checks� Überprüfung der Datenqualität anhand definierter Regeln� Aussteuern von fehlerhaften Datensätzen

Data Cleansing� Automatisch: Standardisierung� Manuell: Korrektur von fehlerhaften Datensätzen

Aufgaben Staging & Cleansing Prozess


Im Staging Mapping werden die Daten aus der Quelle in die Zieldatenbank geholt. Dann werden sie im Cleansing Mapping geprüft und aufbereitet.

Übersicht Staging ProzessStaging Mapping MAP_STG_XXX

� Lesen aus Flat File oder aus Datenbank in Staging Tabelle

Cleansing Mapping MAP_CLS_XXX

� Lesen der Daten aus der Staging Tabelle

� Typ-Prüfung und -Konvertierung anhand Definition in INTERFACE_DEFINITION

� Code-Übersetzung mit Regeln aus CODE_MAP

� Überprüfung Wertebereich gegen Referenz in LIST_OF_VALUES

MAP_CLS_XXX

CLS_XXX

LIST_OF_VALUES

INTERFACE_DEFINITION

CODE_MAP

DQ_ERROR

ERR_XXX

STG_XXX

DB Link

Flat File

MAP_STG_XXX


Übersicht Cleansing Mapping

Cleansing Mapping Details

MAP_CLS_XXX

STG_XXX CLS_XXX

DQ_ERRORLIST_OF_VALUES

INTERFACE_DEFINITION

CODE_MAP

ERR_XXX


Cleansing Mapping (1/5): Lesen der Staging Daten, Mapping der Codes und Verknüpfung mit Fehlern (für Korrekturläufe bei denen nur fehlerhaft Datensätze verarbeitet werden).


Cleansing Mapping (2/5): Filtern der Delta Sätze, Schreiben in TMP_TEST und Aufruf der Data Quality Table Function.


Cleansing Mapping (3/5): Details zum Aufruf der Data Quality Table Function. Als erstes wird der REFCUR von Typ erstellt, dann der eigentliche Aufruf der Table Function.


Cleansing Mapping (4/5): Mit dem Construct Object Operator erstellen wir ein Binding zu dem REFCUR in dem Package PCK_DQ_xxx_TFN.

Construct Object Operator


Cleansing Mapping (5/5): Die Table Function liefert zwei „Typen“ von Datensätzen: einzelne Attribut-Fehler und den geprüften und konvertierten Staging-Datensatz. Diese laufen in die entsprechenden Tabellen.



Staging Prozess

Data Quality Checks


1

3

2

Generierung4

Fazit5


Cleansing Mapping (3/5): Details zum Aufruf der Data Quality Table Function. Als erstes wird der REFCUR von Typ erstellt, dann der eigentliche Aufruf der Table Function.


Die Table-Function liefert AUDIT-Datensätze und DATA-Datensätze für die Cleansing-Tabellen CLS_xxx und Error-Tabellen ERR_xxx und DQ_ERROR

Ausgabe der Table-Function

Pro In-Datensatz (RECORD_ID) werden im Fehlerfall mehrere Out-Datensätze erzeugt




Es gibt zwei Arten von Datensätzen: AUDIT-Data und der (standartisierte) eigentliche Datensatz. Unterschieden werden die Datensätze anhand von AUDIT_IS_DATA




Pro fehlerhaften Attribut wird ein AUDIT-Datensatz erzeugt. Er enthält Name des Attributes, seinen Wert und den Fehlertext. Die DATA-Felder sind nicht gefüllt.




Der DATA Datensatz enthält zusätzlich zu den Daten die Fehlertexte aller seiner Attribute.


Für jedes Interface wird ein DQ Package generiert. Es definiert einen REF CURSOR mit allen Attributen des Interfaces und bietet eine Table Function zum Cleansing.

Package PCK_DQ_xxx_TFN


Für die Ausgabe der Data Quality Table Function sind Objekt und PL/SQL Table zu definieren.

CREATE OR REPLACE TYPE OBJ_OUT_DQ_TEST AS OBJECT (

RET_RECORD_ID NUMBER,

DQ_AUDIT_ID NUMBER,

DQ_AUDIT_DATE DATE,

DQ_DELIVERY_ENTITY VARCHAR2(50),

DQ_DELIVERY_DATE DATE,

DQ_INTERFACE VARCHAR2(30),

DQ_ATTRIBUTE VARCHAR2(25),

DQ_ERROR VARCHAR2(4000),

DQ_VALUE VARCHAR2(4000),

DQ_RECORD_KEY VARCHAR2(4000),

DQ_RECORD_ID NUMBER,

RET_TESTKEY VARCHAR2(4000),

RET_NUMBER1 NUMBER,

-- alle weiteren Attribute folgen

AUDIT_ERROR_TEXT VARCHAR2(4000),

AUDIT_IS_DATA VARCHAR2(1)

);

/

CREATE OR REPLACE TYPE TAB_OUT_DQ_TEST AS TABLE OF OBJ_OUT_DQ_TEST;

/


Das Kernstück das generierten Data Quality Packages ist die Table Function TFN_CHECK_xxx. In ihr wird jedes Attribut jedes Datensatzes geprüft.

FUNCTION tfn_check_test(p_row pck_dq_test_tfn.refcur_in_dq_test) RETURN

tab_out_dq_test

PIPELINED IS

l_error VARCHAR2(4000);

l_attr_error VARCHAR2(4000);

l_out_rec obj_out_dq_test := obj_out_dq_test(NULL, ...);

l_in_rec type_in_dq_test;

BEGIN

LOOP

FETCH p_row

INTO l_in_rec;

EXIT WHEN p_row%NOTFOUND;

l_out_rec := obj_out_dq_test(NULL,

.. .);

l_error := NULL;

l_out_rec.audit_is_data := 'N';

l_out_rec.dq_interface := 'TEST';

l_out_rec.dq_record_key := l_in_rec.src_testkey;

l_out_rec.dq_record_id := l_in_rec.src_record_id;

l_out_rec.ret_record_id := l_in_rec.src_record_id;

l_out_rec.dq_attribute := 'TESTKEY';

l_out_rec.dq_value := l_in_rec.src_testkey;


Pro Attribute können mehrere Checks ausgeführt werden. Liefert einer oder mehrere einen Fehler, so wird ein Einzel-Fehlersatz für die Tabelle DQ_ERRORS ausgegeben.

-- pro Attribut

l_attr_error := NULL;

l_attr_error := l_attr_error ||

pck_dq.fun_check_not_null_character('TESTKEY',

l_in_rec.src_testkey);

l_attr_error := l_attr_error ||

pck_dq.fun_check_type_character('TESTKEY',

l_in_rec.src_testkey,

30, 'Y');

IF l_attr_error IS NOT NULL THEN

l_out_rec.dq_error := l_attr_error;

PIPE ROW(l_out_rec);

l_error := l_error || l_attr_error;

END IF;

-- alle weiteren Attribute folgen


Am Ende wird pro gelesen Datensatz ein Datensatz wieder ausgegeben. Er enthält die konvertierten Werte sowie optional alle gesammelten Fehler der einzelnen Attribute.

l_out_rec.dq_attribute := NULL;

l_out_rec.dq_value := NULL;

l_out_rec.dq_error := NULL;

l_out_rec.audit_is_data := 'Y';

l_out_rec.audit_error_text := l_error;

l_out_rec.ret_testkey := l_in_rec.src_testkey;

l_out_rec.ret_number1 := l_in_rec.src_number1;

-- alle weiteren Attribute werden gefüllt

PIPE ROW(l_out_rec);

END LOOP;

CLOSE p_row;

RETURN;

END tfn_check_test;


Der Aufruf der generische Data Quality Checks wird aus der Interface Definition generiert. Dafür stehen Funktionen in dem Framework Package PCK_DQ bereit.

Generische Data Quality Checks


Beispiel eines generischen Checks zum Prüfen, ob ein NUMBER Attribut der geforderdenPRECISION und SCALE entspricht.

Beispiel (1/2): FUN_CHECK_TYPE_NUMBER


Beispiel eines generischen Checks zum Prüfen, ob das Attribut dem definierten Wertebereich (List-of-Values) entspricht.

Beispiel (2/2) FUN_CHECK_LOV


Fachliche Data Quality Checks können nicht generisch erzeugt werden, sie werden manuell in den generierten Code eingefügt.

Fachliche Data Quality Checks� Implementierung direkt in der Table-Function TFN_CHECK_xxx

� Eigentliche Logik sollte in einem extra Package gekapselt werden, damit diese bei einer Neu-Generierung nicht aus Versehen überschrieben werden kann

l_attr_error := l_attr_error || my_pck.fun_my_check('MY_ATTRIBUTE', ...);



Staging Prozess

Data Quality Checks


1

3

2

Generierung4

Fazit5


Auf der Überblicksseite werden alle fehlerhaften Datensätze in komprimierter Form dargestellt.

Data Cleansing Application: APEX GUI: Overview


In der Cleansing-Maske können die Werte überschrieben werden. Per Trigger wird der Wert des entsprechenden Attributes in der Staging Tabelle korrigiert.

Data Cleansing Application: APEX GUI: Cleansing


Der Trigger auf der Error-Tabelle DQ_ERROR ruft die Funktion PRO_CORRECT_STG_ATTRIBUTE in dem Package PCK_DQ auf.

Data Cleansing Application: Funktionsweise


In der Funktion PCK_DQ.PRO_CORRECT_STG_ATTRIBUTE wird ein dynamisches SQL zur Korrektur der Daten in der Staging Tabelle erzeugt.

Data Cleansing Application: Funktionsweise



Staging Prozess

Data Quality Checks


1

3

2

Generierung4

Fazit5


Für die Generierung wie auch zur Laufzeit wird eine minimale Infrastruktur benötigt. Die Logik ist in dem Package PCK_OWB_GENERATOR, die Definition in der Tabelle INTERFACE_DEFINITION.

Infrastruktur für die Generierung


Die Schnittstelle wird definiert, PL/SQL-Code wird generiert und in den OWB importiert. Im OWB werden weitere Objekte per OMB*Plus generiert und schließlich deployt.

Generierung OWB Objekte

Generierung Datenbankobjekte

Definition Schnittstellen

Deployment der generierten Objekte

Import von Datenbankobjekten

PL/SQL package PCK_DQ_xxx

OWB table CLS_XXX

OWB table ERR_XXX

OWB table STG_XXX

OWB mapping MAP_CLS_XXX

Generierte Objekte (DB & OWB)Generierungsschritte

1

2

3

4

5

OWB table TMP_XXX

Generierung Schritt-für-Schritt

Types [OBJ,TAB]_OUT_DQ_TEST


Als erstes ist in der Tabelle MAINT_INTERFACE die Schnittstelle anzulegen. Dies kann einfach per APEX Anwendung geschehen.

Anlegen von Schnittstellen

1 Definition Schnittstellen


In der Tabelle MAIN_INTERFACE_DEFINTION werden alle Schnittstellen auf Attribut-Ebene definiert.

1

Jedes Attribut der Schnittstelle muss definiert werden

� INTERFACE: Name der Schnittstelle

� ATTRIBUTE: Name des Attributes

� DATA_TYPE: [CHARACTER|NUMERIC|DATE|TIMESTAMP]

� LENGTH: Länge des Datentyps CHARACTER

� PRECISION: Precision des Datentyps NUMERIC

� SCALE: Scale des Datentyps NUMERIC

� THRESHOLD: Prozentsatz ein numerischer Wert darf sich im Vergleich zur letzten Lieferung ändern

� FORMAT: Zahlen- bzw. Datumsformat (nur für Konvertierung). Hat keine Auswirkung wenn die Staging Tabelle per DB-Link geladen wird.

� LOV_DOMAIN: Domain einer List-of-Values welcher die gültigen Wertausprägungen für dieses Attribut enthält.

� NOT_NULL: NULL-Wert erlaubt (N) oder nicht (Y).

� IS_KEY: Attribut ist (Teil) des Primary Keys (fachlicher Schlüssel)

� DESCRIPTION: Beschreibung (nur zur Dokumentation).



Die Daten in der Tabelle der Tabelle INTERFACE_DEFINITION sind Basis für die Generierung verwendet.

1

Daten in der Tabelle INTERFACE_DEFINITION



In der Tabelle CODE_MAPPING können mehrere Quell-Werte auf einen Zielwert gemapptwerden. Jedes dieser Mappings ist einer Domain zugeordnet.

1

Mapping von Codes in der Tabelle CODE_MAPPING



Die Tabelle LIST_OF_VALUE enthält erlaubte Werte für verschieden Domains.

Wertebereiche

1

Wertebereiche in der Tabelle LIST_OF_VALUE



Interface Generation Guide

2

select pck_owb_generator.fun_generate_plsql('DEMO') from dual;

� Als erstes ist mit dem Package PCK_OWB_GENERATOR der PL/SQL-Code zu generieren:

� Das Query liefert den Code für das Package PCK_DQ_xxx:

� Dieser Code ist per Copy&Paste im Target Schema auszuführen

CREATE OR REPLACE PACKAGE PCK_DQ_DEMO AS

FUNCTION FUN_CHECK_DEMO (

[...]

END PCK_DQ_DEMO;

/

CREATE OR REPLACE PACKAGE body PCK_DQ_DEMO AS

FUNCTION FUN_CHECK_DEMO (

[...]

END PCK_DQ_DEMO;

/

Generierung Datenbankobjekte



3 Import von Datenbankobjekten



4

select pck_owb_generator.fun_generate_omb('DEMO') from dual;

� Als erstes ist mit dem Package PCK_OWB_GENERATOR der OMB-Code zu generieren:

� Das Query liefert einen CLOB mit dem OMB-Code

� Dieser Code ist per Copy&Paste im OMB*Plus Fenster des OWB Design Centers auszuführen

OMBCC '/DWH_DEMO/STG_DEMO'

# drop and create mapping

catch {OMBDROP MAPPING 'MAP_CLS_DEMO'}

OMBCREATE MAPPING 'MAP_CLS_DEMO'

OMBALTER MAPPING 'MAP_CLS_DEMO' SET PROPERTIES (GENERATION_LANGUAGE)

VALUES ('PLSQL')

[...]

Generierung OWB Objekte


Das Mapping kann per Parameter auf zwei Arten ausgeführt werden: zur Verarbeitung aller Daten in der Staging Tabelle oder als Delta mit fehlerhaften Datensätzen.

declare

l_status varchar2(4000);

begin

l_status := pck_dq.fun_run_cleansing(

PS_INTERFACE => 'TEST',

ps_delivery_entity=>'demo OE',

pd_delivery_date => to_date('24.05.2011', 'dd.mm.yyyy'),

ps_delta => 'N'); -- oder Y

dbms_output.put_line(l_status);

commit;

end;

/


Durch das Deployment werden in dem Zielschema die Tabellen und das Mapping angelegt.

5

Generierte OWB Objekte

Deployment der generierten Objekte



Staging Prozess

Data Quality Checks


1

3

2

Generierung4

Fazit5


Bei vielen Schnittstellen (bzw. Änderungen) lohnt sich der Aufwand. Die Definition per APEX ist wie die Generierung komfortabel.

Fazit

Aufwand

� Implementierung Framework (bzw. Einbindung vorhandenes Metadaten-Repository)

� Implementierung Generator� Erstellung APEX-Anwendung

Handhabung� Einmalige Definition

� Schnittstellen, Code-Maps, List-of-Values� Generierung überwiegend automatisiert

Nutzen

� Zentrale Definition der Schnittstellen � Zeitersparnis bei vielen Schnittstellen/Änderungen� Einheitliches Vorgehen & Geringere Fehleranfälligkeit� Einfache Neugenerierung bei (funktionaler) Prozesserweiterung


Data Quality ist leicht zu implementieren. Die Herausforderungen liegen in dem Gesamt-Prozess mit seinen fachlichen und organisatorischen Anforderungen.

Vor der IT-Umsetzung …

Data Quality Prozess

� Definition des Data Quality Prozess� Beteiligte: IT, Fachbereich, Compliance, Datenlieferanten

Fachlich

� Welche Daten können überhaupt korrigiert werden?� Welche Daten sollten nur markiert werden?� Dürfen Teil-Lieferungen ohne fehlerhafte Daten

weiterverarbeitet werden?� …

Organisatorisch

� Zuständigkeit: Wer korrigiert die Daten?� (Bis) Wann müssen die Daten korrigiert werden?� Closed-Loop: Wie werden die Daten im Quellsystem korrigiert?� …

Herzlichen Dank!

metafinanz Informationssysteme GmbHLeopoldstr. 146Phone: +49 89 360531-0Fax: +49 89 350531-5015Email: [email protected]

Documents

Generiertes OWB Staging inklusive Data Quality Checks - DOAG