Oracle Warehouse Technologie Single-Engine-Based-Data-Warehouse

1. Oracle Warehouse TechnologieSingle-Engine-Based-Data-Warehouse

2. Performantes Data Warehouse Effiziente, integrierte Data Warehouse Architekturen auf der Basis von Oracle 10 g Alfred SchlaucherGerd Schoen Stichpunkte zu Ressourcen – schonenden Technikenmit dem Oracle – basierten Data Warehouse

3. Information Management und Data Warehouse Themen • Anforderungen und Architekturen • Vorgehensweisen und Modelle • Datenintegration • Datenqualität • Aufbau eines Data Warehouse Systems • Optimierungen der Datenhaltung

4. Klassisch Trends Anzahl Benutzer Anzahl Benutzergruppen Anzahl Schnittstellen Latenzzeit Granularität Datenmengen Data Warehouse Anforderungen

5. BI Services Rules Rules Rules Wahlfreie Analysenzugriffe Wahlfreie Positionierung ETL. Oracle DWH Referenzarchitektur Master Data Hub Enterprise Service Bus Adapter Routing UDDI BPEL Process Manager Work- flow Nativ BPEL Rules Kunden Produkte Top Level Management Data Mart Stage Prüfungen Data Warehouse Kenn- zahlen- systeme ODS Beliebig komplexe Abfragen Unified Repository Mitarbeiter operative Ebene operative und dispositive Metadaten Qualitätsstandards und Servives RAC Verbund

6. Flexible Bereitstellung von Business Intelligence Informationen Komplexe multidim. KennzahlenAbonnement Metadaten Oracle DWH Plattform Experten/ Spezial- anwendung Austauschbare Frontends und Anwendungen Data Mining Analyse- Komplexität Standard Ad Hoc Generische Verwendung Fachspez. Kennzahlen Fachspezifische Transformationen Vorgelagerte zentrale Transformationen und generische Kennzahlen Data Quality Regelbausteine / abgebildete Business Rules I n t e g r i e r t e D a t a W a r e h o u s e P l a t t f o r m

7. Options: RAC CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU Verteilung der Last in einem RAC-Verbund - Tagsüber Load-Job 1 InteraktiveAnalysen Standard-Reporting InteraktiveAnalysen Knoten 1 Knoten 2 Knoten 3 Knoten 4 Eine Datenbank Schema CRM SchemaPlanung SchemaStamm- daten SchemaDWH SchemaData Mining

8. Options: RAC CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU Verteilung der Last in einem RAC-Verbund - Nachts Load-Job 1 Load-Job 2 Standard-Reporting Load-Job 3 Knoten 1 Knoten 2 Knoten 3 Knoten 4 Eine Datenbank Schema CRM SchemaPlanung SchemaStamm- daten SchemaDWH SchemaData Mining

9. Effiziente Datenhaltung SpeichertechnikILMHardwareASMOLAP Verwaltung und Dokumentation MetadatenOwnerschaftenGrid Control Qualitäts-management Data ProfilingData Auditing Datenintegration schnelles Bereitstellen DB-basiertes Laden MDMETL-Option BI-Anwendungen Standard-Berichte Interaktive BerichteData MiningKomplexe Analysen Daten-Zugriff SecurityMandanten Technologien und Verfahrenzum Aufbau und zur Verwaltung von Data Warehouse-Umgebungen

10. Verwaltung und Dokumentation MetadatenOwnerschaftenGrid ControlB&R Effiziente Datenhaltung SpeichertechnikILMHardwareASMOLAP Qualitäts-management Data ProfilingData AuditingData Rules Compression Data Guard Streams Data Quality Option Enterprise-ETL Bitmapped Data Mining OBI SE OBI EE Flashback Parallel Query Label Security Daten-Zugriff SecurityMandanten Technologien und Verfahren Oracle EE Oracle Enterprise Edition Datenintegration schnelles Bereitstellen DB-basiertes Laden Master Data ManagementETL-OptionSAP Zugriff RMAN Diagnostic Pack Tuning Pack RAC Repository (OWB) Partition BI-Anwendungen Standard-Berichte Interaktive BerichteData MiningKomplexe Analysen SAP Connect OLAP Gateways

