Oracle Data Warehouse

Oracle Data Warehouse Konzepte und Methoden 1 Viele Wege führen nach Rom ... 5 Die Bedeutung des Data Warehouse in heutigen Unternehmen Vier wichtige Aspekte machen das Data Warehouse so erfolgreich Der Aspekt des „Unternehmensweiten“ Rolle und Eigenschaften haben sich permanten verändert Operationalsierung des Data Warehouse Aktuelle Herausforderungen von Data Warehouse Architekturen 1. Flexibilität bei der Informationsauswahl für die Endbenutzer 2. Bereitstellung Ressourcen, Kapazität und Performance für Endbenutzer 3. Agilität bei der Bereitstellung von neuen Informationen für die Endbenutzer 4. Kosten und Effizienz bei Aufbau und Betrieb der Systeme 6 6 7 7 8 9 9 9 9 9 Das Schichtenmodell Schichten-­‐ Modellierung im Data Warehouse: Effizienz durch Strukturierung von Informationen 3-­‐Schichten-­‐Modell historisch Die Mehrwert-­‐Leistungen des Data Warehouse Trotz datenmodellierung bleibt die Gefahr von Informationschaos Ziel die Erwartungen der Endbenutzer treffen 11 11 11 12 12 12 Die Schichten im Detail Die Data Mart Schicht (User View Layer) Data Marts sind redundant und „flüchtig“ Herleiten der multidimensionalen Modelle eines Data Marts Schritt 1 (Fachanwenderfragen und Analysemodell) Schritt 2 (Geschäftsobjekt-­‐Modell) Schritt 3 (Multidimensionale Struktur, Fakten und Dimensionen) Schritt 4 Hinzufügen von interessanten Referenzdaten (optional) Die Umsetzung des multidimensionalen Modells in eine physische Speicherung Das Star-­‐Schema Varianten des Star Schemas Degenerated Dimension Factless Fact Table Empfehlungen für den Aufbau von Faktentabellen Die Wahl der Granularität einer Fakten-­‐Tabellen beieinflusst die Flexibilität der Abfrage Auslagern von weniger häufig genutzt Attributen Umgang mit großen Faktentabellen Historisierung Schlüssel in Dimensionen Wahl der Schlüsselfelder Umschlüsselung Business Key Quellsysteme Umgang mit dem Aspekt des Operativen der Vorsysteme Umgang mit Datenqualitätsproblemen in den Vorsystemen Stage – Schicht (Data Integration Layer) Archivierung gelesener Sätze Generische Stage-­‐Strukturen Operational Data Store (ODS) Die (Kern-­‐) Data Warehouse – Schicht (Enterprise Information Layer) Anwender erwünscht! Keine Angst vor Security und schlechter Abfrage-­‐Performance Security-­‐Aspekte in der Data Warehouse Schicht (Enterprise Information Layer) Verhindern von Datenchaos: Management von Informationen in der Data Warehouse Schicht Klassifizierung von Informationen Hilfsmittel: Dokumenation der Informationen 14 14 14 14 15 16 16 17 17 17 18 19 19 19 20 20 21 21 22 22 22 22 22 23 23 24 24 24 25 25 26 26 27 28 28 Organisation der Daten in Tabellen und Partitionen 30 2 Partitioning-­‐Struktur und Varianten Range Partitioning List Partitioning Hash Partitioning Sub-­‐Partitioning Partition Wise Join Reference -­‐ Partitioning Interval Partitioning Verwaltung von Partitionen 31 31 31 32 32 33 34 35 36 Indizierung im Data Warehouse In welchen Situationen nutzt man im Data Warehouse Indexe bzw. Schlüssel? Index-­‐Varianten Btree-­‐Indexe Bitmap-­‐Indexe Platzbedarf Star-­‐Transformation Globale und lokale Indexe (Partitionieren von Indexen) Prüfen, ob ein Index benötigt wird oder nicht Wo wird wie indiziert (Gesamtsicht) Data Marts Data Warehouse-­‐Schicht (Enterprise Information Layer) Stage bzw. Integrations-­‐Schicht Typischer Umgang mit Indexen im DWH aus der Sicht der ETL-­‐Aufgabenstellung 37 37 37 37 37 38 40 41 41 42 42 42 43 43 Benutzer-­‐Sichten und Kennzahlensystemen (MViews) Einsatz von Materialized Views anstelle von Summentabellen Vorteile von Materialized Views im Data Warehouse – Kontext Bedienen unterschiedlicher Zielgruppe und Aufbau von Kennzahlensysteme Erstellungs-­‐ und Aktualisierungskonzepte Benutzerabfragen automatisch umlenken – Query Rewrite Hierarchische Struktur einer Dimension für das Rewrite nutzen Hilfsmittel „um Materialized Views zu verstehen“ 44 44 44 45 46 47 49 49 ETL – Extraktion, Transformation, Load Datennahe Transformationen für Daten-­‐orientiertes Data Warehouse Daten oder Prozesse? Technische Mittel in der Datenbank Generieren statt Programmieren Die ETL-­‐Aktivitäten und das Schichten-­‐Modell Phase 1 Kopieren Phase 2 Integrieren Phase 3 Informations-­‐Mehrwerte schaffen Phase 4 Sammeln Phase 5 Endbenutzersichten aufbauen Die Verteilung von Ladeaktivitäten im Schichten-­‐Modell Stichwort „1 zu 1 Kopien“ Wiederholbarkeit des Ladelaufs Deltadatenerkennung Altbacken, aber immer noch die Masse aller Input-­‐Daten für das Data Warehouse (External Tables) Schnelles Prüfen und Laden mit SQL-­‐Mitteln – Nutzen des „Mengen Paradigmas“ Mengen-­‐basiertes ETL Laden mit aktiven Constraints DML Error Logging – Eindeutigkeit prüfen Typ-­‐Format, Null-­‐ und Werte-­‐Prüfungen Umschlüsselung von Stamm-­‐ und Bewegungsdaten Einfache aber schnelle mengenbasierte SQL-­‐Hilfsmittel (Multiple Insert und Merge) Programmiertes Parallelisieren (Pipe-­‐Verfahren mit Table Functions) Schnelles Bewegen von großen Datenmengen innerhalb der Datenbank (PEL) Generieren statt Programmieren: Mit Graphik modellieren 51 51 51 51 52 52 53 53 54 54 54 54 55 56 57 57 59 59 59 60 61 62 62 63 64 3 Umgang mit Hardware-­‐Ressourcen im Data Warehouse Hardware wichtiger Bestandteil im Data Warehouse „Private“ Hardware: ungeschriebene Regel Die Hardware-­‐Konfiguration Speicherhierarchien Information Life Cycle Management (ILM) Plattenplatz für Tabellen optimieren und PCT_FREE Leseleistung messen Calibrate_IO Orion (Oracle IO Numbers) ASM (Automatic Storage Management) RAC (Real Applications Clusters) Kostengünstige Ausfallsicherheit Warehouse-­‐Prozesse gezielt zuteilen Das System überwachen Automatisiertes Schwellwert-­‐Monitoring AWR Report ADDM – Automatic Database Diagnostic Monitor Session-­‐Informationen abfragen Monitoring des Platzverbrauchs 65 65 65 65 66 67 67 68 68 68 69 70 70 70 70 70 71 72 73 73 Dokumentation Metadaten Repository zur Dokumentation der Inhalte und Zusammenhänge 75 75 Verwaltung von Data Warehouse-­‐Systemen Backup im Data Warehouse Stage / Integration Layer Data Mart Data Warehouse – Schicht und alle langlebigen Daten RMAN 77 77 77 77 77 77 4 Viele Wege führen nach Rom ...
...aber es gibt längere und kürzere Wege Data Warehouse Systeme gehören heute mit zu den wichtigsten IT-­‐Systemen eines Unternehmens. Sie haben sich etabliert und müssen ihren Nutzen nicht mehr unter Beweis stellen. Allerdings sind auch heute, nach fast 20 Jahren Data Warehouse-­‐Erfahrung, die meisten Systeme Firmen-­‐individuell konzipiert und erstellt, denn die Systeme gibt es auch heute noch nicht von der Stange als „Fertig-­‐Software“. Auf der anderen Seite gibt es kaum ein System, dessen Architektur so einfach zu standardisieren ist, wie die des Data Warehouse. Die vorliegende Darstellung zu dem Oracle – basierten Data Warehouse stellt die Methoden und Verfahren in den Vordergrund, mit denen man Data Warehouse Systeme auf der Basis einer Oracle Datenbank entwerfen und aufbauen kann. Es sind nicht die technischen Features der Datenbank, die erklärt werden sollen, sondern die Möglichkeiten und Einsatzkonzepte die zur Lösung der Aufgabenstellung „Data Warehouse“ führen. Ein Data Warehouse ist ein System zur Beschaffung, Aufbereitung und Bereitstellung von unternehmensweit anfallenden Informationen. Dieser Text orientiert sich an diesem Informationsbeschaffungsweg. Die ersten Kapitel legen ausführlich die Einteilung eines Data Warehouse Systems in modellierte Schichten dar. Das erleichtert die Positionierung der Datenbank-­‐Technologie in den folgenden Kapiteln, die so „ihren“ Platz in der Schichtenarchtektur findet. Der Text richtet sich an Architekten und Planer von Data Warehouse Systemen. Er ist sowohl geeignet um Data Warehouse Systeme von Grund auf neu zu modellieren oder auch um bestehende Systeme zu überprüfen. Es sind viele Punkte beschrieben, die dem Data Warehouse-­‐Praktiker bekannt vorkommen. In vielen Seminaren und Präsentationen haben hier näher beschriebene einzelne Aussagen allerdings für umfangreiche Diskussion geführt. Denn der Text stellt an mehreren Stellen gewohnte Verfahren in Frage. Mit Recht: Data Warehouse-­‐Systeme muss man heute mit anderen Rahmenparametern betrachten, als dies noch in den 1990er Jahren der Fall war. Die Oracle Datenbank ist sicher die am häufigsten verwendete Datenbank für Data Warehouse Systeme. Das liegt auch daran, dass sie sich sowohl für OLTP als auch für Data Warehouse Systeme sehr gut eignet und man diese Datenbank ohne Technikbruch als ein (!) zusammenhängendes Medium für beide Anforderungen nutzen kann. Leider führt das oft zu einem pauschalen Datenbank-­‐Einsatz ohne die spezifischen Anforderungen der Data Warehouse-­‐Prozesse zu berücksichtigen. Data Warehouse-­‐Anforderungen sind andere als OLTP-­‐
Anforderungen. Nicht jedes Feature der Oracle-­‐Datenbank macht auch in dem Data Warehouse Sinn und umgekehrt gibt es Funktionen, die besonders in Data Warehouse-­‐Systemen Nutzen liefern. Ziel dieses Textes ist es deshalb auch, eine Data Warehouse spezifische Sicht zu vermitteln, damit die Oracle Datenbank so optimal wie möglich die Anforderungen in einem Data Warehouse unterstützen kann. Um den Umfang zu beschränken, ist auf die breite Darstellung von Skripten oder umfangreicher Kommando-­‐Syntax verzichtet worden. Hierzu sollte man die Oracle Dokumentation nutzen. Eine Sammlung mit den wichtigsten Hilfen (auch für Einsteiger) und lauffähigen Beispielen zu den hier besprochenen Themen kann zusätzlich von dem Autor bezogen werden. Diese Skript-­‐Sammlung ist eine Kurzreferenz, um immer wiederkehrende Kommandos ohne langwierige Suche in der Dokumentation oder Internet an einer Stelle kompakt 1
zur Hand zu haben . 1
Diese Kurzreferenz sollte man während des Lesens zur Hand haben. 5 Die Bedeutung des Data Warehouse in heutigen Unternehmen
Vier wichtige Aspekte machen das Data Warehouse so erfolgreich Data Warehouse Systeme gehören heute zu den erfolgreichsten Anwendungen in modernen Unternehmen. Die Sinnhaftigkeit muss nicht mehr argumentiert werden. Während Business Intelligence Anwendungen oft nur für einzelne Fragestellungen stehen, hat das Data Warehouse System schon fast die Rolle eines zentralen „Information-­‐Pools“ übernommen, in dem man alles ablegt, was abseits der konkreten operativen Prozesse, abteilungsübergreifend relevant sein kann. Der Erfolg der Systeme ist auch durch „Mund-­‐zu-­‐Mund-­‐Propaganda“ entstanden: „Es gibt da was an zentraler Stelle, da findest Du alles was Du brauchst“. „Da sind Daten in leicht zugänglicher Form, die findest Du so nicht in Deinem Online-­‐System“. Und dann immer wieder: „Liebes DWH-­‐Team, könnt Ihr nicht auch noch Daten der Systeme X, Y, Z mit in das Warehouse aufnehmen?“. Dann ist wieder passiert, was so typisch für gut funktionierende Data Warehouse Systeme ist: Ein Verfahren nach dem andern, wird an das Data Warehouse – System angeschlossen. Und je mehr Systeme angeschlossen sind, umso mehr vermehren sich die Nutzen-­‐Effekte des zentralen Data Warehouse. Um die historisch gewachsenen Einflusssphären eines Data Warehouse-­‐Systems festzustellen, müsste man es einfach mal abschalten. Wie lange wird es dauern, bis die ersten Beschwerden kommen? Wer beschwert sich? Wie viel Druck entsteht, bis das System wieder 2
läuft? Für diese Erfolgsgeschichte sind 4 Gründe anzuführen, die sich seit Bestehen der Data Warehouse-­‐Idee in den 1990er Jahren nicht verändert haben: 1.
2.
Der erste ist der zentrale Charakter. Ein Data Warehouse ist meist das einzige System, in dem man unternehmensweit an einer einzigen Stelle eine umfassende und zusammenhängende Information erhält. Viele OLTP-­‐Systeme beschäftigen sich dagegen nur mit einzelnen Sachgebieten und Fragenstellungen. Das Data Warehouse stellt jedoch übergreifende Querverbindungen zwischen den Systemen dar. Der zweite wichtige Vorzug des Data Warehouse Systems ist die verständliche Darstellung von Informationen. Während sachbezogene Anwendungen ihre Informationen nur in ihrem speziellen Kontext verwalten, überführt ein Data Warehouse die spezifische Terminologie der Fachanwendung in eine allgemein verständliche Form und Sprache und macht sie so auch für Sachgebietsfremde Mitarbeiter, z. B. aus anderen Abteilungen, verstehbar. Darüber hinaus harmonisiert ein Data Warehouse Begriffe, Formate, Betrachtungsebenen und Definitionen. Informationen aus unterschiedlichen Sachgebieten können somit zusammenhängend benutzt werden, ein einheitliches unternehmensweites Gesamtbild entsteht. D ie vier H a u p tziele d es D a ta W a reh o u se 3.
4.
Der dritte wichtige Aspekt von Data Warehouse Systemen ist das Vorhalten von historischen Daten. Operative Anwendungen verändern ihre Daten permanent. Die Planung und weitere Ausrichtung der Aktivitäten eines Unternehmens erfordern jedoch eine vergleichende Betrachtung aktueller und historischer Daten. Ein Data Warehouse „wirft nichts weg“. Man sieht, wie die wirtschaftliche Situation ein Jahr zuvor war und kann sie mit der jetzigen vergleichen. Nur wer die Vergangenheit kennt, kann die Gegenwart bewerten und Entscheidungen für die Zukunft treffen. Das vierte wichtige Merkmal von Data Warehouse Systemen ist die Separierung von Daten von ihrem operativen Kontext. Durch dieses „Weg-­‐Kopieren“ kann man Daten unabhängig von den operativen Anwendungen beliebig verändern und sie einer weiteren Verwendung zuführen. Operative Systeme bleiben dadurch unberührt. Für die Betrachtung und die Analyse der Daten wird bewusst ein zeitlicher Fixpunkt gesetzt. Man definiert damit einen zeitlichen Bezugspunkt zu dem man eine Aussage formuliert und den man in Relation zu anderen Bezugspunkten bringen kann. 2
Eine interessante Erfahrung wird es sein, wenn sich plötzlich Personenkreise melden, mit denen man nicht gerechnet hat, denn oft werden die Daten aus Data Warehouse-­‐Systemen an Stellen weiterverwendet, an denen man sie nicht vermutet hätte. Oft wird sogar das Top Management dabei sein. Denn auch dort arbeitet man mit Daten aus dem Data Warehouse, ohne dass man es weiss. 6 Viele aktuelle Diskussionen müssen sich an diesen vier Punkten orientierten. Ein Reporting, das direkt auf den operativen Systemen aufsetzt, ist kein Data Warehouse, weil keiner der vier Punkte berücksichtigt wurde. Die Diskussion um Real time-­‐Reporting macht vergessen, dass Mehrwerte oft nur durch einen harmonisierenden, anreichernden und Qualitäts-­‐sichernden ETL-­‐Prozess entstehen und das ist oft in „Real time“ nicht zu machen. Es nützt dabei auch nichts, wenn alle Daten in den Hauptspeicher geladen werden. Der Aspekt des „Unternehmensweiten“ Business Intelligence Lösungen werden meist mit Blick auf einzelne Sachgebiete oder Abteilungen betrachtet. Data Warehouse Systeme begründen sich dagegen über einen unternehmensweiten Wirkungsbereich. Hier liegen die wesentliche Aufgaben und Mehrwerte. Es macht diese Systeme zu einem idealen Monitoring-­‐Instrument für abteilungsübergreifende aber auch Zeiten-­‐übergreifende Vergleiche. Unternehmen müssen heute in immer kürzerer Zeit geänderte oder komplett neue Geschäftspraktiken anwenden, um erfolgreich zu sein. Diese permanenten Änderungen erfordern ein stabilisierendes Kontrollinstrument, um Fragen zu beantworten wie: Welche Bereiche/Produktlinien sind über lange Zeiträume profitabel? Haben sich neue Produktionsverfahren gegenüber früheren bewährt? Gibt es territoriale Veränderungen bei der Geschäftsentwicklung? Direkt betroffenen Fachmitarbeiter können oft nur schwer aus ihrem unmittelbaren Aktionsfeld heraus abstrahieren und räumlich oder zeitlich entfernte Sichten einnehmen. Hier bietet das Data Warehouse eine unternehmensweite, sachgebietsübergreifende und auch historische Datensammlung an, um die nötige Abstraktion von der direkten „Jetzt-­‐ und Hier-­‐Erfahrung“ zu ermöglichen. Ein D ata W arehouse w irkt als Schnittstelle zw ischen vielen U nternehm ensprozessen So lassen sich schneller Fehlentwicklungen erkennen. Ein frühzeitiges Gegensteuern ist möglich. Dies verdeutlicht, wie wichtig es ist, möglichst alle Geschäfts-­‐ (Erfolgs-­‐) relevanten Prozesse an ein Data Warehouse anzuschließen, d. h. Daten aus diesen Prozessen in das zentrale System zu überführen. Umso besser kann das System seine übergreifende Monitoring-­‐Funktion erfüllen. Ein zentrales, unternehmensweites Data Warehouse kann folgende zusätzliche Funktionen bzw. positive Effekte liefern: •
•
•
Unternehmensweit gemeinsame, standardisierte Informationen o
Glossare / Definitionen als Nachschlagewerk o
Standardisierte Begriffe und Hintergründe zu Kennzahlen o
Gleiches Verständnis über den Inhalt von Kennzahlen und Sachverhalten o
Gemeinsam genutzte Berichte Gleicher aktueller Kenntnisstand o
Beschleunigte und zeitgleiche Informationsweitergabe o
Minimierter Kommunikationsaufwand o
Verhindern von “Information Hiding” bestimmter Stellen, weil Informationen durch die Zentralität allen zur Verfügung stehen (Demokratisierung durch gleiche Informationen) Reduzierung des Aufwands für die Informationsbeschaffung o
Wiederverwendung von bereits an anderer Stelle erstellten Informationen o
Weniger Reibungsverluste bei abteilungsübergreifenden Prozessen Rolle und Eigenschaften haben sich permanten verändert Man spricht seit Anfang der 90er Jahre von Data Warehouse Systemen. Aufgrund der vorgenannten positven Eigenschaften finden die Systeme eine immer weit reichendere Verwendung. Dienten sie ursprünglich nur dem Reporting auf einer strategischen Ebene und füllten Monats-­‐/Quartalsberichte, so sind Warehouse -­‐ Systeme heute oft zusätzlich direkt mit operativen Anwendungen verzahnt. Viele Fachmitarbeiter, ausserhalb von Controlling oder Planungsstäben haben das verlockende Informationsangebot des Data Warehouse erkannt und wollen mit ihren Geschäftsprozessen „angedockt“ werden. Das führte zu neuen Datenbereichen und auch zu einer feineren Granularität der Daten, denn auf der operativen Ebene stehen meist konkrete Transaktionen bzw. Geschäftsvorfälle im Vordergrund, die auch in dem Data Warehouse auffindbar sein sollen. 7 D ie R o lle d es D a ta W a reh o u se ist h eu te u m fa ssen d er g ew o rd en Die Folgen sind kürzere Betrachtungszeiträume (der Zeitpunkt des Geschäftsvorfalls wird wichtig) und zwangsläufig ein massives Anwachsen der Datenmengen. Data Warehouse-­‐Systeme sind heute die am schnellsten wachsenden Anwendungen mit deutlich sichtbaren finanziellen Folgen in den IT-­‐Budges. Dies erklärt die z. T. angestrengte Suche von IT-­‐Verantwortlichen nach Alternativen für hochvolumigen Speicher und ressourcen-­‐effizienten Lösungen. Neben der Betrachtung von fachlichen Aspekten bei der Gestaltung und Modellierung von Warehouse-­‐Systemen spielt heute daher auch die Betrachtung von Hardware-­‐und Kosten-­‐Aspekten eine bedeutende Rolle. Operationalsierung des Data Warehouse Diese geänderte Verwendung kann man auch als Operationalisierung des Data Warehouse bezeichnen, in Ergänzung zu der ausschließlich dispositiven Aufgabenstellung in den Anfangsjahren. Also die Nutzung des ursprünglich für dispositive Zwecke entworfenen Systems für operative Aufgaben. B eisp iele fü r o p era tive V erw en d u n g vo n D a ta W a reh o u se-­‐System en Das Hervorheben dieses Aspekts zielt vorallem auf die in einem Data Warehouse verwendetet Datenhaltungsplattform. In der Vergangenheit belieferten die Systeme wenige Personen mit aggregierten Informationen über größere Zeitabstände hinweg. Heute bewegt sich das Data Warehouse schon nahe an den Anforderungen an operative Systeme mit vielen Benutzern, sehr großen Datenmengen und permanenten Änderungsvorgängen. War es früher geboten eine separate für aggregierte Auswertungszwecke passende Datenhaltung zu wählen, ist es heute wichtig nicht mehr zwischen einer Datenhaltung für operative und dispositive Zwecke zu unterscheiden. Heute ist es wichtig eine für OLTP und Data Warehouse-­‐Zwecke durchgängig einheitlich nutzbare Datenhaltungs-­‐Komponente einzusetzen, weil beide Anforderungen durchmischt, eng verzahnt und kaum noch trennbar sind. Hinzu kommt die immer geringere Zeit, die bleibt, um Daten aus einem operativen Verwendungkontext in einen dispositiven zu überführen. Die Oracle Datenbank erfüllt genau diese Anforderung. Ursprünglich für operative Zwecke entwickelt, hat sie sich seit dem Release 7 (1994) kontinuierlich auch als ideale Data Warehouse-­‐Plattform etabliert und ist heute die am meisten verwendete Datenhaltung für ein Data Warehouse. 8 A n D W H -­‐ System e w erden im Zu g e d er O p era tio n a lisieru n g d es D W H s h eu te a u ch O LT P-­‐a rtig e A n forderungen gestellt Aktuelle Herausforderungen von Data Warehouse Architekturen Vor der Darstellung der Modellierung und Architektur, sollten aktuelle Problemstellungen skizziert werden, denn darauf müssen die Modellierung und eine Architektur reagieren. 1.
Flexibilität bei der Informationsauswahl für die Endbenutzer Data Warehouse Systeme präsentieren sich gegenüber Endanwendern meist über eine Reihe von technischen Datenbanktabellen, die im besten Fall durch ein Business Intelligence Tool vor dem Zugriff und den Blicken der Endanwender abgeschottet sind. Diese Tabellen stellen die maximal verfügbare Information für Abfragen dar. Ist eine Information nicht da, dann kann sie auch nicht abgefragt werden. Ziel eines guten Data Warehouse Designs ist es, das Informationsangebot des Data Warehouse so breit zu spannen, dass Endbenutzer möglichst alles, was sie interessiert auch in den Daten finden. Gerade in großen Unternehmen kann das schwer sein, weil die Modellierer der Informationsstruktur in dem Data Warehouse nicht mit allen Endanwendern in Kontakt treten können. Umso wichtiger ist es, Datenmodelle zu entwerfen, die eine möglichst hohe Menge an unterschiedlichen Abfragen ermöglichen. Gleichzeitig dürfen die Modelle nicht zu komplex sein, weil man die Endanwender wegen der Komplexität und Unhandlichkeit überfordern und verlieren kann. Multidimensionale Modelle (z. B. Star Schema) erfüllen diesen Zweck. Sie vereinen Einfachheit (Orientierung an Geschäftsobjekten) und eine hohe Menge an potentiellen Abfragen. 2.
Bereitstellung Ressourcen, Kapazität und Performance für Endbenutzer Schwache Hardware Ressourcen führen zu schlechter Abfrageperformance, oder auch zur Einschränkung der Online-­‐Verfügbarkeit des Data Warehouse-­‐Systems, weil die Zeit, in der das System aktualisiert oder auch gesichert wird, zu lange dauert. Gemeint ist auch, dass nicht genügend Abfragen parallel gestellt werden können um Zeit zu sparen. Die Arbeitszeit des Anwenders sollte optimal genutzt werden. Hier helfen extrem leistungsfähige Systeme, die dem Anwender die Abfrage-­‐Ressource dann zur Verfügung stellen, wenn er sie benötigt. Das kann bedeuten, dass Online-­‐Abfragen in der Zeit beantwortet werden müssen, die der Anwender braucht, um eine neue Anfrage zu formulieren, also echtes interaktives und Adhoc-­‐ Auswerten der Daten. 3.
Agilität bei der Bereitstellung von neuen Informationen für die Endbenutzer Von Endanwendern wird Kreativität gefordert, neue Ideen sollen zusätzliche Marktchance eröffnen oder Prozesse verbessern. Deswegen wollen sie immer neue Informationen aus dem Data Warehouse geliefert bekommen und formulieren Anfragen für neue Tabellen, Spalten und Kennzahlen oder dem Anschließen neuer Datenquellen. Weil zentrale IT-­‐Abteilungen mit der Erfüllung dieser Wünsche nicht Schritt halten können, helfen sich Fachanwender dann selbst, indem sie sich eigene Datenbestände anlegen. Dieses Phänomen ist bei fast allen Warehouse-­‐Umgebungen mehr oder weniger zu erkennen. IT-­‐Abteilungen reagieren oft hilflos mit kurzfristigen Lösungen, in dem sie neben dem konsolidierten Data Warehouse zusätzliche separate Datenbanken bereitstellen, in denen sie den Anwendern freie Hand lassen. Anwender mutieren zu Programmierern ihrer eigenen Auswertesysteme. Das Ergebnis sind zusätzliche Kosten für Hardware, zusätzliche Datenkopier-­‐Läufe (1:1-­‐Kopien) und nicht abgestimmte Kennzahlen, weil jeder nach eigenen Vorstellungen Zahlenwerke aufbereitet. Eine solche Vorgehenswese höhlt Sinn und Zweck des zentralen Data Warehouse aus. Den Druck, der zu solchen Situationen führt, wird man nicht ganz abstellen können. Man kann ihn aber durch ein gewisses Informationsmanagement mildern: •
•
4.
Ein erstes Mittel hierzu ist die Dokumentation aller Informationen, die in dem Data Warehouse vorhanden sind. Das sind Listen aller Kennzahlen, aller Referenzdaten, aller Stammdaten mit ihren Ausprägungen, die in dem Data Warehouse vorhanden sind. Wenn man eine bereits bestehende Information über eine gute Dokumentation wiederfindet, braucht man sie nicht mehr neu zu beschaffen. Ein weiteres Mittel ist die Aufteilung des Data Warehouse in eine zentrale, granular gehaltene Warehouse-­‐Schicht und eine Endbenutzersicht. Die Warehouse-­‐Schicht stellt die Obermenge aller Informationen in dem Data Warehouse dar. Es muss leicht und einfach sein, aus diesem „Pool“ an Daten, die passenden Informationen für eine neue Anfrage zu liefern ohne gleich in die Vorsysteme greifen zu müssen. Kosten und Effizienz bei Aufbau und Betrieb der Systeme Die finanziellen Belastungen von Data Warehouse Systemen sind in den letzten Jahren besonders unter die Lupe genommen worden. Die Kosten sind in dem Data Warehouse aufgrund der hohen Datenmengen sehr hoch. Um noch performant arbeiten zu können,schafft man 9 größere Maschinen an und die Kosten steigen erneut. Viele Hersteller von DWH-­‐Komponenten finden offene Ohren, wenn sie Wege und Mittel aufzeigen, um Kosten zu sparen. Und: Data Warehouse-­‐Systeme gehören wohl zu den Systemen, die am ineffizientesten mit Ressourcen umgehen. Dafür kann man fast in jeder Umgebung eine Reihe von Beispielen liefern. Das können sein: •
•
•
•
•
ein ungeschickter, unüberlegter Tool-­‐Einsatz nicht genutzte Features oder Mittel in der Datenbank die Anwendung falscher Verfahren z. B. beim Backup Mehrfachaufwand durch mangelnde Dokumentation usw. Hier helfen meist einfache Maßnahmen, geschultes Personal, pragmatisches Vorgehen. Wer weiß z. B. genau welche Daten in dem Data Warehouse wirklich genutzt werden? Wenn man das weiss, kann man Plattenplatz, ETL-­‐Läufe und Verwaltungsaufwand sparen. Das bedeutet: Verbesserungspotentiale sind genügend da. 10 Das Schichtenmodell
Data Warehouse Systeme sind nicht einfach nur eine Ansammlung von Daten oder ein Haufen von nach Bedarf zusammenkopierten Tabellen. Das Data Warehouse repräsentiert eine Art „Informationsbeschaffungsweg“ und ist eher als Prozess zu verstehen. Der Prozess ist nicht zufällig, sondern folgt immer wieder gleichen Regeln und Methoden. Schichten-­‐ Modellierung im Data Warehouse: Effizienz durch Strukturierung von Informationen Das Hauptziel von allem Tun im Data Warehouse, ist es den Endbenutzer mit Informationen zu versorgen. Es muss klar sein welchem Benutzer welche Information wann und für welche Aufgabenstellung geliefert werden. Zu diesem Ziel kann man mehr oder weniger effizient gelangen. Effizienz wird durch Synergien erreicht und Synergien entstehen durch möglichst frühzeitiges Erkennen gleicher Schritte, damit sie nur einmal zu bewältigen sind. Ein Hilfsmittel dazu ist das Schichtenmodell. Das Schichtenmodell teilt das Data Warehouse methodisch begründet in mehrere Schichten, in denen die enthaltenen Daten durch entsprechende Bearbeitungsschritte aus ihrer ursprünglichen Form in den operativen Systemen in eine benutzergerechtere Form für die Endbenutzer überführt werden. Vor allem in unternehmensweit aufgestellten Data Warehouse Systemen hat sich die Aufbereitung der Daten in drei Schritten bewährt: 1)
2)
3)
Man sammelt, integriert und prüft auszuwertende Daten der operativen Vorsysteme. (Stage oder Data Integration Layer genannt). Man granularisiert diese Daten in einem alle Sachgebiete umfassenden (in Richtung 3 NF tendierenden) -­‐ Schema. (DWH-­‐Schicht oder Enterprise Information Layer genannt). Je nach Zielgruppe und Sachgebiet fügt man im letzten Schritt die granularen Informationsbausteine meist zu multidimensionalen Endbenutzer-­‐Modellen (meist Star Schemen) zusammen. (Data Marts oder User View Layer genannt). Diese Ressourcen-­‐schonende Vorgehensweise zielt auf die Wiederverwendung von Daten und Entwicklungsarbeiten in einer zentralen Schicht, bei gleichzeitiger Flexibilität durch beliebige Auswerte-­‐Szenarien in der Data Mart-­‐Schicht. 3 – Sch ich ten -­‐M o d ell Das Ziel der 3-­‐Schichten-­‐Architektur ist der Entwurf einer möglichst umfassenden, mehrere Unternehmens-­‐ und Themenbereiche abdeckenden stabilen Informationsablage, die in kurzer Zeit konsolidierte Berichte und Analysen für alle (!) Zielgruppen des Unternehmens bereitstellt. Das 3-­‐Schichten-­‐Modell •
gibt dem Data Warehouse die Möglichkeit, Informationen zunächst zu harmonisieren und zu vereinheitlichen, bevor sie in Business Intelligence Systemen ausgewertet werden, •
sorgt für eine „zeit-­‐und Phasen-­‐überdauernde“ Konstante, während sich Geschäftsprozesse wesentlich häufiger verändern, •
kann durch das Ausnutzen von Synergien Aufwand und Zeit sparen. Allerdings wächst heute auch Kritik an diesem Modell: •
Zu viele redundante Daten (Daten werden zwar in unterschiedlicher Form, aber doch von ihrem Inhalt her redundant in den unterschiedlichen Schichten vorgehalten) •
Gefahr für höherer Latenzzeiten (durch den vermehrten Aufwand für datentransformationen und Kopien) •
Hoher Entwicklungsaufwand (für mehr Transformationen und mehr Tabellen) Diese Punkte sollten ernst genommen werden, da sie in manchen Diskussionen die unbestreitbaren Vorteile des 3-­‐Schichten-­‐Modells überlagern können. Im Ergebnis können dann heutige Lösungen wie Lösungen aus den 1980ern aussehen, bei denen Daten direkt aus den operativen Systemen in die proprietäre Speicherung bestimmter BI-­‐Tools gelangen. Die Intention dieser kritik ist berechtigt: Sie will Daten schneller und mit weniger Aufwand zu dem Enbenutzer bringen und ihn damit besser unterstützen. Dieses Ziel sollte jedoch nicht erreicht werden, indem man gleich die Ziele des Data Warehouse (stimmige, konsolidierte, leicht verständliche Daten) abschafft, sondern, indem man das Data Warehouse effizienter macht. Um Synergie-­‐Effekte möglichst komplett zu nutzen empfiehlt Oracle alle 3 Verarbeitungsschritte bzw. Schichten in einer einzigen Datenbank-­‐Instanz und damit auch Hardware-­‐Umgebung anzusiedeln. 3-­‐Schichten-­‐Modell historisch Dieses 3-­‐Schichten-­‐Modell hat sich aufgrund der Erfahrungen, seit den 1980er Jahren entwickeln. Hatte man ursprünglich direkt auf den operativen Systemen ausgewertet, stellte man fest, dass es günstiger war, Auswertungen auf Kopien der operativen Daten durchzuführen. Dann passten die separat erstellten Auswerteergebisse auf separaten Datenkopien der unterschiedlichen Sachgebiete nicht zusammen und es fehlten einheitliche sachgebietsübergreifende Auswertungen. Letztlich führte man eine einheitliche, zentrale und sachgebietsübergreifende Warehouse-­‐Schicht ein, um Informationen zunächst zu sammeln und zu konsolidieren, bevor man dann wieder 11 in die sachgebietsgebzogene Auswertung ging. Dieses Prinzip hat sich bei den meisten unternehmensweit aufgestellten Data Warehouse-­‐
Systemen seit Ende der 1990er Jahre etabliert und ist heute ein nicht mehr in Frage gestellter Standard. Die Mehrwert-­‐Leistungen des Data Warehouse Auf dem Weg zu den Benutzern durchlaufen operative Daten mehrere Wandlungsschritte in den einzelnen Warehouse-­‐Schichten. Operative Systeme nutzen Daten für ihre Zwecke ideal zusammengesetzt und auch nur diejenigen Daten, die sie wirklich benötigen. Für Analysten oder Mitarbeiter, die eine unternehmensweite, prozessübergreifende Aufgabe verfolgen, reicht das nicht aus, bzw. die Daten sind oft wenig verständlich. Das Data Warehouse löst diesen Konflikt. Der Wandlungsprozess im Data Warehouse löst die Datenstrukturen der operativen Systeme auf und überführt die Kerninformation in eine Form, die leicht und flexible für unterschiedliche Zielgruppen und deren Bedürfnisse neu kombiniert werden kann. Man bedient sich der Hilfsmittel aus der Datenmodellierung, mit denen man operative Daten in einem ersten Schritte in die kleinsten möglichen Informationsbausteine zerlegt (Normalisierung in Richtung 3 NF) und dann je nach Endbenutzeranforderung wieder neu kombiniert. Die Vorgänge finden bei dem Übergang von Stage zur DWH-­‐Schicht (Normalisierung) und von der DWH-­‐Schicht in die Data Mart-­‐Schicht (Denormalisierung) statt. Die Daten in der zentralen Data Warehouse-­‐Schicht sind anwendungs-­‐ und Endbenutzer-­‐neutral. Gleichzeitig reduziert das Data Warehouse die Menge der zur Verfügung stehenden operativen Daten, da nicht alle auswerte-­‐relevant sind. Dieses „Verständlich-­‐machen“ von operativen Daten ist eine wichtige Mehrwertleistung des Data Warehouse. N orm alisierungs -­‐ / D en o rm a lisieru n g sp rin zip im D a ta W a reh o u se Trotz datenmodellierung bleibt die Gefahr von Informationschaos Das Normalisierungs-­‐/Denormalisierungsverfahren verhindert noch nicht ein potentielles Informationschaos im Data Warehouse. Denn aufgrund der sog. Homonymen-­‐/Synonymen-­‐Problematik können bei einem unklaren Begriffsverständnis unerkannt Informationen mehrfach in einem Data Warehouse abgelegt sein. Diese Gefahr ist umso größer, je mehr Prozesse und Abteilungen an dem DWH-­‐System angeschlossen sind. Hier helfen: •
Glossars mit abgestimmten Begriffsdefinitionen •
Deskriptoren-­‐Verfahren zur Standardisierung und für das Wiederauffinden von Begriffen •
Metadatenmanagement •
Datenqualitätsstandards Ziel die Erwartungen der Endbenutzer treffen Die Erwartungen der Endbenutzer an die Informationen des Data Warehouse gehören zu den Schlüsselaufgaben. Diese Erwartungen richtig zu treffen ist ein sich wiederholtender Entwicklungsschritt. Die erwarteten Informationen muss man aus den Fragestellungen und Prozessbeschreibungen der Fachanwender herleiten. Ein Data Warehouse ist jedoch nach den Konstruktionsprinzipien von Datenstrukturen konzipiert und bildet die Informationen zu den Objekten der Geschäftsprozesse ab, nicht aber die Geschäftsprozesse selbst. Ein wichtiger Modellierungsschritt ist daher das Identifizieren der richtigen Geschäftsobjekte und die IT-­‐technische Beschreibung der Geschäftsobjekte und das Darstellen mittels leicht verstehbaren und leicht auswertbaren Datenmodellen bzw. Tabellenstrukturen. Der Data Warehouse-­‐Modelliere entwirft daher zunächst meist multidimensionale Modelle für die Data Mart – Schicht (User View Layer). Damit ist der Informationsbedarf der Fachanwender beschrieben und eingegrenzt. Diese „Endanwender“-­‐Modelle bilden dann die Grundlage für die zentrale Data Warehouse Schicht, die eine Obermenge aller Informationen von allen Data Marts darstellt. 12 13 Die Schichten im Detail
Die Data Mart Schicht (User View Layer) Der Hauptzweck der Data Mart-­‐Schicht (User View Layer) ist die Endbenutzer-­‐gerechte Aufbereitung der Warehouse-­‐Daten. Gegenüber der Data Warehouse-­‐Schicht (Enterprise Information Layer) entstehen keine zusätzlichen Informationen. Aber die Datenmodellart und die Granularität der Daten kann sich ändern. Als Datenmodellart bevorzugt man multidimensionale Modelle (Star-­‐Schema, Snowflake), weil diese für Endbenutzer intuitiver sind. Ziel der Modellierung eines Data Mart ist es, so viele Abfrage-­‐Ergebnisse wie möglich präpariert zu haben bevor der Anwender seine Abfragen formuliert. Deswegen ergänzt man multidimensionale Abfragemodelle auch durch vorbereitete Kennzahlen-­‐Tabellen, die man in dem Oracle Data Warehouse meist als Materialized Views erstellt. Kennzahlenbäume sind aufeinander aufbauende Materialized Views. M o d ell-­‐V arianten im U ser V iew Layer Die Eigenschaften der Data Mart-­‐Daten und Modelle sind: •
Alle Datenmodell-­‐Varianten können vorkommen, meist multidimensional (Star) o
Star, Snowflake, Ansammlung einzelner Tabellen, Cubes o
Außer 3 NF-­‐Modelle •
Abfrage-­‐/End User-­‐orientierte Namen und denormalisierte Daten. Sie orientieren sich an den dem Endbenutzer bekannten Geschäftsobjekten. •
Teils temporär. Data Marts werden eventuell nur bei Bedarf neu aufgebaut. •
Data Marts können historisiert sein (wenn nicht in der Warehouse-­‐Schicht bereits historisiert wurde). •
Die Daten sind redundant zur DWH-­‐Schicht. Data Marts sind redundant und „flüchtig“ Aufgrund des 3-­‐Schichten-­‐Konzeptes sind Data Marts prinzipiell redundant. Daher ist die Frage berechtigt, ob zu jedem Zeitpunkt alle Data Marts auch physisch vorhanden sein müssen, oder nur dann, wenn Endbenutzer die Marts benötigen. Um Kosten zu senken, können Data Marts auch auf Anforderung erzeugt werden (Dynamische Data Marts). Die Data Marts komplett neu aufzubauen erleichtert die Verwaltung und das Laden eines Data Marts. Denn: 3
•
Es sind keine Updates, sondern nur Inserts (schnelles Insert Append ) in der Datenbank nötig. •
Das schnelle Neuaufbauen erfolgt als sogenannter Massenladevorgang und ermöglicht das leichte Komprimieren der Daten (Standard-­‐Compression Funktion der Oracle Datenbank). •
Durch regelmäßiges Neuaufbauen wird das Ansammeln von alternden Daten verhindert. Es befinden sich nur wirklich benötigte Daten in dem Data Mart und teurer Plattenplatz wird gespart. Herleiten der multidimensionalen Modelle eines Data Marts Data Marts stellen verständlichen Daten für Endbenutzer bereit. Das bedeutet, dass die Informationen in der Sprache der Anwender und als Kombination von Geschäftsobjekten und den Aktionen rund um die Geschäftsobjekte präsentiert werden. Gleichzeit müssen diese Informationen so flexibel angeboten werden, so dass alle Informationsbedürfnisse des Benutzers abgedeckt sind. Eine Umsatzangabe soll z. B. nicht nur bezogen auf ein Stück vorhanden sein, sondern auch in einer gewissen Verpackungsmenge, Gebindemenge, Liefereinheit, bezogen auf Lagerfläche, bezogen auf Verkaufseinheiten usw. Aber auch bezogen auf Tage, Monate und Jahre und bezogen auf Filialen, Orte, Regionen, Länder usw. Je breiter solche Auswahlmöglichkeiten gespannt sind, umso flexibler ist die Nutzung der Daten durch die Endbenutzer. Eine der wichtigsten Aufgaben bei dem Design von Data Warehouse Systemen ist daher das Erforschen der Erwartungshaltung und Bedürfnisse der Endanwender. Ziel ist nicht möglichst viele Daten des Unternehmens im Data Warehouse zu speichern, sondern diejenigen Daten, die die Endbenutzer jetzt und wahrscheinlich auch in der Zukunft abfragen wollen. Diese Aufgabe löst man durch das Entwickeln von multidimensionalen Modellen gemeinsam mit den Fachanwendern. Die Aufgabe gliedert sich in 4 Schritten: 3
Ein Insert mit dem Hint APPEND ist ein schneller, sog. mengenbasierter Vorgang in der Oracle-­‐Datenbank. 14 1.
2.
3.
4.
Analyse Fachanwenderfragen (mit Analysemodell bzw. Prozessmodell) Herleiten (Geschäfts-­‐)Objektmodell zur Darstellung der Zusammenhänge der Daten und Bilden von Datenstrukturen Bilden einer Multidimensionale Struktur Hinzufügen von allgemein interessierende Ergänzungen Der Schritt 1 erfolgt gemeinsam mit den Fachanwendern. Schritte 2 und 3 erledigt der Modellierer allein an seinem Schreibtisch auf einem Blatt Papier oder mit einem Datenmodellierungstool, das logische Datenmodelle darstellen kann. Die Abfolge 1-­‐3 ist iterativ zu verstehen. Schritt 1 (Fachanwenderfragen und Analysemodell) Ausgangspunkt für die Analyse und Herleitung von Informationsanforderungen sind Fragen, die gemeinsam mit Fachwendern entwickelt und beantwortet werden: Beispiele: Die Informationen in diesen Fragen lassen sich klassifizieren in: •
Bewegungsdaten •
Stamm-­‐Objekte •
Eigenschaften der Stamm-­‐Objekte Die Schwierigkeit besteht darin, ein Geschäftsobjekt genau abgrenzen zu können. D. h. zu erkennen, wann ein Begriff ein Bezeichner eines Geschäftsobjektes ist und wann ein Begriff nur eine Eigenschaft eines Objektes beschreibt. Oder wann beschreibt ein Begriff ein Geschäftsobjekt und wann beschreibt der Begriff eine Aktion rund um das Objekt, also die Unterscheidung zwischen Stamm-­‐ und Bewegungsdaten. Wie schwierig das sein kann zeigt der Begriff „Bankkonto“. Auf den ersten Blick beschreibt der Begriff ein Geschäftsobjekt also etwas Statisches, etwas was man beschreiben kann, und etwas was Eigenschaften besitzen kann. Bei genauerer Betrachtung kann es aber auch eine Bewegungsinformation sein, denn es interessiert eigentlich nur der Betrag, der auf dem Konto verbucht ist. Der ist aber zeitabhängig und verändert sich. D. h. die Bewegungsdaten sind hier die Sätze mit Zuwächsen und Abgängen. Diese Fragen bearbeitet man gemeinsam mit den Fachanwendern in entsprechenden Workshops, meist an einer Wandtafel. Das genannte Konto-­‐Beispiel suggeriert, dass der Modellierer, der den Workshop leitet, ein Kenner von Bankgeschäftsprozesse sein muss. Das ist nicht der Fall. Sicherlich hilft ein gewisses Verständnis für Bankprozesse und Begrifflichkeiten. Er braucht aber auch eine gewisse Neutralität, die es ihm ermöglicht, abseits der Bankenfachlichkeit und Bankensprache auf einer sprachlichen Ebene Sachzusammenhänge neutral zu formulieren und neu zu sortieren. Er muss Fragen formulieren, die dazu führen die Fachlichkeit darzustellen und zu klären. Eine gewisse Fachfremdheit des Modellierers führt zu unkonventionellen Fragen und fordert die Fachmitarbeiter auf, ihre Sachzusammenhänge mit einfachen Mitteln zu erklären. Der Modellierer muss den Sachzusammenhang durch die Erklärungen des Fachmitarbeiters verstehen können und dann kann er die Einteilung nach Bewegungs-­‐Daten, Stammdaten und Eigenschaften der Stammdaten vornehmen. Die Ergebnisse lassen sich in einem Analysemodell (Prozess-­‐Sicht) darstellen. Das Analysemodell ist eine extrem vereinfachte Darstellung der Geschäftsprozesse mit dem Ziel, die Geschäftsobjekte als solche von den Geschäftsprozessen isoliert darzustellen und zwischen Geschäftsobjekten und deren Attributen zu unterscheiden. Geschäftsobjekte sind als Kreise mit Namen dargestellt. Bewegungsdaten sind gerichtete Pfeile zwischen den Geschäftsobjekten. Diese Vorarbeit führt später zur Unterscheidung von Fakten-­‐ und Dimensionsdaten. Attribute lassen sich graphisch an den Kreisen platzieren. 15 A n a lyse -­‐ b zw . Pro zessm o d ell Schritt 2 (Geschäftsobjekt-­‐Modell) Durch eine Umgruppierung bzw. Zusammenfassung oder auch Aufspaltung von Geschäftsobjekten des Analysemodels (Spezialisierungs-­‐ und Generalisierungsverfahren aus der Datenmodellierung) leitet man das Objektmodellleiten. Das ist dann bereits ein Vorläufer für die Dimensionen des dimensionalen Modells, weil schon Hierarchien und Attribute der Dimensionen erkennbar sind. Das Zusammenfassen von generalisierbaren Objekten (Ort -­‐> Region, Artikel -­‐> Artikelgruppe, Kunde -­‐> Bewohner einer Region, Tag -­‐ > Monat usw.) führt zu den späteren Drillpfaden, die man Anwendern für ihre interaktiven Analysen anbietet (Aggregatlevel). O bjektm odell Schritt 3 (Multidimensionale Struktur, Fakten und Dimensionen) In dem Objektmodell hatte man nur die Kreise (Geschäftsobjekte) des Analysemodells weiterverarbeitet. Die Pfeillinien des Analysemodells fasst man zu Bewegungsdatentabellen zusammen. Das dokumentiert, was mit den Geschäftsobjekten geschieht. Dieses Geschehen ist zählbar, messbar. Damit haben wir die Kennzahlentabellen, oder aus dimensionaler Sicht gesprochen, die Fakten-­‐Tabellen. Ein neues sog. multidimensionales Modell wird jetzt erstellt: •
Jedes Geschäftsobjekt ergibt eine Dimension. •
Die in dem Objektmodell gefundenen Aggregationen und Spezialisierungen bilden die Hierarchie-­‐Stufen der neu geschaffenen Dimensionstabellen. •
Alle (!) Eigenschaften eines Geschäftsobjektes sind als Spalten in den Dimensionstabellen zu beschreiben. •
Dimensionsobjekte sind eindeutig. Ein eindeutiger Identifier „zählt“ sie „durch“. Daraus entsteht später der Primary Key der Dimensionstabellen. •
Bewegungsdaten werden zu Fakten-­‐Tabellen-­‐Feldern, die eine gewisse Größe ausdrücken (Kennzahlen). •
Die Beziehung zwischen den Geschäftsobjekte und dem was passiert (Bewegungsdaten) stellt man durch Schlüssel dar, wobei die Dimensionstabelle das Master-­‐Objekt ist und seinen Primary -­‐ Key an die Detail-­‐Tabelle (Fakten) vererbt. •
Die Schlüsselbildung erfolgt von den Geschäftsobjektetabellen (Dimensionen) in Richtung der Bewegungsdatentabellen (Fakten) als 1:n – Zuordnung. Für jedes an den zu analysierenden Geschäftsprozessen beteiligten Geschäftsobjekten wird eine Dimension erzeugt und die Verbindung zu den relevanten Bewegungsdaten erzeugt man durch eine Schlüsselbildung. Das beschreibt den Prozess vollständig, so dass zu erwarten ist, 4
dass möglichst viele Endanwenderfragestellungen bedienbar sind . A u fb a u sch em a ein er D im en sio n 4
Können Geschäftsobjekte nicht mit Bewegungsdaten in Verbindung gebracht werden, dann hat man entweder beim Modellieren etwas falsch gemacht oder man hat eine Schwachstelle in dem Geschäfstprozess aufgedeckt. 16 Schritt 4 Hinzufügen von interessanten Referenzdaten (optional) Viele interessante Bezüge zu anderen Fakten oder Gegebenheiten, sind jetzt noch nicht in dem Modell enthalten, weil sie in den Anwenderfragen zunächst keine Rolle spielten. Aus Analysegesichtspunkten kann man solche Informationen hinzufügen. Z. B. die Darstellung der Zeit in Form einer Zeit-­‐Dimension bestehend aus Tag, Monat, Quartal, Jahr usw. Oder die Darstellung von räumlichen Bezügen, man erweitert den Horizont von der Filiale auf gröber gefasste räumliche Einheiten. Z. B. wird die Zuordnung von Filialen zu Landkreisen in den operativen Daten nicht vorkommen, sie kann aber von einem Analysestandpunkt aus interessant sein. Die Information zu den Landkreisen wird dann in eine Regionen-­‐Dimension mit aufgenommen. Die Umsetzung des multidimensionalen Modells in eine physische Speicherung Diese multidimensionale Modellform besagt noch nichts über die potentielle Speicherung von Analysedaten in eine Datenbank. Es ist ein logisches Modell. Das Modell unterscheidet sich von dem 3. Normalform-­‐Modell der Warehouse-­‐Schicht grundlegend durch eine Umgruppierung der Daten. Man kann es in mehreren Arten in der Oracle Datenbanken speichern. •
•
•
Star Schema: Die Daten werden in einer relationalen Datenbank, wie Oracle, in Tabellen abgelegt. Eine Dimension wird durch genau eine Tabelle abgebildet, die durch eine Primary Key / Foreign Key – Beziehung mit einer oder mehreren Faktentabellen verbunden ist. Snowflake Schema: Die Daten liegen in einer relationalen Datenbank. Die Dimensionsdaten sind aber normalisiert auf einzelne Tabellen verteilt. (Hier fehlt der Generalisierungs-­‐ und Spezialisierungsschritt in dem oben genannten Objektmodell). Vorberechneter Würfel: Um eine Benutzeranfrage zu bedienen erfordern die vorgenannten Star-­‐ und Snowflake-­‐Formen in relationalen Datenbanken eine Join-­‐Abfrage auf Dimensions-­‐ und Faktentabellen. Dabei muss das Datenbanksystem große Teile zuerst lesen, bevor ein Ergebnis feststeht. Um diesen Aufwand zu minimieren, baut das Würfelverfahren schon zum zu dem Zeitpunkt der Würfeldefinition alle möglichen Join-­‐Varianten einmal auf und speichert die gefundenen Werte. Es gibt letztlich nur noch vorkombinierte und vorberechnete Ergebniswerte in sog. Zellen. Die Kombinationsvarianten aus denen heraus die Werte gebildet wurden entsprechen den Dimensionstabellen des Starschemas, sie sind aber physisch nur noch als Metadaten vorhanden, über die man auf die vorberechneten Werte zugreift. Um diese Unterschiede auch sprachlich auszudrücken spricht man bei den ersten beiden Varianten von ROLAP (relational abgebildetes 5
OLAP) und bei der Würfelvariante von MOLAP (multidimensionales OLAP) . Es sind aber alle drei Varianten Formen von multidimensionalen Modellen. Diese Feststellung ist wichtig, weil die Eigenschaft „Multidimensionalität“ in vielen Diskussionen irreführend nur der Würfelvariante zugeschrieben wird. Wenn Multidimensionalität eine wichtige Grundlage für flexible Auswertungen ist, dann wären eben nur Würfel flexibel abfragbar. Dabei sind Star Schemen genau so flexibel abfragbar. Das Star-­‐Schema Will man die ROLAP-­‐Variante einsetzen, also will man die multidimensionalen Modelle mit relationalen Tabellen abbilden, dann empfiehlt Oracle das Star Schema und nicht das Snow Flake Schema. Sta r Sch em a Sn o w fla ke D a s O racle D ata W arehouse bevorzugt das StarSchem a Die Gründe sind: •
Bei Abfragen sind weniger Joins aufzulösen. •
Die Datenbank kennt spezielle Performance-­‐Optimierungen für das Stan Schema (Star Transformation ). •
Der ETL-­‐Aufwand ist geringer, da nicht alle normalisierten Tabellen zu aktualisieren sind. •
Auch wenn das Star Schema tchnisch als relationale Tabellen realisiert ist, ist es z. B. in einer graphisch präsentierten Form auch für Fachanwender intuitiv und leicht verstehbar. Es ist als Vermittler zwischen IT und Fachanwender nutzbar. •
Es ist flexibler, wenn Änderungen anstehen. Bei Faktentabellen fügt man einfach nur Spalten an, wenn neue Dimensionen hinzumodelliert werden, und auch die Dimensionen lassen sich einfach um weitere Felder erweitern. 6
5
Während in der Oracle Datenbank das Star Schema mit einfachen Tabellen und einer speziellen Star Schema-­‐Optimierung umgesetzt wird, bietet Oracle für den MOLAP-­‐Bereich die Oracle OLAP Option (Lösung innerhalb der Oracle-­‐Datenbank) und Hyperion Essbase (Lösung außerhalb der Oracle Datenbank) an. 6
Die Star Transformation wird weiter in einem späteren Kapitel besprochen. 17 M u ltid im en sio n a le Stru ktu r (lo g isch u n d p h ysisch ) Diese Modell-­‐Form stellt eine ideale Brücke zwischen Fachanwender-­‐Fragen (Informationsbedürfnis der Anwender) und den technischen Möglichkeiten der Oracle-­‐Datenbank dar. Star Schema-­‐Modelle können direkt von Fachanwendern verstanden werden, da die Dimensionen den Informationsgehalt der Geschäftsobjekte wiedergeben. Schon durch die graphische Anordnung von Dimensions-­‐ und Faktentabellen lassen sich Benutzerabfragen erahnen. In dem dargestellten Beispiel ist das: Welche Artikel werden von welchen Kunden in welcher Zeit in welcher Regionen gekauft? Varianten des Star Schemas Star Schemen kommen in der Praxis in vielen Spielarten vor. Man kann nahezu alle Informationsanforderungen durch diese Modellform abbilden. •
Dimensionen können und sollten mehrere Fakten-­‐Tabellen referenzieren. •
Zwischen mehreren Fakt-­‐Tabellen können selbst PK/FK-­‐Beziehungen bestehen. •
Zwischen Fakten-­‐Tabellen und Dimensionen können N : N -­‐ Beziehungen bestehen, die man mit entsprechenden Mitteln auflöst. Sta r Sch em en kö n n en a u ch ko m p lexer a u fg eb a u t sein 7
Die in der Graphik dargestellten Faktentabellen sind alle über die Produkte-­‐ und Zeit-­‐Dimension miteinander verbunden . Damit können alle Daten in diesen Faktentabellen (alle Kennzahlen) mit zusammenhängenden SQL-­‐Abfragen ausgewertet werden. User View Layer (Data Mart Schichten) sollten so aufgebaut sein, dass möglichst viele Faktentabellen über das Gros der Dimensionen zugreifbar ist. Das in der Graphik dargestellte Beispielmodell erlaubt es z. B. in einer einzigen SQL-­‐Abfrage Verkauf und Einkauf von Produkten, die Aufteilung auf Anteilsmengen der Teile der Produkte und Verkaufsanteile von Verkäufern an den Produkten zu messen. Und das auch noch bezogen auf Zeit und Ort. Auch wenn manche Abfragemöglichkeiten augenfällig keinen Sinn ergeben, so garantiert das Modell die Stimmigkeit der Fakten untereinander (Stimmigkeit der Kennzahlen) und ist die Grundlage für eine Flexibilität, die man den Benutzern über Business Intelligence-­‐Tools anbieten kann. Fehlende Flexibilität auf der Endanwenderseite entsteht oft dadurch, dass Kennzahlen nicht miteinander in Verbindung gesetzt werden können bzw. nicht zusammen in einem Report erscheinen können. Ursache ist fehlende Voraussicht bei der Modellierung der Data Marts (User View Layer), weil diese Tabellen nur als einzelne isolierte Tabellen dargestellt sind. Wenn Data Marts sogar in unterschiedliche Datenbank-­‐Schemen abgelegt oder sogar mit unterschiedlicher Technologie realisiert sind, dann können Zusammenhänge auf der Endanwenderseite nicht mehr gebildet werden. 7
Dimensionen, mit den man auf mehrere Fakten-­‐Tabellen zugreifen kann nennt man auch „Conformed Dimensions“. 18 Im UserView Layer sollten allen Fakten nach Möglichkeit über gemeinsam genutzte Dimensionen zusammenhängend zugreifbar sein. D ie D a ta M a rt-­‐Sch ich t (U ser V iew La yer) so llte d u rch g ä n g ig-­‐a b fra g b a r a u fg eb a u t sei. Sta r Sch em en so llten ü b er g em ein sg en u tzte D im en sio n en verb u n d en sein Degenerated Dimension Dimensionen können auch völlig in eine Fakten-­‐Tabelle übernommen werden. Das wird dann gemacht, wenn die Dimensionen-­‐Tabelle nur wenige Spalten umfasst und man damit für die spätere Abfrage einen Join sparen kann. D eg en era ted D im en sio n : E in e Ku n d en-­‐D im en sio n w u rd e in d er F a kten ta b ellen a u fg elö st Typische Beispiele sind Dimensionen wie Geschlecht mit den Werten [Maennlich / weiblich / XX]. Anstelle des Schlüssels kann man hier den 8
Wert direkt in die Fakten-­‐Tabelle aufnehmen. Dimensionstabellen mit nur einem Feld sind daher unüblich . Factless Fact Table Factless Fact Table: Zähl -­‐Faktentabelle Wenn eine Fakten-­‐Tabelle nur die Foreign Key – Schlüsselwerte der Dimensionstabellen und keine zusätzlichen Wertfelder (Kennzahlenfelder) enthält, dann wird sie nur zum Zählen der jeweiligen Dimensionen-­‐Verknüpfungen genutzt. Empfehlungen für den Aufbau von Faktentabellen Effizient aufgebaute Fakten-­‐Tabellen sind nach folgende Regeln modelliert: •
Eine Faktentabelle sollte so granular wie möglich aufgebaut sein. Je granularer sie definiert ist, um so mehr Aggregate lassen sich zu einem späteren Zeitpunkt daraus bilden. Das kann bei einem Lebensmittelhändler die Milchpackung sein, die über das Band an der Kasse läuft oder bei einer Telefongesellschaft das einzelne Telefongespräch. Das hat Auswirkungen auf die Dimensionen (eine Zeit-­‐Dimension muss z. B. ein Level Minuten oder Sekunden haben) und auf die Datenmengen. Die aufgrund der feineren 8
Das mag weit hergeholt sein, aber es gibt Beispiele, bei denen solche Eigenschaften wirklich als Dimension modelliert wurden. Bei dieser Vorgehensweise kommen schnell 100 und mehr Dimensionen zustande. 19 Granularität entstehenden Datenmengen können mit heutigen Datenbanktechniken beherrscht werden. Performance sollte kein Argument für eine höhere Granularität sein. •
Nach Möglichkeit sollten keine separaten Faktentabelle erstellt werden, nur um eine höhere Aggregations-­‐Ebene zu erhalten. Diese Aggregations-­‐Ebenen können zum Abfragezeitpunkt aus einer Faktentabelle mit granularen Daten errechnet werden. 9
Wenn die Performance hier nicht reicht, kann eine Materialized View helfen •
Durch gemeinsam genutzte Dimensionen erstellt man “verwandte” Faktentabellen, damit Abfragen mit möglichst vielen Informationen zusammenhängend flexibel gestaltet werden können. •
Die Faktentabelle besitzt keinen eigenen PK o Der Zugriff erfolgt nur über die Foreign Key-­‐Felder o
•
Sätze müssen nicht eindeutig sein, d. h. die Fakten-­‐Tabelle enthält keinen Primary Key (Ausnahme: Eine Fakten-­‐Tabelle ist Master in einer Master/Detail-­‐Beziehung zwischen Fakten-­‐Tabellen) 10
Fakten-­‐Tabellen sind meistens Partitioning-­‐Kandidaten . Die Wahl der Granularität einer Fakten-­‐Tabellen beieinflusst die Flexibilität der Abfrage Endbenutzer wünschen meist ein Höchstmaß an Abfragemöglichkeiten und Flexibilität. Wählt man die Granularität der Faktentabelle kleiner, so steigt damit automatisch die Menge der Abfrageoptionen. Es steigt aber auch die Menge der Sätze in der Fakten-­‐Tabelle. M eh r A g g reg a t-­‐Level in d en D im en sio n en fü h ren zu ein er V erfielfa ch u n g vo n A b fra g eo p tion en Die Datenbank-­‐Technologie und die Hardware-­‐Mittel sind in den letzten Jahren sehr stark verbessert worden. Daher kann man heute die Granularität der Fakten-­‐Tabellenwerte so klein wie möglich wählen. Ideal ist die Granularität die gleiche, wie die der operativen Quelldaten. Man kann bis auf die Detail-­‐Ebene „drillen“ ohne auf die operativen Daten wechseln zu müssen. D ie G ra n u la ritä t d er F a kten ta b elle erh ö t d ie M en g e d er A b fra g eo p tio n en fü r d ie E n d b en u tzer Auslagern von weniger häufig genutzt Attributen Werden Spalten von Dimensions-­‐Tabellen weniger häufig genutzt, so kann man diese in eine separate Dimension auslagern. An der Faktentabelle ändert sich dadurch nichts, denn es wird derselbe FK-­‐Schlüssel von allen Dimensionen genutzt. Ein analoges Verfahren erhält man, wenn man zwischen zwei Dimensionen eine 1:1 Beziehung erstellt. 9
Materialized Views werden weiter unten besprochen. 10
Partitioning wird in einem separaten Abschnitt besprochen 20 A u sla g ern vo n A ttrib u tg ru p p en in D im en sio n en , d ie fü r u n tersch ied lich e Zw ecke u n d m it u n tersch ied lich er H ä u fig keit g en u tzt w erden. Generell sollte man solche Lösungen gut überdenken, denn sie machen das gesamte Datenmodell komplex. Datenbanken sind heute wesentliche performanter als früher. Daher sind solche Lösungen aus Performancegründen heute kaum noch nötig. Manche Data Warehouse Systeme werden jedoch heute auch operativ genutzt. Durch dieses Verfahren, kann man operativ genutzte Spalten von den übrigen trennen und die Sicht auf die Daten für die jeweiligen Anwendungen einfach halten. Umgang mit großen Faktentabellen Große Fakten -­‐ Tabellen stellen aufgrund des enormen Platzbedarfes und hohem Ladeaufwand eine besondere Herausforderung dar. Es ist daher sinnvoll, solche großen Objekte nur einmal in dem gesamten Data Warehouse aktiv zu halten. Diese Objekte kann man in der Data Warehouse-­‐Schicht einmalig persistent vorhalten und aus den Data Marts darauf verweisen. Das ist ein weiterer Grund, weshalb Data Marts (User View Layer) und Kern-­‐Data Warehouse (Enterprise Information Layer) gemeinsam in 11
einer zusammenhängenden Datenbank-­‐Umgebung anzusiedeln sind . G roße T a b ellen w erd in d em g esa m ten System n u r ein m a l vo rg eh a lten Historisierung Historisierung findet auf zwei unterschiedliche Weisen statt. Zum einen beziehen sich die auszuwertenden Daten auf einen historischen Zeitraum (i. d. R. vom „Jetzt“ bis zu einem Zeitpunkt in der Vergangenheit). Zum anderen können sich die Rahmenbedingungen, unter denen Daten entstehen, verändert haben. In diesem Fall redet man von Slowly Changing Dimensions. Haben sich z. B. Verkaufsgebiete verändert, so hat dies auch Auswirkungen auf Verkaufszahlen. Zur Darstellung der veränderten Rahmenbedingungen wird man bei den beschreibenden Informationen (Dimensionen) Versionen und Gültigkeitszeiträume (Gültig_von/Gültig_bis) einführen. Zur Darstellung der unterschiedlichen Zeiten in denen Daten anfallen nutzt man Zeitattribute in den Bewegubgsdatentabellen bzw. Fakten-­‐Tabellen. Solche Zeitattribute sind gleichzeitig eine gute Voraussetzung für das physische Partitionieren großer Tabellen (Oracle Partition), was dann wieder 12
Abfragen beschleunigen kann . Es gelten folgende Empfehlungen: •
Slowly Changing Dimensions können aufwendig sein. Sie erschweren das Abfragen. Deshalb sollte man diese nur einsetzten, wenn es nötig ist. •
Weil Data Marts flüchtig sein können, sollte die Historisierung bereits in der Kern-­‐DWH-­‐Schicht angelegt sein. Bei dynamisch aufgebauten Data Marts hat man dann die Möglichkeit, nur einen Teil der Historisierung in den Data Marts zu realisieren. 11
Als Gegenargument werden manchmal Security-­‐Gründe angeführt. Gerade von der administrativeb Seite her will man keine Endbenutzer-­‐Zugriffe in der zentralen Data Warehouse-­‐Schicht haben. Security wird jedoch sinnvoll über einen Objekt-­‐bezogenen Zugriff gesteuert. Auch Performance-­‐Probleme in der zentralen Schicht ist kein Argument, denn heutige Systeme sind sehr performant. 12
Auf das Thema Slowly Changing Dimensions wird in diesem Text nicht weiter eingegangen, da es in der Literaturbereits oft darstellt ist (z. B. hat Kimball 3 Varianten unterschieden von denen meist Variante 2 genutzt wird (Versionsnummern und Gültigkeitsattribute). 21 Schlüssel in Dimensionen Dimensionen sind als eindeutige Tabellen aufzubauen. Sie besitzen also einen Primary Key, der so auch in der Datenbank angelegt ist. Dieses Schlüsselfeld ist i. d. R. sehr einfach gehalten und wird im Verlauf des ETL-­‐Prozesses gebildet. Weitere eindeutige Schlüssel (Alternate Key, Unique Key) können enthalten sein, sie spielen aber für die Konstruktion des Star Schemas keine Rolle. Durch den Denormalisierungs-­‐Schritt gibt es im Gegensatz zu den 3NF-­‐Modell der Kern-­‐Data Warehouse -­‐ Schicht auf den höheren Aggregat-­‐Stufen einer Dimensionstabelle keine eindeutigen Schlüssel mehr, auch wenn die Namensgebung dieses suggeriert. Wahl der Schlüsselfelder Als eindeutige Schlüssel werden für die Dimensionen meist künstliche Schlüssel im Verlauf des ETL-­‐Prozesses generiert. Wenn kein künstliches Zählfeld als Primary Key gewählt wird, sondern bestehende, fachliche Felder, dann sollten folgende Regeln angewendet werden: •
Einfach benutzbare und kurze Felder, um Speicherplatz zu sparen und Fehler zu vermeiden o
Nach Möglichkeit nicht zusammengesetzte Schlüssel nutzen, weil das das Formulieren von Abfragen unnötig komplex macht und zu viel Vorwissen erfordert. Ausserdem können Schlüsselbestandteile NULL-­‐Werte annehmen. •
Keine Felder wählen, die NULL werden können. •
Spaltenwerte sollten stabil sein und sich nicht mehr ändern. Data Warehouse Systeme haben ein längere Lebensdauer (~ 10 Jahre) als OLTP-­‐Systeme. Schlüssel sollten über diese Zeit hinweg gültig sein. Umschlüsselung Das Umschlüsseln erfolgt zwar bereits in der Stage-­‐Schicht. Es hat aber Auswirkungen auch auf die Data Mart -­‐ Schicht. Denn Geschäftsobjekte erscheinen hier entgegen der Situation in den operativen Systemen mit neuen künstlichen Schlüsseln, die auch bei Abfragen genutzt werden müssen. Eine Umschlüsselung auf künstliche Schlüssel ist aus mehreren Gründen notwendig: •
Integration: In mehreren Vorsystemen kann es unterschiedliche Schlüssel geben. Um nicht ein System zu bevorzugen, in dem man sein Originalschlüssel in das Data Warehouse übernimmt, wählt man einen neuen künstlichen Schlüssel im Data Warehouse. •
Zeitstabilität: Natürliche Schlüssel können sich leichter durch Veränderung der Geschäftsprozesse ändern, während ein Data Warehouse meist langlebiger ist, als operative Anwendungen. Daten sollten im Data Warehouse eigene Schlüssel erhalten, damit sie nicht von historischen und eventuell obsoleten Anwendungen beeinflusst werden. •
Performance: Künstliche Schlüssel können sich bei Abfragen aufgrund von Struktur und Länge performanter verhalten. Business Key Die Originalschlüssel aus den Vorsystemen übernimmt man zur Orientierung der Endbenutzer als sogenannte Business Keys bis in die Dimensionen hinein. Dort findet man sie auf der gleichen Stufe wie die neuen künstlichen Schlüssel. Es kann vorkommen, dass in einer Dimension gleich mehrere Originalschlüssel enthalten sind, weil die Geschäftsobjekte in mehreren Vorsystemen unterschiedlich verwendet wurden und unterschiedliche Schlüssel hatten. In d iesem B eisp iel fin d et m a n d ie b eid en „B u sin ess Key“ „Ku n d en _ N R “ u n d „Pa rtn ernum m er“ n eb en d em kü n stlich en Sch lü ssel „D im _ Kd _ N R “ Quellsysteme Mit einem Sprung an den gegenüberliegenden Startpunkt des Informationsbeschaffungsprozesses treffen wir auf die Herausforderungen der Quellsysteme. Diese Herausforderungen sind meist weniger technischer Art, sondern es sind organisatorische Dinge wie Verfügbarkeit oder auch Beschaffenheit der Quelldaten. Bei den Quellsystemen, deren Daten man in das Data Warehouse importieren will, handelt es sich oft um autarke Systeme mit autarken Verantwortlichen. Erschwerend kommt hinzu, dass die Mitarbeiter, die die Vorsysteme verantworten und pflegen oft nicht zu Nutznießer des Data Warehouse gehören und ungerne zusätzliche Aufwende oder Prämissen für ein nachgelagertes System, wie das Data Warehouse akzeptieren. In der Vergangenheit hat man solche Schwierigkeiten mit der Autorität von 22 Sponsoren des Data Warehouse gelöst. Heute ist es oft so, dass das Data Warehouse zu einem allgemein akzeptierten und notwendigen Bestandteil der Unternehmensanwendungen gehört und man auf Kooperation setzt. Q u ellsystem e Herausfordungen bleiben aber, denn es gehen Daten aus einer Anwendung in eine andere über. Es entstehen Fragen wie •
•
•
•
Wem gehören die Daten, nachdem sie in das Data Warehouse gelangt sind? Wer ist für den Zustand der Daten (z. B. Datenqualität) verantwortlich? Wie, wo und mit welchem Aufwand werden Daten für den Transport in das Data Warehouse vorbereitet? Wie weit darf das Quellsystem durch das Abziehen der Daten beeinflusst werden? Es treten verfahrenstechnische Fragestellungen auf wie: •
Wie werden Deltadaten identifiziert? •
Wie reagiert man auf nachträgliche Änderungen im Quellsystem? •
Wie wird die Datenmenge reduziert? 13
•
Wie greift man auf Daten von gekapselten Systemen zu? •
Probleme der zeitlichen Verfügbarkeit oder: wann ist der beste Zeitpunkt zum Abgreifen? •
Wie geht man mit 24 Stunden Online-­‐Betrieb der operativen Systeme um? •
Wie geht man mit schwer nachvollziehbarer Ablagelogik um? („Wenn in Feld A der Wert XYZ dann in Feld B ein gütiger Wert“). Schwer wird es, wenn Quelldaten nicht dokumentiert sind, und auch niemand die entsprechende Quelldatenstruktur kennt. Hier hilft dann nur manuelles Anfassen der Datenbestände. Der Vorgang ist im besten Fall mit Data Profiling Werkzeugen zu automatisieren. Diese Werkzeuge liefern Informationen über vorherrschende Feldstrukturen, die am häufigsten vorkommenden Begriffe, Füllgrade, Abhängigkeiten von Feldinhalten untereinander usw.. Technisch gibt es heute kaum Hürden zwischen den Vorsystemen und dem Data Warehouse. Über ETL-­‐Tools lassen sich die Strukturen der Vorsystem-­‐Daten als Metadaten anzeigen. Mit graphischem „Drag and Drop“ erstellt man über sog. Mappings eine Zuordnung zwischen den Tabellen des Quellsystems und den Tabellen des Stage-­‐Bereichs des Data Warehouse. Die Werkzeuge generieren dann meist Zugriffsprogramme in der passenden Sprache für die jeweiligen Betriebssysteme und Datenbanken. Umgang mit dem Aspekt des Operativen der Vorsysteme Operative Vorsysteme sind eingebunden in z. T. erfolgskritische Unternehmensprozesse. Daher unterliegt der Umgang mit ihnen aus Sicht des Data Warehouse besonderen Regeln: •
•
Vorsysteme dürfen durch das Abziehen der Daten nicht behindert werden o
Kein Einfluss auf die Performance o
Kein Anhalten, um Daten herauszulesen Vorsysteme sollten nicht modifiziert werden müssen o
Keine zusätzlichen Tabellen einfügen o
Keine Trigger einfügen •
Es sollten keine Daten-­‐manipulierenden Aktivitäten erfolgen (Inserts, Updates), also auch keine “Marken”, die bereits gelesene Informationen kennzeichnen. •
Auf den Maschinen des Quellsystems sollten nach Möglichkeit keine zusätzlichen Programme installiert werden, die sich negativ auf das Performanceverhalten auswirken. Die größte Unabhängigkeit zwischen Vorsystemen und Data Warehouse wird erreicht, wenn man Textdateien mit aktuellen Bewegungsdaten, die aus einem bestimmten Grund sowiso schon existieren, wieder verwenden kann. Oderman nutzt Werkzeuge wie Oracle Golden Gate, die direkt auf den Log-­‐Files der Quellsysteme arbeiten ohne die Quell-­‐Anwendung selbst aufzurufen. Umgang mit Datenqualitätsproblemen in den Vorsystemen Operative Vorsysteme gehen in einer besonderen Art und Weise mit Datenqualitätsproblemen um. Meist benutzen die Systeme die Daten aufgrund ihrer eigenen Verwendungsansprüche und die Daten sind aus dieser Sicht stimmig und sauber. In Data Warehouse Systemen treffen diese Daten jedoch auf Daten anderer Vorsysteme oder sie werden anders verwendet. Aus der übergreifenden Sicht des Data Warehouses können die Quelldaten dann fehlerhaft sein. Zur Lösung gibt es folgende Verfahren: •
Festlegen von Qualitätsstandards gemeinsam mit den Vorsystem-­‐Verantwortlichen (z. B. Fachanwendern). 13
Z. B. SAP 23 •
Prüfung der Einhaltung dieser Standards in der Stage-­‐Schicht (Data Integration Layer) des Data Warehouses. •
Nach Möglichkeit fehlerhafte Informationen zurückweisen, bzw. aussondern und sie den Vorsystemverantwortlichen zur Korrektur anbieten. •
Fehlerhafte Sätze sollten nicht „auf eigene Faust“ in dem Data Warehouse korrigiert werden. Stage – Schicht (Data Integration Layer) Die Stage -­‐ Schicht oder „Data Integration Layer“ ist eine eher technisch ausgerichtete Arbeitsebene. Hier findet statt: •
Überprüfung von o
Syntaktischer Korrektheit (Feld-­‐Typen, -­‐Länge, NULLs) o
Vollständigkeit der Daten und Mengenverhältnisse o
Gültige Wertebereiche o
Vorhandensein von Referenzdaten o
Eindeutigkeit o
Eliminierung von NULL-­‐Werten •
Zusammenführung operativ getrennter Daten. •
Bilden neuer Informationsobjekte mit dem Ziel der einfacheren Weiterverarbeitung (tempöräre Objekte mit denen sich der ETL-­‐
Prozess einfacher gestalten läst). •
Waisen-­‐(Orphan-­‐)-­‐Management für Child-­‐Sätze, die keine Parent-­‐Sätze haben. •
Bildung von Daten-­‐Deltas, wenn die Deltabildung nicht über Zugriffe in das Vorsystem möglich ist. •
Hinzufügen von ergänzenden, beschreibenden Informationen zu den Datenobjekten oder Aufbau von Referenzen zu Referenzdaten in dem Enterprise Layer. Sta g e / D a ta In teg ra tio n La yer Folgende Empfehlungen gelten für den Umgang mit der Stage Schicht: 14
•
Nach Möglichkeit keine 1:1-­‐Kopien bei dem Einfügen der Sätze von dem Vorsystem in die Stage-­‐Schicht . •
Wenn möglich sollten bereits beim Extrahieren Prüfungen und Wandlungen von Daten vorgenommen werden. •
Die Stage-­‐Schicht hat keine besonderen Datenmodell-­‐Strukturen. •
Keine Indizes und keine Constraints verwenden. •
Die Stage ist leer, wenn nicht geladen wird. Es befinden sich nur ETL-­‐relevanten Daten in der Stage-­‐Schicht. •
Keine separate Hardware oder Datenbank. Es sollten dieselben Medien genutzt werden, wie die übrigen Schichten des Data Warehouse, um den ETL-­‐Aufwand zu minimieren. Archivierung gelesener Sätze Eine sehr praktische Funktion kann es sein, die geladenen Informationen nach der Bearbeitung zu archivieren. Diese Daten können bis zum Abschluss einer folgenden Ladephase aufbewahrt werden, um z. B. Delta-­‐Abgleiche vorzunehmen. Es kann darüber hinaus sinnvoll sein, die eingelesenen Daten zu protokollieren oder auch komplett zu archivieren, um später eventuelle Fehlentwicklungen überprüfen zu können. Es sollte jedoch eine Technologie gewählt werden, die das Data Warehouse bezogen auf die Performance, Speicherplatz und Kosten nicht belastet. Hierzu können gelieferte Textdateien auf Bänder oder kostengünstige Online-­‐Platten geschrieben werden. Gelieferte Bewegungsdaten müssen in dem Kontext ihrer Stammdaten gespeichert werden. Spätestens hier ist kritisch zu hinterfragen, ob das Data Warehouse als Archivierungssystem misbraucht werden soll oder ob es nicht bessere Archivierungskonzepte gibt. Von einer pauschalen Archivierung im Data Warehouse ist eher abzuraten, denn die Aufgabenstellung des Data Warehouse ist auch so schon komplex genug. Generische Stage-­‐Strukturen Eine häufige Anforderung an Data Warehouse-­‐Systeme ist das Einbinden von neuen Eingangsdatenströmen. Um nicht permanent neue Entwicklungsarbeiten an dem System vornehmen zu müssen, kann es sinnvoll sein, für einfach strukturierte neue Quellen (z. B. Text-­‐
14
Das wird sich oft nicht vermeiden lassen. 1:1-­‐Kopien sollten aber nur gemacht werden, wenn auch wirklich Gründe vorliegen. 24 Dateien), generische Eingangsschnittstellen zu entwerfen. Hierbei wird über eine einfache Metadaten-­‐Information (z. B. Feldliste) ein 15
Generator gesteuert, der dann ein neues Einlese-­‐Programm erzeugt. Operational Data Store (ODS) Eine Operational Data Store Schicht gehört eigentlich nicht zu dem 3-­‐Schichten-­‐Modell. Sie ist eine zusätzliche Hilfskonstruktion zum Lösen spezieller Anforderungen. Operational Data Store Strukturen dienen: •
dem schnellen Bereitstellen aktueller Informationen aus den Vorsystemen, ohne dass diese den kompletten Wegdurch das Data Warehouse durchlaufen müssen, •
der Unterstützung von Real-­‐Time-­‐Auswertungen, •
der Unterstützung von operativem Reporting. Die Schicht ist eine Hilfsschicht, um temporär die potentiell aufwendigen Prüf-­‐ und Integrationsvorgänge in der Stage – Schicht (Data Integration Layer) zu umgehen. Operational Data Store -­‐ Informationen gelangen erst zu einem späteren Zeitpunkt oder sogar gar nicht in die Data Warehouse – Schicht (Enterprise Information Layer). Diese Informationen sind nur für ausgesuchte Zwecke bestimmt und erheben nicht den gleichen Anspruch an Qualität und Vollständigkeit wie die übrigen Data Warehouse -­‐ Daten. Operational Data-­‐Store Daten haben in der Regel folgende Eigenschaften: •
Es findet eine auf Subjekte / Objekte bezogene Integration zwischen verschiedenen Vorsystemanwendungen statt. •
Die Gültigkeit ist auf die aktuelle Ladeperiode beschränkt, es wird nicht historisiert und es werden keine bestehenden Datenbestände ergänzt. •
Es sind flüchtige Daten. Ein operational Data Store wird bei jeder Aktualisierungsperiode komplett neu erstellt. •
Die Daten befinden sich auf Transaktionslevel. Es wird nicht aggregiert. •
Direkter Zugriff durch Benutzer ist möglich. •
Daten befinden sich in einem syntaktisch und semantisch geprüften Zustand (wenn möglich). •
Letztlich analog zur DWH-­‐Schicht, nur ohne Historisierung. O perational D ata Layer Die Erstellung der Operational Data Store Schicht muss kein zusätzlicher ETL-­‐Aufwand bedeuten, wenn man die Schicht als Vorstufe für den Enterprise Information Layer konzipiert. Die Daten sind bereits geprüft und z. T. integriert. Sie müssen in einem letzten Schritt nur noch historisiert und in die bestehenden Daten der zentralen Schicht integriert werden. Die (Kern-­‐) Data Warehouse – Schicht (Enterprise Information Layer) Die Kern Data Warehouse – Schicht (Enterprise Information Layer) ist die eigentliche, „wichtige“ Daten-­‐Instanz des gesamten Data Warehouse -­‐ Systems. Hier befinden sich die Warehouse-­‐Informationen, nachdem sie in der Stage Schicht geprüft und integriert wurden und bevor sie wieder in der Data Mart-­‐Schicht für die Verteilung aufbereitet werden. Folgende Informationsarten sind hier zu finden: •
Stammdaten •
Referenzdaten o
•
Kundendaten, Produkte, Lieferanten, Partner etc. o
Meist externe nicht aus dem Unternehmen stammende Daten (Partnerinformationen, Partnerproduktkataloge, Kontaktdaten etc.) o
Interne Verzeichnisse (Mitarbeiter-­‐, Abteilungsverzeichnisse, Abkürzungsverzeichnisse, Glossare, Regeln, Richtlinienverzeichnisse, Handlungsempfehlungen, allgemeine Wissenssammlungen etc.) o
allgemeine Sammlungen wie Feiertage, Steuertabellen, geographische Daten, Währungskurse etc. Bewegungsdaten o
Angesammelte Daten aus allen Berichtsperioden. 15
ETL-­‐Tools wie Oracle Warhouse Builder verfügen über Skritpsprachen, mit denen man das Erstellen von Lade-­‐Routinen automatisieren kann. Oracle Partner-­‐Firmen haben hierzu Lösungen entwickelt. „Das generierte Data Warehouse“ ist das Stichwort. 25 o
Zumindest die letzten Ladeperioden in sehr granularer Form, danach eventuell aggregiert oder mit Referenzen auf Archivierungen. Folgende Anforderungen gelten für die Data Warehouse -­‐ Schicht: •
Eindeutigkeit aller Objekte (Tabellen) und Namen. •
Redundanzfreiheit aller Informationen. Das sollte bereits über die Eindeutigkeit der Objektnamen geregelt sein. Das reicht jedoch nicht, weil die gleichen Informationen in Tabellen mit unter unterschiedlichen Namen abgelegt sein können. •
Bereichs-­‐ und Themenübergreifendes (integriertes) Datenmodell. •
Anwendungs-­‐ und Geschäftsprozess-­‐neutral. •
o
Objekte stammen aus mehreren Geschäftsprozessen. o
Daten müssen tauglich genug sein, um alle Anwendungen zu representieren. o
Genutzt werden anwendungsneutrale Schlüssel (keine Schlüssel der Vorsysteme). Langlebigkeit der Daten. o
Es findet eine Historisierung der Stammdaten statt. Hierzu versioniert man di Stammdaten in Abhängigkeit von Zeitbereichen (Gültig_von, Gültig_bis). o
Die Historisierung wird meist in dieser Schicht angelegt, weil sie über die Lebensdauer des Data Warehouse hinweg bestehen bleibt. In der Data Mart -­‐ Schicht kann es eine alternative oder ergänzende Historisierung geben. •
Die Informationen liegen auf einem sehr granularen Level vor, damit die Daten leicht in neue Informationszusammenhänge überführt werden können. •
Die Modellform geht in Richtung der 3 NF. •
Die Stammdaten sind mit MDM-­‐System synchronisiert. 16
Anwender erwünscht! Keine Angst vor Security und schlechter Abfrage-­‐Performance In manchen Data Warehouse Systemen werden die Endbenutzer bewußt aus dem dem zentralen Enterprise Information Layer ferngehalten. Gründe sind Security und Angst vor Performance-­‐Engpässen. Bei dieser Vorgenehensweise bleiben Chancen ungenutzt: •
Benutzer können die vorgehaltenen Stamm-­‐ und Referenzdaten als zentrales Nachschlagewerk nutzen. Klassisch wird ein Data Warehouse für Analyse-­‐Zwecke, als Berichtessystem und Ansammlung von Kennzahlen verwendet. Es sind aber auch unternehmensweit standardisierte Stammdaten und zusätzliche Referenzdaten enthalten. Warum sollten diese Informationen nicht als eine Art Unternehmensglossar genutzt werden? Diese Verwendung würde das Data Warehouse zusätzlich aufwerten. •
Große Tabellen sollt man nicht mehrfach vorhalten. Wenn 10% der Tabellen 70% des Speicherplatzes fordern, dann liegt das auch daran, dass wenige große Tabellen auch noch doppelt vorgehalten werden, nämlich in dem zentralen Enterprise Information Layer und im User View Layer (Data Marts). Große Tabellen sollte man nur einmal und zwar im Enterprise Information Layer aufbewahren und sie dann aus den Data Marts heraus als Fakten-­‐Tabellen referenzieren. Das Gegenargument „Performance“ ist nicht stimmig. Letztlich ist es gleichgültig, ob Anwender ihre Abfrage in dem zentralen Information Layer oder in einem Data Mart absetzen. Das Argument wäre nur dann stimmig, wenn sich die User View Layer (Data Marts) in einer separaten Hardware – Infrastruktur befinden würden. Aber genau das sollte verhindert werden, um möglichst viele Synergien durch eine integrierte System-­‐Infrastruktur zu erzielen. Security-­‐Aspekte in der Data Warehouse Schicht (Enterprise Information Layer) Auch das Security-­‐Argument führt in die Irre: Wer Endbenutzer aus Security-­‐Gründen nicht in der Data Warehouse-­‐Schicht lesen lässt, schafft Datenzugriffssicherheit, indem er die Daten in die Data Marts (mit einem separaten Datenbank-­‐Schema) kopiert, in denen Benutzer dann lesen dürfen. Das bedeutet zusätzlichen Plattenplatz, zusätzliche ETL-­‐Läufe, zusätzlichen Maintenance-­‐Aufwand und vorallem zusätzliche Latenzzeit. D. h. die Berichteerstellung wird teurer und dauert länger. In der zentralen Data Warehouse-­‐Schichten liegen Daten aus unterschiedlichen Sachgebieten und unterschiedlichen Verantortungsbereichen eng beieinander. Diese integrierende Funktion ist der eigentliche Zweck dieser zentralen Schicht als Enterprise Information Layer. Es liegen daher besondere Security-­‐Anforderungen vor, weil nicht jeder Daten aus den unterschiedlichen Bereichen sehen darf, z. B. muss ein Vertriebsleiter Nord nicht auch die Daten des Vertriebsleiter Süd sehen und umgekehrt. In gut gepflegten Data Warehouse-­‐Systemen sind auch unternehmensweit sensible Daten vorhanden, wie etwa die Entwicklung de Umsatzzahlen oder Einkaufszahlen, Gewinnspannen etc.. Mit wenigen Lesezugriffen kann man bereits sehr viel über den Zustand aber auch die Strategie eines Unternehmens erfahren, zumal die Daten in einer sprechenden und gut dokumentierten Form vorliegen, so dass auch unternehmensfremde Personen sich orientieren können. Durch das Kopieren von Daten in ein separates Schema verlagert man die Thematik nur an eine andere Stelle. Security-­‐Anforderungen sollten nicht in dieser Weise, sondern durch einen Objekt-­‐bezogenen Schutz realisiert werden. D. h. man definiert Benutzerrollen bzw. Profile in denen die Nutzung bestimmter Daten in der Data Warehouse-­‐Schicht erlaubt oder eingeschränkt wird. Durch die Verwendung des Label Security Features der Oracle Datenbank kann dieser Schutz sogar noch auf Zeilenbereiche innerhalb der Tabellen ausgeweitet werden. Endbenutzer wählen sich dann unter den entsprechenden Profilen an. 16
MDM Master Data Management. Standardisierung von Produktnamen, Orten, Namen 26 Für einige Business Ingtelligence Tools kann diese Vorgehensweise eine Herausforderung darstellen, wenn sich diese Tools mit nur einem zentralen Benutzerprofil an der Datenbank anmelden. Diese Werkzeuge haben ihr eigenes Security-­‐Mangement ausserhalb der Datenbank gelöst, was ansich problematisch ist. Security-­‐Massnahmen sollten so dicht wie möglich an den Objekten vollzogen werden, um auch hier 17
möglichst viele Synergien zu nutzen . Spätestens, wenn Excel-­‐Benutzer auf Data Warehouse-­‐Daten zugreifen wollen, muss man schon wieder zusätzliche Security-­‐Mittel eingebauen. Meist stellt man eine separate Datenmenge nur für Excel-­‐Benutzer bereit – mit dem entsprechenden Mehraufwand. Für einige Datenbank-­‐Administratoren erscheint die vorgeschlagene Vorgehensweise des Objekt-­‐bezogenen Schutzes als zu aufwendig, denn sie müssen Profile pflegen und das ist sicherlich aufwendiger, als eine komplette Data Warehouse – Schicht einfach zuzusperren. Und sie müssen ihre Daten kennen, Sie müssen wissen welche Daten welchen Schut benötigen usw.. Viele Datenbank-­‐Administratoren kennen aber nicht die Inhalte der Tabellen. Ziel sollte jedoch nicht die bequeme Verwaltung sein, sondern die Nützlichkeit des Systems für die Endanwender und ein ressourcen-­‐effizientes System. Verhindern von Datenchaos: Management von Informationen in der Data Warehouse Schicht An die Data Warehouse-­‐Schicht (Enterprise Information Layer) werden ganz besondere Ansprüche gestellt: Die in ihr gesammelten Daten sind nicht nur von einer besonderen Datenqualität geprägt. Es ist diese zentrale Data Warehouse Schicht, die die Anforderung nach „Single-­‐
Point-­‐Of-­‐Truth“ lösen muss. D. h. jede Information darf nur einmal vorhanden sein, damit nicht zwei Aussagen aufeinander treffen, von denen mindestens ein dann falsch sein muss. Durch den Normalisierungsschritt bei dem Übergang von Stage in die Data Warehouse – Schicht hat man schon eine gute Grundlage hierzu gelegt. Aber die Anforderung nach Einmaligkeit einer Information ist damit noch nicht gelöst. Die Datenmodellierung löst die Datenobjekte nur nach ihrer Struktur auf. Sie spaltet Informationseinheiten bis auf ihre kleinsten Einheiten so weit auf, bis man sie nicht mehr weiter spalten kann, ohne dass sie ihren Sinn verlieren. Die Datenmodellierung hat aber keinen Einfluss auf die Inhalte der Daten, oder die Namen/Bezeichner der Dinge und Sachverhalte, die beschrieben werden. Und das kann schwerwiegende Folgen haben: Verhindert wird nicht, dass eine bestimmte Information mehrfach unter geänderten Namen vorkommen kann. Man spricht von Homonymen und Synonymen. Ein und dieselbe Information kann unter unterschiedlichen Bezeichnern vorgehalten werden (Kunde/Partner, Artikel/Produkte/Waren/Dienstleistungen/Angebote, Wert/Summe/Betrag usw.). Ein Begriff kann an verschiedenen Stellen genutzt werden, steht aber eigentlich in einem anderen Kontext (Konto/Konto). Diese Problematik ist heute eine der größten Herausforderungen vor allem von großen Data Warehouse-­‐Systemen, denn es sind nicht nur „unglücklich“ gespeicherte Daten sondern: •
•
•
•
Die erstellenden ETL-­‐Prozesse verursachen redundanten Ressourcen-­‐Verbrauch. Es entstehen redundante Berichte und Kennzahlen. Die Inhalte lassen sich nicht sauber abgrenzen. Es entstehen falsche Berichte und Aggregationen sowie Kennzahlenberechnungen. Es entsteht ein unnötig hoher Personalbedarf für die Verwaltung von Ungereimtheiten und Fehlern sowie dem Suchen nach den richtigen Informationen. Diese Situation kann den Nutzen eines Data Warehouse Systems erheblich schmälern. In einigen Fällen sind durch diese unkoordinierte Verwendung von Inhalten mehrjährige Investitionen im Millionen-­‐Umfang gefährdet. Die Ursachen liegen meist in der unkoordinierten Weiterentwicklung des Data Warehouse Systems. Eventuell wurden zusätzliche Vorsysteme für das Data Warehouse erschlossen und in diesen Vorsystemen hat man Sachzusammenhänge durch andere Begriffe bezeichnet, als dies in den bereits erfassten Vorsystemen der Fall war. Unbemerkt schleichen sich synonyme Begriffe für gleiche Sachzusammenhänge ein. In manchen Systemen kann es 3, 4 und mehr synonyme Begriffe für gleiche Schzusammenhänge geben. H o m o n ym en / Syn on ym en -­‐ Pro b lem a tik a ls H a u p tu rsa ch e fü r „D a ten ch a o s“ Gelöst wird die Problematik durch eine Reihe von zusätzlichen Maßnahmen in der Data Warehouse -­‐ Schicht bzw. bei dem Übergang von der Stage -­‐ in die Kern-­‐Warehouse-­‐Schicht. Diese Maßnahmen können im weitesten Sinn auch als Data Governance-­‐Maßnahmen bezeichnet werden. Die Aufgabenstellung und die damit verbundenen Lösungen sind alt, der Begriff Data Governance wird heute aber breit diskutiert. Die Datenablage muss in einer besonders systematisierten Art und Weise nach semantischen Gesichtspunkten erfolgen. Die Tabellen in einem Data Warehouse entsprechen den Entitäten in einem logischen Datenmodell. Dort gilt: Entitäten unterscheiden sich nur durch ihre Attributierung. 17
Wenn mehrere Business Intelligence-­‐Werkzeugfe im Unternehmen genutzt werden, muss man u. U. Security-­‐Zugriffe mehrfach verwalten. 27 Also sie unterscheiden sich nach Menge und Art der beschreibenden Eigenschaften. Die Attribute der Entitäten entsprechen im physischen Datenbankmodell den Columns. Bei der Suche nach gleichen Informationen müssen also alle Columns betrachtet werden. In einem ersten Schritt hilft eine alphabetisch sortierte Liste aller Column-­‐Namen in einem Datenbankschema in Verbindung mit den Tabellennamen. Darüber findet man, dass bestimmte Spalten in mehreren Tabellen vorkommen. Mit diesem ersten Indiz können diese so gefundenen Tabellen näher auf Gleichheit untersucht werden. Das hilft nur bedingt, weil auch die Column-­‐Namen unterschiedlich formuliert sein können, obwohl sie vieleicht gleichen Inhalt haben. Eine tiefer gehende Anlayse zerlegt die Spaltennamen in mögliche Wortteile (Wortstamm-­‐Analyse). Hierüber kann man die eigentlichen Hauptwörter in einem Spaltennamen herauslösen. z. B. Verrechnungskontonummer  Verrechnungs|Kontonummer  Verrechnungs|Konto|nummer
Damit hat man Begriffe gefunden, nach denen man die Column-­‐Namensliste neu sortieren kann. Begriffe wie Nummer kommen als Wortbestandteil in anderen Spaltennamen immer wieder vor. Nimmt man dieses Wort als führende Sortierbegriff, so können neue Ähnlichkeiten zu anderen Spaltennamen auftauchen z. B.: Nummer Buchung Konto
Nummer Verrechnung Konto
Auch das hilft nur begrenzt, weil auch die Verwendung einzelner nicht zusammengesetzter Wörter standardisiert werden muss: Ein weiterer Schritt ist die Standardisierung und Festschreibung von Kernbegriffen, die immer wieder in Verbindung mit anderen Begriffe zu Spaltennamen zusammengesetzt werden (z. B. Nummer, Konto, Wert). Dabei handelt es sich um eine allgemeine Beschreibung der Form wie etwa: Nummer: numerischer Wert, geeignet als Sortierkriterium
Klassifizierung von Informationen Das ausgereifteste Verfahren zur eindeutigen Bestimmung der Inhalte von Spaltennamen ist jedoch das Klassifizierungsverfahren. Hierfür definiert man sich zunächst wesentliche Merkmale, die zur Bestimmung eines Spalteninhalts geeignet sind (Beschreibungs-­‐Klassen). Meist sind das unterschiedliche Betrachtungsweisen, von unterschiedlichen Benutzergruppen oder Herleitungsverfahren oder Verwendung der Begriffe. Damit diese Beschreibungsweisen nicht auch wieder zufällig ausfallen können, legt man zu jedem Merkmal eine Reihe von gültigen Begriffen fest, von denen man einen für jedes Merkmal zur Beschreibung des Begriffes auswählt. Beschreibungsmerkmal/Klasse
[gültiger Wert | gültiger Wert | gültiger Wert |...........]
Herkunft der Information
[System X | Extern | Kundeninformation |gekaufte Inforamtion..]
Art der Entstehung
[Berechnung |Festlegung | ausgelesen aus System | Befragung.. ]
Fachlicher Hintergrund
[Buchungsvorgang | Rechnungswesen | Bestandswesen ....]
Kontext der Verwendung
[Rechenfeld | Beschreibungsfeld | ...]
Art und Weise des Umgangs
[Messwert | allgem. Infofeld | Feld zur freien Verwendung ]
Format
[Zahl
| Text | Datum ....]
Alle Spalten des Systems müssen mit diesem Verfahren klassifiziert werden. Je nach Schärfe der Klassenbegriffe kann man so auch ähnliche Spalten finden, die völlig unterschiedliche Namen tragen. Über die Klassifizierungsbegriffe kann man neue sortierte Listen erstellen und darüber auch nach ähnlichen bereits bestehenden Spaltennamen suchen. Wird jetzt ein zusätzliches Vorsystem an das Data Warehouse angebunden, so kann man in dem bestehenden Spaltennamen-­‐Vorrat nach berreits vorhandenen Datenstrukturen in dem Data Warehouse suchen und vermeidet synonyme Einträge. Hilfsmittel: Dokumenation der Informationen Zur Verwaltung dieser Beschreibungen und Klassifizierungen definiert man sich Verwaltungstabellen. Z. B. eine Tabelle mit Tabellennamen und eine mit Columnnamen. Weil Columns in einem n zu n Verhältnis in Tabellen vorkommen hilft eine Verbindungstabelle, in der die Verwendung der Columns in den Tabellen dokumnetiert wird. In der Tabelle für die Column-­‐Namen kann man als Spalten die oben vorgestellten Klassenbegriffe nutzen und dort dann die definierten Beschreibungswerte aufnehmen. Die Tabelle mit den Tabellennamen kann zusätzliche Spalten enthalten für: •
•
•
•
•
•
Aliasnamen und synonyme Begriffe Fachliche Definition (Kurzform) Fachliche Beschreibung (Langform) Klassifizierende Begriffe, die als Suchbegriffe nutzbar sind (unterschieden nach technischen und fachlichen Aspekten) Verwaltungsdaten o Wer ist für den Inhalt verantwortlich o Abgestimmter Inhalt Datenqualitätsaussagen o Formulierte Erwartungen 28 o
Grad der Erfüllung der Erwartungen Auch alle in dem Data Warehouse genutzten Tabellen sind sortiert nach Sachgebieten und entsprechend ihrer Funktion als Stammdaten-­‐, Referenz-­‐ bzw. Bewegungsdaten-­‐Information beschrieben. Es entsteht eine Art eine Inventarliste zu den Informationen in dem Data Warehouse mit über Klassenbegriffen definierten Suchpfaden. Anstelle dieser sehr spartanischen Lösung kann man natürlich besser ein Metadaten-­‐Repository nutzen. Hier kann man dann auch wahlfrei 18
weitere Beschreibungen aufnehmen . Die Dokumentation zu den Informationen in einem Data Warehouse beschränkt sich nicht nur auf die zentrale Warehouse-­‐Schicht, sondern wird z. B. auch die Dimensionen und Fakten in den Data Marts beinhalten. Man dokumentiert auch die Ableitungsvorgänge der Informationen in den Data Marts aus den Informationen der Warehouse-­‐Schicht, also die ETL-­‐Schritte. Das sieht sehr aufwendig aus, und viele sehen den Sinn nicht ein. Es ist aber der einzige Weg, um Struktur und Systematik in die Dateninhalte des Data Warehouse zu bekommen. Auf der anderen Seite ist man bereit für redundante Datenmengen, redundante Berichte und redundante ETL-­‐Läufe erhöhte Summen für Hardware-­‐ Erweiterungen und Verwaltung zu bezahlen und kann Endbenutzern dennoch keine klare Informationsstruktur anbieten. Einige Unternehmen denken bereits um und führen wieder die Rolle des Datenadministrators ein. Eine Rolle die in der Zeit der zentralen Mainframe-­‐Systeme in den 1980er eine Selbstverständlichkeit war. Ein Datenadministrator übernimmt genau die oben beschriebenen Aufgaben. B eisp ielstruktu r fü r ein e D oku m en ta tio n vo n kla ssifizieren d en B esch reib un gen fü r Spalten u nd T ab ellen in einem D ata W arehouse zur V erm eidung von unerkannten inhaltlichen Ü berschneidungen 18
Solche Metadaten-­‐Repository-­‐Tools kann man kaufen oder man nutzt das in diesem Text weiter unten vorgestellte Repositoy-­‐
Framework, das für diese Aufgabenstellung völlig ausreicht und kostenfrei zu beziehen ist. 29 Organisation der Daten in Tabellen und Partitionen
Das Partitionieren großer Tabellen gehört mit zu den wichtigsten Hilfsmitteln des Oracle Data Warehouse. Es teilt die Daten großer Tabelle in physikalisch separierte Mengen und spricht diese Teilmengen separat an. Dadurch müssen bei entsprechenden Abfragen nur die Partitionsdaten aber nicht die gesamte Tabelle gelesen werden, so dass eine bessere Abfrageperformance entsteht. Zum anderen schafft es durch die kleineren Datenpakete Verarbeitungseinheiten für die Verwaltung des Data Warehouse. Es bleibt jedoch die Gesamtsicht auf alle Daten erhalten. Pa rtitio n ierte Ta b ellen h a b en m eh rere Sich ten . D ie Ta b elle ka n n a ls g a n zes o d er b ezo g en a u f ein zeln e Pa rtitio n en a n g esp ro ch en w erd en . Data Warehouse – Systeme haben genau mit diesen Herausforderungen zu kämpfen. Trotz der rasanten Weiterentwicklung der Hardware wachsen einzelnen Data Warehouse – Tabellen in den Bereich von vielen Milliarden Sätzen an und sollten physikalisch partitioniert, d. h. in Teilmengen behandelt werden können. Auf der anderen Seite wird meist nur ein Teil der Daten für bestimmte Arbeiten benötigt (die letzte Ladeperiode, der letzte Monat, das letzte Quartal usw.). Der größte Teil der Warehouse-­‐Daten liegt über große Zeitspannen hinweg brach. Daher ist es nicht ganz passend den Nutzen des Partitioning-­‐Features der Datenbank nur auf Performanceverbesserung zu reduzieren. Mindestens ebenso wichtig ist die Möglichkeit Tabellen in Teilmengen aufzuspalten, um mit diesen Teilmengen unterschiedliche Maßnahmen im Rahmen der Warehouse-­‐Verwaltung durchzuführen: •
•
•
•
•
•
•
Beispiel ILM: Bei dem ILM-­‐Verfahren (Informatione Life Cycle Management) peichert man unterschiedlich alte und unterschiedlich häufig genutzte Daten auf verschieden teure, schnelle und ausfallsichere Platten. Während traditionelle 19
20
Speichermittel (Plattensysteme) immer günstiger geworden sind, ist ihre Performance gleich geblieben . SSD-­‐Platten sind heute erschwinglich geworden und können partiell Spindel-­‐Festplatten ersetzen. Sie sind allerdings immer noch zu teuer, um z. B. 50 Terabyte große DWH-­‐Systeme zu versorgen. Aber die aktuellen Daten einer mit historischen Daten gefüllten Fakten-­‐Tabelle können durchaus auf einzelnen SSD-­‐Platten gelagert werden. Das Partitionieren ermöglicht dies, durch das Zuweisen unterschiedlicher Datenträger zu den unterschiedlichen Partitionen. Beispiel Backup: Die meisten Daten eines Data Warehouse Systems ändern sich nicht mehr, nachdem sie einmal gespeichert wurden. Und das ist oft über Jahre hinweg so. Deswegen muss man auch nur die immer wieder aktuell hinzugekommenen Daten sichern. Großen Tabellen lassen sich durch Partitioning leicht aufteilen in Partitionen, die sich geändert haben und solche, die nur 21
„ältere“ Daten beinhalten und nur diese sind mit RMAN zu sichern. Beispiel ETL: Mit Partitioning hat man immer einen leichten Zugriff auf die Daten einer Ladeperiode. Wählt man für die neu zu ladenden Daten einer Ladeperiode eine Partition so kann man diese schnell zu einer Warehouse-­‐Tabelle hinzufügen oder im Fehlerfall wieder entfernen. Beispiel Sichtbarkeit: Für spezielle Analysezwecke mag es sinnvoll sein, nur bestimmte Zeitperioden, Sachgebiete etc. in einem Bericht zu berücksichtigen. Bevor man Abfrage-­‐Statements in BI-­‐Tools modifiziert, kann es einfacher sein, solche Daten durch Ein-­‐ und Ausblenden von Partitionen in Sekundenschnelle „sichtbar“ oder „unsichtbar“ zu machen. Beispiel Indizierung: Partitionen lassen sich separat von einander indizieren (local Index). Damit erspart man sich das zeit-­‐ aufwendige Aktualisieren eines kompletten Index. Beispiel Verfügbarkeit: Aktive ETL-­‐Prozesse beanspruchen Tabellen oft so stark, so dass Endanwender nicht immer einen 22
uneingeschränkten Zugriff auf die Daten haben . Geschickter ist es Tabellendaten zu einem definierten Zeitpunkt entweder komplett aktualisiert oder noch in dem Zustand vor der Aktualisierung dem Endanwender zu präsentieren. So lange ein ETL-­‐Lauf die Daten noch ändert, kann man die Partitionen, die von solchen Änderungen betroffen sind, noch zurückhalten und erst dann veröffentlichen, wenn der ETL-­‐Lauf beendet ist. Während des ETL-­‐Laufs können die Anwender die nicht von dem ETL-­‐Lauf betroffenen Daten benutzen. Beispiel Komprimierung: Oracle bietet unterschiedliche Komprimierungsvarianten an. Mit Partitioning können je nach Bedarf Daten derselben Tabelle unterschiedlich komprimiert werden. Partitioning ist daher in den meisten Warehouse-­‐Systemen ein Muss! 19
Schnelle Platten erreichen max 15000 Umdrehungen pro Minute. Damit ist das physikalische Limit erreicht. Höhere Performance-­‐Werte sind nur noch über eine größere Schreibdichte auf den Platten erreichbar. Das Steigerungspotential dieser traditionellen Spindeltechnik ist allerdings begrenzt. 20
SSD Solid State Drive, Festkörperspeicher 21
RMAN: Recovery Manager, kostenfreier Bestandteil der Oracle-­‐Datenbank zum Sichern von Daten 22
Oracle erlaubt zwar gleichzeitiges Lesen und Schreiben auf einer Tabelle. Durch das häufige Nachlesen von bereits durch den ETL-­‐Prozess geänderten Blöcken kommt es jedoch zu Zeitverzögerungen. Zudem entstehen verzerrte Daten, da zu unterschiedlichen Abfragezeitpunkten auch unterschiedliche Ergebnisse geliefert werden. 30 Partitioning-­‐Struktur und Varianten Ein wichtiges Merkmal des Partitioning ist der Partition-­‐Key. Das ist das Feld in der Tabellenstruktur über dessen Inhalt das System die Zuordnung von Sätzen zu einer bestimmten Partition festlegt. Damit ist ein sehr praktisches Feature des Partitioning benannt: Es ist das System, das bei einem INSERT einen Satz automatisch in die 23
passende Partition aufnimmt. Derjenige, der den ETL-­‐Prozess steuert, muss sich also darum nicht kümmern. A u to m a tisch es V erteilen d er d u rch d en E T L-­‐Pro zess g elieferten n eu en Sä tze a u f d ie Pa rtitio n en ein er T a b elle Der Umgang mit dem Partition Key wird durch die Partition Variante (Range, List, Hash) bestimmt. Range Partitioning 24
Die am häufigsten genutzte Partitioning-­‐Variante ist das Rang-­‐Partitioning bei dem die Einteilung der Partitionen entlang definierter Wertebereiche erfolgt. Das im DWH am häufigsten genutzte Kriterium ist die Zeit (z. B. Tage, Monate), also ein Feld mit einem Datumswert. CREATE TABLE F_bestellung_part_range (
SUMME
NUMBER(14,0),
MENGE
NUMBER(14,0),
BESTELLDATUM
DATE,
FK_ARTIKEL_ID
NUMBER,
FK_KUNDEN_ID
NUMBER,
FK_ORT_ID
NUMBER,
FK_DATUM_ID
NUMBER,
auftragsart
VARCHAR2(30))
PARTITION BY RANGE (bestelldatum) (
PARTITION jan11 VALUES LESS THAN (TO_DATE('2011-02-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL,
……
PARTITION feb12 VALUES LESS THAN (TO_DATE('2012-03-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL,
PARTITION next_month VALUES LESS THAN (MAXVALUE) TABLESPACE DWH_SPINDEL);
Aber auch zusammengesetzte Felder oder über Funktionen ermittelte Werte, sowie nach dem Alphabet sortierte Werte können ein Partitionierungskriterium sein. CREATE TABLE kunde_part_range_alpha (
kundennummer NUMBER,
vorname
VARCHAR2(20),
kundenname
VARCHAR2(40) )
PARTITION BY RANGE (kundenname) (
PARTITION kunde_ae VALUES LESS THAN ('F%') TABLESPACE part_range1,
PARTITION kunde_fl VALUES LESS THAN ('M%') TABLESPACE part_range2,
…..
Wird nach Zeit partitioniert so muss in der WHERE-­‐Klausel einer Abfrage das Partitionierungsschlüsselfeld abgefragt werden. Wird nach Monaten partitioniert, dann muss dann muss nach einem Datum abgefragt werden. Wenn das der Fall ist, wird nur die Partition physisch gelesen, in der das Datum zu finden ist (Partition Pruning). Zum Glück verfügt ein großer Teil der Benutzerabfragen über solche zeitlichen Einschränkungen, denn das Data Warehouse ist meist historisch aufgebaut und es interessiert oft ein Blick in die Vergangenheit. Kann kein Zeitkriterium mitgegeben werden, dann muss das System die komplette Tabelle lesen. List Partitioning Die Variante List-­‐Partitionierung ordnet Daten aufgrund der Zugehörigkeit zu definierten Werten oder Begriffen. Die Einteilung nach Verkaufsgebieten „Nord“, „Sued“, „West“, „Ost“, „Mitte“ ist ein Beispiel. CREATE TABLE bestellung_part_list (
bestellnr
NUMBER(10) NOT NULL,
kundencode
NUMBER(10),
bestelldatum
DATE,
23
Das ist besonders hervorgehoben, weil das Verhalten bei vergleichbaren Lösungen nicht selbstverständlich ist. Auch heute noch kann beobachtet werden, dass ähnliche Lösungen wie das Partitioning manuell gelöst sind, d. h. z. B. mit mehreren unterschiedlich benannten Tabellen. Dann muss man das Einsortieren neuer Sätze ebenfalls manuell im ETL-­‐Prozess steuern. 24
Die Häufigkeit liegt geschätzt 80% 31 lieferdatum
DATE,
bestell_total
NUMBER(12,2),
auftragsart
VARCHAR2(30),
vertriebskanal NUMBER )
PARTITION BY LIST (auftragsart)
( PARTITION produkt VALUES ('ARTIKEL','TAUSCHWARE'),
PARTITION service VALUES ('SERVICE','REISE'),
PARTITION storno VALUES ('RETOURE','KOMMISSION'),
PARTITION andere VALUES (DEFAULT));
Hash Partitioning Nicht immer liegen Partitionierungskriterien vor. Die Hash-­‐Partitionierung überlässt das Partitionieren der Daten der Datenbank. Dies ist dann hilfreich, wenn einerseits Datengruppen gebildet werden sollen und andererseits Daten gleichmäßig über die Datengruppen hinweg zu verteilen sind. Beispiel kann eine große Kundentabelle sein, deren Eindeutigkeitskriterium die Kundennummer ist. Hier kann es schwer fallen, fachliche Kriterien für eine Gruppierung zu finden. CREATE TABLE "F_BESTELLUNGEN_HASH"
"SUMME"
NUMBER(14,0),
"MENGE"
NUMBER(14,0),
"BESTELLDATUM" DATE,
"FK_ARTIKEL_ID" NUMBER,
"FK_KUNDEN_ID"
NUMBER,
"FK_ORT_ID" NUMBER,
"FK_DATUM_ID"
NUMBER)
PARTITION BY HASH (BESTELLDATUM);
(
Das Hash-­‐Partitioning wirkt ähnlich wie ein Index auf das Feld, das als Partitionierungskriterium dient. Wenn in einer Abfrage in der WHERE-­‐Klausel das Partition Key-­‐Feld angesprochen wird, führt das dazu, dass nur die entsprechende Partition gelesen wird. Also auch hier wirkt das Partition Pruning. Nutzt man das Hash-­‐Partitioning alternativ zu einem non-­‐unique Index, so spart man sich die Pflege und den Platzverbrauch des Index. Ein wichtiger Einsatzgrund für Hash-­‐Partitioning ist die Möglichkeit Daten gleichmäßig auf Storage-­‐Einheiten zu verteilen. Hierzu gibt man der Definition der Hash-­‐Tabelle die Namen der Tablespaces mit, hinter denen die entsprechenden Datenträger stehen. Die Anzahl der Tablespaces muss nicht mit der Anzahl der Hash-­‐Partitionen übereinstimmen. V erteilung der Partitionenauf unterschiedliche Sp eich erb ereich e (T a b lesp a ce / D a ta file) Die Laufzeiten bei dem Anlegen von HASH-­‐Tabellen sind wesentlich größer als bei dem Anlegen von Range-­‐partitionierten Tabellen, was verständlich ist, denn das Schreiben von jedem einzelnen Satz muss gesondert bearbeitet werden. Sub-­‐Partitioning Die Varianten lassen sich über Sub-­‐Partitioning mischen, um das Partitionieren noch flexibler zu gestalten. Das grundlegende Partitionierungskriterium kann z. B. eine Monatseinteilung sein (Range) und die Monatspartitionen können noch weiter nach Verkaufsgebieten (List) unterteilt sein. Mit Sub-­‐Partitioning gewinnt man ein zusätzliches fachliches Abfragekriterium wenn man die Kombinationen •
•
•
•
Range/Range, Range/List, List/List List/Range nutzt. Ein einfacher Test zeigt bereits die Performance-­‐Effekte des Partitioning und Sub-­‐Partitioning. Die Beispiel-­‐Faktentabelle wird in 3 Varianten und zwar nicht partitioniert, partitioniert (Range) und sub-­‐partitioniert (Range/List) erstellt: SUMME
MENGE
NUMBER(14)
NUMBER(14)
32 BESTELLDATUM
FK_ARTIKEL_ID
FK_KUNDEN_ID
FK_ORT_ID
FK_DATUM_ID
AUFTRAGSART
DATE
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(30)
Die Beispielabfrage auf die Daten und die Laufzeiten25:
select sum(summe) from F_BESTELLUNG_[xxx] where Auftragsart = 'SERVICE'
and BESTELLDATUM = to_date('10.10.2011','DD.MM.YY');
TABLE_NAME
BLOCKS
GB
Millionen
Zeit
Zeilen
---------------------------------------------------------------------------------------------------------------------------------F_BESTELLUNG_X
2431440
19,45
411
314,3 Sek
F_BESTELLUNG_PART_RANGE_LIST
2463418
19,71
411
5,6 Sek
F_BESTELLUNG_PART_RANGE
2429231
19,43
411
17,3 Sek
Wie deutlich zu erkennen ist, liefert die sub-partitionierte Tabelle die beste Performance. Sie ist mit 5,6 Sekunden deutlich schneller als die
einfache Partitionierung. Die Zeit bei fehlender Partitionierung muss nicht kommentiert werden. Es ist bereits gesagt worden: Partitioning ist
im Data Warehouse bei großen Tabellen ein Muss!
Der Test wurde mit einem Demo-System mit nur einer einzigen Platte durchgeführt. Das erklärt sicher den extremen Unterschied zwischen
der nicht partitionierten und den partitionierten Tabellen. Bei dem Einsatz von mehr Platten würde der Unterschied sicherlich nicht so
gravierend ausfallen. Wobei ein erheblicher Hardware-Aufwand getrieben werden müsste, um in diesem Beispiel auf die entsprechenden
Werte zu kommen.
B eisp iel fü r Su b p a rtitio n in g R A N G E / LIST Partition Wise Join Ein Starschema besteht aus einer Reihe von Joins zwischen meist einer sehr großen Faktentabelle und mehreren kleineren Dimensionstabellen. Ist die Faktentabelle partitioniert, so kann eine Abfrage mit einem Join zwischen Fakten-­‐ und Dimensionstabelle in mehrere kleine Joins zergliedert werden. Für jede Partition der partitionierten Fakten-­‐Tabelle wird ein separater Join zur Dimensionstabelle gebildet (Partial Partition Wise Join). Ist die Dimensionstabelle ebenfalls partitioniert und zwar nach dem gleichen Partitionierungsschlüssel wie die Faktentabellen, so werden die einzelnen „Teil-­‐Joins“ zwischen den jeweils passenden Partitionen gebildet (Full Partition Wise Join). Dieses Vorgehen findet auch bei Sub-­‐Partitioning statt. Bleibt die Frage nach sinnvollen Einsatzgebieten. Weiter unten wird das Thema Star Query Transformation vorgestellt. Das ist bereits eine effektive Methoden, um komplexere Joins im Star Schema performant abzufragen. Das Partition Wise Join ist dagegen bei einem Join 26
zwischen nur zwei Tabellen sinnvoll . In vielen Anwendungen sind die Dimensionen im Vergleich zur Fakten-­‐Tabelle sehr klein, so dass die Dimensions-­‐Tabellen selbst kaum partitioniert sind. Es wird zu einem „Partial Wise Join“ kommen. Eine Ausnahme bilden große Dimensions-­‐Tabellen wie Kunden-­‐ oder Produkt-­‐Dimension bei einigen Firmen mit mehreren Hunderttausend oder Millionen von Sätzen. In diesem Beispiel tritt aber das Problem auf, dass die Dimension-­‐ und Faktentabellen nach unterschiedlichen Schlüsseln partitioniert sind. I. d. R. ist die Fakten-­‐Tabelle nach Zeit und die Dimensions-­‐Tabelle nach dem jeweiligen Geschäftsobjekteschlüssel (z. B. Kundennummer oder Produktnummer) partitioniert. Eine pragmatische Anwendung ist daher die Partitionierung der Faktentabelle nach Zeit (Range) und eine Subpartitionierung nach einem Wert der auch in der Dimensions-­‐Tabelle als Partitionierungskriterium dienen kann (Range/List). Das sollte dann auch wieder eine Range-­‐ oder List-­‐Partitionierung sein. 25
Der Test wurde auf einem einfachen Laptop mit simulierten 4 Kernen (CPU), 8 GB RAM und einer (!) Platte durchgeführt. 26
Die Star Query Transformation setzt eine komplexere Abfrage mit Joins zwischen Fakten-­‐Tabellen und mindestens zwei Dimensionstabellen voraus, wobei mehrere Bitmap-­‐Indexe auf den Foreign Key-­‐Feldern der Fakten-­‐Tabelle sein müssen. 33 Auch hier ein Performance-­‐Test um die Relationen zu erkennen. Als Fakten-­‐Tabelle dient wieder die Tabelle F_BESTELLUNG_PART_RANGE_LIST. Sie ist nach Zeit Range-­‐partitioniert und hat eine Sub-­‐Partition mit List. Sie umfasst 400 Millionen Sätze (~20 GB) und ist über einen Join mit einer zweiten Tabelle verbunden, die etwa 8 Millionen Sätze umfasst (~300 MB) und einmal nicht partitioniert ist, ein zweites Mal nach dem gleichen Schlüssel List-­‐partitioniert ist, wie die Sub-­‐Partition der Fakten-­‐Tabelle. Die Tabellen Auftragsart und Auftragsart_PART_LIST haben die Struktur: AUFTRAGSART_NR
AUFTRAGSART
BESCHREIBUNG
VERANTWORTLICH
ABTEILUNG
NUMBER
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
VARCHAR2(50)
Die Abfrage: select sum(b.summe),a.auftragsart, a.beschreibung
from
F_BESTELLUNG_PART_RANGE_LIST b,
AUftragsart a
where b.Auftragsart = 'TAUSCHWARE'
and b.BESTELLDATUM = to_date('10.11.2011','DD.MM.YY')
and b.Auftragsart = a.Auftragsart
group by a.auftragsart,a.beschreibung;
Die Tabellen und das Abfrageergebnis: Join zwischen TABLE_NAME
BLOCKS
GB
Millionen
Zeit
Saetze
---------------------------------------------------------------------------------------------AUFTRAGSART
36617
0,29
8
13 Sek
F_BESTELLUNG_PART_RANGE_LIST
2463418
19,71
411
na
---------------------------------------------------------------------------------------------AUFTRAGSART_PART_LIST
37001
0,3
8
5 Sek
F_BESTELLUNG_PART_RANGE_LIST
2463418
19,71
411
na
---------------------------------------------------------------------------------------------AUFTRAGSART
36617
0,29
8
270 Sek
F_BESTELLUNG_X
2431440
19,45
411
na
Über diesen einfachen Test kann man bereits sehen, wie effektiv die Partitioning-­‐Technik auch bei großen Tabellen wirkt. Als Vergleich wurde die Abfrage noch völlig ohne Partitioning (F_BESTELLUNG_X) durchgeführt. Dieses Ergebnis muss jedoch nicht mehr kommentiert werden. Reference -­‐ Partitioning Hängen zwei Tabellen über eine Parent/Child-­‐Beziehung (Foreign-­‐Key Constraint) zusammen, so kann man mit dem Reference Partitioning die vorher aufgezeigten Vorteile des Partition Wise Join auf Tabellen-­‐Paare übertragen, bei denen die abhängige Tabelle nicht über den gleichen Partitionierungsschlüssel verfügt. Um diesen Effekt zu erreichen, musste man in den Datenbank-­‐Releasen vor Oracle 11 noch das Partitionierungskriterium der Parent-­‐Tabelle mit in die Child-­‐Tabelle nehmen, auch wenn dies fachlich und aus Sicht der Datenmodellierung wenig Sinn machte. CREATE TABLE bestellung_part_range_ref (
bestellnr
NUMBER(10) NOT NULL,
kundencode
NUMBER(10),
bestelldatum
DATE,
lieferdatum
DATE,
bestell_total
NUMBER(12,2),
auftragsart
VARCHAR2(30),
vertriebskanal NUMBER )
PARTITION BY RANGE (bestelldatum)
PARTITION dez09 VALUES LESS THAN (TO_DATE('2010-01-01', 'SYYYY-MM-DD')) TABLESPACE part,
….
);
ALTER TABLE bestellung_part_range_ref ADD CONSTRAINT pk_bestellnr_part_range_ref PRIMARY KEY (bestellnr);
CREATE TABLE best_position_part_range_ref (
posnummer
NUMBER(10) NOT NULL,
artikelnr
NUMBER(10) NOT NULL,
bestellnr
NUMBER(10) NOT NULL,
bestellmenge NUMBER(10),
CONSTRAINT fk_bestellnr_part_range_ref FOREIGN KEY (bestellnr) REFERENCES bestellung_part_range_ref
(bestellnr) )
PARTITION BY REFERENCE (fk_bestellnr_part_range_ref);
34 B eisp iel R eferen z-­‐Pa rtitio n in g (V erb in d u n g zw eier Fa kten ta b ellen ) Einen praktischen Einsatz im das Data Warehouse ist das zusammenhängende Partitionieren von zwei Fakten-­‐Tabellen. In manchen Situationen ist es sinnvoll, Fakten-­‐Tabellen mit unterschiedlicher Granularität anzulegen. Diese sind dann über eine Foreign-­‐Key-­‐Beziehung miteinander verbunden. Eine solche Struktur müsste jedoch in einer besonderen Art und Weise abgefragt bzw. z. T. über Views vorselektiert werden. Interval Partitioning Wenn man das Partitioning zur ETL-­‐Prozess-­‐Unterstützung nutzt, wird man für die entsprechenden Ladeperioden immer neue Partitionen 27
anlegen müssen. Der Administrator sorgt normalerweise vor, indem er auch für künftige Ladeperioden bereits Partitionen anlegt. Wenn er es jedoch vergisst oder in den Ladedaten Sätze enthalten sind, die weit in die Zukunft weisen, dann wird es zu einem Fehler führen und die ETL-­‐Prozesse können abbrechen. Interval Partitioning beugt dieser Situation vor, indem das System automatisch Partitionen anlegt, wenn Werte nicht in den vorgegebenen Range-­‐Partition-­‐Grenzen untergebracht werden können. Man muss dem System eine Vorschrift mitgeben, nach der es neue Partitionen bilden kann. In dem folgenden Beispiel legt das System Partitionen mit der Größes eines Monats an. Die entsprechende Klausel lauten INTERVAL(NUMTOYMINTERVAL(1,'MONTH'). CREATE TABLE "BESTELLUNG" (
"BESTELLNR" NUMBER(10) NOT NULL,
"KUNDENCODE" NUMBER(10),
"BESTELLDATUM" DATE,
"LIEFERDATUM" DATE,
"BESTELL_TOTAL" NUMBER(12, 2),
"AUFTRAGSART" VARCHAR2(30),
"ORDER_ID" NUMBER )
PARTITION BY RANGE ("BESTELLDATUM")
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))(
PARTITION "Jan07" VALUES LESS THAN (TO_DATE('2007-01-31 00:00:00','SYYYY-MM-DD HH24:MI:SS')),
PARTITION "Feb07" VALUES LESS THAN (TO_DATE('2007-02-28 00:00:00','SYYYY-MM-DD HH24:MI:SS')));
In dem nächsten Beispiel ist ein numerisches Feld mit Partitionsgrößen von „5000“ definiert. Die Interval-­‐Klausel lautet: Interval (5000). create table MA
(MA_NR number(6),
NACHNAME varchar2(30),
GEHALT number(6))
partition by range(GEHALT)
Interval (5000)
( partition p1 values less than (5000),
partition p2 values less than (10000),
partition p3 values less than (15000),
partition p4 values less than (20000));
Die Datenbank wird synthetische Namen für die neuen Partitionen generieren. Anhand dieser Namen kann man die Grenzwerte der Partitionen nicht erkennen, zumal auch Partitionen angelegt werden, ohne dass die „Zwischenpartitionen“ bereits existieren. Helfen kann man sich indem man sich die Partitionen zusammen mit ihren Grenzwerten anzeigen lässt und die Namen dann ändert. SELECT table_name, partition_name, high_value, num_rows
FROM
user_tab_partitions
ORDER BY table_name, partition_name;
27
Wie man Partitioning für den ETL-­‐Prozess nutzt wird weiter unten bei den ETL-­‐Techniken in der Datenbank erläutert. 35 Verwaltung von Partitionen 28
Partitionen können sehr flexibel verwaltet werden . Möglich sind: •
•
•
•
•
•
•
•
Hinzufügen neuer Partitionen Löschen von Partitionen Zusammenfügen von zwei Partionen zu einer Aufspalten von Partitionen Umwandeln einer Partition in eine einfache Tabelle Überführen einer Tabelle in eine Partition Komprimieren einzelner Partitionen Zuordnung einzelner Partitionen zu bestimmten Tablespaces In den folgenden Kapiteln wird immer wieder auf das Partitioning zurückgegriffen. Partitioning bieten Möglichkeiten •
•
•
•
•
bei dem Refresh von Materilized Views (Partition Change Tracking), bei der Struktur eine Index (local Index), im ETL-­‐Prozessen (Partition Change Tracking), bei der Parallelisierung (es untertützt die Datenbank bei der Festlegung von Datenmengen, die parallel zu bearbeitet werden können) und bei der Verwaltung (RMAN, Bereitstellung neuer Daten usw.) 28
Syntax-­‐Beispielen zu den genannten Varianten sind in der Kurzreferenz zu diesem Text enthalten. 36 Indizierung im Data Warehouse
Der Umgang mit Indexen bei OLTP-­‐ und Data Warehouse Systemen unterscheidet sich deutlich. Während man in OLTP-­‐Systemen Indexe meist zur Beschleunigung von Einzelzugriffen nutzt, kann dies in Warehouse-­‐Systemen sogar schädlich sein, weil es in DWH-­‐Systemen eher um das Bewegen großer und zusammenhängender Datenmengen geht, als um Zugriffe auf einzelne Sätze. In welchen Situationen nutzt man im Data Warehouse Indexe bzw. Schlüssel? Typische Anwendungsfälle sind: 1.
Beim Anreichern von Daten durch Zugriffe auf Referenz-­‐ bzw. Stammdatentabellen. -­‐
Das macht man sowohl bei Prüfvorgängen im Stage-­‐Bereich aber auch -­‐
bei dem Aufbau von Dimensionen für die Data Marts. Hier müssen die Referenztabellen eindeutig sein und benötigen einen Unique-­‐Key. 2.
3.
Bei der Umschlüsselung von operativen Schlüsseln der Vorsysteme hin zu eindeutigen Schlüsseln im Data Warehouse. Hiervon sind meist Referenz-­‐ und Stammdatentabellen betroffen. Zur Beschleunigung von Lesezugriffen. Im Gegensatz zu OLTP-­‐Systemen geht es hier und das Eingrenzen von Wertebereichen in Tabellenspalten. Index-­‐Varianten In der Oracle-­‐Datenbank stehen Btree-­‐und Bitmap-­‐Indexe zur Verfügung. Beide Varianten haben im Data Warehouse eine genau abgegrenzte Aufgabe und Verwendung. Btree-­‐Indexe 29
Btree-­‐Indexe sind für Einzelzugriffe z. B. für Lookup-­‐ und Referenztabellen z.T. notwendig. Fordert ein ETL-­‐Prozess einen Referenzwert an, 30
so muss dieser in der Referenztabelle eindeutig identifizierbar sein . Eindeutigkeit gelingt durch die Definition eines Unique-­‐Keys (eindeutiger Index). Außerhalb dieser Verwendung spielen Btree-­‐Indexe im Data Warehouse kaum eine Rolle. Btree-­‐Indexe speichern für einen bestimmten Wert einer Spalte die dazu passende Speicheradresse (Rowid) der Tabellenzeile, in der der Wert vorkommt. Da es sich hier um sehr viele Werte handeln kann und gleichzeitig die aufwendige Suche in langen Listen vermieden werden muss, sind die Speicherblöcke des Btree-­‐Index in der Form eines auf dem Kopf stehenden Baumes angeordnet. Über drei Ast-­‐
Ebenen hinweg wird die Suchanfrage durch Verfeinern der Bereichsgrenzen letztlich auf den sog. Leafblock mit der RowID, also der Speicheradresse des gesuchten Satzes, gelenkt. Im einfachsten Fall greift die Suchabfrage dabei auf mindestens 4 Speicherblöcke zu. (Bei großen Indexstrukturen auf entsprechend mehr Blocke). Das können auch teure physikalische Festplattenzugriffe sein. In tern er A u fb a u vo n B T R E E-­‐In d exen . M in d esten s vier Lesezu g riffe sind nötig, um einen Satz über einen Indexzugriff zu lesen. Es wird deutlich, dass das Lesen von Sätzen über einen Btree-­‐Index nur bis zu einer bestimmten Anzahl von zu suchenden Sätzen performant ist, denn für jeden Zugriff sind im ungünstige Fall mindestens 4 physische Zugriffe auf die Speicherplatte nötig. Sollen eine größere Anzahl an Sätzen gelesen werden, so kann daher das Lesen der kompletten Tabelle (Full Table Scan) günstiger sein als das Lesen über einen Index. Die Oracle Datenbank steuert diese Entscheidung automatisch. Bitmap-­‐Indexe Bitmap-­‐Indexe sind geeignet, um eine größere Anzahl von Sätzen zu selektieren. Die Performance hängt nicht von der Menge der „Treffer“ ab. 29
Beispielskript bitte in der Quickreferenz nachlesen. 30
Bestimmte ETL-­‐Tools ermöglichen auch die Auswahl eines ersten, n-­‐ten, letzten etc. Wertes einer nicht eindeutigen Spalte in einer Lookup-­‐ bzw. Referenztabelle. Das ist eine gut gemeinte Funktion. Sie kann jedoch zu Fehlern führen, weil das Mittel der Eindeutigkeit nicht mehr zwingend zur Qualitätssicherung eingesetzt werden kann. 37 Für jeden unterschiedlichen Wert in einer Spalte legt das System einen Bitstrom (1 und 0) an. Verfügt eine Spalte über 100 unterschiedliche Werte verteilt auf 10000 Tabellenzeilen, dann legt ein Bitmap-­‐Index 100 Bitströme mit 10.000 gesetzen (1) oder nicht gesetzten Bits (0) für diese Spalte an. Kommt in einer Zeile der entsprechende Wert, zu dem der Bitstrom definiert wurde vor, so erscheint für diesen Satz eine 1 in dem Bitstrom. In dem folgenden Beispiel verfügt die Spalte „Abschluss“ über einen Bitmap Index. Vier unterschiedliche Werte kommen vor (Klasse_10, Abitur, Diplom, Doktor). Für jeden dieser vier Werte gibt es in dem Bitmap Index einen Bitstrom in dem mit der Setzung einer 1 vermerkt ist, ob der Wert in einer Zeile vorkommt. A u fru fb eisp iel ein es B itm a p-­‐In d ex fü r vier W erte d er T a b ellen sp a lte „A b sch lu ss“ Platzbedarf Die Bitmap Index-­‐Vorgehensweise sieht nach aufwendig hohem Platzverbrauch aus. Das täuscht jedoch, denn zum einen handelt es sich um Bits (und nicht um Bytes) und zum anderen sind die Bit-­‐Daten komprimiert gespeichert. Der folgende Versuch mit einer einfachen 100000 Zeilen umfassenden Tabelle zeigt den Speicherplatzbedarf für Btree und Bitmap-­‐Indexe im Vergleich. CREATE TABLE I_Kunde
(KD_NR
number,
Name
varchar2(30),
Geb_Dat
date,
Bildungsgruppe
varchar2(30),
KR_Rating_1_bis_Variabel number);
Die Spalten unterscheiden sich vor allem durch die unterschiedliche Häufigkeit ihrer Werte. Das Feld KD_NR ist eindeutig und hat in jeder Zeile einen anderen Wert. In dem Feld Berufsgruppe sind nur fünf unterschiedliche Werte zu finden. Die Größe und die Häufigkeit der einzelnen Werte kann man z. B. mit folgender Abfrage analysieren: SELECT index_name,index_type, blevel, leaf_blocks, distinct_keys FROM user_indexes;
In einem ersten Versuch legt man einen Btree-­‐Index auf alle Spalten und misst den Platzverbrauch über die Anzahl der „Leaf_Blocks“. In einem zweiten Versuch definiert man Bitmap-­‐Indexe. Spalte
Anzahl
Sätze
Bildungsgruppe (5 Werte)
100000
Bildungsgruppe (100 Werte)
100000
Geb_Dat
100000
KR_Rating_1_bis_Variabe
100000
KD_NR
100000
Distinct
Werte
5
Prozent
Leaf_
Blocks
BTree
Leaf_
Blocks
Bitmap
0.005
271
11
100
0.1
192
34
14575
14.575
265
97
43211
43.211
220
179
100000
100
222
348
Der Platzverbrauch für die Btree-­‐Indexe bleibt für alle Spalten ähnlich hoch. Bei den Bitmap-­‐Indexen erkennt man einen sehr geringen Platzverbrauch für die Spalten mit sehr wenigen unterschiedlichen Werten (5, 100). Erst wenn die Menge der unterschiedlichen Werte etwa die halbe Gesamtzeilenanzahl erreicht, wird der Platzverbrauch des Btree-­‐Indexes erreicht. Wird ein Bitmap-­‐Index auf das eindeutige Feld KD_NR gelegt, wird wesentlich mehr Speicherplatz benötigt. 38 Pla tzverb ra u ch en tsp rech en d d er Ka rd in a litä t d er W erte ein er T a b ellen sp a lte Wenn wir davon ausgehen, dass in den meisten Fällen die Nicht-­‐Schlüssel-­‐Columns redundante Werte besitzen, so ist die Verwendung von Bitmap-­‐Indexen bezogen auf den Plattenplatz vorteilhafter. Allerdings wird sich zeigen, dass der Plattenplatzverbrauch für Bitmap-­‐Indexe bei häufigen Updates auf die Tabelle steigt. Genau das ist aber ein Vorgang, den wir im Data Warehouse seltener haben bzw. versuchen zu vermeiden. Bleibt die Frage nach der Zeit der Erstellung der unterschiedlichen Index-­‐Varianten. In folgendem Versuch wird in einer Tabelle mit etwa 102 Millionen Sätzen einmal ein Bitmap-­‐Index und danach auf dasselbe Feld ein Btree-­‐Index gesetzt. SQL> Create bitmap index idx_KUNDE_ID_BM on F_BESTELLUNGEN (FK_KUNDEN_ID);
Index wurde erstellt.
Abgelaufen: 00:00:17.84
SQL> Create index idx_KUNDE_ID_BT on F_BESTELLUNGEN (FK_KUNDEN_ID);
Index wurde erstellt.
Abgelaufen: 00:01:43.59
SELECT index_name,Table_name,status, num_rows,index_type, blevel, leaf_blocks, distinct_keys FROM user_indexes; INDEX_NAME IDX_KUNDE_ID_BM IDX_KUNDE_ID_BT TABLE_NAME STATUS F_BESTELLUNGEN VALID F_BESTELLUNGEN VALID NUM_ROWS INDEX_TYPE LEAF_BLOCKS DIST. 86476 102400000 BITMAP NORMAL 43233 212695 1027 1027 Man erkennt deutlich, dass die Erstellung eines Bitmap-­‐Index wesentlich weniger Zeit (etwa ¼) beansprucht. Betrachtet man die Leaf_Blocks, so erkennt man auch den Grund. Das System muss in diesem Beispiel nur etwa ¼ Blöcke beschreiben. Dann ist die Frage die Frage nach der Abfrageperformance zu beantworten. Das folgende Beispiel zeigt, dass die Abfrageperformance bei beiden Index-­‐Arten in diesem Beispiel gleich ist. Abfragen mit Btree-­‐Index: Abfrage mit Btree-Index:
SQL> select sum(summe) from f_bestellungen where FK_KUNDEN_ID = '10';
SUM(SUMME):
5325312
Abgelaufen: 00:00:09.36
SQL> select sum(summe) from f_bestellungen where FK_KUNDEN_ID = '100';
SUM(SUMME):
4673536
Abgelaufen: 00:00:09.06
Abfragen mit Bitmap-Index:
SQL>
select sum(summe) from f_bestellungen where FK_KUNDEN_ID = '10';
SUM(SUMME)
5325312 Abgelaufen: 00:00:09.71
SQL> select sum(summe) from f_bestellungen where FK_KUNDEN_ID = '100';
SUM(SUMME)
4673536 Abgelaufen: 00:00:09.18
Bei genauer Betrachtung der Parallelität stellt man trotz gleicher Antwortzeiten jedoch noch einen Vorteil bei der Ressourcen-Nutzung bzgl.
der Rechenleistung fest. Die Abfrage bei der Verwendung des Bitmap-Index wird im Gegensatz zu der Abfrage mit dem Btree-Index nicht
parallelisiert.
31
Der Optimizer fand es offensichtlich nicht für nötig die zur Verfügung stehende Parallelisierung zu aktivieren . Auch wenn die gleiche
Abfrageperformance bei beiden Index-Varianten erzielt wurde, stehen in einem Multi-User-Betrieb anderen Benutzern bei der Verwendung
von Bitmap-Indexen mehr Ressourcen zur Verfügung.
31
Die Parallelisierung wurde bei diesem Test mit parallel_degree_policy = AUTO durchgeführt. Danach entscheidet das Datenbanksystem selbst den Grad der Parallelisierung. 39 Danach lautet die Empfehlung Bitmap-­‐Indexe immer dann zu nutzen, wenn innerhalb einer Spalte viele Wiederholerwerte vorkommen. Dieser Wert kann durchaus bei bis zu 50% liegen. Die Spalteneigenschaften (NUM_DISTINCT) kann man mit folgender Abfrage feststellen: select TABLE_NAME,COLUMN_NAME,substr(DATA_TYPE,1,20) Type,NUM_NULLS,NUM_DISTINCT,USER_STATS from
user_tab_columns where table_name = 'F_BESTELLUNGEN'
TABLE_NAME COLUMN_NAME TYPE NUM_NULLS NUM_DISTINCT -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐ F_BESTELLUNGEN FK_KUNDEN_ID NUMBER 0 1027 F_BESTELLUNGEN FK_DATUM_ID NUMBER 0 3072 F_BESTELLUNGEN FK_ORT_ID NUMBER 0 12832 F_BESTELLUNGEN FK_ARTIKEL_NUMMER NUMBER 0 63 F_BESTELLUNGEN SUMME NUMBER 0 98 F_BESTELLUNGEN MENGE NUMBER 0 98 Die Dimensionstabellen der Starschemen in der Data Mart-­‐Schicht haben daher fast ausschließlich Bitmap-­‐Indexe auf den Columns. Ausnahme sind hier nur die Primary Key Felder und Felder, die nahezu eindeutig sind. Star-­‐Transformation Eine wichtige Anwendung von Bitmap Indexen ist die sog. Star-­‐Transformation. Hier gelingt es Star Schema –Strukturen auch bei sehr großen Fakten-­‐Tabellen ohne einen Full Table Scan (FT-­‐FTS) auf der Faktentabelle auszuwerten. Eine Join-­‐Abfrage über mehrere Dimensionen und der Fakten-­‐Tabelle wird dadurch beschleunigt. Voraussetzung für die Star-­‐Transformation sind Bitmap Indexe auf den Foreign Key – Feldern der Faktentabelle. Bei aktivierter Star Transformation (Alter Session set star_transformation_enabled=TRUE), löst das System zunächst alle Argumente der WHERE-­‐Klausel bezogen auf die Dimensions-­‐Tabellen auf und merkt sich die so gefundenen Sätze pro Dimension. In einem zweiten Schritt sucht das System zu den Primary Key Werten der gemerkten Dimensions-­‐Sätze in den Bitmap-­‐Indexen der Fakten-­‐Tabelle und findet dort die RowIDs der Treffermenge in der Fakten-­‐Tabelle. 32
Eine Beispielabfrage auf ein Star Schema mit den Dimensionen D-­‐ARTIKEL, D_KUNDE, D_REGION, D_ZEIT und der Faktentabelle F_UMSATZ arbeitet bei aktivierter Star-­‐Transformation nach folgendem Plan. SELECT sum(summe) FROM F_Bestellungen B,D_Artikel A,D_Region R,D_Zeit Z,D_Kunde K
WHERE B.FK_Kunden_ID
= K.Kunden_ID
AND B.FK_Datum_ID
= Z.Datum_ID
AND B.FK_Ort_ID
= R.Ort_ID
AND B.FK_Artikel_ID
= A.Artikel_ID
AND Z.JAHR_NUMMER
= 2008
AND A.GRUPPE_NR
= 3
AND K.KUNDENART
= 8;
32
Beispiel-­‐Skript 8 in Kurzreferenz 40 Pla n ein er A b fra g e a u f ein Sta r Sch em a b ei a ktivierter Sta r-­‐Transform ation Die Tabellen D_Kunde, D_Zeit, D_Artikel und D_Region werden komplett gelesen um die Argumente in der WHERE-­‐Klausel aufzulösen. Es bleiben nur die Sätze aus den Dimensionstabellen übrig, die die Argumente der WHERE-­‐Klauseln erfüllen: AND Z.JAHR_NUMMER AND A.GRUPPE_NR AND K.KUNDENART = 2008 = 3 = 8 Für die Tabelle D_Region nimmt das System alle Sätze, weil sie nicht über Argumente eingeschränkti ist, aber über den Foreig Key / Primary Key-­‐Vergleich in dem Join miteinbezogen ist. Weil die Tabellen relativ klein sind, hat man in diesem Beispiel noch keine Indexe auf die Spalten der Dimensionstabelle gelegt. Daher erfolgt ein FTS auf diese Tabellen. Dann wird über die Bitmap-­‐Indexe der Fakten-­‐Tabelle für die Felder FK_Artikel_ID, FK_Kunden_ID und FK_Datum_ID über eine „BITMAP AND“-­‐Operation zugegriffen. Diese BITMAP-­‐AND-­‐Operation ist sehr schnell, weil das System für jede Zeile in den Index nur 3 Bits vergleichen muss und das auch nur für die in dem Schritt zuvor gefundenen Dimensionswerte. Die Treffermenge der BITMAP-­‐AND-­‐Operation wandelt das System dann in die entsprechenden RowIDs um (BITMAP CONVERSION TO ROWIDS) und hat die gesuchten Sätze der Fakten-­‐Tabelle. Den Abgleich mit der Dimensions-­‐Tabelle D_Region löst das System über einen Hash-­‐Join. Offensichtlich ist dieser Weg aus der Sicht des Optimizers schneller als der Weg über den Bitmap-­‐Index und BITMAP-­‐MERGE. Das Ergebnis ist eine Abfragezeit von unter 2 Sekunden auf einer Faktentabelle mit 102 Millionen Sätzen und das auf einem handelsüblichen Demo-­‐Laptop. Globale und lokale Indexe (Partitionieren von Indexen) Analog zu Tabellen, lassen sich auch Indexe partitionieren. Man unterscheidet zwischen Local -­‐ und Global – Partitioning. Bei dem Local Partitioning orientiert sich die Index-­‐Partitionierung an dem Partitionierungskriterium der zugehörigen Tabelle. Bei dem Global Partitioning legt man eigene Partitionierungskriterien also z. B. entsprechende Max-­‐Wert für die Index-­‐Partitionen fest. Zusätzlich unterscheidet man bei dem Local Partitioning noch zwischen Prefixed Local Partition und Non-­‐Prefixed Partition. Bei dem Prefixed Partition ist das Partitionierungskriterium der Tabelle auch die Spalte, über die primär indiziert wird (bei zusammengesetzten Schlüsseln ist dies die führende, linke Spalte). Wenn Partition-­‐Pruning stattfindet, also wenn das Partitionierungskriterium in der WHERE-­‐
Klausel der Abfrage vorkommt, wird auch nur die Index-­‐Partition gelesen, die zu der zu lesenden Tabellen-­‐Partition passt. Das ist also der optimalste Fall. Non-­‐Prefixed Partitioned Indexe partitionieren den Index nach einem anderen Kriterium, als dem Partitionierungskriterium der zugehörigen Tabelle. Das wird in Ausnahmefällen gemacht, wenn wichtige Abfragen nicht das Partitionierungskriterium in ihrer Where-­‐
Klausel haben und der Index sehr groß ist. Im Data Warehouse nutzt man überwiegend die Local-­‐Indizierung, weil man das Partitioning der Tabellen zur Unterstützung des ETL-­‐
Prozesses verwendet. In ETL-­‐Verfahren wählt man häufig eine Tabellenpartition zur Kennzeichnung von Lade-­‐Inkrementen, d. h. bei jedem Ladelauf hängt man die neuen Sätze als zusätzliche Partition an Zieltabelle an. Bei täglich neu geladenen Sätzen wächst die Zieltabelle 33
jeden Tag um eine Partition . Außerdem ist die Zeit eines der am häufigsten vorkommenden Abfragekriterien und große Faktentabellen werden meist nach Zeit partitioniert. Das passt also gut zusammen. Lo ka le In d izieru n g p ro Pa rtitio n Prüfen, ob ein Index benötigt wird oder nicht Der beste Index ist der, den man nicht braucht. Das spart Plattenplatz und Pflegeaufwand. Deswegen sollte regelmäßig die Notwendigkeit von Indexen überprüft werden. Hierzu kann man zeitweise das Usage-­‐Monitoring für einzelne Indexe einschalten: 33
Siehe auch PEL, Partition Exchange Load im Rahmen der ETL-­‐Prozessbeschreibung 41 ALTER INDEX index_name MONITORING USAGE.
Und dann die Abfrage nach: SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED
Wo wird wie indiziert (Gesamtsicht) Data Marts Vor dem Hintergrund der Star-­‐Transformation wird in Data Marts bzgl. der Star Schemen wie folgt indiziert: •
•
•
•
Auf den Nicht-­‐PK-­‐Spalten der Dimensions-­‐Tabellen liegt i. d. R. ein Bitmap-­‐Index, wenn es sich um große Tabellen handelt (größer 34
50000 Sätze) und die Selektivität der Spalten bei unter 50 % liegt Die Primary Key – Spalten der Dimensions-­‐Tabellen erhalten einen Unique-­‐Key Index. Dieser ist nur zur Prüfung der Eindeutigkeit, nicht aber für die Star-­‐Transformation nötig. Jedes Foreign Key Feld der Fakten-­‐Tabelle erhält einen Bitmap-­‐Index. Die Fakten-­‐Tabelle muss i. d. R. nicht eindeutig sein. Einsatz von BTREE und BITMAP – Schlüssel im Star Schema. Die grün gekennzeichneten Felder werden potentiell mit Bitmap-­‐Index belegt, die roten mit BTREE-­‐Indexen. Bei den hellroten Felder muesste die Kardinaität der Werte genauer geprüft werden Data Warehouse-­‐Schicht (Enterprise Information Layer) Bei der zentralen Data Warehouse-­‐Schicht ist zwischen den Referenz-­‐ bzw. Lookup-­‐Tabellen und den Bewegungsdaten-­‐Tabellen zu unterscheiden. •
•
•
Referenz-­‐ bzw. Lookup-­‐Tabellen haben einen Unique Index auf den Primary Key-­‐Feldern, damit die Eindeutigkeit bei Lookup-­‐
Zugriffen gewahrt ist. Nicht-­‐PK-­‐Spalten der Referenz-­‐ bzw. Lookup-­‐Tabellen erhalten einen Bitmap-­‐Index, wenn auf die Felder oft zugegriffen wird, die Tabellen im mindestens 50000 Sätze umfassen und die Selektivität der Werte bei unter 50% liegt. Werden die Bewegungsdaten-­‐Tabellen nur komplett gelesen oder große Teile davon gelesen (mehr als 25% der Sätze), dann sollten diese nicht indiziert werden, weil FTSs i. d. R. schneller sind. Die Entscheidung, ob ein Index genutzt wird oder nicht, kann man zwar dem Optimizer der Datenbank überlassen. Aber dazu muss ein Index angelegt und auch gepflegt werden und das bedeutet Mehraufwand für den ETL-­‐Prozess. Sollen dennoch Indexe genutzt werden, so ist die Bitmap-­‐Variante zu bevorzugen, wenn die Selektivität weniger als 50 % beträgt. 34
Die Selektivität errechnet sich aus NUM_DISTINCT/Anzahl Sätze der Tabelle * 100. NUM_DISTINCT erhält man über den Dictionary View user_tab_columns. 42 Stage bzw. Integrations-­‐Schicht In dieser Schicht wird normalerweise nicht indiziert, weil man Tabellen i. d. R. als Ganzes verarbeitet. Typischer Umgang mit Indexen im DWH aus der Sicht der ETL-­‐Aufgabenstellung ETL-­‐Läufe zwingen meist zur einer besonderen Umgangsweise mit Indexen im Data Warehouse: •
•
•
Das Schreiben in eine Tabelle verursacht auch das Aktualisieren der Indexe dieser Tabellen. Dieser Vorgang ist bei dem Schreiben einzelner Sätze kaum spürbar. Das massenhafte Schreiben im Rahmen des ETL wird jedoch durch die permanente Index-­‐
Aktualisierung stark behindert. ETL-­‐Läufe sind mengenbasierte Vorgänge bei denen jede Einzelsatzverarbeitung wie Prüfungen oder Index-­‐Aktualisierungen ausgeschlossen sein sollte. Daher werden bestehende Indexe vor dem Massenladen gelöscht und danach komplett neu aufgebaut. Das Löschen ist meist der erste und das Wiederaufbauen der letzte Schritt im ETL-­‐Prozess. Indexe auf großen Tabellen sollten, wie die Tabelle selbst, partitioniert sein, damit bei einer Aktualisierung durch den ETL-­‐
Vorgang nur die Daten der von der Aktualisierung betroffenen Partition erneuert werden müssen. Local Indexing ist die vorherrschende Indizierungsvariante bei partitionierten Tabellen. Weil Indexe aus Sicht des ETL-­‐Prozesses immer besondere Herausforderungen darstellen, sollte bei jedem Index überlegt werden, ob er tatsächlich benötigt wird. 43 Benutzer-Sichten und Kennzahlensystemen (MViews)
Einsatz von Materialized Views anstelle von Summentabellen Frühere Warehouse – Systeme bestanden zu einem großen Teil aus Summentabellen. Sie waren oftmals das Herz der Systeme. So war auch eine der häufigsten Erwartungen an die Systeme, dass die wesentlichen Informationen nur in Form von Summentabellen an die Benutzer geliefert werden können. Gedankliche Synonyme „Warehouse = aggregierte Daten = Summentabellen“ waren an der Tagesordnung und wer Daten auf Transaktionslevel ins Warehouse lud, hatte einen methodischen Fehler gemacht. Die Zeit ist vorbei. Längst hat man erkannt, dass der Grad der Granularität wesentlich zur flexiblen und vielfältigen Verwendung der Systeme beiträgt. Die Notwendigkeit Daten zu verdichten, um Informationen in Sekundenschnelle liefern zu können, ist jedoch geblieben auch wenn die Datenbank heute mit wesentlich mehr Daten auf feinerem Level umgehen kann. Für die Oracle Datenbank heißt heute die Empfehlung Materialized Views zu verwenden. Die klassischen Summentabellen haben ausgedient. Vorteile von Materialized Views im Data Warehouse – Kontext Zusammenfassend findet man folgende Vorteile der Verwendung von Materialized Views gegenüber Summentabellen: •
•
•
•
•
•
Wegfall eines gesonderten ETL – Schrittes. Die Datenbank aktualisiert die Materialized Views selbständig im Hintergrund. Vereinfachung des Workflow / Scheduling, da die Datenbank das Aktualisieren automatisiert übernimmt. Gibt es kein Ladeprogramm, das die Summentabellen aktualisiert, dann muss es auch nicht aufgerufen, verwaltet und durch Monitoring beobachtet werden. Transparenter Aufruf. Der Aufruf erfolgt für den Endbenutzer transparent. Der Benutzer weiß nichts von der Existenz der Materialized Views. Das Datenbanksystem analysiert die Abfrage des Benutzers und versucht diese mit dem Informationsangebot der Materialized Views in Deckung zu bringen (Query Rewrite). Die Notwendigkeit und der Bedarf für eine Materialized View wird durch das System festgestellt. Ihr Gebrauch wird über Datenbankstatistiken gemessen. Minimierung der Anzahl der Objekte. Die Gesamtanzahl der Objekte wird kleiner. Bei Summentabellen musste für jede (!) verdichtete Abfrage aufgrund der notwendigen textlichen Gleichheit von Abfrage und Tabellendefinition eine separate Summentabelle erstellt werden. Das hat in der Vergangenheit zu dem hohen Anteil von Summentabellen an dem Gesamtsystem von oft über 50 % geführt. Materialized Views können von mehreren Abfragen mit unterschiedlichem Informationsbedarf gleichzeitig genutzt werden. Denn es zählt nicht die textliche Gleichheit, sondern die Gleichheit der I nformation, auch wenn diese nur in Teilen vorhanden ist. Synchronisierung bei Datenänderung. Bei separaten Summentabellen besteht immer die Gefahr, dass die in den Summentabellen gespeicherten Informationen nicht mehr stimmen, weil sie nicht richtig mit Quelltabellen synchronisiert sind. Für die Aktualität der Daten muss das durch ein Projekt entwickelte und festgelegt Data Warehouse – Management mit einer Reihe von Hilfsmitteln sorgen. Dazu gehören regelmäßiges Scheduling und Zeitstempel in den Daten, die besagen wann die Daten zuletzt aktualisiert wurden. Ob sich die Basisdaten geändert haben oder nicht, muss das Warehouse Management dann schon irgendwie wissen. Das Verfahren ist komplex und fehleranfällig. Ein großer Teil der Projektarbeit wurde in der Vergangenheit für die Planung und das Design eines solchen Warehouse Managements verwendet. Bei Materialized Views sorgt die Datenbank für die Synchronität von Basisdaten und Materialized View-­‐Daten. Die Materialized View wird durch das Datenbanksystem als nicht mehr gültig deklariert und dann automatisch aktualisiert werden. Wegfall von Summentabellen spart ETL -­‐ Schritte und schafft mehr Flexibilität 44 Bedienen unterschiedlicher Zielgruppe und Aufbau von Kennzahlensysteme In Unternehmen entstehen sehr schnell auseinanderdriftende Erwartungen der verschiedenen Benutzergruppen. Fachmitarbeiter brauchen eher Detaildaten während das strategische Management verdichtete Informationen benötigt. Aus dieser Situation heraus sollte man jedoch nicht für jede Zielgruppe eine separate Datenhaltung bzw. Datenhaltungsobjekte erstellen. Dies führt zu einem unnötig komplizierten System. Außerdem sollte die Datengrundlage für alle Zielgruppen dieselbe sein. Schließlich sind es die sehr granularen Faktendaten, mit deren Hilfe unterschiedliche Benutzergruppen ansprechbar sind. Es gilt folgendes Verfahren: •
•
•
Die Basis für unterschiedlich verdichtete Sichten sind immer granulare Faktendaten. Je granularer desto besser. Flexibilität entsteht durch Granularität. Sichten auf unterschiedlichen Verdichtungsebenen (verschiedene Verwendungszwecke für unterschiedliche Zielgruppen) sollten über sich selbst aktualisierende Materialized Views bereitgestellt werden. Wer glaubt, das ginge nicht mit Materialized Views, weil dahinter nur SQL steht, das nicht für komplexe Kennzahlenberechnungen geeignet ist, der irrt. Mit SQL sind nahezu alle Berechnungen möglich. Wenn es zu komplex wird, dann helfen Table Functions die in ihrem Kern über Programmierlogik 35
verfügen . Materialized Views können als Schichten angelegt sein. D. h. eine erste Schicht umfasst Abfragen direkt auf den Fakten und und eventuell mit Joins verknüpften Dimensionstabellen. Ein zweite Schicht beinhaltet Materialized Views, die auf der ersten Schicht aufsetzen usw.. Verdichtungen werden also selbst noch einmal verdichtet bzw. , und das ist der Hauptzweck, verdichtetet Daten können neu miteinander kombiniert werden. Kennzahlen muss man nicht wieder neu aus den Basisdaten heraus abfragen werden. Hier sieht man, dass das Metadaten-­‐Management wichtig wird. Die in den Materialized Views besonders performant abfragbaren Informationen sollten dokumentiert sein. Das sind Kennzahlenlisten, die man über einen Metadaten – Browser für Endbenutzer anzuzeigen kann oder auch in Business Intelligence Tools als Kennzahlen in deren Metadaten-­‐Verzeichnis aufgelistet sind. Erstellung von aggregierten Sichten auf der Basis von granularen Faktendaten und Bereitstellung für unterschiedliche Zielgruppen Letztlich geht es immer wieder um ein und dieselben Daten bzw. Informationen. Sie werden für unterschiedliche Benutzergruppen und Anwendungsfälle unterschiedlich verdichtet und aufbereitet. Der Kostenvorteil, der bei dieser Vorgehensweise entsteht, kann nicht oft genug wiederholt werden. Dadurch, dass die Materialized Views ihre Daten selbst aktualisieren, entsteht für die Bedienung und Nutzung für die unterschiedlichen Zielgruppen kein Mehraufwand. Es fallen keine separaten ETL – Schritte oder zusätzliche Selektionen in Auswahlmasken an. Wie effizient die in Schichten angeordneten Materialized Views wirken können, zeigt das folgende Beispiel in dem Daten aus einem Star Schema gelesen werden. Die Faktentabelle (Basistabelle) ist granular gehalten (so granular wie die operativen Transaktionen). Auf die Fakten-­‐ und Dimensionstabellen greifen eine Reihe von Materialized Views zu (Level 1). Sie enthalten nur einfache Joins zwischen der Faktentabelle und den benötigten Dimensions-­‐Tabellen. •
Auf einem Level 2 hat man für alle Produkte den Umsatz pro Produktgruppe berechnet. In einer zweiten Linie berechnete man den Gesamtumsatz pro Monat. •
Auf dem Level 3 errechnet man in dem Beispiel eine Reihe von Werten auf Jahresbasis. •
Auf dem Level 4 berechnet man Zusammenhänge von Produktruppen und Jahresumsatzwerte. Diese Berechnungen könnten auch alle einzeln durchgeführt werden. Allerdings müsste man dann immer wieder die aufwendigen Join-­‐
Operationen auf den granularen Faktendaten durchführen. Das Beispiel ist schematisch dargestellt und vereinfacht. In der Praxis können über hundert und mehr Materialized Views an dem System beteiligt sein. Um die nötige Übersicht zu bewahren ist ein dokumentierendes Metadatensystem unumgänglich. •
•
35
Siehe hierzu die Beschreibung von Table Functions in dem Abschnitt zu ETL und Laden im Data Warehouse 45 Sich selbst verwaltendes hierarchisiertes Kennzahlensystem mit Materialized Views Erstellungs-­‐ und Aktualisierungskonzepte Materialized Views entstehen durch ihre Definition mit Hilfe eines SELECT-­‐Statements, analog zu dem View-­‐Konzept. Anders als bei Views wird das SELECT jedoch bereits bei der Definition und für eine nicht geänderte Datenmenge auch nur ein einziges Mal ausgeführt. Die Ergebnismenge wird fest im Storage-­‐System gespeichert. Je nach Anforderung kann man das SELECT sofort ausführen (BUILD IMMEDIATE) oder verzögert (BUILD DEFERRED) erstellen lassen. Über die Option ON PREBUILD wird dem Datenbanksystem mitgeteilt, dass die Datenbasis der Materialized View nicht permanent auf Veränderungen hin untersucht werden muss. Bezieht die Materialized View ihre Daten aus einer anderen View-­‐Definition oder sogar aus einem SELECT-­‐Statement mit einer Table Function, dann muss man das Erstellen der Daten und auch das Aktualisieren von außen anstoßen und kontrollieren. Das macht das Konzept der Materialized Views aber auch so mächtig. Große Materialized Views kann man wie normale Tabellen partitionieren und indizieren. Das kommt jedoch eher seltener vor. CREATE MATERIALIZED VIEW mview_name
{PARTITION BY RANGE (feld_name) …}
BUILD [IMMEDIATE,DEFERRED, ON PREBUILD]
REFRESH [COMPLETE, FAST, FORCE, NEVER] [ON COMMIT, ON DEMAND]
ENABLE QUERY REWRITE
AS SELECT …..
Wenn Materialized Views bereits existieren, so können auch sie wie die ehemaligen Summentabellen „stale“ werden, d. h. die Daten in der Basistabelle können sich ändern und die Daten in der Materialized View sind nicht mehr aktuell. Die Aktualisierung kann entweder manuell angefordert (EXECUTE DBMS_VIEW.REFRESH(‘mview_name‘,‘refresh-­‐methode‘) oder zeitgesteuert durch das Datenbanksystem erfolgen. Durch die Abhängigkeit von ETL-­‐Prozessen macht in dem Data Warehouse System nur das manuelle Aktualisieren Sinn und zwar nach Abschluss des ETL-­‐Prozesses und nachdem alle Indexe aktualisiert wurden. Manuell bedeutet hier natürlich das Einhängen des entsprechenden Aufrufs in die ETL-­‐Job-­‐Kette. So gesehen ist das auch ein automatischer Vorgang. Der Aufruf EXECUTE DBMS_MVIEW.REFRESH_ALL; aktualisiert alle Materialized Views, die nicht mit REFRESH NEVER markiert sind. Die Art des Aktualisierens kann noch gesteuert werden. Haben sich nur wenige Sätze einer sehr großen Basistabelle geändert, so ist ein komplettes Neuerstellen einer Materialized View sehr aufwendig, zumal bei großen Faktentabellen oftmals nur Partitionen an das Ende angehängt werden. Daher gibt es die Möglichkeit des inkrementellen Aktualisierens von Materialized Views. Es stehen 2 Varianten zur Verfügung: •
•
Die ältere Materialized View Log – Variante: Hier hängt die Datenbank ein Log-­‐Objekt an die Basis-­‐Tabelle. In diesem Log-­‐Objekt sammeln sich Änderungen (INSERTS, UPDATES, DELETES) die in der Basistabelle stattfinden. Das modernere Verfahren des Partition Change Tracking: Hier durchsucht das Datenbanksystem nur die Partitionen der Basistabelle, die sich auch geändert haben. Das setzt natürlich eine partitionierte Basistabelle voraus. Das View Log-­‐Verfahren hat im Data Warehouse eine Reihe von Nachteilen. Zum einen können nicht alle Änderungsaktivitäten für das Ändern der Materialized Views herangezogen werden und zum anderen bremsen die Log-­‐Objekte einen Massen-­‐Insert, wie er gerade im Data Warehouse vorkommt, aus. Ein kleiner Test zeigt dieses Verhalten: SQL> insert into f_umsatz_log select * from f_umsatz_small;
1999999 Zeilen erstellt.
Abgelaufen: 00:01:37.99
SQL> insert into f_umsatz_nolog select * from f_umsatz_small;
1999999 Zeilen erstellt.
Abgelaufen: 00:00:08.21
Beide Tabellen in dem Test sind leer bevor sie beschrieben wurden. Der INSERT-­‐Vorgang von 2 Millionen Sätzen dauert bei der Tabelle ohne Log-­‐Objekt etwa 8 Sekunden, bei der Tabelle mit Log-­‐Objekt jedoch fast 100 Sekunden. Löscht man beide Tabellen wieder mit TRUNCATE, ahnt man, warum die LOG-­‐Objekte das Laden ausbremsen. Das TRUNCATE, das normalerweise sehr schnell arbeitet, dauert bei der Tabelle mit Log-­‐Objekt fast eine Minute. Offensichtlich werden einzelne Inserts auf Satz-­‐Eben bzw. einzelne Deletes bei dem TRUNCATE 46 durchgeführt. LOG-­‐Objekte eignen sich eher für OLTP-­‐Systeme mit einzelnen Inserts. In der Tat kann man in OLTP-­‐Systemen dieses Verfahren auch zum Replizieren von Tabellen benutzen, denn man muss dazu nur wenig Implementierungsaufwand betreiben. Für das Data Warehouse eignet sich eher die Partition Change Tracking Methode (PCT). Hierzu muss die Basistabelle partitioniert sein und in der WHERE-­‐Klausel der Materialized View Definition muss das Partition-­‐Key-­‐Feld enthalten sein. Hat man keinen Zugriff auf das Partition-­‐Key-­‐Feld, dann hilft die PMARKER-­‐Funktion. Diese Funktion liefert zu jeder Row-­‐ID eines Satzes der Basistabelle die zugehörige Partition. Nutzt man diese Funktion, dann wird praktisch jede Zeile einer Materialized View – Ergebnismenge durch die passende Zugehörigkeit zu einer Partion markiert. CREATE MATERIALIZED VIEW MV_standard_PCT_PM ENABLE QUERY REWRITE
AS SELECT
DBMS_MVIEW.PMARKER(u.rowid),
z.jahr_nummer Jahr, z.monat_desc Monat, sum(u.umsatz) Summe, a.artikel_id ID, count(u.umsatz)
FROM
f_umsatz_par u, d_artikel a, d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY DBMS_MVIEW.PMARKER(u.rowid), z.jahr_nummer, z.monat_desc, a.artikel_id;
Wann wird das Refresh angestoßen? Zunächst wird bei der Definition der Materialized Views über den REFRESH – Parameter festgelegt, ob nach jeder Transaktion in der Basistabelle (ON COMMIT) oder nur bei expliziter Anforderung (ON DEMAND) aktualisiert wird. Die ON COMMIT Variante passt erwartungsgemäß eher für einen OLTP-­‐Betrieb der über ein LOG-­‐Objekt arbeitet. Im Data Warehouse nutzt man in der Regel On Demand, weil das Refresh nach Abschluss des Ladeprozesses erst auf Anforderung starten soll. Bei der Definition der Materialized Views legt man auch fest, wie das Refresh durchgeführt wird, ob nur inkrementell (REFRESH FAST) oder ob das Datenbanksystem aufgrund der zu erwartenden Refresh-­‐Laufzeit zwischen inkrementell und komplett entscheiden soll (FORCE) oder ob ein Refresh in jedem Fall komplett durchgeführt werden soll (COMPLETE). Diese Festlegung kann zu einem späteren Zeitpunkt bei dem Starten des Refresh-­‐Vorgangs überschrieben werden: EXECUTE DBMS_VIEW.REFRESH(‘mview_name‘,‘refresh-methode‘)
Refresh-methode:
COMPLETE
FAST
FORCE (default)
(C)
(F)
(?)
-> immer komplettes Lesen der Basis-Tabelle
-> Inkremtentelles Lesen, wenn möglich (View-Log oder PCT)
-> beide vorgenannte Varianten, abhängig von der dafür
benötigten Zeit
Pauschal kann man für die Verwendung in dem Data Warehouse folgende Vorgehensweise als Konzept empfehlen: •
•
•
•
Basistabellen ohne View-­‐Log definieren. REFRESH mit der Option FORCE und ON DEMAND definieren. Pauschal alle Materialized Views nach dem ETL-­‐Lauf aktualisieren mit EXECUTE DBMS_MVIEW.REFRESH_ALL. Sind in einer Basistabelle keine Änderungen angefallen, dann wird das Datenbanksystem die abhängige Materialized View auch nicht aktualisieren, wenn doch, wird entschieden, ob Partition Change Tracking möglich ist oder nicht und schließlich wird geprüft, ob vollständig oder inkrementell aktualisiert wird. Sind Abweichungen von diesen Regeln zu erwarten (z. B. für On PREBUILD – Materialized Views), kann man für bestimmte Tabellen das REFRESH auch einzeln starten. Benutzerabfragen automatisch umlenken – Query Rewrite Eine der Hauptaufgaben der Materialized Views ist die Verbesserung der Abfrageperformance für Anwender des Data Warehouse. Die Arbeit der Benutzer sollte dabei jedoch nicht beeinträchtigt werden. Sie sollen lediglich die verbesserte Performance merken und brauchen auch von der Existenz der Materialized Views nichts wissen zu müssen. Es ist die Aufgabe des Datenbanksystems die Benutzerabfragen zu analysieren und in dem Vorrat der Materialized Views nachzusehen, ob die Abfrageergebnisse bereits in Form einer Materialized View vorliegen oder der aufwendige Weg über das Lesen der Basistabellen gegangen werden muss. Wenn die Abfrage über eine Materialized View bedient werden kann, dann schreibt das Datenbanksystem die Abfrage für den Benutzer im Hintergrund um und liest die Materialized View aus. Diesen Vorgang nennt man Query Rewrite. Nicht alle Abfragen werden umgeschrieben, auch wenn eine Materialized View vorhanden ist. An das Query Rewrite sind eine Reihe von Bedingungen geknüpft. Zunächst müssen Grundeinstellungen des Datenbanksystems gegeben sein: •
•
Der Session -­‐ Parameter query_rewrite_enabled muss auf TRUE stehen. Damit wird das Query Rewrite überhaupt erst eingeschaltet. Es muss die Toleranzschwelle gegenüber Änderungen in der Basistabelle festgelegt werden. Grundsätzlich sollten Materialized Views nicht mehr genutzt werden, wenn sich die Basistabelle geändert hat, denn dann werden die Abfrageergebnisse falsch. Das entspricht der Einstellung QUERY_REWRITE_INTEGRITY=ENFORCED. Wenn das Rewrite auch bei einer bereits geänderten Basistabelle wirken soll, dann muss der Parameter auf STALE_TOLERATED stehen. Die Schalterwahl TRUSTED wird genutzt, wenn die Prüfung auf Änderungen in der Basistabelle z. B. bei PREBUILD-­‐Materialized Views ausgesetzt werden soll. Sind die Datenbank-­‐Parameter passend eingestellt, so muss die Materialized View Definition noch einige Anforderungen erfüllen. 47 Bei der Suche nach einer potentiellen Gleichheit zwischen Abfrage und Materialized View – Definition untersucht die Datenbank in der folgenden Reihenfolge: 1.
Textvergleich der SELECT-­‐Argument-­‐Liste o
o
2.
3.
Die Reihenfolge der abgefragten Spaltennamen spielt dabei keine Rolle. Ein Auflösung von möglichen Berechnungen findet statt. Vergleich der Join-­‐Bedingungen (Welche Felder sind in der Where-­‐Klausel genutzt?). Vergleich der GROUP BY-­‐Klausel. Folgendes Beispiel zeigt einige Rewrite-­‐Varianten auf. Zunächst folgt eine Darstellung der Basistabellen, auf die abgefragt werden soll. B eisp ielta b ellen Und hier die Definition der Materialized View: CREATE MATERIALIZED VIEW MV_UMS_ART_Zeit
REFRESH COMPLETE ENABLE QUERY REWRITE AS
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz)Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz u, d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY z.jahr_nummer, z.monat_desc, a.artikel_id;
Diese Materialized View kann folgende Abfrage ersetzen, weil sie in der SELECT-­‐Sektion dieselben Felder findet. Die Reihenfolge der Felder kann beliebig sein. Auch der AVG-­‐Ausdruck wird aufgelöst, weil in der Materialized View-­‐Definition eine COUNT-­‐ und eine SUM-­‐Funktion enthalten ist. Daraus berechnet das System den Average-­‐Wert. SELECT
z.jahr_nummer Jahr,
sum(u.umsatz)Summe,
avg(u.umsatz)Durchschnitt,
a.artikel_id ID,
z.monat_desc Monat
FROM
WHERE
f_umsatz u, d_artikel a,
d_zeit z
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc, a.artikel_id;
Abfragbar ist ebenfalls alles, was in der GROUP BY Klausel steht. In der folgenden Abfrage werden nur Jahreswerte abgefragt, während die Materialized View die Informationen auch auf Monatsebene vorhält. Das System liest die Sätze in der Materialized View und aggregiert sie zusätzlich noch einmal auf Jahresebene. Es führt automatisch ein ROLLUP durch. Wäre in der Materialized View keine GROUP BY Klausel enthalten, und die Abfrage würde nach aggregierten Werten auf Monats oder Jahresebene abfragen, dann müsste das System ein Rollup komplett aus den Detaildaten durchführen. Also auch dann, wenn die Materialized View Definition nur aus einer Join-­‐Bedingung besteht, finden ein Rewrite statt. SELECT
FROM
z.jahr_nummer Jahr, --> Bezugsgröße in MAV ist Monat
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
f_umsatz u,d_artikel a,d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
48 GROUP BY
z.jahr_nummer, a.artikel_id;
Auch wenn nicht alle in der Abfrage gewünschten Felder in der Materialized View-­‐Definition enthalten sind, kann ein Rewrite stattfinden. In der folgenden Abfrage wird aus der Tabelle D_ARTIKEL das Feld ARTIKEL_NAME gewünscht. Dieses Feld ist nicht in der Materialized View-­‐Definition enthalten, sondern nur das Feld ARTIKEL_ID. Weil die Tabellen über eine Schlüsselverknüpfung miteinander verbunden sind (WHERE-­‐Klausel), kann das Feld ARTIKEL_NAME durch einen sogenannten JOIN-­‐BACK nachgelesen werden. Damit das funktioniert, muss jedoch die Tabelle D_Artikel einen Primary Key auf dem Feld ARTIKEL_ID besitzen und das Feld ARTIKEL_ID in der WHERE-­‐Klausel vorkommen. Alter table d_artikel add constraint pk_artikel PRIMARY KEY (artikel_id);
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
a.artikel_name Artikel,
sum(u.umsatz) Summe
FROM
f_umsatz u,d_artikel a,d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY z.jahr_nummer, z.monat_desc, a.artikel_Name;
Hierarchische Struktur einer Dimension für das Rewrite nutzen Dimensionstabellen in einem Star Schema haben immer eine gleiche Struktur. Die Spalten lassen sich in Gruppen zusammenfassen, und die Gruppen stehen in einer hierarchischen Beziehung zu einander. Durch den Vorgang der Denormalisierung wurden aus den ursprünglich normalisierten Tabellen mit echten 1 :n-­‐Beziehungen Spaltengruppen, die ebenfalls in einer 1:n-­‐Relation zu einander stehen. Diese Struktur nutzt auch das Query-­‐Rewrite, um über die gesamte Dimensions-­‐Tabelle hinweg entsprechend der hierarchisch strukturierten Spaltengruppen aggregieren zu können. Die Strukturierung einer Dimension, d. h. die Information darüber, welche Columns zu welcher Gruppe gehören und welche Gruppe eine 1:n Beziehung zu einer anderen Gruppe hat, wird in dem Datenbank-­‐Objekt DIMENSIONAL_TABLE festgelegt. D im en sio n a l T a b le – O b jekt zu r M eta d a ten d a rstellu n g ein er D im en sio n Die Dimensional-­‐Table Definition beschreibt jede Spaltengruppe als Hierarchie-­‐Level und gibt diesem Level einen Namen sowie ein Schlüsselattribut. Dann legt man fest, welche Columns zu diesem Level gehören (ATTRIBUTES) und beschrebt die Hierarchisierung als Parent-­‐Child Paare. CREATE DIMENSION d_artikel
LEVEL artikel IS d_artikel.artikel_id
LEVEL gruppe IS d_artikel.gruppe_nr
LEVEL sparte IS d_artikel.sparte_nr
HIERARCHY h_art (artikel CHILD OF gruppe CHILD
ATTRIBUTE att_artikel LEVEL artikel DETERMINES
ATTRIBUTE att_gruppe LEVEL gruppe
DETERMINES
ATTRIBUTE att_sparte LEVEL sparte
DETERMINES
OF sparte)
d_artikel.artikel_name
d_artikel.gruppe_name
d_artikel.sparte_name;
Definiert man jetzt eine Materialized View, in der nur das Schlüsselfeld der niedrigsten Hierarchie-­‐Stufe vorkommt (in dem Beispiel hier das Feld ARTIKEL_ID), dann ist diese Materialized View Rewrite-­‐fähig, auch dann, wenn in der Abfrage ein Feld mit einer Aggregation auf einem anderen Hierarchie-­‐Level gefordert ist. Hilfsmittel „um Materialized Views zu verstehen“ Setzt man Materialized Views ein, so kann man das eine oder ander Mal ins Grübeln darüber geraten, ob ein Rewrite stattfindet oder nicht. Und das obwohl man glaubt alle Einstellungen richtig vorgenommen zu haben. Es gibt zwei Prozeduren, die das Verhalten von Materialized Views erklären. •
•
dbms_mview.explain_mview: erklärt die Eigenschaften einer Materialized View. dbms_mview.explain_rewrite: Zeigt auf ob und warum eine Materialized View zu einem REWRITE führt oder nicht dazu führt. Hierzu muss man zunächst jeweils eine Ergebnistabelle anlegen. 49 start D:\O11\db11\RDBMS\ADMIN\utlxmv.sql
Start D:\O11\db11\RDBMS\ADMIN\utlxrw.sql
Für den Test der Materialized View gibt man der Prozedur dbms_mview.explain_mview das SELECT-­‐Statement als Parameter mit, aus dem auch die Materialized View besteht. Danach fragt man die Ergebnistabelle ab. EXEC dbms_mview.explain_mview(‘SELECT a.sparte_name Sparte,sum(u.umsatz) Summe FROM f_umsatz u, d_artikel
a WHERE a.artikel_id = u.artikel_id GROUP BY a.sparte_name;
SELECT capability_name, possible p, substr(related_text,1,20) obj, substr(msgtxt,1,100) erklaerung FROM
mv_capabilities_table;
Im Enterprise Manager kann man diese Auswertung noch einfacher erhalten. Hierzu sucht man unter den Schema-­‐Objekten die Materialized Views und wählt dann die Option EXPLAIN. Will man festellen lassen, ob für eine Abfrage ein Rewrite stattfinden wird, führt man die Routinen dbms_mview.explain_rewrite mit der zu prüfenden Abfrage als ersten Parameter aus. Der zweite Parameter ist der Name der Materialized View die man prüfen will. Die Prozedur schreibt entsprechende Hinweise auf den Bildschirm. Begin
dbms_mview.explain_rewrite('
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID
FROM f_umsatz u, d_artikel a, d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc,
a.artikel_id', 'MV_UMS_ART_Zeit_Join');
end;
50 ETL - Extraktion, Transformation, Load
Data Warehouse Systeme erhalten in definierten Zeitabständen neue Daten aus den operativen Vorsystemen (periodischer Massen-­‐Load). Waren früher die Zeitabstände eher länger (Wochen, Monat, Quartal), so ist heute das tägliche Laden der Standard-­‐Fall, oft findet man 136
bereits untertägiges, stündliches Laden oder sogar das permanente Laden (Trickle and Feed oder Real time Load). Auch wenn sich die Aktualisierungsrythmen ändern, muss das Data Warehouse seine vier originären Funktionen erfüllen. •
•
•
•
Die zentrale Bereitstellung von Daten (und damit Integration von verteilten Datenbeständen). Die semantische Anreicherung (das Überführen von Daten in verstehbare Informationen und Hinzufügen von Erklärungen). Die Historisierung. Die Herauslösung der Daten aus den operativen Datenbeständen. Im Gegensatz zu OLTP-­‐ oder SOA-­‐basierten Systemen sind Data Warehouse -­‐ Systeme Daten-­‐orientiert. Wenn die zuvor genannten 4 Funktionen erfüllt sind, stellt die Datenablage an sich den eigentlichen Wert dar. Geschäftsprozesse sind auf einen möglichst effizienten Ablauf hin optimiert. Data Warehouse Systeme sind in ihrer Kernfunktion aber nicht Bestandteil der Geschäftsprozesse. Datennahe Transformationen für Daten-­‐orientiertes Data Warehouse Daten oder Prozesse? Der Daten-­‐orientierte Charakter macht das Data Warehouse zu einem statischen Gebilde. Das Datenbank-­‐System Oracle stellt die ideale Plattform für ein Data Warehouse dar. Es ist nur folgerichtig, wenn auch das Laden und Transformieren möglichst nahe an den Daten – Datenbank-­‐orientiert -­‐ stattfindet. Auch wenn moderne Anwendungen heute über eine Middleware -­‐ Komponente verfügen, ist das Laden und Transformieren über die Datenhaltungs-­‐Instanz am sinnvollsten, da hier die größten Synergie-­‐Effekte erzielbar sind. Gründe sind: •
•
•
•
Datenvolumen und leichteres mengenbasiertes Bewegen und Transformieren. Der Aufwand verteile Informationen auch semantisch zu integrieren und dabei bestehende Informationen in dem Data Warehouse mit einzubeziehen. Modell-­‐Neutralität der Daten im Data Warehouse gegenüber den operativen Systemen. Separierung und Entkoppelung von physische Ressourcen ( es werden nicht die Ressourcen der operativen Quellsysteme, sondern des Data Warehouse genutzt). ETL-­‐O perationen im D ata W arehouse sollten D aten -­‐zentriert in der D atenbank stattfinden u n d n ich t vo n ein er M id d lew a re-­‐Ko m p o n en te a b h ä n g ig sein Das bedeutet, auch wenn operative Systeme z. B. über eine Service-­‐orientierte Kommunikationsstruktur verfügen und darüber Daten auch in ein Data Warehouse gelangen können, so sollten das Gros der Transformationsroutinen und Logik Daten-­‐nah, also mit den Bordmitteln der Datenbank stattfinden. Für die Bereiche, in denen ein Data Warehouse auch operative Aufgaben erfüllt und an operative Systeme angeschlossen ist, ist es dagegen sinnvoll Schnittstellen mit einem direkten Bezug zu operativen Transaktionen aufzubauen (Trickle and 37
Feed ). In manchen Diskussionen wird der Real Time-­‐Aspekt als Merkmal des Modernen schlecht hin dargestellt und der originäre Zweck der Data Warehouse Systeme aus dem Blickfeld verloren. Leider wird dann Technologie zum Selbstzweck. In der Praxis finden wir auch heute noch, also nach mehr als 15 Jahren DWH-­‐Technologie-­‐Entwicklung in weit über 95% aller Fälle den periodischen Massen-­‐Load, obwohl die Technologie für Real time-­‐Loading schon fast genau so lange existiert. Technische Mittel in der Datenbank Die Oracle-­‐Datenbank verfügt über eine Reihe technischer Mittel, um Daten-­‐nahes Laden und Transformieren innerhalb der Datenbank sehr einfach zu gestalten: •
Standardfunktionen Insert, Update, Delete, Merge (Insert / Update) aber auch zusammenführende und spaltende Transformationen (Join / Split, Multiple Inserts). •
Formatprüfungen bei Text-­‐Dateien z. B. mit External Tables und Aussteuern fehlerhafter Sätze. 36
Trickle and Feed – engl. Begriff als Synonym für Real Time basiertes Laden des Data Warehouse. 37
dto. 51 •
Prüfen, Filtern und Bedingungen mit Where-­‐Klauseln, einfachen SQL-­‐String-­‐Funktionen, Regular Expressions, Case-­‐Konstrukt, wahlweise Einsatz von temporären Zwischentabellen. •
Komplexe Prüfungen mit programmierbaren Table Functions. •
Gruppierende Transformationen, Segmentierung und Aggregationen über SQL-­‐Mittel (Group By, Partition By mit analytischen Funktionen). •
Pivotierende Transformationen mit SQL (Pivot, UnPivot). •
Berechnungen mit SQL –Mitteln bzw. Table Functions. •
Formatieren von Daten mit SQL-­‐Funktionen. •
Anreichernde Transformationen (Lookups über Outer-­‐Join, Konstanten). •
Aussortieren / Trennen von Datenbereichen (mit CASE und Multiple Insert). •
Prüflogik (logische, fachliche, physische und technische Prüfungen). •
Protokollierende Maßnahmen (Log Files schreiben, Statistiken aus den DB-­‐Views entnehmen, Counts merken etc.) •
Zusätzliches manuell gesteuertes Parallelisieren mit Table Functions (über das Mass hinaus, das die Datenbank bereits mit Parallel Server ermöglicht). •
Job-­‐Steuerungen und Monitoring (Integriertes Runtime-­‐System, Scheduler der Datenbank). •
Data Quality Steuerungen (Rules-­‐Systeme mit PL/SQL-­‐Mitteln). •
Kommunizieren mit anderen Systemen (Messages aus der Datenbank heraus senden / empfangen / quittieren, Queueing). Das kann an dieser Stelle nur ein Auszug sein, da letztlich alle Datenbank-­‐Features für das Data Warehouse nutzbar sind. Generieren statt Programmieren Die meisten dieser Funktionen muss man nicht manuell in einem Texteditor erstellen. ETL-­‐Tools sorgen schon seit Jahren an dieser Stelle für Fehlerminimierung (weil man sich nicht um Syntax kümmern muss) und Übersichtlichkeit, in Form von graphisch dargestellten Datenflüssen. Obwohl erfahrene SQL-­‐Programmieren den ETL-­‐Code ebenso schnell erstellen können, wie jemand, der solche Tools einsetzt, ist ein Tool-­‐Einsatz schon wegen der automatischen Dokumentation zu bevorzugen. Für das Oracle Data Warehouse sollten jedoch Tools gewählt werden, die einen größt möglichen Anteil an originären Datenbankfunktionen erzeugen und möglichst viele Features 38
der Datenbank nutzen ohne diese in dem Tool implementieren zu müssen . An dieser Stelle treffen zwei Meinungen auf einander. Die eine will möglichst wenig ETL-­‐Logik in die Datenbank verlagern, weil man damit potentielle Migrationsprojekte von einem Datenbankhersteller zu einem anderen erschwert. Die andere Meinung besagt, dass wenn schon ein bestimmtes Datenbank-­‐System eingesetzt wird, dann sollten man diese Datenbank auch zu 100% nutzen und nicht Features brach liegen lassen die einem die Arbeit massiv erleichtern. Beide Positionen haben ihren wahren Kern, denn die ETL-­‐Prozesse stellen den aufwendigsten und Kosten-­‐intensivsten Bereich in einem Data Warehouse-­‐System dar, zumal neben den Projektkosten (Personal, Lizenzen) immer noch der Faktor Zeit mit berücksichtigt werden muss. Aufgrund der langjährigen Beobachtung von vielen Data Warehouse Systemen kann man jedoch feststellen: •
Die effektivsten Data Warehouse Systeme nutzen am intensivsten die Bordmittel der Datenbank. Effektiv bedeutet hier der Einsatz von geringeren Mittel (Personal und Zeit) und den relativ reibungslosen Betrieb des Systems über Jahre hinweg. •
Es ist nicht das ETL-­‐Tool, das den Erfolg eines Data Warehouse-­‐Systems ausmacht, sondern die Fähigkeiten (Skills) der Mitarbeiter in den Projekten und im Betrieb. Dieser Punkt spricht eindeutig für den Einsatz der Bordmittel, weil ein Datenbank-­‐neutrales ETL-­‐
Tool die Projektmitarbeiter von tiefer gehendem Datenbank-­‐Know how fern hält, was sich dann in Notsituationen rächen kann. •
Jedes ETL-­‐Tool kann nicht verhindern, dass doch an einigen Stellen mit Datenbank-­‐Programmiermitteln (z. B. Oracle PL/SQL) Kern-­‐Routinen erstellt werden, die das ETL-­‐Tool dann nur noch aufruft. Denn es gibt Aufgabenstellungen, die z. B. direkt in PL/SQL programmiert leichter gelöst werden können. Eine einfache Form dieser „Programmierlösung“ ist auch das Erstellen von Views um komplexe SQLs zu kapseln. Die ETL-­‐Aktivitäten und das Schichten-­‐Modell Betrachtet man die Architektur des Data Warehouse, so erkennt man die verschieden Stadien und Aktivitäten, die zu durchlaufen sind 39
bevor die Daten Endbenutzern zur Analyse angeboten werden können. Dieser ETL (Extrakt Transition Load) bzw. ELT -­‐ Vorgang gliedert sich in 5 Phasen. 38
Oracle verfügt gegenwärtig in der Version 11 über zwei ETL-­‐Tools: Oracle Warehouse Builder (OWB) und Oracle Data Integrator (ODI). Während ODI eine zugekaufte Technologie darstellt und eher Datenbank-­‐neutral arbeitet, wurde OWB seit 1998 von Oracle selbst entwickelt und nutzt heute den größeren Anteil an originären Oracle-­‐Datenbank Features. Ab einer Version 12 sollen beide Tools miteinander verschmelzen. 39
In jüngster Zeit ist in Diskussionen der Begriff ETL durch den Begriff ELT ersetzt worden. Mit dem Begriff ELT (Extract Load Transform) will man zum Ausdruck bringen, dass Daten aus den Vorsystemen zunächst in das Zielmedium (Datenbank) geladen werden, um sie dort zu transformieren. Die Datenbank ist die eigentliche Transformations-­‐Engine. Die Argumentation kommt ehemals von der Firma Sunopsis, die damit das Native-­‐Generieren von SQL-­‐Kommandos unterstreichen wollte. Dies ist eine Abgrenzung gegenüber Engine-­‐basierten Werkzeugen wie Informatica, Data Stage oder Ab Initio, bei denen Daten in einem separaten Programm (Engine) transformiert werden und das Ergebnis dann in die Zieldatenbank gelangt. 52 A ktivitä ten im V erla u f d es In fo rm a tio n sb esch a ffu n g sp ro zesses in d em D a ta W a reh o u se Phase 1 Kopieren Hierunter ist zunächst das Kopieren von Daten aus den Vorsystemen hin zum Data Warehouse zu verstehen. In klassischen Data Warehouse-­‐Umgebungen bevorzugt man bis heute das 1:1-­‐Kopieren. Gründe hierfür sind: •
In dieser Phase sind meist technische Systemgrenzen zu überwinden, so dass man als erstes Hauptziel das erfolgreiche Überführen der Daten auf die Data Warehouse – Plattform (Maschine) anstrebt. •
Man will einen Konsistenzpunkt schaffen, um zu dokumentieren, dass unmittelbar nach dem “Anlanden” der Kopie die Verantwortung für die Verarbeitung auf das Data Warehouse-­‐System übergeht. •
Man will möglichst schnell den Zugriff auf operative Systeme abschließen, um diese nicht unnötig zu beeinflussen. Diese Vorgehensweise kann heute durchaus kritisch bewertet werden, denn ein effizienter Ladeprozess versucht reine 1:1-­‐Kopien zu vermeiden. Die darauf folgenden Prüfungen der Daten teilen sich in einen syntaktischen, semantischen und logischen Teil. Zumindest die syntaktische Prüfung kann z. T. bereits bei dem Zugriff auf die Vorsysteme erfolgen. Das sind •
Prüfungen auf NULL-­‐Werte, •
Zeichensatzkonvertierungen •
Vollständigkeit der Daten (Anzahl Sätze, Anzahl Dateien, erwartete Satzlängen, Vorhandensein von Headern •
syntaktische Korrektheit von Formaten (numerisch, Datum, Zeichenkette) usw.. Vorallem Textdateien, die immer noch einen hohen Anteil der Vorsystem-­‐Daten ausmachen, können so geprüft werden, bevor man sie in das Data Warehouse lädt. Aus diesem Grund sollte man auch schon in der Integration Layer-­‐Schicht mit den echten Datentypen der Felder arbeiten und nicht alle 40
Werte pauschal in einen Character-­‐Typ überführen, um die Feldtyp-­‐Prüfungen von dieser Basis bei „Null beginnend“ durchzuführen. Ergebnis der Phase 1: Daten in einer syntaktisch korrekten Form und Tabellenform in der Data Warehouse Datenbank. Phase 2 Integrieren Da die Daten aus unterschiedlichen Vorsystemen stammen, müssen sie für die gemeinsame Nutzung im Data Warehouse harmonisiert werden: •
Identische oder zusammenhängenden Informationen sind zu identifizieren (Lösen der Synonymen-­‐/Homonymen-­‐Problematik). Begriffe müssen z. T. transformiert/übersetzt werden. •
Offenlegen von Widersprüchen in Herleitungs-­‐ und Berechnungsregeln. •
Die Stimmigkeit mit fachliche Regeln ist zu überprüfen. Sunopsis wurde von 2006 von Oracle gekauft und das Produkt in ODI umgetauft. Oracle verfügte jedoch bereits über ein Native-­‐SQL-­‐
generierendes Werkzeug (Oracle Warehouse Builder). Die Argumentation ELT vs ETL wurde beibehalten. Sie trifft aber auch für Warehouse Builder zu. Hier werden beide Begriffe synonym genutzt, da es in dieser Darstellung weniger um Marketingaussagen gehen soll. 40
Ein solches Vorgehen ist einfach nur zeitaufwendig und heute kaum noch begründbar. Oftmals wurde es aus früherer Zeit einfach nur übernommen. In den 1980er und 1990er Jahren und gerade auf Mainframe-­‐Systemen war dies eine beliebte Praxis. 53 •
Aggregationslevel und Betrachtungsebenen sind anzugleichen. •
Formate, Zustände, Sichtweisen, Maßeinheiten, Sprachen etc. sind anzugleichen. Ergebnis der Phase 2: Eine einheitliche Sicht auf die geladenen Daten der Vorsysteme, auf deren Verwendung und Bedeutung. Phase 3 Informations-­‐Mehrwerte schaffen Bevor die Daten in ener granularen Form in den zentralen Enterprise Layer gelangen, kann man sie mit relevanten Informationen anreichen, die in den Quelldaten noch nicht vorhanden waren, aber für den Endbenutzer nützlich sein könnten: •
Marktdaten, Vergleichszahlen aus dem Umfeld. •
Zusätzliche Einheiten, Währungen, Sprachen, Gebinde etc. •
Begriffsdefinitionen und Beschreibungen. •
Standardisierende Begriffe/Bezeichnungen. Ergebnis der Phase 3: Komplette (Vorsystem-­‐übergreifende) granulare Informationen. Phase 4 Sammeln Die zentrale „Enterprise Layer“-­‐Schicht ist die eigentliche Ablage der Daten im Data Warehouse. Die Daten sind hier in einer besonderen Form abzulegen: •
Nahezu normalisiert (3 NF). Nahezu bedeutet, soweit dies sinnvoll ist. Es sollte so granular gespeichert werden, dass in der letzten, der Aufbereitungsphase möglichst viele Kombinationsmöglichkeiten und damit Flexibilität entsteht. •
Man sollte die Daten „inventarisiert“ ablegen . In einem zusätzlichen System (z. B. Metadaten-­‐Repository) kann man Wertebereiche, Ladezeitpunkt, Verantwortliche usw. vermerken. •
Es muss eventuell umgeschlüsselt werden, denn in dem Data Warehouse gelten künstliche Schlüssel als gemeinsamer Nenner zu den unterschiedlichen Schlüsseln in den Vorsystemen. •
Es muss historisiert werden. Stammdaten sind so abzulegen, dass ihre Veränderungen im Verlauf der Zeit erkennbar sind. Ergebnis der Phase 4: Granulare Daten, die sortiert und übersichtlich für das Aufbauen der Endbenutzersichten bereit stehen. Das sind Bewegungsdaten, Referenzdaten, Stammdaten. Phase 5 Endbenutzersichten aufbauen Die granularen Daten des Enterprise Layer sind aufgrund ihrer Komplexität und granularen Form noch nicht Endbenutzer-­‐tauglich. Ausgehend von den Anforderungen der Endanwender werden Daten passend bereitgestellt: •
Man überführt die Daten i. d. R. in eine multidimensionale Form (Star Schema oder OLAP-­‐Würfel), also denormalisiert. Das kann ein einfacher Kopiervorgang sein, ist aber oft ein Join über eine Reihe von Stammdaten-­‐ oder Referenztabellen und einem Ausschnitt aus den Bewegungsdaten des Enterprise Layer. •
Man erstellt Aggregate wenn diese als Verdichtungen angefordert werden. Die Aggregate können als View (Materialized View) realisiert sein, so dass kein ETL-­‐Schritt modelliert werden muss. •
Es erfolgt der Aufbau von Kennzahlensystemen als Kombination von aufeinander aufbauenden Aggregaten. •
Es sind Datengruppen für spezifische Analysearten zu erstellen (z. B. Data Mining – Daten). •
Ergänzt werden die Daten durch zusätzliche Statistiken und Übersichten über das Data Warehouse System für die Endanwender (z. B. Informationen über die Aktualität der Daten, Verantwortliche, Qualitätszustand usw. ). Ergebnis der Phase 5: Durch Business Intelligence-­‐Tools leicht abgreifbare und vorstrukturierte Daten. Die Verteilung von Ladeaktivitäten im Schichten-­‐Modell Das Schichten-­‐Modell kann und sollte auch zur besseren Organisation des ETL-­‐Prozesses beitragen. Ein gut organisierter ETL-­‐Prozess versucht so viele Transformationen so früh wie möglich durchzuführen, damit diese nur ein einziges Mal zu erledigen sind. Übertragen auf das Schichten-­‐Modell bedeutet dies, dass die meisten Transformationen innerhalb der Stage-­‐Schicht stattfinden sollten. Auf dem Weg der Daten in die Data Mart-­‐Schicht sollten dagegen nur einfache, aggregierende Join-­‐Operationen erfolgen. Zusam m enziehen von Trans fo rm a tio n en in d en B ereich d er Sta g e -­‐Sch ich t 54 Alle ETL-­‐Aktivitäten sollten zusammenhängend über den kompletten Informationsbeschaffungsweg in ein Gesamtkonzept eingebunden sein. Nur so kann man redundante ETL-­‐Schritte verhindern. Redundante ETL-­‐Schritte treten oft bei isoliertem Vorgehen in der Data Mart-­‐
Schicht (User View Layer) auf, wenn „mal eben schnell“ neue Daten bereitgestellt werden. Hat man eine Übersicht über alle bereits 41
existierenden ETL-­‐Schritte und Daten, kann man eher eventuell bereits bestehende Resourcen wiederfinden . Stichwort „1 zu 1 Kopien“ 1 zu 1 Kopien, d. h. die Bewegung von Daten im Data Warehouse, ohne dass an den Daten etwas verändert wird (im besten Fall natürlich die Daten veredelt, verbessert, verfeinert wird), sollte man vermeiden, weil sie keinen Mehrwert schaffen und nur kosten. Es gibt eine Reihe von Gründen, weshalb man 1:1 – Kopien fast in jedem Data Warehouse – System findet. Die Ursachen liegen fast immer in der fehlenden ganzheitlichen Betrachtung des kompletten Informationsbeschaffungsprozesses ohne sich über die Konsequenzen Gedanken gemacht zu haben: •
•
•
•
•
Technikbrüche in der Datenhaltung. Gerade im Data Mart-­‐Bereich gibt es in Unternehmen oft separate Datenhaltungs-­‐Systeme, die nur zur Speicherung eines Data Marts für eine spezielle Zielgruppe betrieben werden. Meist liegen die Daten im Data Warehouse schon passgenau vor. Sie werden jedoch über ein Netzwerk auf einen zusätzlichen Server mit einer separaten 42
Datenhaltung kopiert. Solche zusätzlichen Server kann es aus historischen Gründen geben oder weil man glaubt, die Daten müssten möglichst nah an die Zielgruppe herangeführt werden. Bestimmte Business-­‐Intelligence-­‐Tools (BI) verlangen nach einer BI-­‐Tool-­‐spezifischen Datenhaltung und empfehlen gleich den Aufbau einer spezifischen Datenhaltung. Das zentral verwaltete Data Warehouse stellt „Daten zur Verfügung“. Diese oft zu hörende Formulierung ist eine Umschreibung für eine tatsächlich stattfindende 1:1 Kopie. Data Warehouse Architekten nehmen die Theorie des Schichtenmodells zu genau und definieren Schnittstellen z. B. zwischen der Data Warehouse und der Data Mart –Schicht. Die Bedienung der Schnittstellen bedeutet dann 1:1-­‐Kopie. Die Systemverantwortlichkeit von Data Warehouse und Data Mart (als Endbenutzerdatenhaltung) sind unterschiedlich und man besteht aus organisatorischen Gründen auf einer eigenständigen Datenhaltung. Datenbankadministratoren bestehen darauf, dass Endbenutzer nicht auf die zentrale Data Warehouse – Schicht zugreifen dürfen. Warum auch immer. Das Thema Security wird oft vorgeschoben oder die Angst davor, dass unglücklich formulierte Endbenutzerabfragen das zentrale Data Warehouse in Knie zwingen könnten. Also stellt man dedizierte Benutzer-­‐Datenbanken 43
zur Verfügung, die dann wieder mit 1:1 Kopien zu bedienen sind. Ein Nachteil dieser Vorgehensweise ist der zusätzliche ETL-­‐Aufwand und damit verbunden Laufzeit, Rechenressource, Speicher-­‐Ressourcen. Ein zweiter oft unterschätzter Nachteil ist der, dass mit der Freigabe sogenannter Endbenutzer-­‐Datenbanken oder freigegebener 1:1-­‐
Kopien, sich die Verwaltung des zentralen Data Warehouse nicht mehr verantwortlich für diese Daten fühlt. Es entwickeln sich nachgelagert eigenständige ETL-­‐Aktiviten, entweder manuell durchgeführt oder mit Hilfe komfortabler BI-­‐Tools. Durch diese Loslösung verliert man die Chance Endbenutzer-­‐Informationsanforderungen rechtzeitig zu erkennen und zu versuchen, diese global, also möglichst frühzeitig im Informationsbeschaffungsprozess bereits in dem zentralen Data Warehouse zu lösen. U ngeschickte V erteilung von D ata W arehouse -­‐Sch ich ten a u f u n tersch ied lich e H a rd w a re-­‐G ruppen führt zu unnötigen 1:1 -­‐Ko p ien Man erkennt wie wichtig die umfängliche und ganzheitliche Planung des ETL-­‐Prozesse ist. Die Planung des ETL-­‐Prozesses beginnt bei den Informationsbedürfnissen aller Endbenutzer. Steht der Informationsbedarf fest, kann entschieden werden, wo in in welcher Art und Weise Informationen möglichst Ressourcen-­‐schonend beschafft werden können. Wenn man den gesamten Informationsfluss betrachtet, hat man größten Freiheitsgrade bei der Gestaltung. Immer wieder kehrende Transformationen kann man an zentraler Stelle positionieren, damit sie nur einmal, durchlaufen werden müssen. Spezifische Transformationen werden dagegen in Richtung User View-­‐Layer verschoben. Das ist aber nur möglich, wenn sich alle Daten nur in einem zusammenhängenden Medium – in einer Datenbank -­‐ befinden. Technikgrenzen, Systemgrenzen, wie sie automatisch bei separaten Datenbanken und Server entstehen, sind dabei hinderlich. 41
Auch ETL-­‐Schritte sollten in einem Metadaten-­‐Repository erfasst und dokumentiert sein. 42
Eine 1:1 Kopie ist es auch dann noch, wenn bei diesem Vorgang Feldtyp-­‐bezogene Anpassungen gemacht werden. 43
Security-­‐ Anforderungen sollten durch einen Objekt-­‐bezogenen Schutz und nicht durch Kopien gelöst werden. 55 Zusam m enhängende Speicherung aller D ata W arehouse -­‐Sch ich ten a u f ein er H a rd w a re-­‐G ruppe schafft Sysgerien und m indert den Aufw and Wiederholbarkeit des Ladelaufs Ladeläufe sollten generell so gestaltet sein, dass sie jederzeit mit der gleichen Quelldatenmenge wiederholt werden können. Hilfsmittel hierfür sind: •
Flashback (Oracle Datenbank -­‐ Flashback, damit wird ein Zustand vor der Ladeaktivität wieder eingestellt). •
Steuerung über eindeutige Schlüssel. Hierbei merkt man sich die durch die letzte Ladeaktivität neu entstandenen Schlüssel. Durch Mengen-­‐basiertes oder durche einzele Deletes löscht man fehlerhaft geladene Sätze und setzt so die durch den ETL-­‐Lauf gemachten Änderungen wieder zurück. •
Laden in ein separates Datenschema und späteres Überführen in das eigentliche Zielschema bzw. Laden in getrennten Partitionen. •
Steuerung über Inkrement-­‐Load-­‐Nummer oder Tagesdatum. Mengen-­‐basiertes oder Einzel-­‐Delete löscht die fehlerhaften Sätze im Fehlerfall. Die Wiederholbarkeit wird durch die Einteilung des gesamten Ladelaufs in sog. Load-­‐Inkremente unterstützt. Man unterteilt den Gesamtlauf in einzelne Ladestrecken-­‐Etappen. Orientieren kann man sich dabei an o
Den Schichten (Integration-­‐, Enterprise-­‐, User View Layer) o
Den Quellsystemen (für jedes Quellsystem eine eigene Ladestrecke) o
Zielsystemen (User View Layer / Data Marts). o
Datum / Zeit (wenn zeitabhängiges Laden möglich ist, z. B. Vormittags-­‐Load/Nachmittags-­‐Load) o
Versionen (das ist eine künstliche Einteilung z. B, durch eine laufende Nummer) Load-­‐Inkremente können in den Schichten unterschiedlich behandelt werden und realisiert sein. Während sie in dem Integration Layer oft einfach nur neu gestartet werden, sind im Enterprise Layer eventuell Vorbereitungen für einen Wiederholerlauf nötig. V erteilung von Load -­‐In krem en ten a u f d ie W a reh o u se-­‐Sch ich ten Load Inkremente können schließlich mit einem Load-­‐Manager verwaltet werden. Der Load Manager stellt nur ein einfaches Tabellenwerk dar, in dem alle Load-­‐Inkremente erfasst sind. Über eine zentrale Load-­‐Nummer (RUN_NUMBER) werden alle Inkremente eines zusammenhängenden Ladelaufs identifiziert. 56 Tabellen des Load -­‐M a n a g ers Deltadatenerkennung Das Erkennen von geänderten Daten der operativen Vorsysteme stellt eine besondere Herausforderung dar. Hierfür gibt es folgende technische Möglichkeiten: •
Das Vorsystem liefert nur die geänderten Daten. Dies stellt die einfachste Lösung dar, sie ist jedoch selten, da dies zusätzlichen Aufwand für die Vorsysteme bedeutet. Allerdings kommt es im Umfeld von SAP R/3 oft vor, weil R/3 sehr stark gekapselt ist und verantwortliche Stellen ungern Zugriffe von aussen erlauben. •
Abgreifen über Zeitstempel in den Quell-­‐Tabellen. Diese einfache Methode funktioniert nur, wenn es solche Zeitstempel gibt. •
Erstellen von Triggern im Quellsystem, die jede Quelldatenänderung in temporäre Tabellen schreiben. Diese Variante ist nicht zu empfehlen, da sie das Quellsystem beeinflusst und bei Massenänderungen belastet. •
Auslesen der Log-­‐Dateien. Diese im Prinzip sehr gute Variante funktioniert jedoch nur bei Datenbanken mit Log-­‐Dateien . •
Komplett-­‐Kopie und SQL-­‐Minus-­‐Operation. Diese auf den ersten Blick sehr aufwendige Operation funktioniert in der Praxis trotz der großen Datenmenge, die bewegt werden muss, erstaunlich gut, da sie zu den schnellen Mengenoperationen in der Datenbank gehört. i44
D elta -­‐D a ten-­‐Erkennung m it SQ L -­‐M in u s-­‐O peration Altbacken, aber immer noch die Masse aller Input-­‐Daten für das Data Warehouse (External Tables) Eine der wichtigsten Datenquellen sind auch heute noch Textdateien. Für Real time –Systeme oder vollständig mit Hilfe von Messages kommunizierende Systeme stellen Textdateien natürlich ein rotes Tuch dar. Aber sie bieten auch heute noch eine Reihe von Vorteilen für das Data Warehouse: •
•
•
•
Sie definieren schon alleine durch ihr Dasein einen Konsistenzpunkt. D. h. die Tatsache, dass eine Datei in dem Dateiverzeichnis bereit liegt, kann den Startpunkt für den ETL-­‐Prozess definieren, und damit auch die Verantwortung über die weitere Verarbeitung der Daten. Sie stellen automatisch ein Archiv der geladenen Daten dar, ohne dass man sie in die Warehouse-­‐Datenbank geladen hat. Man kann (wenn mit External Tables gearbeitet wird) gezielt Teile daraus extrahieren, ohne das Warehouse-­‐System mit dem kompletten Quellbestand zu belasten. Das Textdatei-­‐Format ist meist der kleinste gemeinsame Nenner, wenn zwei Anwendungen mit einander kommunizieren sollen, d. h. es müssen keine aufwendigen Schnittstellen realisiert werden. In vielen Data Warehouse Systemen sind auch heute noch weit über 50% der Eingabedaten Flatfiles. Bei dem Einladen von Textdateien in das Data Warehouse sind mehrere Herausforderungen zu lösen: •
Automatisierung bei der Bereitstellung und Kennzeichnung der Bestände, z. B. müssen täglich gelieferte Dateien entsprechend ihrem Lieferzeitpunkt oder sonstigen Eigenschaften bei dem Einlesen interpretiert werden. 44
Oracle Golden Gate nutzt diese Variante. 57 •
•
Zeichensatzkonvertierung (EBCDIC,ASCII, nationale Schreibweisen usw.). Vollständigkeitsprüfung. Bis auf die Vollständigkeitsprüfung lassen sich diese Anforderungen mit dem Oracle Feature External Table lösen. External Tables haben ab dem Datenbank-­‐Release 9 den SQLLoader (den es weiterhin gibt) abgelöst. Mit External Tables lassen sich Text-­‐Dateien direkt aus der Datenbank heraus zugreifen. Man kann Sie wie normale Tabellen mit SQL-­‐SELECT lesen. Die praktische Erfahrung zeigt viele Vorteile gegenüber dem SQLLoader: •
Das 1:1-­‐Laden mit dem Loader in die üblichen Staging-­‐Tabellen entfällt, weil mit External Tables bereits bei dem externen Zugriff selektiert, gefiltert bzw. formatiert werden kann. Der Ladevorgang gestaltet sich homogener. •
Joins auf externen Dateien sind möglich. •
Zum Filtern, Aggregieren und Wandeln sind SQL-­‐Mittel und PL/SQL-­‐Prozeduren einsetzbar. •
Das Datenbanksystem regelt selbständig die Parallelisierung zumindest für den INSERT-­‐Teil eines Lade-­‐Befehls wie INSERT INTO zieltabelle SELECT * FROM external_table_name. External Tables übernehmen automatisch die Typ-­‐Konvertierung, wenn das Datenbanksystem dies zulässt. Man definiert zum einen die Columns der Tabelle wie sie aus der Sicht der Datenbank benötigt werden und wie sie in die Zieltabelle zu schreiben sind. Zum anderen beschreibt man unter der FIELDS-­‐Klausel die Struktur der Werte, wie sie in der Textdatei vorkommen. Dabei können die Werte für die Textdatei auch als Character beschrieben werden, obwohl sie innerhalb der Datenbank als numerischer Wert deklariert sind. Wenn in der Textdatei für die entsprechende Spalte nur Zahlenwerte vorhanden sind, so überführt der Lesevorgang mit External Tables das Format von Text in einen numerischen Typ. Wenn keine numerischen Werte vorkommen, dann führt dies zu einem Fehler und man kann den Satz über den Parameter BADFILE in eine separate Fehlerdatei umlenken. Hier liegt noch eine Schwäche. Es wäre praktisch diese fehlerhaften Sätze ebenfalls in der Datenbank zu haben, um sie innerhalb der Datenbank weiter verarbeiten zu können. So muss man einen weiteren Verarbeitungsschritt einbauen und auch eine BADFILE-­‐Datei zusätzlich als Textdatei einlesen. Wie die Typ-­‐Konvertierung, so werden auch Zeichensätze automatisch in das von der Datenbank genutzte Format überführt. Das ist bei EBCDIC-­‐Dateien praktisch, weil man keine separaten Konverter mehr braucht. Die Felder in der Textdatei können durch ein bestimmtes Zeichen (Komma, Semikolon etc.) getrennt sein, oder die Felder sind durch ihre Länge beschränkt (Fixed Length Fields). All diese Einstellungen nimmt man in der FIELDS-­‐Sektion der External-­‐Table-­‐Beschreibung vor. CREATE TABLE external_table_name
(
Feld1
INTEGER,
Feld2
VARCHAR2(255),
...)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY directory_name
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
CHARACTERSET WE8MSWIN1252
STRING SIZES ARE IN BYTES
BADFILE ’src.bad’
DISCARDFILE ’src.dis’
LOGFILE ’src.log’
FIELDS
TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"' AND '"'
NOTRIM
(
Textfeld1 CHAR,
Textfeld2 CHAR,
...
))
LOCATION ('filename.LST'
))
REJECT LIMIT UNLIMITED
NOPARALLEL;
Bei der Definition einer External Table kann das Beschreiben der vielen einzelnen Parameter mühsam sein. Auch die Beschreibung von Feldlängen bei Fixed Length-­‐ Dateien kann schwerfallen, wenn man die korrekten Feldlängen nicht weiß. Dann hilft nur ein Blick in die Datei. Dazu benötigt man dann wieder einen Text-­‐Editor, der zum einen größere Datenmengen lesen (bzw. einschränken kann) und zum anderen eine Zeichenkonvertierung (z. B. EBCDIC nach ASCII) vornimmt. Wenn man Oracle Warehouse Builder nutzt, stehen alle diese Funktionen in einer graphischen Oberfläche zur Verfügung und die Definition von External Table erfolgt in kürzester Zeit. 58 G raphische Erfassung von Textdateien Schnelles Prüfen und Laden mit SQL-­‐Mitteln – Nutzen des „Mengen Paradigmas“ Mengen-­‐basiertes ETL Die meisten der oben genannten technischen Mittel ermöglichen das sogenannte Mengen-­‐basierte Laden mit SQL. SQL ist eine „Mengen-­‐
bezogene“ Sprache und die Oracle Datenbank ist auf den Umgang mit Mengen spezialisiert. Dies ist der entscheidende Punkt, wenn nach schnellem und langsamen ETL gefragt wird. Es gibt kein schnelleres mit DML-­‐Operationen arbeitendes ETL, als das mengenbasierte 45
Vorgehen (oder auch SET-­‐Based). Um performante ETL-­‐Prozesse zu erreichen muss man konsequent jeden Teilschritt Mengen-­‐basiert umsetzen das bedeutet: •
Ohne Datenbank-­‐Constraints arbeiten. Diese in OLTP-­‐Umgebungen wichtige Features führen zur Einzelsatzverarbeitung und bremsen. •
Ohne Trigger arbeiten. •
Nur in Index-­‐lose Tabellen schreiben, d. h. Indexe vor dem Laden großer Datenmengen zunächst löschen und dann wieder neu anlegen. •
Logging temporär für den Zeitraum des Ladens deaktivieren. •
Mit External Tables anstatt mit dem SQL Loader arbeiten. Laden mit aktiven Constraints DML Error Logging – Eindeutigkeit prüfen Mengen-­‐basiertes Laden mit aktiven Constraints auf den jeweiligen Tabellen ist zunächst nicht möglich, weil ein INSERT unabhängig von der Menge der zu ladenden Sätze abbricht, sobald eine Constraint-­‐Verletzung auftritt. Damit hat man nicht nur nicht seine Sätze geladen, sondern man weiß auch nicht welcher Satz den Constraint verletzt hat. Die klassische Lösung ist eine PL/SQL-­‐Prozedur mit einer Schleife, in der alle Inserts als langsame Einzelsatzverarbeitung abgesetzt und in der der Return-­‐Code abgefragt wird. Das Feature DML Error Logging löst diese Situation. Man kann viele einzelne Inserts (aber auch Updates und Merges) auf eine Tabelle mit aktiven Constraints absetzen. Der Vorgang bricht bei einer Constraint-­‐Verletzung nicht ab und man hat dennoch die fehlerhaften Eingabesätze in einer sog. Error_Log-­‐
Tabelle. In dem folgenden Beispiel sind 1 Million Sätze einer Quelltabelle (KD_X) in eine Zieltabelle (EL_KUNDE) mit aktivem Unique Key Constraint (KUNDENNR) eingefügt worden, drei Sätze enthalten eine doppelte Kundennummer: insert into el_kunde select * from kd_x LOG ERRORS INTO el_kunde_errors REJECT LIMIT
UNLIMITED;
1000000 Zeilen erstellt.
Abgelaufen: 00:00:38.68
select KUNDENNR,ORA_ERR_MESG$ from el_kunde_errors where rownum < 3;
KUNDENNR
ORA_ERR_MESG$
--------------- -------------------------------------------------366
ORA-00001: Unique Constraint (DWH.PK_KD) verletzt
1556
ORA-00001: Unique Constraint (DWH.PK_KD) verletzt
6734
ORA-00001: Unique Constraint (DWH.PK_KD) verletzt
45
Es gibt ETL-­‐ Lösungen, die die reine Transformations-­‐ und Prüflogik in C-­‐Routinen ausgelagert haben. Diese sind extrem schnell und sicher auch schneller als eine in PL/SQL implementierte Logik. Aber auch solche Routinen müssen zu einem bestimmten Zeitpunkt Daten aus einer Datenbank lesen und auch wieder schreiben. Dann können diese Werkzeuge die Vorteile eines kompilierten C-­‐Programms nicht mehr ausspielen. Es bleibt die Frage, wie häufig bestimmte Logiken extrem schnell abgearbeitet werden müssen und ob es nicht einfacher ist, solche C-­‐Programme aus der Datenbank heraus aufzurufen oder ob nicht doch die Performance einer komplierten PL/SQL Routine ausreicht. 59 Diese Lösung scheint langsam zu laufen. Der Vorgang, benötigt 38 Sekunden. Die Prüfung mit DML Error Logging ist daher nur für eher kleinere Datenmengen einsetztbar. Die folgende Lösung ohne das DML_Error-­‐Logging ist schneller und ist bei mehreren Millionen Sätzen zu bevorzugen: SQL> Create table non_uni as
2
select distinct kundennr
3
(select count(*) c, kundennr
from
from kd_x group by kundennr) where c > 1;
Tabelle wurde erstellt.
Abgelaufen: 00:00:00.90
SQL> insert into el_kunde
2
select * from kd_xx where kundennr not in (select * from non_uni);
999997 Zeilen erstellt.
Abgelaufen: 00:00:01.94
Es werden zunächst die mehrfachen Einträge in der Quelltabelle in eine Zwischentabelle geschrieben. Dann überführt man nur die Quellsätze in die Zieltabelle, die nicht in der Zwischentabelle stehen. Das sieht aufwendig aus, weil die Quelltabelle zweimal gelesen wurde. Es ist in der Summe mit unter 3 Sekunden aber wesentlich schneller als die oben vorgestellte DML Error Logging-­‐Methode. Typ-­‐Format, Null-­‐ und Werte-­‐Prüfungen Das Prüfen von Feldtypen, z. B. numerisch, Datums-­‐ und Zeitangabe sind aufwendige Prüfungen, weil hierfür oft Hilfsfunktionen nötig sind, die zu dem Anfassen von jedem einzelnen Satz führen. Und das bremst die Ladeperformance. Liegen die zu ladenden Daten noch als Textdatei vor, so kann man die Prüffunktionalität des SQL-­‐Loaders bzw. der External Tables nutzen. Sätze mit fehlerhaften Feldinhalten lassen sich hier in separate Fehlerdateien aussortieren. Allerdings fehlt dann die Information über die genaue Fehlerursache. Wenn man schon prüfen und jeden Satz einzeln anfassen muss, sollte man alle Prüfungen dieser Art an einer Stelle zusammenfassen. Dabei überführt man die Sätze der zu prüfenden Tabelle in eine temporäre Hilfstabelle, die neben den Spalten der zu prüfenden Tabelle zusätzliche Flag-­‐Spalten für jede Prüfung und jedes Feld enthält. Kern von solchen Prüfungen ist das CASE – Statement. Hierüber kann man eingebettet in eine SELECT-­‐Abfrage für einzelne Spalten bedingte Selektionen durchführen und damit prüfen. Werte-­‐Prüfungen ( „> 0“, „between 10 and 20“, „substr(Feld_X,1,3) = ‘ABC‘“) sind einfach machbar. Die Prüfung zur Korrektheit eines Datumsfelds oder Feldtyp „numerisch“ kann nur über eine separate Funktion 46
geschehen . Die Flag-­‐Felder markiert man entsprechend der Prüfergebnisse mit einfachen Code-­‐Werten wie 1 oder 0. Diese einfachen Zahlenwerte lassen sich später für die schnelle Berechnung von Qualitätsstatistiken gut nutzen. Die zusätzlichen Flag-­‐Felder in der so entstandenen temporären Tabelle können jetzt nach Belieben ausgewertet werden. Je nach Schwere und Häufigkeit bestimmter Fehler kann man entscheiden, ob ein Satz in die Zielumgebung überführt werden soll oder nicht. Die Lösung kann unterschiedlich aufwendig gestaltet sein, z. B.: 1.
2.
3.
Im einfachsten Fall fragt man bei der Überführung in die Zieltabelle die einzelnen Flag-­‐Felder ab. Man kann diese Überführung aber auch flexibel gestalten, indem man die Argumente in der WHERE-­‐Klausel z. B. über dynamisches SQL variabel gestaltet. Der Prüfvorgang kann sogar von außen gesteuert werden, indem man die Codes der Flag-­‐Felder in eine Steuertabelle aufnimmt. In einer solchen Steuertabelle können alle Prüffälle mit ihren gültigen Code-­‐Werten in der temporären Tabelle erfasst werden. Damit hat man auch eine einfache Dokumentation von Prüfregeln. 46
Siehe Skripte [isDate] [isnumeric] in der Kurzreferenz 60 Mit Hilfe der temporären Tabelle ist eine Statistik über die Häufigkeit bestimmter Fehler für jedes Feld und jede Prüfung pro Feld und das Ganze für jeden Ladelauf möglich. Überführt man solche Statistiken für jeden Ladelauf in eine Fehlerstatistik-­‐Tabelle, so kann man die Qualität der Daten über die Zeit hinweg verfolgen. Das ist der erste Schritt in Richtung eines Datenqualitäts-­‐Dashboards mit dem man den Qualitätszustand von neuen Daten für das Data Warehouse monitoren kann. Umschlüsselung von Stamm-­‐ und Bewegungsdaten Data Warehouse-­‐Systeme führen eigenständige, von den Vorsystemen unabhängige Schlüssel für die Stammdaten bzw. gespeicherte Geschäftsobjekte. Diese künstlichen Schlüssel bildet man bei dem Aufbau der Stammdatentabellen im Data Warehouse meist bei der Überführung der Stammdaten der Vorsysteme in das Data Warehouse. Ein klassisches Datenbankhilfsmittel hierfür sind die SEQUENCE-­‐
Objekte, die bei einem Zugriff mit Select pro Zugriff einen aufsteigenden sequentiellen Zahlenwert liefern. Sequences sind das einfachste Mittel, allerdings nicht immer praktisch, weil sie ohne Rücksicht auf außergewöhnliche Ereignisse, z. B. die Wiederholung eines Ladelaufs, immer weiter aufsteigende Zahlenwerte liefern. Es können Lücken in einer aufsteigenden Nummernfolge entstehen. Es können im Extremfall sogar doppelte Werte entstehen, wenn Sequences versehentlich gelöscht und dann wieder angelegt werden. Daher haben sich eine Reihe von Alternativen herausgebildet. Künstliche Schlüssel sollten einfach gehalten sein, aber dennoch dauerhaft Bestand haben. Eine Lösung ist die Verwendung des aktuellen Tagesdatums als numerischer Wert in Verbindung mit dem ROWNUM-­‐Wert eine Daten-­‐liefernden Abfrage. insert into Zieltabelle select to_number(to_char(sysdate,'YYYYMMDD'))||rownum nummer ,wert from Quelltabelle;
Bei dieser Lösung darf der Ladelauf nur einmal am Tag stattfinden, damit keine doppelten Werte entstehen. Man kann den Schlüssel noch um eine Ladenummer oder Laufnummer ergänzen. Die Ladenummer kennzeichnet jeden Lauf für eine bestimmte Datenmenge. Sie wird in einer Kontrolltabelle für die ETL-­‐Läufe gebildet und kann für ein „logisches Undo“ genutzt werden, d. h. um Sätze im Fehlerfall wieder aus dem Zielbestand zu entfernen. variable laufnummer number;
begin
select max(nr) into :laufnummer from kontrolltabelle;
end;
/
insert into Zieltabelle
select to_number(to_char(sysdate,'YYYYMMDD'))||lpad(:laufnummer,0,5)||rownum nummer ,wert
from Quelltabelle;
Bei dieser Lösung kann man zu einem späteren Zeitpunkt noch feststellen, wann und mit welchem Ladelauf der Satz zum ersten mal gebildet wurde. Es entstehen Schlüssel nach dem Format: 20120202000041
20120202000042
20120202000043
20120202000044
Durch das Auffüllen mit Null-­‐Werten befindet sich alle Teilwerte immer an der gleichen Position (20120202 00004 1) Es gibt sicher noch eine Reihe weiterer Varianten einen Schlüssel zu bilden, z. B. kann man einen Präfix-­‐String mit einer Abkürzung passend zur Stammdateninformation hinzunehmen. Damit kann man den Schlüssel bei der Verwendung als Foreign-­‐Key in den Sätzen einer Child-­‐
Tabelle leicht erkennen. Allerdings handelt man sich damit einen alphanumerischen Schlüssel ein. Das muss nicht nachteilig sein. Das Datenbanksystem arbeitet auch damit performant. In speziellen Situationen will man jedoch sogar mit Schlüsseln rechnen. Der Weg bleibt dann versperrt. Das Umschlüsseln der Stammdaten ist vergleichsweise einfach. Die Bewegungsdaten die in der Regel die Original-­‐Primary Keys der Stammdaten der Vorsysteme als Foreign Keys mit sich führen, müssen, nachdem die Stammdaten gepflegt wurden, auch noch umgeschlüsselt werden. Während die Stammdaten noch ihre Originalschlüssel mitführen, entfällt bei den Bewegungsdaten der ursprüngliche Foreign Key komplett. Er wird im Data Warehouse nicht mehr gebraucht. Das Herleiten des Originalzusammenhangs erfolgt über die neu gebildeten Stammdaten in dem Data Warehouse bzw. über die Dimensionstabellen. Das Umschlüsseln der Bewegungsdaten geschieht mit einem Lookup: Man bildet einen Join zwischen der Bewegungsdatentabelle und den bereits aktualisierten Stammdatentabellen über den Originalschlüssel aus dem operativen Vorsystem und findet den in dem Data Warehouse neu gebildeten Stammdatenschlüssel. Dieser Vorgang setzt voraus, dass es einen passenden Referenzsatz in der Stammdatentabelle gibt. Um den Ladeprozess nicht durch die Datenbank abbrechen zu lassen, wird man einen Outer-­‐Join verwenden. Im Fehlerfall, wenn in der Lookup-­‐Stammdatentabelle kein passender Wert gefunden wird, wäre das entsprechende Foreign Key – Feld in der Zieltabelle leer. Es kann schwierig sein, diese Sätze nachträglich zu pflegen. Daher sollte man vor einem solchen Ladeprozess die Existenz der Referenzsätze prüfen. Man sucht alle operativen Schlüsselwerte der Bewegungsdaten aus dem Vorsystem, die nicht als Business Key in der Stammdatentabelle (oder Dimensionstabelle) des Data Wasrehouse enthalten sind. Für die gefundenen Werte errstellt man eine Art Dummy-­‐Satz in der Stammdatentabelle des Data Warehouses mit einem neuen künstlichen Schlüssel. Der eigentliche ETL-­‐Lauf zum Füllen der Zieltabelle kann jetzt wie erwartet umschlüsseln, ohne dass er abbricht. Die Zieltabelle muss nicht mehr nachbearbeitet werden. Man muss jedoch die Stammdatentabelle in dem Data Warehouse korrigieren, was in der Regel der geringere Aufwand ist. 61 H era n zeieh en d er kü nstlich en Sch lüssel aus ein er D im en sio n un d B ild en vo n tem p o rä ren Sch lü sseln Einfache aber schnelle mengenbasierte SQL-­‐Hilfsmittel (Multiple Insert und Merge) Die Weiterentwicklung der SQL-­‐Sprache hat in den letzten Jahren die Anforderungen des schnellen Ladens von Daten in Tabellen besonders unterstützt. Mit dem „Multiple Insert“ und dem „Merge“-­‐Feature stehen zwei praktische Hilfsmittel zur Verfügung. Das Multiple Insert erlaubt es in Abhängigkeit von Werten einer Spalte einer Quelltabelle den Quelldatenstrom in mehrere Zieltabellen zu lenken. Die Quelltabelle muss dabei nur einmal gelesen werden. Man kann dieses „Splitten“ in dem Data Mart-­‐Bereich nutzen, um Teilmengen mit besonderen Merkmalen zu bilden. Ein weiterer Einsatzfall ist das Aussortieren von fehlerhaften Sätzen in eine „Bad“-­‐Tabelle, wenn man fachbezogene Prüfkriterien hat und fehlerhafte Sätze zur Nachbearbeitung separieren will. Das Merge-­‐Statement vereint UPDATE und INSERT. Es entfällt bei einem INSERT in eine mit einem Primary Key eindeutig gemachten Zieltabelle die Prüfung, ob ein einzufügender Satz in der Zieltabelle bereits vorhanden ist. Diese Prüfung wurde in der Vergangenheit immer in einer programmierten Prozedur mit einem einzelnen INSERT und dem Abfangen des potentiellen Fehler-­‐Codes der Datenbank realisiert, was eine langsame Einzelsatzverarbeitung darstellte. Das MERGE-­‐Statement erledigt diese Prüfung Datenbank-­‐intern, ohne dass man sie ausprogrammieren muss. Hierfür muss die Zieltabelle jedoch einen aktivierten Primary Key Constraint besitzen. Dennoch ist ein MERGE schneller als die programmierte Lösung und sollte immer bevorzugt werden. Programmiertes Parallelisieren (Pipe-­‐Verfahren mit Table Functions) SQL ist eine Programmier-­‐ und Abfragesprache mit der man fast alle Anforderungen umsetzen kann. Bestehende Grenzen wird man mit den Standardanforderungen, wie sie in einem klassischen Data Warehouse vorkommen, kaum erreichen. Umfangreiche SQL-­‐
Programmierung kann aber unpraktisch und unübersichtlich werden. Komplexe und bedingte Gruppenwechsel sind mit reinem SQL herausfordernd, aber mit geschachtelten Schleifen in PL/SQL sehr einfach und bequem. Auch wenn man gute SQL-­‐Kenntnisse besitzt und eine Abfrage mit reinem SQL lösbar wäre, sollte man Aufwand und Nutzen in Einklang bringen und PL/SQL als Programmiermittel hinzuziehen. Dann allerdings vergräbt man die Logik einer Verarbeitung in Prozeduren, die wie eine Black Box wirken. Ist man gewohnt SQL wie Fließtext zu lesen, dann stört die Black Box. Gut wäre es, wenn man Bausteine mit einer Input-­‐ und Output-­‐Schnittstelle frei programmieren könnte und diese Bausteine dann als Bestandteil in dem SQL-­‐Text einfach benutzt. Ein zweiter unschöner Effekt von reinem SQL ist die manchmal schwer beinflussbare Reihenfolge in der Abarbeitung. Wenn z. B. das Ergebnis der Abarbeitung einer Datenmenge A als Input für eine Folgeverarbeitung mit einer Datenmenge B gebraucht wird, dann wird die Datenbank erst die Datenmenge A vollständig bearbeiten bevor die Datenmenge B an der Reihe ist. Ein Kommando der Struktur SELECT a,b,c,d FROM X, (SELECT l,m,n FROM Y) WHERE .... wird erst das Sub-­‐Select in der FROM – Klausel auflösen, bevor mit der Verarbeitung der übrigen Bestandteile des Haupt-­‐Selects begonnen wird. Das kann bei großen Datenmengen hinderlich sein. Geschickter wäre es wenn das Sub-­‐Select bereits fertige Ergebnissätze freigibt, während es noch weiter am arbeiten ist, so dass das Hauptstatement mit seiner Arbeit bereits beginnen kann. Beides ist mit Table Functions machbar. Table Functions stellen in der FROM-­‐Klausel eines SELECT-­‐Statements einen Platzhalter für die dort normalerweise aufgeführten Tabellennamen dar. Wie eine Tabelle liefern sie Sätze. Aber die Sätze werden über eine PL/SQL-­‐
Programmierung erstellt und über die FROM-­‐Klausel dem SELECT-­‐Statement geliefert. Eine Table Function kommuniziert mit dem aufrufenden SELECT-­‐Statement über eine zu definierende virtuelle Record-­‐Struktur, d. h. auch bei dem Einsatz einer Table Function wird der FROM-­‐Klausel eine Tabelle übergeben, aber diese befindet sich bereits im Hauptspeicher und wird mit PL/SQL-­‐Variablen in der Prozedur gefüllt, ohne dass physikalische IOs stattfinden. Die Table Functions können diese virtuelle Tabelle entweder komplett in einem Schritt an das aufrufende Statement übergeben (RETURN OBJSET) oder satzweise (PIPE ROW a,b,c;). Mit der letzten Variante lässt sich eine Parallel-­‐Verarbeitung logisch programmieren. Meist wird man in der Table Function eine oder mehrere Schleifen programmieren und am Ende einer Schleifenverarbeitung über ein „PIPE ROW feld1, feld2...“ Kommando den gerade fertig erstellten neuen Satz nach oben weitergeben, so dass dieser Satz in dem aufrufenden SELECT schon weiter verarbeitet werden kann. 62 Erstellt man eine Table Function, so muss man sich zunächst mit dem Umgang mit virtuellen Tabellen vertraut machen. Virtuelle Tabellen sind das Übergabe-­‐Medium von der Table Function-­‐Prozedur zu dem aufrufenden SELECT-­‐Statement. Sie verfügen über die gleiche Feldstruktur, wie sie auch von einer Tabelle in der FROM-­‐Klausel erwarten wird. Diese Satz-­‐Struktur definiert man als Object-­‐Type – Definition CREATE TYPE record_type_name AS OBJECT (
Feld1
number(n),
Feld2
varchar2(n)
…);
Die Satzstruktur ist dann die Grundlage für die eigentliche TABLE-­‐Type – Definition: CREATE TYPE table_type_name AS TABLE of record_type_name;
Die im Hauptspeicher befindliche virtuelle Tabelle wird in der programmierten Table Function angelegt. Man nutzt dazu den oben definierten Table-­‐Type in der RETURN-­‐Klausel des Funktions-­‐Headers. Von jetzt an übergibt man mit der PIPE ROW – Anweisung, die einzelnen Sätze an das aufrufende SQL-­‐Select-­‐Kommando. Die PIPE ROW – Anweisung enthält Variablen, die in Anzahl und Typ der Satzstruktur der virtuellen Tabelle entsprechen. CREATE OR REPLACE table_function_name RETURN
IS
table_type_name PIPELINED
Var1
number;
var2
varchar2(n);
BEGIN
beliebiger Code;
LOOP
beliebiger Code;
PIPE ROW record_type_name(Feld1, Feld2);
END LOOP;
END;
Die Table Funktion wird in der FROM-­‐Klausel des aufrufenden Select wie eine normale Tabelle genutzt. SELECT * FROM TABLE(table_function_name());
Table Functions sind die letzte Wahl, wenn man mit reinem mengenbasierten SQL nicht mehr weiter kommt. Bei der Überarbeitung von zu lange laufenden ETL-­‐Strecken helfen Table Functions, um die Performance zu erhöhen. Der Vorteil liegt in der Pipeline-­‐artigen Verarbeitung und in der Ausnutzung von virtuellen Tabellen, die quasi im Hauptspeicher liegen. Schnelles Bewegen von großen Datenmengen innerhalb der Datenbank (PEL) Die meist wenigen sehr großen Tabellen in einem Data Warehouse Systemen sind fast immer partitioniert. Das sind i. d. R. die große Bewegungsdaten-­‐ und Faktentabellen in den Starschemen, Sie kommen in der Data Warehouse-­‐ und Data Mart-­‐Schicht vor. Hier hat man die durch den ETL-­‐Prozess neu hinzugekommenen Daten bereits geprüft und will sie nur noch in die bestehenden Tabellen einfügen. Bei den großen Tabellen nutzt man dazu das Partitioning und lädt Partitions-­‐weise. Die geprüften Sätze der Stage-­‐Schicht sammelt man dazu in einer temporären Tabelle. Durch den PARTITION EXCHANGE – Befehl hängt man diese temporäre Tabelle als weitere Partition an eine partitionierte Zieltabelle (Fakten-­‐ oder Bewegungsdatentabelle) an. Das Verfahren nennt sich Partition Exchange and Load (PEL). Warum schreibt man nicht gleich in die Zieltabellen? Dafür gibt es mehrere Gründe: •
•
•
•
Der Vorgang des PARTITION EXCHANGE ist nur ein Umhängen des Namens der temporären Tabelle in die Liste der Partitionsnamen einer anderen Tabellen in dem Datenbank-­‐Katalog. Die Daten werden selbst nicht bewegt. D. h. der Vorgang ist extrem schnell und kann auch durchgeführt werden, während die Zieltabellen von Endanwender genutzt wird (was man während vieler INSERT-­‐Vorgänge in die Zieltabelle eher nicht machen würde). Wird zunächst eine temporäre Tabelle geschrieben, kann immer noch entschieden werden, ob diese Sätze letztendlich in die Zieltabelle übernommen werden oder nicht. Die Vorgehensweise bietet mehr Kontrollmöglichkeiten. Die temporäre Tabelle wird über CREATE TABLE table_name AS SELECT.... (CTAS) angelegt. Das ist ein schnelles Schreiben (Direct Path). Die Datenbank schreibt ohne Suche nach freien Stellen in den Datenbankblöcken auf der Platte direkt in die über eine High Water Mark – Position markierten freien Stellen in den Blöcken. Ein weiterer Vorteil hängt mit der Indizierung der Zieltabelle zusammen. Werden Daten zu einer indizierten Tabelle hinzugefügt, so muss das Datenbanksystem den Index bei jedem hinzugefügten Satz aktualisieren. Das dauert wesentlich länger, als das Schreiben in eine nicht indizierte Tabelle. Partitionierte Tabellen sind meist lokal indiziert, also jede Partition hat ihren eigenen „privaten“ Index. Wenn eine Partition gelöscht wird, wird auch der lokale Index gelöscht, ohne dass der gesamte Index erneuert werden müsste. Umgekehrt muss auch nur ein lokaler Index für eine neu hinzugekommene Partition erstellt werden und nicht der gesamte Index. Bei dem Partition Exchange indiziert man die temporäre Tabelle bevor man sie über den Exchange-­‐Befehl an die Zieltabelle anhängt. Der Index der temporären Tabelle wird zum lokalen Index der neuen Partition. 63 Pa rtitio n Exch a n g e a n d Lo a d Generieren statt Programmieren: Mit Graphik modellieren Auch wenn mit Bordmitteln der Datenbank (SQL, PL/SQL) gearbeitet wird, sollten die nötigen Datenbank-­‐Objekte nicht manuell programmiert werden. Hierzu stehen heute graphische Editoren, wie z. B. durch Oracle Warehouse Builder (In Database ETL) oder Oracle Data Integrator zur Verfügung. G raphisches M odellieren und Schichtenm odell Das graphische Modellieren hat nur Vorteile: •
Es verhindert Fehler durch Handprogrammierung (Schreibfehler). •
Tabellen-­‐ und Spaltennamen müssen nicht mehr mühsam geschrieben werden (schnelleres Code-­‐Erstellen). •
Die Steuerung vieler Entwicklungsschritte durch Wizards verhindert das Vergessen einzelner Teile oder Schritte. •
Automatische Steuerung von Ziel-­‐ und Quellschemen. •
Automatische Validierung (z.B. Typverträglichkeiten). •
Debugging der Laderoutinen. •
Laufzeitumgebung wird mitgelierfert (ohne diese Werkzeuge müsste eine Laufzeit-­‐Umgebung entwickelt werden). •
Dokumentation aller Schritte mit graphischen Mitteln. 64 Umgang mit Hardware-Ressourcen im Data Warehouse
Hardware wichtiger Bestandteil im Data Warehouse Für die Konzeption von Data Warehouse-­‐Systemen ist in den letzten Jahren die Hardware noch stärker in den Fokus gerückt. Gründe sind die enormen Leistungsanforderungen eines DWH-­‐Systems gegenüber den meisten OLTP-­‐Anwendungen wobei z. T. sehr große Datenmengen in angemessener Zeit zu bearbeiten sind. Bei der Lösung dieser Herausforderung helfen gleich mehrere Technologien. Die Hardware stellt eine wichtige Voraussetzung dar. „Private“ Hardware: ungeschriebene Regel DWH Systeme nutzen Speicherplatten und Netzwerk in einer anderen Weise als OLTP-­‐Anwendungen. Daten werden in größeren Mengen und eher in zusammenhängenden Einheiten verarbeitet. In der Regel lesen Data Warehouse Systeme zusammenhängende Daten. Wichtig ist die Datenmenge pro Sekunde, die ein System bereitstellen kann (MB/sec). Es kommt darauf an eine möglichst hohe Menge an Daten (viele Megabyte oder Gigabyte pro Sekunde) für eine vergleichsweise geringe Anzahl an Benutzern oder auch nur einzelnen Benutzern zur Verfügung zu stellen. OLTP-­‐Systeme lesen und schreiben dagegen mit höherer Frequenz kleinere Datenmengen. Es werden einzelne Transaktionen verarbeitet und davon dann sehr viele für eine hohe Anzahl von Benutzern. Die Anzahl der IO-­‐Operationen pro Sekunde ist die Kenngröße (IOPS). Die Hardware kann nicht für beide gegenläufige Anforderungen gleichzeitig konfiguriert werden. Lange laufende Leseoperationen mit einem hohen Datenvolumen konkurrieren mit vielen anderen Prozessen, in denen kleine Datenmengen verarbeitet werden. Eine einfache Lösung ist das Trennen der Hardware für DWH und OLTP-­‐Systeme. DWH-­‐Systeme sollten über eigene Rechner, eigenen Speicher und ein separates Netzwerk verfügen, über das auf die Speicherplatten zugegriffen werden kann. Bei Performance-­‐Analysen ist dieser Punkt einer der ersten, der geprüft wird. Data Warehouse Systeme haben auch heute noch in einigen Unternehmen einen geringeren Stellenwert und man weist dem Warehouse „eine Ecke“ im zentral verwalteten Storage zu. So wirken sich gleich mehrere Effekte nachteilig für den Warehouse-­‐Betrieb aus: Konkurrierende Storage und Netzwerknutzung gehen zu Lasten der Performance in dem Data Warehouse. Data Warehouse – Daten werden wie OLTP-­‐Daten verwaltet, d. h. mit den gleichen Administrationsaufwende für Backup usw. o Unnötig lange Backup-­‐Zeiten. o Oftmals veraltete Patch-­‐ und Release-­‐Stände, weil produktive OLTP-­‐Systeme aufgrund des Fehlerrisikos seltener aktualisiert werden als Data Warehouse Systeme, die meist einen weniger hohen Verfügbarkeitsgrat haben. •
Die zentrale Verwaltung stellt für die Bereitstellung der Ressourcen und den Betrieb für das Data Warehouse die gleichen Kosten in Rechnung wie für OLTP Systeme. Weil Data Warehouse Systeme größere Datenvolumen besitzen, fällt auch ein relativ hohe Kostenanteil an das Data Warehouse. Hinzu kommt, die nicht differenzierbare Bereitstellung von Plattenspeicher. Hier werden meist aufgrund der Forderung nach einer hohen Ausfallsicherheit teure Plattenspeicher pauschal für alle Systeme genutzt, auch wenn das für Data Warehouse Systeme nicht nötig wäre. Unterm Strich erscheinen Data Warehouse Systeme bei einer nicht differenzierten Betrachtung von außen als besonders teuer, bei gleichzeitiger Benachteiligung z. B. bei Performance aufgrund konkurrierender Storage-­‐ und Netznutzung. In folgenden Bereichen kann gespart bzw. ressourcenschonender gearbeitet werden: •
Kostengünstigere Speicherlösung. Es müssen nicht die teuersten Plattensysteme sein, weil ein Data Warehouse meist nicht hochkritisch und hochverfügbar sein muss. •
Die Backup-­‐Konzepte können für Data Warehouse –Anforderungen angepasst werden. Schon alleine deswegen, weil nicht alle Daten gesichert werden müssen. •
Die Ausfallsicherheit kann durch günstigere Rechner im Clusterverbund günstiger gewährleistet werden, als durch teure große Systeme. Das ist einer der Gründe, weshalb sich in den jüngsten Jahren das Thema Data Warehouse -­‐ Appliance entwickelt hat. D. h. die Bereitstellung von einer fertig implementierten und konfigurierten Data Warehouse Maschine, die über ihr eigenes Netzwerk als Verbindung von Platten und Datenbankservern verfügt und über eigene Speicherplatten. In solchen Appliance-­‐Lösungen kann der Speicher 47
so angesprochen werden, wie es die Anforderungen in einem Data Warehouse verlangen . •
•
Die Hardware-­‐Konfiguration Ein Hardwaresystem besteht aus mehreren Komponenten (Speicherplatten, Netzwerk, Platten-­‐Controller, Servermaschinen mit Hauptspeicher und den CPUs). Die Dimensionierung dieser Komponenten muss aufeinander abgestimmt sein, denn die Gesamtleistung des Systems ist nicht die Summe der Leistungsfähigkeit von allen Komponenten, sondern sie kann nie besser sein, als das schwächste Glied in dieser Kette. Wenn das Ziel ein möglichst hoher Datendurchsatz gemessen im MB/Sek. ist, dann müssen die einzelnen Komponenten eine gewisse Datenmenge auch verarbeiten können. Ansonsten entsteht bei der schwächsten Komponente ein Flaschenhals. Die Hardware-­‐Entwicklung der vergangenen Jahre hat die Recheneinheiten, die CPUs, immer schneller und kostengünstiger gemacht. Auch wenn die Taktraten nicht mehr steigen, so steigt die Anzahl der sog. Cores in den CPUs. D. h. es stehen immer mehr parallelisierbare Recheneinheiten (Cores) zur Verfügung. Die zweite starke Weiterentwicklung fand in dem Bereich der Festkörperspeicher als Alternative zu Plattenspeicher mit Spindeln statt. Hier sind SSDs (Solid State Disks) bzw. Flash-­‐Speicher zu nennen, die heute in einem preislich attraktiven Bereich liegen. Bei den Spindel-­‐basierten Plattenspeichern ist offenbar das physikalische Limit erreicht. Schnelle Platten erreichen 15000 Umdrehungen in der Minute. Mehr geht bei vertretbarem Aufwand nicht mehr. 47
Z. B. Oracle Exadata 65 48
Heutige CPUs (Core) haben eine Taktrate von mindestens 2 GHz. Damit sind sie in der Lage ein Datenvolumen von etwa 200 MB pro Sekunde zu bearbeiten. D. h. 200 MB/sec ist die Datenmenge, die das Storage-­‐System liefern muss, damit die CPU ihre Leistungsfähigkeit vollständig ausschöpfen kann. Für eine Beispielrechnung mit 2 -­‐ 6-­‐Core-­‐CPUs benötigt man demnach 2 CPU*6 Core *200 MB/Sec = 2400 MB /Sec oder 2,4 GB/Sec
Hier merkt man bereits dass ein großes Missverhältnis zwischen der zur Verfügung stehenden IO-­‐Kapazität des Storage-­‐Systems und der Menge CPUs auftritt. Die Anzahl der Cores in den CPUs und die Taktrate in den CPUs ist gestiegen aber die Platten sind auf ihrem Leistungsvermögen geblieben. Um die heutigen CPUs gut mit Daten zu versorgen, muss man die Anzahl der Platten erhöhen. Dabei ist weniger die Kapazität der Platten relevant sondern mehr ihre Anzahl, damit die IO-­‐Leistung durch paralleles Drehen addiert werden kann. 49
Um 2 schnelle 6-­‐Core CPUs zu bedienen sind nach dieser Rechnung bis zu 50 Platten (ungespiegelt) nötig . Diese Zahlen gelten für Situationen in denen relativ wenige Benutzer große Datenmengen bewegen bzw. Abfragen. Das ist nicht immer der Fall und die CPUs sind auch mit anderen Aktivitäten beschäftigt, als dem Verarbeiten von Daten aus dem Plattenstapel. Daher handelt es sich hier um Grenzwerte. Allerdings 30 Platten sollten es bei diesem Beispiel schon sein. Es kommt die Spiegelung hinzu und die Empfehlung die Platten nur zur Hälfte zu beschreiben, d. h. es sollte immer die doppelten Nutzkapazität vorgehalten werden, als tatsächlich belegt ist. Eine Storage Management-­‐Software wird bei dem Beschreiben der Platten mit den äußeren Zylindern beginnen. Auf den äußeren Zylindern können mehr Daten gespeichert werden, als auf den inneren. Der Lesearm der Platten wird mit einem einzigen Zugriff auf den äußeren Zylindern mehr Daten lesen können. Nehmen wir 30 Platten mit einer Kapazität von 500 GB an, die nur zur Hälfte belegt sind, dann lässt sich mit 50
diesem System ein Nutzvolumen von etwa 7,5 TB vernünftig mit etwa 2,4 GB / Sec Datendurchsatz bearbeiten . 500 MB(Plattengröße) * 30 (Anzahl) = 15000 MB / 2 = 7500 MB (Speicherkapazität)
Die Plattenkontroller können ebenfalls noch einen Engpass darstellen. Hierbei sollte man immer von etwa 70% der von den Herstellern genannten Durchsatzraten ausgehen, da diese nur unter Laborbedingungen erreicht werden. Für die Hauptspeichergröße ist von mindestens 2 GB pro Core auszugehen, besser sind 4 GB oder sogar mehr, wenn in dem Data Warehouse z. B. viel aggregiert wird bzw. sehr viel mit Hash-­‐Joins gearbeitet wird. Für die oben gemachte Beispielrechnung von 2 6-­‐Core CPUs sind das dann 48 GB Hauptspeicher bei der Annahme von 4GB pro Core. Da die Kosten für Hauptspeicher in den Letzten Jahren enorm gefallen sind, bleibt die Frage ob man diesen Wert nicht noch erhöhen kann, also z. Auf 8 GB pro Core. Dann ist man bei 96 GB für die Beispiel-­‐Rechnung. 4 GB / Core * 2*6 (Cores)
= 48 GB Hauptspeicher
Plattenspeicher und die zugehörige Peripherie wie Netzwerk und Controller machen das Lesen der Daten von den physikalischen Medien zur eigentlichen Performancebremse. Bei vielen Betrachtungen gilt es daher die physischen Lesezugriffe (physical IO) zu minimieren. Bei dem noch zu besprechenden AWR-­‐Report wird nach Wartezuständen der einzelnen Hardware-­‐Komponente gesucht. Wenn z. B. erkannt wurde, dass es sehr viele Physical IOs gibt, die CPUs jedoch kaum ausgelastet sind, dann ist das ein Indiz für zu wenig Datendurchsatz in dem Plattensystem. Speicherhierarchien Anwender nutzen die Daten in einem Data Warehouse meist unterschiedlich häufig. Die wenigsten Daten nutzen sie regelmäßig und die Nutzungshäufigkeit nimmt mit zunehmendem Alter der Daten ab. Heute stehen unterschiedlich performante Speichermedien bereit, wobei eine höhere Zugriffsgeschwindigkeit auch höhere Kosten mit sich bringen. Die Oracle Datenbank erlaubt es mit ihren technischen Möglichkeiten unterschiedliche Speichervarianten auszunutzen. Häufig genutzte Daten können permanent im Hauptspeicher (RAM) vorgehalten werden. Damit sind komplette Data Marts extrem performant InMemory abfragbar, auch wenn sie viele Gigabyte an Daten umfassen. Tabellen werden mit ALTER TABLE table_name CACHE;
permanent in dem Speicher festgehalten, nachdem sie einmal geladen wurden (Pinning). Eine passende Strategie darüber, welche Tabellen in dem Speicher permanent „fest-­‐gepinnt“ sind, muss man fallweise entwickeln. In einem Star Schema macht es z. B. wenig Sinn die kleinen Dimensionstabellen in den Speicher zu packen, denn kleine Tabellen werden durch das Datenbanksystem wahrscheinlich auch ohne Pinning in dem Hauptspeicher sehr schnell mit wenigen physikalischen Lesezugriffen gelesen. Es lohnen sich da schon eher größere Tabellen in den Speicher zu legen, wenn genügend Speicher vorhanden ist. Es ist durchaus eine Überlegung wert, einen großen Hauptspeicherausbau zu betreiben und den größten Teil davon für bestimmte Tabellen zu reservieren, die man als wichtig und häufig genutzt identifiziert hat. Das ist eine Rechenübung. Die nächste etwas kostengünstigere Technologie, Flash Speicher, kann man als sog. 2nd Level Cache neben dem Hauptspeicher konfigurieren. Flash-­‐Speicher sind Festkörperspeicher-­‐Chips (EEPROMs), die Daten im Gegensatz zu dem Hauptspeicher persistent speichern. Flash stellt heute inform von SSDs (Solid State Discs) oder als Speicherkarte eine Alternative zu Festplatten dar, mit einer etwa 48
CPU steht in diesem Fall für eine Recheneinheit. Gemeint ist tatsächlich ein Core, also Rechenkern. Hat eine CPU 6 Cores, dann gehen wir von 6 Recheneinheiten aus. 49
Angenommen wird, dass eine Platte 50 MB/sec liefern kann. 50
Durch Spiegelung entsteht mindestens die doppelte Menge an Platten 66 200 mal höheren Performance. Entweder man legt direkt bestimmte Tabellen über ihre Tablespace-­‐ und Datafile-­‐Zuordnung auf diesen Speicher, oder man nutzt ein Feature der Oracle Datenbank. Die Datenbank kann ab Release 11.2 häufig genutzte Daten in diesen Flashspeicher legen. Werden Datenblöcke von der Datenbank in den Hauptspeicher gelesen, dann bleiben sie dort so lange erhalten, bis sie nicht mehr im Zugriff sind. Werden sie nicht mehr benötigt, bleiben noch im Speicher bis sie von neu geladenen Blöcken verdrängt werden, weil kein weiterer Hauptspeicherplatz mehr zur Verfügung steht. Gibt es Flash-­‐Speicher in dem Verfügung, so verschiebt die Datenbank die verdrängten Blöcke zunächst in diesen Flash-­‐Bereich, bis auch dieser voll ist. So gewährleistet die Datenbank, dass sich immer die am häufigsten genutzten Daten auf den schnellsten Speichermedien befinden. 51
Zwei Parameter steuern die Lage und die Größe des zur Verfügung stehenden Flash-­‐Speichers : db_flash_cache_file = <filename>
db_flash_cache_size = <size>
Die nächste Stufe der Speichermedien sind die Speicherplatten bei denen man zwischen schnellen und auch teuren Platten und weniger schnellen und dafür günstigeren Platten unterscheiden kann. Man kann sich eine Strategie überlegen, bei der man für oft genutzte Daten teurere und schnellere Platten nutzt, die weniger häufig genutzten Daten schiebt man dann auf günstige Platten. Die Masse der Daten in dem Data Warehouse wird weniger oft gelesen. Sp eich erh iera rch ie vo n sch n ellem u n d w en ig er sch n ellem Sp eich er Information Life Cycle Management (ILM) Für die Zuordnung zu den jeweiligen Medien nutzt man de Tablespace/Datafile-­‐Zuordnung und einen Kopier-­‐Mechanismus (Prozedur), der die Tabellen von Tablespace zu Tablespace verschieb. Das Partitionieren von großen historisierten Tabellen hilft dabei, weil man Partitionen zu unterschiedlichen Tablespaces zuordnen kann. Ein Konzept für diese Zuordnung kann als Infromation Life Cycle – Management-­‐Konzept Kosten sparen. Plattenplatz für Tabellen optimieren und PCT_FREE Durch permanentes Schreiben mit einzelnen Inserts in Tabellen kann eine ungünstige Verwendung des Plattenplatzes für Tabellen entstehen. Das folgende Beispiel zeigt drei Tabellen mit demselben Inhalt. Sie belegen jedoch unterschiedlich viel Plattenplatz: TABLE_NAME
PCT_FREE BLOCKS
GB
NUM_ROWS
COMPRESS
EMPTY_BLOCKS
---------------------------------------------------------------------------------------------F_BESTELLUNGEN
10
632088
5,06
102400000 DISABLED
0
F_BESTELLUNGEN_S
10
370311
2,96
102400000 DISABLED
0
F_BESTELLUNGEN_CC
0
334592
2,68
102400000 DISABLED
0
Die Ursprungstabelle war F_BESTELLUNGEN. Durch eine einfache Kopie (CTAS) entstand F_BESTELLUNGEN_S mit fast nur noch dem halben Platzverbrauch. Durch minimieren von PCT_FREE auf 0 senkte sich der Plattenplatz um weitere 10%. Durch die Behandlung mit Standard-­‐Compression wird die Tabelle automatisch „reorganisiert“ und man erzielt einen hohen Platzgewinn. alter table F_BESTELLUNGEN move compress;
TABLE_NAME
PCT_FREE
BLOCKS
GB
NUM_ROWS
COMPRESS
EMPTY_BLOCKS
---------------------------------------------------------------------------------------------F_BESTELLUNGEN
0
292256
2,34
102400000 ENABLED
0
F_BESTELLUNGEN_S
10
370311
2,96
102400000 DISABLED
0
F_BESTELLUNGEN_CC
0
334592
2,68
102400000 DISABLED
0
Grundsätzlich können große Bewegungsdaten-­‐ bzw. Fakten-­‐Tabellen mit der Storage-­‐Anweisung PCTFREE 0 angelegt werden. In einem klassischen ETL-­‐Lauf schreibt man meist Tabellen nur fort, aber man ändert die Sätze später nicht mehr. Create Table xxxx (Feld Typ)... PCTFREE 0;
Durch die Behandlung mit Standard-­‐Compression wird die Tabelle automatisch auf PCTFREE 0 gesetzt. 51
Gilt nur für Linux und Unix-­‐Systeme 67 Leseleistung messen Nicht immer ist man in der Situation, dass man die Lesegeschwindigkeit des Speichersystems einfach messen kann. Als Data Warehouse-­‐
Entwickler wird man nicht die gleichen Möglichkeiten haben, wie der Datenbank-­‐Administrator oder das Data Warehouse-­‐Team hat keine Zugriffsberechtigung auf das Betriebssystem des Datenbank-­‐Servers usw. Es gibt mehrere Methoden, um die Leistungsfähigkeit des Datenspeichers zu messen. Die einfachste Methode ist das Messen der Antwortzeit einer Abfrage auf eine definierte Datenmenge. Diese Messung kann auch ein Data Warehouse Anwender durchführen. Allerdings ist sie aufgrund der Einfachheit auch ungenau. create Table tt (F1 varchar2(20), F2 Varchar2(20), F3 varchar2(20)) PCTFREE 0;
Insert into tt select '12345678901234567890','12345678901234567890','12345678901234567890' from dual
connect by level < 1000001;
create table ttt as select * from tt;
EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','TT',estimate_percent=>100);
EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','TTT',estimate_percent=>100);
SELECT table_name, num_rows, blocks, blocks*8 KB,blocks*8/1000 MB,blocks*8/1000000 GB
where table_name like 'TT%';
TABLE_NAME
NUM_ROWS
BLOCKS
KB
MB
FROM user_tables
GB
------------------------------ ---------- ---------- ---------- ---------- ---------TT
TTT
alter system flush buffer_cache;
set timing on;
set autotrace traceonly;
alter table ttt noparallel;
select * from ttt;
1000000 Zeilen ausgewΣhlt.
Abgelaufen: 00:00:03.49
select 431/6.6 from dual;
1000000
1000000
53957
9656
431656
77248
431,656
77,248
,431656
,077248
-> 22 (MB/Sec)
Das Beispiel ermittelt einen Wert von knapp über 22 MB/Sec. Das entspricht ungefähr dem, was man ein einer einzelnen Notebook-­‐
Festplatte erwarten kann. In einem System mit vielen Platten, einem zusätzlichen Netzwerk für die Plattenzugriffe und Controller wird dieser Wert deutlich höher liegen. Jetzt kann man die Aussage treffen: Innerhalb der Datenbank sind 22 MB/Sec messbar. Wie dieser Wert zustande kommt, ob zu wenig Platten, zu schwaches Netzwerk oder zu schwache Controller oder aber auch durch zu viele Prozesse auf der Server-­‐Maschine, kann nicht geklärt werden. Das ist aber auch gleichgültig. Es zählt, was der Benutzer bei seiner Abfrage messen kann. Calibrate_IO Die etwas genauere Methode ist die Funktion Calibrate_IO aus dem Oracle Ressourcen Manager. Die Funktion wird ebenfalls aus der Datenbank heraus gestartet, was für jemanden, der keinen Betriebssystem-­‐Zugriff hat, von Vorteil ist. Folgendes Skript liefert die entsprechenden Werte: SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
--- Die Ausgabe zeigt dann:
max_iops = 73
latency = 12
max_mbps = 20
Die Eingabewerte DISKS und MAX_LATENCY, sind Annahmewerte, um die Prozedur voreinzustellen. In diesem Bespiel ist ein Datendurchsatzwert von 20 MB/Sec gemessen worden. Orion (Oracle IO Numbers) Waren die vorgenannten Methoden noch relativ ungenau, so gibt es mit dem ORION-­‐Utility eine genauere Meßmethode. Das Programm 52
ORION wird ab der Datenbankversion 11 mit der Oracle-­‐Datenbank-­‐Software mit ausgeliefert . Orion wird von der Betriebssystem-­‐Ebene 52
In dem BIN-­‐Verzeichnis der Datenbanksoftware: ... app\product\11.2.0\dbhome_1\bin 68 aus aufgerufen und setzt entsprechende Zugriffsrechte voraus. Es darf auch nur eingesetzt werden, wenn sich auf den Platten keine schützenswerten Daten befinden, weil Orion nicht nur Lesezugriffe sondern auch Schreibzugriffe testet und durchführt. Man kann den Testlauf von Orion für unterschiedliche Lastprofile parametrisieren. Man unterscheidet z. B. zwischen einem DSS (Data Warehouse) und OLTP Lastmeßverfahren oder dem Lesen von großen Datenbereichen und dem Lesen von vielen kleinen Datenmengen. Eine erste Hilfestellung erhält man durch den Aufruf von ORION –HELP. Hier listet die Software eine Beschreibung der Aufrufparameter inklusive Beispielaufrufen auf. 53
In eine Textdatei schreibt man die Namen zur Verfügung stehenden Luns und gibt den Namen dieser Textdatei dem Orion-­‐Lauf als Parameter mit. Orion wird einen Zyklus von Testreihen durchführen und die Ergebnisse in 5 Ausgabedateien schreiben. Wer sich nur für das Gesamtergebnis interessiert, dem reicht die Datei SUMMARY. D a s A b la u fsch em a vo n O rio n Ein Beispiel-­‐Output der Summary-­‐Datei sieht so aus (bzgl. der 4 gemessenen Platten wurde 109,67 MB/Sec Datendurchsatz festgestellt): ORION VERSION 11.2.0.1.0
Command line:
-run dss -testname 'D:\Orion\laufwerk' -num_disks 4 -cache_size 0
These options enable these settings:
Test: D:\Orion\laufwerk
Small IO size: 8 KB
Large IO size: 1024 KB
IO types: small random IOs, large random IOs
Sequential stream pattern: one LUN per stream
Writes: 0%
Cache size: 0 MB
Duration for each data point: 240 seconds
Small Columns:,
0
Large Columns:, 4, 8, 12, 16, 20, 24, 28, 32, 36, 40, 44,
Total Data Points: 19
Name: \\.\D:
Size: 500105217024
Name: \\.\E:
Size: 500105217024
Name: \\.\F:
Size: 500105217024
Name: \\.\G:
Size: 500105217024
4 files found.
Maximum Large MBPS=109.67 @ Small=0 and Large=56
48,
52,
56,
60
ASM (Automatic Storage Management) Oben wurde „Private Storage“ für das Data Warehouse gefordert. Das kann bedeutet, dass man für das Data Warehouse ohne Unterstützung einer zentral für das Unternehmen organisierten Storage-­‐Verwaltung auskommen muss. Mit ASM (Automatic Storage Management) ist die Verwaltung von Platten sehr erleichtert worden, so dass auch der Datenbankadministrator den Storage selbst verwalten kann. Die Verwaltung beschränkt sich auf die Zuweisung von physikalischen Platten zu ASM-­‐Diskgroups, über die man dann die Data Files mit den zugehörigen Tablespaces anlegt. 53
LUN: Logical Unit Number, logische Storage-­‐Einheit, virtuelle Platte, die über eine Datenträgerzuordnung in dem Betriebssystem definiert und dem System unter einem festgelegten Namen bekannt gemacht wird. 69 D ie Ko m p o n en ten vo n A SM ASM sorgt für Ausfallsicherheit bei den Platten. Alle Platten sind mindestens 2-­‐mal gespiegelt. ASM verhindert Hotspots, es verteilt häufig genutzte Daten auf mehrere Platten, um die IOs pro Platte zu verteilen. ASM sorgt auch dafür, dass die Platten zunächst von ihren äußeren Zylindern her beschrieben werden, weil hier mehr Daten bei den Lesezugriffen erfasst werden können. RAC (Real Applications Clusters) RAC gehört schon lange zur Oracle Datenbank. Es ist das Koppeln von mehreren kleineren Standard-­‐Rechnern zu einem Cluster-­‐Verbund, 54
55
der es mit der Leistungsfähigkeit von großen SMP-­‐ oder auch MPP-­‐Maschinen aufnehmen kann, allerdings zu günstigeren Anschaffungskosten. Die Rechner kommunizieren über eine separate Interconnect-­‐Netzwerkverbindung miteinander. Die parallele Verarbeitung von Lesezugriffen kann z. B. über die Rechnergrenzen hinweg verteilt werden. Hat eine SELECT-­‐Abfrage einen Parallelisierungsgrad von 16 und stehen 4 RAC-­‐Knoten-­‐Rechner zur Verfügung, so kann das System jeweils 4 Prozesse auf jeden einzelnen RAC-­‐Knoten verteilen. Kostengünstige Ausfallsicherheit Einer der wichtigsten Vorteile ist die hohe Ausfallsicherheit des Gesamtsystems. Nehmen wir an, die Erwartungen an die Verfügbarkeit des Data Warehouse-­‐Systems ist nicht so, wie bei einer operativen OLTP-­‐Anwendung, so wird man nicht teuerste Rechner für das System einsetzen. Fällt einer der Rechner aus, dann übernehmen die anderen Rechner die Arbeit des ausgefallenen Rechners. Das System ist dann nicht mehr so leistungsfähig bis der ausgefallene Rechner wieder einsatzfähig ist. Arbeitet man nicht mit einem RAC-­‐System und die Hardware fällt aus, so steht das Data Warehouse entweder ganz still oder man muss vorsorgen und einen zweiten Rechner für diese Notsituation bereithalten, was teurer ist, als eine RAC-­‐Lösung. Warehouse-­‐Prozesse gezielt zuteilen Data Warehouse Prozesse wie ETL, Online-­‐Adhoc Analysen oder etwa das Aggregieren und Vorbereiten von Standardabfragen können sich gegenseitig behindern. Diese Prozesse sind jedoch meist über den Tag verteilt unterschiedlich stark aktiv. Während ETL-­‐Läufe oft nachts stattfinden und Adhoc-­‐Abfragen tagsüber, findet das Standard-­‐Reporting vielleicht in den frühen Morgenstunden statt. Man kann den einzelnen Prozesse jedoch bestimmte Knoten fest zuordnen und so zusätzlich für eine Lastenverteilung sorgen, ohne dass sich die Warehouse-­‐Prozesse gegenseitig behindern. Das System überwachen Die Verwaltung der Oracle – Datenbank ist mit den letzten Releases zunehmend einfacher geworden. Ziel von Oracle ist es, so wenig wie nötig tiefes Expertenwissen zu Verwaltung heranziehen zu müssen. Meist reichen Grundkenntnisse von Konzept und Struktur der 56
Datenbank, um den normalen Betrieb zu ermöglichen . Mittlerweile reichen Grundkenntnisse von dem Datenbankkonzept. Zur Überwachung des Datenbank-­‐Systems gibt es mehrere sehr stark automatisierte Verfahren: •
•
•
Automatisiertes Schwellwert-­‐Monitoring AWR Report ADDM – Automatic Database Diagnostic Monitor Alle diese Mittel stehen über die graphische Oberfläche des Enterprise Manager zur Verfügung. Automatisiertes Schwellwert-­‐Monitoring Es gibt weit über hundert definierte Ausnahmesituationen, die für den Betrieb des Datenbanksystems kritisch sein können. Z. B. kann ein Tablespace im Verlauf des ETL-­‐Prozesses über zu wenig Platz verfügen, weil unerwartet viele neuen Quellsätze aus dem Vorsystem geliefert wurden. Man kann für solche Fälle Schwellwerte setzen, bei deren Überschreiten man gewarnt wird und rechtzeitig neuen Platz zur Verfügung stellen kann. Am Einfachsten macht man das über den Enterprise Manager unter dem Punkt SERVER und hier unter METRICS and POLICY SETTINGS. Entsprechende Warnungen erscheinen dann auch gleich in der Startmaske des Enterprise Managers. A n zeig e vo n A lters im E n terp rise M a n a g er 54
SMP: Symetric Multi Processing. Zusammenschluss von vielen Prozessoren, die einen gemeinsamen Speicheradressraum nutzen. Prozesse werden gleichmäßig auf alle Prozessoren dynamisch verteilt. 55
MPP: Massively Parallel Processing. Verwendung von vielen Prozessoren oder Knotenrechnern mit eigenständigen Verarbeitungseinheiten und Speicherraum, die durch ihre Masse ihre Leistungsfähigkeit erreichen. Prozesse werden nicht dynamisch auf viele Prozessoreinheiten verteilt, wie bei SMP-­‐Architekturen, sondern fest den einzelnen Prozessoren oder Knotenrechnern zugeteilt. 56
Damit soll nicht gesagt werden, dass es nicht auch Situationen gibt, in denen nur entsprechendes Fachwissen weiterhilft. Aber der normale Betrieb sollte sehr einfach möglich sein. 70 AWR Report Seit dem Release 11 werden alle Zustände und Aktivitäten in der Oracle Datenbank regelmäßig (im Default alle 60 Minuten) gemessen und die Meßergebnisse an zentraler Stelle (im Tablespace SYSAUX) für eine gewisse Zeit (im Default 8 Tage) gespeichert. Über diese Sammlung der Meßergebnisse (Snapshot genannt) kann man Berichte in einem HTML-­‐aufbereiteten Form erstellen und anzeigen lassen. Im Default entsteht damit alle 60 Minuten ein Snapshot, so dass man bei der Auswertung einen Zeitraum auswählen kann. Will man z. B. sehen, was zwischen 2 und 4 Uhr in der Nacht beim Ablauf eines bestimmten ETL-­‐Jobs passiert ist, so wählt man als Betrachtungszeitraum genau den Bereich zwischen dem Zeitpunkt des Snapshot der gegen 2 Uhr lief und dem Snapshot, der gegen 4 Uhr erstellt wurde. D er A b la u f d es A u to m a tic W o rklo a d R ep o sito ry-­‐M ech a n ism u s (A W R ) u n d d er A u to m a tic D a ta b a se D ia g n o stic M o n ito r Die AWR Reports erstellt man am einfachsten in dem Enterprise Manger unter dem Reiter SERVER und hier unter AUTOMATIC WORKLOAD REPOSITORY. Wenn man jetzt die Option RUN AWR REPORT startet, erhält man im nächsten Bildschirm die Auswahloption „Begin Snapshot/End Snapshot“. Dort wählt man dann die jeweilige Snapshot-­‐Nummer, die zu dem gewünschten Zeitpunkt passt. A W R R ep o rt erstellen la ssen Ein AWR-­‐Report besteht aus einem Kopfbereich mit zusammenfassenden Informationen zu der Maschine auf der die Datenbank läuft, die aufaddierte Datenbankrechenzeit, aufaddierte logical/physical Reads u. a. Einer der wichtigeren Bereiche ist die Auswertung der SQL-­‐Statements, die SQL STATISTICS. Hier werden alle SQL-­‐Statements des untersuchten Zeitraums nach unterschiedlichen Kriterien gerankt. Liste der verschiedenen SQL-­‐Statistiken Man kann hier auf die Suche nach den am längsten gelaufenen Statements, den Statements mit dem größten CPU Verbrauch oder den Statements mit den meistens I/O Waits u. a. gehen und schließlich konkrete Statements herausfiltern um sie dann einer genauen Betrachtung zu unterziehen. 71 Auswahl einer Statisitik mit den IO-­‐intensivsten SQL-­‐Befehlen ADDM – Automatic Database Diagnostic Monitor Der ADDM (Automatic Database Diagnostic Monitor) geht noch über den AWR-­‐Report hinaus. Er entwickelt basierend auf den gesammelten Daten in den Snapshots gepaart mit allgemeinen Erfahrungswerten Handlungsvorschläge für den Administrator. Diese Handlungsvorschläge gehen bis zu dem automatischen Generieren von DDL-­‐Statements, wen z. B. Index-­‐Definitionen vermisst werden. Der ADDM-­‐Monitor liefert im ersten Schritt sogenannte Findings, das sind Problempunkte als Ausgangspunkt für die Recommendations und die davon abgeleiteten Actions als konkrete Maßnahmen, um die Problempunkte zu beheben. Den Einstieg findet man über den Enterprise Manager in der Einstiegsmaske unter dem Punkt ADVISOR CENTRAL. Hier findet man in der Regel zu jedem Snapshot, der im Default alle 60 Minuten erzeugt wird, auch einen ADDM-­‐Report. Auswahl von ADDM-­‐Berichten über den Enterprise Manager Wählt man jetzt einen ADDM-­‐Bericht aus, erhält man eine Zeitachse auf der die Snapshots wieder zu finden sind. Snapshots mit Findings des ADDM sind durch ein Häkchen markiert. Die Berichte ohne Häkchen können ignoriert werden. Wählt man einen Bericht mit Häkchen, so wird eine Liste mit Findings eingeblendet. Selektion eines ADDM-­‐Reports 72 Jetzt kann man unter Finding einen Punkt selektieren und erhält die dazu passenden Recommendations. Auflistung von Recommendations Session-­‐Informationen abfragen Über den Enterprise Manager kann auch leicht Informationen zu den aktuellen Sessions abfragen. Hierzu wählt man unter dem Reiter PERFORMANCE die Option SEARCH SESSIONS. Durch weitere Selektionen findet man auch die SQL-­‐Statements, die zuletzt gelaufen sind bzw. auch noch laufen. Durch entsprechende Dictionary Views kann man sich seine individuellen Such-­‐Skripte auch selbst erstellen. Hilfreich sind hier vorallem die Dictionary-­‐Views V$SQL_Monitor und V$Session. Hier nur einige Beispiel, weil letztlich alles in jeder Form abfragbar ist: SESSIONLISTE:
column machine format a30
select SID,username,status,schemaname,osuser,machine,module from v$session where TYPE = 'USER';
LETZTE STATEMENTS VON EINEM USER/SCHEMA:
select userName, status,ELAPSED_TIME, SQL_ID ,SQL_EXEC_ID, to_char(SQL_EXEC_START,'DD-MM-YYYY
HH24:MI:SS'), substr(SQL_TEXT ,1,75) SQL_Text from v$sql_monitor where USERNAME = 'DWH' order by
SQL_EXEC_START
Monitoring des Platzverbrauchs Ein Data Warehouse gehört zu den Anwendungen in einem Unternehmen, die den größten Platz-­‐Ressourcen-­‐Bedarf beanspruchen. Entsprechend muss man immer wieder die Notwendigkeit dieser Ressource rechtfertigen. In vielen Systemen wird allerdings nicht platzsparend gehandelt. Einige Fragen werden viele Administratoren nicht immer sofort beantworten können: •
•
•
Wer benutzt welche Daten im Data Warehouse? Welche Daten werden gar nicht genutzt? Wie viele Daten werden in dem ETL-­‐Prozess bewegt, und gelangen nicht in die Auswertungen? Man sollte die Verwendung der Daten in dem Data Warehouse beobachten und neben einem Inventar der Daten auch eine Übersicht über den Verwendungsgrad einzelner Datenbestände haben. Das mag jetzt erschrecken, wenn man bedenkt, dass manche Data Warehouse – Systeme mehrere hundert Tabellen umfassen. Hier kann man jedoch pragmatisch heran gehen und berücksichtigen, dass nur die wenigsten Tabellen wirklich groß sind. Oft belegen nur wenige Tabellen den größten Teil des Systems. select table_name, Blocks from user_tables
where rownum < 10 order by blocks desc;
Wenn 10 Tabellen schon mehr als 80% des Plattenplatzes beanspruchen, lohnt sich das Platzverbrauchs-­‐Monitoring dieser Tabellen. 57
Die Information über die Gesamtgröße des Data Warehouses sollte ständig parat sein. Z. B. ein sog. Free Space Report: Database Size
Free space
-------------------- ------------102291 MB
30633 MB
Und eine Übersicht über alle Datafiles und die prozentuale Belegung: Tablespace Name Filename
FILE_ID
FILESIZE
USED
PCT_USED
---------------- ------------------------------------------------- ---------- ---------- ---------DWH_SPINDEL
F:\ORA_DATA\DWH_SPINDEL1
13 2,1475E+10 2,1475E+10
100
57
Beispielabfragen siehe Kurzreferenz 73 DWH_SPINDEL
DWH_SPINDEL
EXAMPLE
SYSAUX
SYSTEM
T1
T2
T3
TEMP
TEMP
USERS
DWH_SICHER
DWH_SPINDEL
DWH_SSD
T4
UNDOTBS1
F:\ORA_DATA\DWH_SPINDEL2
F:\ORA_DATA\DWH_SPINDEL3
E:\APP\PRODUCT\ORADATA\ORCL\EXAMPLE01.DBF
E:\APP\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
E:\APP\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
F:\ORA_DATA\T1.DBF
F:\ORA_DATA\T2.DBF
F:\ORA_DATA\T3.DBF
E:\APP\PRODUCT\ORADATA\ORCL\TEMP01.DBF
E:\APP\PRODUCT\ORADATA\ORCL\TEMP2
E:\APP\PRODUCT\ORADATA\ORCL\USERS01.DBF
F:\ORA_DATA\DWH__SICHER
F:\ORA_DATA\DWH_SPINDEL
E:\APP\PRODUCT\ORADATA\ORCL\DWH_SSD
F:\ORA_DATA\T4.DBF
E:\APP\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
avg
sum
14
15
5
2
1
8
9
10
1
2
4
12
7
6
11
3
2,1475E+10
1,0737E+10
104857600
786432000
754974720
10485760
10485760
10485760
2844786688
2147483648
1966080000
5368709120
2,1475E+10
1,0737E+10
10485760
7187988480
----------
1,9685E+10
91
1552285696
14
82247680
78
743112704
94
749862912
99
1179648
11
1179648
11
1114112
10
2843738112
99
57671680
2
1153040384
58
2972254208
55
2,1475E+10
100
1048576
0
1114112
10
95944704
1
---------- ---------49
1,0710E+11 7,2891E+10
Hat man die wichtigsten Tabellen identifiziert, dann kann man für diese ein Verwendungsmonitoring aufsetzen. Hier helfen die DBA_HIST_...-­‐ Dictionary Views. Das System aktualisiert diese immer dann, wenn Aktivitäten in der Datenbank stattgefunden haben und wenn ein AWR-­‐Snapshot gezogen wurde, also im Default alle 60 Minuten. In diesen Views sind auch die „physical Reads“ auf einzelne Tabellen ablesbar und die Delta-­‐Information zum letzten Snapshot. Man kann also herausfinden zu welcher Zeit welche Tabelle gelesen wurde. Select distinct * from
(select
to_char(begin_interval_time,'dd.mm.yyyy:hh24:MI') Zeit,
logical_reads_total log_rd,
logical_reads_delta log_rd_delta,
physical_reads_total phy_rd,
physical_reads_delta phy_rd_delta
from
dba_hist_seg_stat
s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot
sn
where
o.owner = 'DWH1'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id and
object_name = 'UMSATZ')
order by zeit;
ZEIT
LOG_RD LOG_RD_DELTA
PHY_RD PHY_RD_DELTA
---------------- ---------- ------------ ---------- -----------06.09.2010:22:00
3357520
3357520
3355361
3355361
06.09.2010:23:00
4030816
673296
4028177
672816
07.09.2010:12:32
8060160
4029344
8054609
4026432
07.09.2010:15:50
688
688
1
1
Ergänzend kann man noch über die View v$sql_monitor die zugreifenden User herausfinden. So dass man eine Benutzer-­‐bezogene Nutzungsstatistik für die einzelnen Tabellen und eventuell ihre Partitionen erhält. Solche Informationen kann man sich dann in eine Statistik-­‐Tabelle schreiben, die folgende Struktur hat: Einfache Tabelle zum D okum entieren der N utzung von D aten im D ata W arehouse
74 Dokumentation
Metadaten Repository zur Dokumentation der Inhalte und Zusammenhänge Die bisherigen Ausführungen haben an den unterschiedlichen Stellen des Data Warehouse-­‐Systems immer wieder Dokumentationsaufgaben offengelegt. Z. B.: •
•
•
•
•
•
•
•
•
Aufstellung zu allen ermittelten und formulierten Informationsanforderungen der Endbenutzer Informationskataloge zu den Tabellen und Spalten der zentralen Warehouse-­‐Schicht Ein zusätzliches Klassifizierungsverfahren zum Verhindern von Synonymen und Homonymen Nachweis darüber, welcher Benutzer welche Daten nutzt Dokumentation der über Materialized Views aufgebauten Kennzahlen-­‐Hierarchien Dokumentation aller Dimensionen sowie ihrer Hierarchien und die hierüber selektierbaren Felder Dokumentation der Kennzahlen in den Fakten-­‐Tabellen Datenqualitätsregeln in dem Integration Layer Ein Schlagwortverzeichnis könnte noch hinzugefügt werden Diese Liste könnte noch durch die entsprechenden Metadaten der eingesetzten Werkzeuge (ETL, Reporting) sowie das Datenbank-­‐
Dictionary erweitert werden. Es wäre ein zusätzlicher Gewinn, wenn diese Dokumentationsaufgaben nicht separat an den genannten Stellen gelöst werden, sondern zusammenhängend an einer Stelle. Dann könnte man Querbeziehungen zwischen den Bereichen darstellen und hätte alle diese Meta-­‐
Beschreibungen sehr schnell ohne aufwendige Suche. Bei allen Überlegungen zu dem Thema Dokumentation und Data Warehouse kommt man immer wieder zu dem einen Schluss: Ein gut entwickeltes Data Warehouse verfügt über ein Metadaten – Repository! Man kann sogar noch einen Schritt weiter gehen und sagen: Ein Data Warehouse schöpft sein volles Potential nur dann aus, wenn es seinen Nutzern über ein Metadaten-­‐Repository einen einfachen Zugang zu seinen Daten anbietet! Ein Data Warehouse ohne Metadaten-­‐Repository ist wie ein Fachbuch ohne Inhalts-­‐ und Stichwortverzeichnis. Wenn in Data Warehouse-­‐
Systemen Informationen doppelt und dreifach zu finden sind, wenn dadurch unnötig Ressourcen verschwendet werden, wenn neue Projekte immer wieder bei Null anfangen müssen, weil sie nicht wissen, was schon alles da ist, dann fehlt ein Repository. Ein solches Metadaten-­‐Repository muss folgendes leisten: •
•
•
•
•
•
Man sollte alle Arten von Metadaten über das Repository beschreiben können, d. h. technische wie fachliche Beschreibungen. Es sollte alle Aufgabenstellungen in einem Data Warehouse abdecken, d. h. ETL-­‐Strukturen, Berichte-­‐Strukturen und Inhalte, Datenmodelle, die Art der Verwendung durch Benutzer, Datenqualitätsstandards und Regeln. Alle Informationsarten sollten über Beziehungen miteinander zu verbinden sein. Neue Beschreibungsarten sollten leicht integrierbar sein. Die Informationen sollten leicht abfragbar sein. Abfragen über Beziehungen zwischen den Informationen sollten möglich sein. Das alles ist nur über ein generisches Metadaten Repository machbar. Generisch bedeutet, dass man sogenannte Metadaten-­‐Typen und Beziehungen zwischen diesen Metadaten-­‐Typen frei definieren kann. Will man z. B. ausdrücken, dass Abteilungen Berichte benutzen und die Berichte die Daten aus bestimmten Tabellen beziehen benötigt man 3 Metadaten-­‐Typen: Abteilung, Bericht und Tabelle. Diese Typen müssen dann natürlich noch in eine Beziehung zu einander gesetzt werden. Also definiert man die Beziehungen „ABTEILUNG_nutzt_BERICHT“ und „BERICHT_liest_aus_TABELLE“. Dieses einfache Beispiel zeigt, wie mächtig die Dokumentationsmittel eines Repositories sein können. Durch das Bilden von Beziehungen werden einzelne Objekte nicht nur einfach aufgelistet, sondern sie werden in einen Kontext gestellt. Das Beispiel zeigt auch, wie technische Informationen mit nicht technischen Informationen zusammenhängend dargestellt werden. Tabellen würde man eher in dem Datenbank-­‐Dictionary erwarten und Abteilungsnamen in einer Unternehmensbeschreibung. Die Dokumentation über ein Metadaten-­‐Repository legt alles offen. Sie zeigt nicht nur die Inhalte und die technische Struktur eines Data Warehouse, sondern sie zeigt auch, wie man mit Objekten des Data Warehouse umgeht, wie es gelebt wird. In dem folgenden einfachen Metadaten-­‐Informationsmodell sind exemplarisch Metadaten-­‐Typen aus den Bereichen Business Area, Organisation, Dimensional Model, Data Governance, Physical Model, Architecture Model, Operational Activities und Reporting dargestellt. Über die ebenfalls dargestellten Beziehungen zwischen den Metadaten-­‐Typen erkennt man deutlich, welche Informationen abgebildet werden können. Nicht dargestellt sind die Attribute der Metadaten-­‐Typen. Neben Standard-­‐Attributen wie Beschreibung, Definition usw. haben die Metadaten-­‐Type spezifische Attribute. Z. B. kennt der Typ Business_Rule eine Vorschrift darüber, wie eine Regel zu prüfen ist, 58
welcher Erfüllungsgrad erwartet wird oder wer für diese Regel verantwortlich ist. 58
Eine mögliche technische Umsetzung wird an dieser Stelle nicht weiter ausgeführt. Ein Beispiel dafür kann an einem kostenfreien Repository-­‐Framework von Oracle Consulting nachvollzogen werden. Hier hat man eine solche Lösung mit Oracle APEX realsiert. Die Dokumentation und die Sourcen können über diesen Link http://www.oracledwh.de/downloads/AutoIndex-­‐
2.2.4/index.php?dir=downloads/Repository_for_free/&file=Repository_MD04.7z bezogen werden. Hier handelt es sich um ein 75 Prin zip d es g en erisch en 4-­‐Sch ich ten-­‐R ep o sito ries vollständiges Repository-­‐System, das als Tabellenwerk in der Oracle Datenbank und mit einem APEX-­‐Web-­‐Frontend erstellt ist. Das System ist offen, und kann leicht angepasst und weiterentwickelt werden. 76 Verwaltung von Data Warehouse-Systemen
Backup im Data Warehouse Die Sicherung der Daten in einem Data Warehouse ist eine der aufwendigsten Maßnahmen. Oft wird sie von den Data Warehouse Teams als solche nicht besonders wahrgenommen, weil sie in das Aufgabenfeld der zentralen Stellen im Rechenzentrum fällt. Allerdings übertragen diese zentralen Stellen die Kosten dafür anteilig auf die einzelnen Anwendungen. Nimmt man als eine der Berechnungsgrundlagen den belegten Plattenplatz, so fällt ein größerer Teil auf das Data Warehouse, weil dieses oft zu den größten Systemen gehört. Ein Ressourcen-­‐schonend aufgebautes Data Warehouse minimiert den Aufwand für Backup, indem es die zu sichernde Datenmenge reduziert. Ein Data Warehouse-­‐spezifisches Backup-­‐Konzept muss her, denn: •
•
•
Data Warehouse-­‐Systeme umfassen größere Datenmengen und der höhere Aufwand zwingt zum Reduzieren. In Data Warehouse-­‐Systemen gibt es größere Redundanzen, so dass nicht immer alle Daten in allen Schichten gesichert werden müssen. Große Datenbereiche verändern sich nicht täglich, sondern werden einmal erstellt und nicht mehr mit einem Update angefasst. Für die Sicherung gelten folgende Regeln: Stage / Integration Layer Dieser Bereich sollte keine Daten umfassen. Der Bereich ist nur ein Hilfsbereich im Rahmen des ETL für Prüf-­‐ und Integrationsaufgaben. Nach Abschluss einer Ladeperiode sollte dieser Bereich leer sein. Die Stage-­‐Schicht sollte man nicht sichern. Wird die Stage zu Archivierungszwecken für Änderungsdaten des operativen Systems genutzt, dann sollte man diese Daten separat unabhängig von den übrigen Data Warehouse-­‐Daten sichern, denn es sind Sicherungen von operativen Datenbeständen, die nichts mit dem Data Warehouse zu tun haben. Data Mart Baut man die Data Marts regelmäßig neu auf, dann muss man diese Daten ebenfalls nicht sichern, denn man kann sie immer wieder aus der Data Warehouse – Schicht neu aufbauen. Dies ändert sich, wenn man in den Data Marts historisiert hat und einmalige Informationen vorhanden sind. Data Warehouse – Schicht und alle langlebigen Daten Für die Data Warehouse – Schicht und alle langlebigen Daten unterscheidet man zwischen den relativ wenigen großen Tabellen und den vielen Kleinen. Große Tabellen sollten partitioniert sein. Dann kann man die Partitionen, deren Daten sich nicht mehr ändern auf Read Only-­‐Tablespaces positionieren und sie von da an nicht mehr sichern. Die neu hinzugekommenen Daten in den großen Tabellen sichert man am besten direkt nachdem sie durch den ETL-­‐Prozess erstellt wurden. Die vielen kleinen Tabellen sichert man inkrementell mit RMAN und macht eine wöchentliche Vollsicherung. Wenn diese Aufteilung des Backup-­‐Vorgangs in mehrere Teilverfahren zu aufwendig oder zu komplex erscheint, dann sollte man die Alternativen einer Vollsicherung mit RMAN und inkrementelles Sichern bedenken. Das ist jedoch nur möglich, wenn man die Datenbank in dem Archivlog-­‐Modus fährt und das bremst den ETL-­‐Prozess aus. Zumindest bei dem Laden von großen Tabellen und dem Erstellen von 59
temporären Tabellen empfiehlt man die NOLOGGING-­‐Option . Und damit ist auch keine inkrementelle Sicherung durch RMAN möglich. Also wird man diese Daten durch ein zusätzliches Wegschreiben sichern müssen. RMAN Die Sicherung erfolgt mit RMAN (Recovery Manager) von Oracle. Die Vorteile liegen auf der Hand: •
•
RMAN ist Bestandteil der Datenbank und damit kostenfrei. RMAN kennt die Struktur und die Files der Oracle Datenbank. 59
ALTER TABLE xxx NOLOGING; oder CREATE TABLE xxx NOLOGGING AS SELECT * FROM xxx; 77 •
•
RMAN prüft die Datenbankdaten. D. h. die Daten werden auf „corrupted blocks“ hin untersucht, bevor RMAN sie wegschreibt. Sichert man nur auf Datei-­‐Ebene, durch Weg-­‐kopieren der Datenbankdateien, kann man fehlerhafte Böcke mit wegschreiben. Das Verfahren ist nicht sicher. Das kann mit RMAN nicht geschehen. RMAN komprimiert beim Wegschreiben. Mit RMAN kann man inkrementell sichern. Man kann z. B. einmal pro Woche eine Vollsicherung und zwischen diesen Sicherungen nur die geänderten Daten wegschreiben. 78