Keep it Simple_Stahnke_PPI
Werbung
Keep it Simple ein rein PL/SQL basiertes Framework zur DWH Beladung Jörg Stahnke 10. Oracle Datawarehouse Konferenz Agenda Ausgangssituation Ziel grundsätzliche Architektur technische Umsetzung Aufgabentrennung fachliche Vorgaben technisches Framework Vorteile Einsatzempfehlung Todo‘s beim Einsatz Anlagen Detailfolien © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 1 Ausgangssituation 1/2 Frameworkeinsatz ist besonders geeignet wenn Sie: ein „kleines“ DWH Projekt haben Team besteht nur aus wenigen Personen DWH / Reporting ist evtl. nur ein kleines Teilprojekt eines größeren Vorhabens sehr schnell auf veränderte / ergänzte fachliche Anforderungen reagieren müssen unter Kostendruck stehen aufgrund des kleinen Teams nicht die Möglichkeit haben, technisches Spezial KnowHow in verschiedenen Gebieten aufzubauen Datenbank ETL Tools Mitarbeiter haben, die über gute fachliche Kenntnisse und Basiskenntnisse in SQL verfügen © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 2 Ausgangssituation 2/2 Voraussetzung für den Frameworkeinsatz ist: die Komplexität der Berechnungen ist gering bis mittel z.B. Transformation relationales Modell in ein Starschema z.B. Zusammenführen und Plausibilisieren mehrerer Liefersysteme Berechnungen können mit Hilfe der Grundrechenarten / SQL -Funktionen erfolgen sehr komplexe Berechnungen (z.B. finanzmathematische Bewertungen von Derivaten) sind nicht enthalten © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 3 Ziel vollständige Trennung fachlicher Vorgaben und technisches Framework technisches Framework braucht (fast) nie angepasst werden die laufende Entwicklung besteht nur aus der Anpassung der fachlichen Vorgaben Entwickler konzentrieren sich nur auf fachliche Fragen haben keinen technischen „Overhead“ das technische Framework übernimmt alle technischen Aufgabenstellungen Steuerung der Beladung einzelner Tabellen in der korrekten Reihenfolge Fehlerhandling optimierte Performance Protokollierung Statushandling Historisierung © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 4 Grundsätzliche Architektur Technik Fachlichkeit Quelldaten liest verwendet technisches Framework fachliche Definition schreibt Ziel Datenmodell © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 5 Technische Umsetzung Schema Quelldaten enthält Quelldaten unverändert verwendet Views als fachliche Definition PL/SQL Package als technisches Framework lesen DB-User Transformation lesen/schreiben Schema Reporting Schema Error Reporting Schema enthält verarbeitete Daten Error Schema enthält fehlerhafte Sätze mit fachlichen Fehlertexten lesen DB-User Reporting © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz verwendet Views zum Zugriff auf Daten im Rahmen des Reporting 6 Aufgabentrennung - Fachlichkeit - Technik Ablauf ohne Framework Steuerung anpassen Dokumentation anpassen Datenmodell Datenfluss Entwicklertest inhaltlich Status-/Fehlerhandling/Performance u.a. Performanceoptimierung Software ändern inhaltlich Status-/Fehlerhandling u.a. Datenbank anpassen Analyse der Fachanforderung Entwickler © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 7 Aufgabentrennung - Fachlichkeit - Technik Ablauf mit Framework Framework Entwickler Steuerung anpassen Performanceoptimierung Dokumentation anpassen Datenmodell Datenfluss Entwicklertest = Abfrage View Entwicklertest View inhaltlich ändern Status-/Fehlerhandling/Performance Datenbank anpassen Status-/Fehlerhandling u.a. Datenbank anpassen © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz Analyse der Fachanforderung 8 Aufgabentrennung - Fachlichkeit - Beispiel Die Views haben typischerweise folgenden Code: CREATE FORCE VIEW TRANSFORMATIONUSER.verbund_konto as select a.Verbundnummer ,a.SNPVom as SNP_Vom ,a.rzmandant as mandant ,a.Kontonummer ,a.Waehrung ,a.Saldo ,case when a.saldo>0 then 0 else (-1)*a.saldo end as GESAMTRISIKO ,k.kurs from QUELLSCHEMA.fmverbundkonto a join REPORT_SCHEMA.cog_kurs_euro k on k.mandant =a.rzmandant and k.stichtag=&&Aktstichtag and k.waehrung=a.waehrung © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz View inhaltlich ändern Felder aus Quellschema übernehmen einfache Berechnung redundantes Feld aus Kurstabelle Zugriff auf Quellschema Zugriff auf Reportingschema Verwendung eines Parameters 9 Vorteile 1/2 fachliche Vorteile fachliche Änderungen können sehr schnell umgesetzt werden Entwicklungsaufwand zur Ergänzung von wenigen Feldern unter 1 PT geringe Fehleranfälligkeit, da Statushandling, Steuerung, Logging usw. immer korrekt sind weniger Testaufwand Logiken können durch Abfrage von Views sofort getestet werden fachlich orientiertes Fehlerhandling regelmäßiges Feedback über Fehler an Liefersystem möglich Korrektur von Fehlern an der Quelle © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 10 Vorteile 2/2 technische Vorteile maximal mögliche Parallelisierung bei der Beladung nutzt im Oracle Cluster alle Knoten serielle Abarbeitung nur, wenn dies fachlich zwingend erforderlich ist sehr gute Performance durch konsequente Massenverarbeitung einheitliche technische Architektur für alle Beladungen Framework belädt alle Tabellen mit gleichem technischen Vorgehen Protokollierung, Statushandling, Fehlerhandling u.a. ohne Ausnahme immer gleich Budgetvorteile spart Kosten kostenloses Basic Komprimierung geringer Implementierungs- und Testaufwand weniger Fehler © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 11 Einsatzempfehlung typische Einsatzszenarien sind z. B. Erstellen Reporting auf einem vorhandenen Datenmodell ein relationales Datenmodell ist bereits vorhanden auf diesem Datenmodell soll ein Reporting erstellt werden für ein optimales Reporting soll das vorhandene Datenmodell in ein Starschema transformiert werden Ergänzen einer Historisierung ein Datenmodell ist bereits vorhanden, enthält aber immer nur aktuell gültige Daten für Revisions- oder Analysezwecke sollen die Daten historisiert aufbewahrt und ausgewertet werden Zusammenführung verschiedener Liefersysteme Daten aus verschiedenen Systemen werden in einer Datenbank zusammengeführt die Daten werden auf Inkonsistenzen geprüft Konsistenzfehler werden an Liefersysteme gemeldet und dort korrigiert liefersystemübergreifende Auswertungen sind möglich © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 12 Todo‘s beim Einsatz einmalige Aufwände Übernahme des bei PPI vorhandenen Quellcodes Absprache der konkreten technischen Anforderungen des Projektes evtl. Anpassung des technischen Frameworks an geänderte technischen Anforderungen Ausführung der Installationsskripte Test des Frameworks mit Dummy-Logiken Übergabe der Installationsskripte an fachliches orientiertes Entwicklungsteam laufende Projektarbeit Einsatz des Frameworks im Projekt Änderungen am Framework nicht mehr notwendig nur noch Entwicklung fachlicher Logiken (Views) und Ergänzung des fachlichen Datenmodells © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 13 Kontakt Jörg Stahnke Dipl. Mathematiker | Principal Database Engineer PPI Aktiengesellschaft Moorfuhrtweg 13 22301 Hamburg © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz Telefon: +49 40 227433-1329 Mobil: +49 1603841771 [email protected] 14 Anlagen Die folgenden Folien sind nicht zur Präsentation während des Vortrages vorgesehen. Bei Interesse können hier Details zu einzelnen Features des Framework nachgelesen werden. © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 15 Technische Umsetzung 1/2 Es werden mehrere Schemata / DB-User genutzt DB-Schema Quelldaten enthält Daten der Quellsysteme ohne Änderung wird per SQL-Loader / Datapump gefüllt bzw. ist ein DB Link auf Quellsysteme DB-User Transformation enthält ein PL/SQL Package, welches das technische Framework repräsentiert enthält für jede zu füllende Zieltabelle einen gleichnamigen View, welcher die Logik der Befüllung dieser Tabelle repräsentiert © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz Schema Quelldaten DB-User Transformation 16 Technische Umsetzung 2/2 DB-Schema Reporting enthält die zu füllenden Zieltabellen in einem für das Reporting optimiertem Datenmodell wird mit fehlerfrei angelieferten Daten gefüllt Schema Reporting DB-Schema Error enthält für jede zu füllenden Zieltabelle des Reporting Schemas eine strukturgleiche Fehlertabelle ergänzt um Felder zur Fehlerbeschreibung wird mit fehlerhaft angelieferten Daten inklusive fachlicher Fehlermeldung gefüllt DB-User Reporting hat Views auf DB Schemata Reporting und Error wird vom Reporting-Tool verwendet © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz Schema Error DB-User Reporting 17 Aufgabentrennung - Fachlichkeit 1/3 Es gibt eine klare Aufgabentrennung zwischen fachlichen Vorgaben und technischem Framework. Bei ergänzten / geänderten fachlichen Vorgaben ist Folgendes zu tun bei Bedarf Änderung des Datenmodells für Quelldaten bei Bedarf Änderung des Datenmodells für das Reporting Schema Änderung der Views Test der geänderten fachlichen Logiken durch Abfrage der Views evtl. Konfiguration der anzuzeigenden fachlichen Fehlertexte Diese Arbeiten erfordern folgende Kenntnisse: fachliche Kenntnisse der Datenmodelle fachliche Kenntnisse für die umzusetzenden Logiken Basiskenntnisse in SQL (Schreiben von Select-Statements) © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 18 Aufgabentrennung - Fachlichkeit 2/3 Features der Definition von fachlichen Logiken: fachliche Logiken werden ausschließlich durch Views definiert weitere Arbeiten sind zur Implementierung einer Logik nicht erforderlich Logiken sind durch Abfrage der Views sofort testbar Durch eine Abfrage der View kann der Entwickler sofort testen, ob die vorgenommenen Änderungen wie erwartet wirken. Eine erneute Beladung ist dazu nicht notwendig. Durch Abfrage der View für bestimmte „kritische“ Geschäfte können die Logiken für einzelne Geschäfte geprüft werden. © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 19 Aufgabentrennung - Fachlichkeit 3/3 Die Views dürfen folgende Objekte referenzieren: Tabellen im Quellschema Tabellen und Views im Reporting Schema durch Referenzen auf diese Tabellen/Views können einfache, mit einem einzigen SQL umsetzbare Logiken implementiert werden Packages des Transformationsusers durch Nutzung von pipelined Funktions, welche in den Views referenziert werden, können auch Logiken mit mittlerer Komplexität implementiert werden Systemkontexte Systemkontexte repräsentieren Parameter der jeweiligen Beladung und werden während der Beladung durch das Framework gesetzt zum Testen können Systemkontexte durch Logon-Trigger oder manuell gesetzt werden © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 20 Aufgabentrennung - technisches Framework 1/6 Initialaufwand Einmalig ist bei Einführung des technischen Frameworks Folgendes zu tun: Analyse der konkreten technischen Anforderungen evtl. Anpassung des PL/SQL Packages (z.B. Definition der verfügbaren Parameter) Tests aller technischen Funktionalitäten Diese Arbeiten erfordern folgende Kenntnisse: detaillierte PL/SQL Kenntnisse detaillierte Datenbankkenntnisse KEINE fachlichen Kenntnisse Nach der Erstellung des technischen Frameworks muss dieses nur angepasst werden, wenn sich die technischen Anforderungen (z. B. Art und Weise der Historisierung) ändern. Geänderte fachliche Anforderungen führen NICHT zu Änderungen am Framework. © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 21 Aufgabentrennung - technisches Framework 2/6 Aufgaben Framework Das technische Framework übernimmt folgende Aufgaben vollautomatisch ohne dass Anpassungen erforderlich sind: Analyse der fachlichen Abhängigkeiten der Views Analyse der Foreign Key Abhängigkeiten der Zieltabellen Festlegen der Reihenfolge der Tabellenbefüllung auf Basis der Abhängigkeiten Überprüfen aller Foreign Keys, Unique Keys und Not-Null Constraints soweit auf Basis der Abhängigkeiten möglich paralleler Start der Befüllung der Zieltabellen Statushandling, so dass der Reporting User auch während einer Beladung nur auf konsistente Daten zugreifen kann Protokollierung aller Vorgänge in Log-Dateien und Protokolltabellen © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 22 Aufgabentrennung - technisches Framework 3/6 weitere Aufgaben Framework Einfügen fehlerfreier Daten in Report Schema Einfügen fehlerhafter Daten in das Error Schema inklusive Kategorisierung der Fehler Folgefehler - z.B. Konto nicht verarbeitbar, weil zugehöriger Kunde nicht verarbeitbar war fachlicher Fehler mit zugeordneten fachlichen Fehlertexten z.B. - fehlerhafte Zuordnung einer nicht gültigen Kundennummer als Kontoinhaber im Liefersystem „Konto“ - doppelte Anlieferung der gleichen Kontonummer - fehlende Zuordnung einer Kontokategorie zum Konto (Zwangsfeld) unerwarteter Fehler - alle sonstigen Fehler - sind in der Regel ein Hinweis auf Fehler in den programmierten Logiken oder fehlende Konfigurationen für fachliche Fehler - sollten im Produktionsbetrieb nicht auftreten © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 23 Aufgabentrennung - technisches Framework 4/6 automatisierte Checks Das technische Framework führt folgende Checks automatisch durch: sind die Views, welche die fachlichen Logiken repräsentieren, tatsächlich strukturgleich zur Zieltabelle (Feldnamen und -formate) ? werden festgelegte technische Eigenschaften der Zieltabellen eingehalten? Partitionierung Komprimierung Indizierung von Foreign Keys u.a. gibt es fehlerhafte zyklische Abhängigkeiten? Durch diese automatisierten Checks werden versehentliche Fehler im Design des Datenmodells und bei der Definition der Logiken vermieden. © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 24 Aufgabentrennung - technisches Framework 5/6 automatisierte Anpassungen Das technische Framework führt folgende Anpassungen automatisch durch: bei Änderungen des Datenmodells im Reporting Schema wird das Error Schema automatisch analog angepasst weitgehende automatisierte Dokumentation des Datenflusses durch Auswertung der Abhängigkeiten und des Quellcodes der Views Vergabe notwendiger Rechte Anpassung der Views für den DB User Reporting Durch diese automatisierten Anpassungen wird der Entwicklungsaufwand verringert. Datenmodelländerungen / Logikänderungen werden nur einmal kodiert und notwendige redundante Folgearbeiten erfolgen automatisch. © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 25 Aufgabentrennung - technisches Framework 6/6 sonstige Features weitere Features des technischen Frameworks: optimale Performance durch Parallelverarbeitung und konsequente Nutzung massenorientiertes SQL nur massenorientierte „insert append“ / truncate Partition Befehle genutzt Nutzung Parallel Query (Lesezugriffe) und Parallel DML (Schreibvorgänge) parallele Befüllung mehrerer Tabellen mit Hilfe von dbms_schedule soweit keine fachlichen Abhängigkeiten dies verhindern optimales Partitionpruning Nutzung der kostenlosen Basic Komprimierung, da alle Randbedingungen zur Nutzung dieser Option eingehalten werden automatische Administration der Partitionierung Restartfähigkeit bei Abbrüchen schnelles Housekeeping durch truncate Partition Befehle Übergabe der Parameter von Beladungen (z.B. Stichtag) an die Views mit Hilfe von Systemkontexten dadurch können diese Parameter von View an jeder Stelle des SQL referenziert werden (z.B. in join Klauseln) unterstützt Partitionpruning sehr effektiv © PPI AG 10.03.2015 10.Oracle Datawarehouse Konferenz 26