11. Information Management und Data Warehouse Themen • Datenintegration und Modellbasiertes ETL Komponenten

12. Wie war das nur? „Lösungen“ der Vergangenheit • Programmierung von Hand • Zerstreute Programm-Sourcen • Fehler bei der Entwicklung • Unnötige Doppelarbeit • Schlechte oder fehlende Dokumentation • Schlechte Wartbarkeit • Enorme Folgekosten • Unkündbare „Inselexperten“ • Immer wieder „Katastrophen“ im Echtbetrieb

13. Die Geschichte der ETL-Tools geht in Richtung integrierter Werkzeuge 1992 1996 2000 2005 Datenbankbasierte ETL-Werkzeuge Separate Engine-gestützteETL-Werkzeuge Programm- generatoren Handprogrammierung

14. Es gibt 3 Hauptgründe für den Einsatz von OWB • Performance • Effizientere Warehouse Architekturen (integriert in Oracle) • Preis

15. Oracle Warehouse Builder ist das ETL-Tool der Wahl in Oracle-Umgebungen! • Design des kompletten Data Warehouse Systems • Logisches Design und Dokumentation • Physisches Oracle Datenbank Design • Fast alle Datenbankobjekte von Oracle 10g • 100 % SQL • 100 % PL / SQL - Generierung • Bereitstellung der Datenbeschaffungsroutinen • Laufzeit – System zur Fehlerkontrolle • Universelles Metadaten-Repository • Automatisiertes ETL durch Scriptsprache • Data Quality / Data Profiling • Hat bereits mehr Installationen als andere Mitbewerber

16. Log PL/SQL Schnittstellenkomponenten Oracle Data Warehouse In Memory nn JCA COM+ SOAP WSIF & JBI Enterprise Service Bus Routing QOS BPEL Transform Rules FlatFile Oracle (Remote) XML FlatFile SAP Int. DB2 OS390, UDB Sybase, Informix, SQL-Server... Warehouse Datenbank XML Port FTP Port Ext. Table Streams tcp CDC Access/Excel Gateway UTL_FILE MessageBroker DB-Link XML ODBC Queue DB-Link Peoplesoft Adapter XML Queue Siebel Tabellen View SQL Loader Sequenz Index Cube Webservices MView Procedure Function FlatFile XML eMail

17. OWB live

18. Information Management und Data Warehouse Themen • Data Quality und Data Profiling

19. Marketing Werbung Adresse Liefer-schein KD-Daten Spedition Stamm- daten Lager Order Kredit OK BedarfAdresseKredit-daten Bestell-daten Rechnung Kunden-betreuer Mahnung Logistik- system Buch-haltung Kunde Kunde Bezahlung Reklamation Verkaufs-daten Angebot Bestand Ohne Daten kein Business Unternehmen funktionieren nur mit Daten Information Chain Operative Prozesse

20. Methoden Data Profiling mit OWB Die operativen Daten Feintuning zu den Analyse- methoden Proto- kollierung laufende Analysen Drill Down zu den operativen Daten

21. Analyseumgebung • Oracle Datenquellen • Alle Gateway- lesbare Quellen • SAP-Daten • Flat Files • Adress-/LDAP-Verzeichnisse LDAP / DBMS_LDAP / Table Function Gateway / ODBC / FTP non Oracle Oracle 9i / 10g DB2, SQL Server Informix, Teradata SAP Source Schema Profiling Stage SAP Integrator Oracle External Table Source Schema Transportable Module RAC

22. Unterstützung von Software-Projekten Durch den Feldnamen vermutet man rein numerische Inhalte Übereinstimmung von Feldname „...nr“ und Feldtyp Firmenrabatt ist in der Regel ein Rechenfeld Kundennr ist ein wichtiges Feld. Es sollte stimmig sein. ? sieht gut aus !

23. Unterstützung von Software-Projekten Die Zahl 17 kommt häufig vor, hier muss es eine „systematische“ Ursache geben ? Felder sind nicht gepflegt kritisch! da es sich um einen Schlüssel- kandidaten handelt kritisch! weil doppelte Kundennummern ? OK

24. Metadatenmanagement

25. Daten-OwnerschaftDie Rolle von Metadaten • Wem gehören welche Daten? • Wer nutzt welche Daten? • Wer hat an welchen Daten welches Interesse? • Wer hat welche Daten wie oft benutzt? • Welche Prozesse sich auf welche Daten angewiesen? • Welche Prozesse sind datenabhängig von anderen Prozessen?

26. SubjectArea Entity Stakeholder Abteilung Data Set / Record(Name Location) Cost Data Owner Mitarbeiter Org Role

27. Impact / Lineage - Metadatenanalyse Zurück

28. Aufbau eines DWH

29. Starschema • Mviews • Analytische Funktionen • Mandantenfähigkeit • Partitioning • Transportable Tablespace • Bitmap Indizierung • Table Function

30. Einstiegspunktefür Abfragen Umsetzung in technische Lösungen - Dimensionale Sicht und relationale Datenbank V1 V2 V3 V4 Maier Müller Schmid Verkäufer Engel 1 : n Verkäufe Produkttabelle Zeit P1 P2 P3 P4 Prod1 Lief1 P1 P2 P3 P4 R1 R2 R3 R4 Z1 Z2 Z3 Z4 4 V1 V2 V3 V4 Z1 Z2 Z3 Z4 6.7.99 Q3 Q3 Q3 Q3 1 : n n : 1 Prod3 Lief4 4 7.7.99 Prod5 Lief5 9 8.7.99 Prod6 Lief9 8 9.7.99 N : 1 • Starschema • flexibel • Graphisch auch für Business-User verständlich R1 R2 R3 R4 München Berlin Hamburg Regionen Frankfurt

31. Länder Analytical- Functions Regionen Level 3DefinitionenAttribute Orte Level 2DefinitionenAttribute Star-Transformation Level 1 DefinitionenAttribute Bitmap-Index QueryRewrite Partitions MaterializedView Die Datenbank für das Warehouse fit machen (Beispiele) DimensionOrt Zeit FK_Ort FK_Zeit FK_Produkt Umsätze Parallel+ Cluster Produkt Kunde

32. Partitioning

33. Feb 02 Aug 02 Jul 02 Mai 02 Apr 02 Mar 02 Sep 02 Okt 02 Nov 02 Dez 02 Jun 02 Jan 02 Fallbeispiel:4 Terabyte Warehouse einer der grössten Banken Deutschlands 13 Tabellen Monatliches Ladevolumenvon mehreren 100 GB View • Ergebnisrechnung von 3000 Profitcentern • 4 Mill. Kunden • 8000 zugel. Nutzer • tägl. 2500 ReportServer- Zugriffe • tägl. 1500 Discoverer Zugriffe • tägl. Ca 800 Plain SQL Auswertungen View View

34. Partitioning • Hauptgründe für das Partitioning • Managebility • Abfrageperformance • Verfügbarkeit • Arten des Partitioning • Range • List • Hash • Composite Range-Hash • Composite Range-List

35. Range-Partitionierung Jahr Zeit Quartal Monat Region Umsatz FK_Ort FK_Zeit FK_Produkt Qx9999 Artikel Kunde Q12000 Nach Quartalenund Jahren partitioniert Q22000 Q32000

36. Join-wise-Partitioning Jahr Zeit Quartal Monat Region Umsatz FK_Ort FK_Zeit FK_Produkt Kunde Partition-Join 1:1 2:2 3:3 4:4 5:5 6:6 7:7 8:8 9:9 1 2 3 4 5 6 7 8 9 Artikel 1 2 3 4 5 6 Hash-Partition 7 8 9 Hash-Partition

37. 1 2 3 4 5 6 7 8 9 Join-wise-Sub Partitioning (Range und Hash) Jahr Zeit Quartal Monat Region Umsatz FK_Ort Artikel FK_Zeit FK_Produkt Kunde Partition-Join 1:1 2:2 3:3 4:4 5:5 6:6 7:7 8:8 9:9 1 2 3 4 5 6 7 8 9 Q22000 Range-PartitionnachZeit Hash-Partition Q32000 Q42000 Hash-Partition

38. Arten der Indizierung bei der Partitionierung GlobalPartitioned Index GlobalNon Partitioned Index Partitionierte Tabelle Local Index Partition 1 Partition 2 Partition 3 Partition 4 Partition 5 Partition 6

39. Fallbeispiel zur Lade- und Abfrageperformance

40. Customer 1.000.000 Products 10.000 HP Proliant DL380 G3 6 GB RAM 2 CPU 3 GHz Times 2.557 Sales 292.282.479 Promotions 1.001

41. 300 Mio Sätze Index Insert into TGT Select * from SRC TempTable Allgemeines zum Verfahren

42. Jährliches Wachstum 20% • Besonders viele Daten im November, Dezember, dafür weniger Daten im April, Juni, August (keine gleichmäßige Verteilung über alle Monate) • Initial Load Jan 2002 – Nov 2004 • External Tables • ca. 27 Minuten für beide Varianten

43. Zeit für die Indexerzeugung Initial Load Platzverbrauch für Bitmap Gesamtindex ca. 30 MB

44. Nachladen 1 Zeitscheibe Dezember 2004 Oracle 10G ALTER TABLE sales ADD PARTITION sales_dec_2004 VALUES LESS THAN (TO_DATE('01-jan-2005','dd-mon-yyyy')); 1 < 1 Sec < 1 Sec CREATE TABLE sales_temp_dec_2004 AS SELECT * FROM sales WHERE ROWNUM < 1; 2 INSERT INTO sales_temp_dec_2004 SELECT * FROM salesxt; 3 2 Min 6 Sec CREATE BITMAP INDEX sales_cust_id_bix_dec_2004 ON sales_temp_dec_2004 (cust_id) NOLOGGING PARALLEL; 29 Sec 4 ALTER TABLE sales EXCHANGE PARTITION sales_dec_2004WITH TABLE sales_temp_dec_2004 INCLUDING INDEXES WITHOUT VALIDATION; < 1 Sec 5 < 1 Sec 6 Drop Partition

45. Nachladen 1 Zeitscheibe Dezember 2004 ohne 10G - Features Drop auf alle Indexe 1 wenige Sekunden 6 Minuten Laden neue Daten (parallel) mit External Table 2 Neuerzeugen des Index 3 Platzverbrauch für Btree Gesamtindex ca. 1094 MB insgesamt 800 Minuten

46. Löschen des alten Monats Januar 2002 Oracle 10g Traditionell ALTER TABLE SALES DROP PARTITION SALES_JAN_2002; ca. 1 Sec. DELETE FROM SALES WHERE TIME_ID < TO_DATE('01-FEB-2002','DD-MON-YYYY'); 7 Stunden 51 Minuten 28 Sekunden Rollbacksegment wird genutzt: ca 4000 MB Plattenplatz

47. Abrageperformance Abfrage 1 Abfrage 2 SELECT p.prod_name, SUM(s.amount_sold) FROM sales s, products p, channels ch, promotions pm WHERE s.prod_id = p.prod_id AND s.channel_id = ch.channel_id AND s.promo_id = pm.promo_id AND ch.channel_desc = 'Catalog' AND pm.promo_category = 'flyer' AND p.prod_subcategory = 'Shorts - Men' GROUP BY p.prod_name; select p.prod_name, sum(s.amount_sold) from sales s, products p, channels ch, promotions pm, times t where s.prod_id = p.prod_id and s.channel_id = ch.channel_id and s.promo_id = pm.promo_id and s.time_id = t.time_id and ch.channel_desc = 'Catalog' and pm.promo_category = 'flyer' and t.calendar_quarter_desc ='2000-Q2' and p.prod_subcategory = 'Shorts - Men' group by p.prod_name;

48. Abrageperformance • select count(*) from sales; • 2. select count(*) from sales • where • promo_id = 714 and • channel_id = 'S'; • 3. select count(*) from sales • where • promo_id = 714 and • time_id = to_date('20-MAY-2004','DD-MON-YYYY') • and channel_id = 'S';

49. Viele Auswertemodelle sind zu komplex für Endbenutzer (z. B. Snowflake) • Komplizierte ETL-Prozesse • Aufwendige Erstellung und Wartung Aus 5 mach 3Verfahren einfach halten Quellen Stage ZusätzlicheVerdichtungs- /Abfragelogik Summe SRC1 Inserts/ Updates SRC2 Summe Mart Quellen Stage Sich SelbstpflegendeMaterialized Views SRC1 SRC2 External Tables / Multiple InsertsMerge... Mart

50. Mandantenfähigkeit