Haben Sie die Zeit im Griff? Zeitdimensionen - DOAG 2012

download Haben Sie die Zeit im Griff? Zeitdimensionen - DOAG 2012

of 27

Embed Size (px)

description

Dani Schnider, Principal Consultant und DWH/BI Lead Architect bei Trivadis in Zürich, referierte an der DOAG Konferenz 2012 in Nürnberg.

Transcript of Haben Sie die Zeit im Griff? Zeitdimensionen - DOAG 2012

  • 1. Partitionierung im DWH:Erkenntnisse aus der PraxisOracle DWH KonferenzDani SchniderTrivadis AGKassel, 19. Mrz 2013BASEL BERN BRUGG LAUSANNE ZRICH DSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MNCHEN STUTTGART WIEN2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20131

2. Dani Schnider Principal Consultant undDWH/BI Lead Architectbei Trivadis in Zrich Kursleiter fr Trivadis-Kurseber Data Warehousing, SQLOptimierung und OracleWarehouse Builder Co-Autor des Buches DataWarehousing mit Oracle2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20132 3. Kurzvorstellung Trivadis.Trivadis ist fhrend bei der IT-Beratung, der Systemintegrationund der Erbringung von IT-Services mit Fokussierung auf - und-Technologien im D-A-CH-Raum.Unsere Leistungen erbringen wir aus den strategischen Geschftsfeldern:Trivadis Services bernimmt den korrespondierenden Betrieb Ihrer IT Systeme.2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20133 4. Mit ber 600 IT- und Fachexperten bei Ihnen vor Ort.2014 Trivadis11 Trivadis Niederlassungen mitber 600 Mitarbeitenden200 Service Level AgreementsMehr als 4'000 TrainingsteilnehmerForschungs- und Entwicklungs-budget:CHF 5.0 / EUR 4 Mio.Finanziell unabhngig undnachhaltig profitabelErfahrung aus mehr als 1'900Projekten pro Jahr bei ber 800KundenPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20134 5. Der richtige Partition KeyLaden durch AustauschenRollende ZeitfensterGlobale Statistiken2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20135 6. Der richtige Partition KeyRichtiger Partition Key ist wichtig fr: Abfrageoptimierung Partition Pruning Partition-wise Joins ETL Performance Partition Exchange Data RetentionTypisch fr Data Warehouses: RANGE-Partitionierung von Faktentabelle Partition Key: Datum Aber welches Datum?2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20136DimensionDimensionDimensionFactTableDimension 7. Praxisbeispiel 1: Fluggesellschaft Flugbuchungen in partitionierter Faktentabelle RANGE Partitionen pro Monat, Partition Key: BuchungsdatumJan 09 Feb 09 Mar 09 Apr 09 Mai 09 Jun 09 Jul 09 Aug 09 Sep 09 Oct 09 Nov 09 Dec 09 Problem: Die meisten Abfragen beziehen sich auf das Abflugdatum Flge knnen 11 Monate im Voraus gebucht werden 11 Partitionen mssen fr ein spezifisches Abflugdatum gelesen werden2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20137Alle Buchungen frFlge im November2009 8. Praxisbeispiel 1: Fluggesellschaft Lsung: Partition Key Abflugdatum statt Buchungsdatum Daten werden in aktuelle und zuknftige Partitionen geladen Abfragen nach Abflugdatum lesen nur eine Partition Abfragen nach Buchungsdatum mssen 11 (kleine) Partitionen lesen2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20138 9. Praxisbeispiel 1: Fluggesellschaft Bessere Lsung: Composite RANGE-RANGE Partitioning RANGE Partitionen auf Abflugdatum RANGE Subpartitionen auf Buchungsdatum Mehr Flexibilitt fr Abfragen auf Abflug- und/oder Buchungsdatum2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 20139 10. Praxisbeispiel 2: Internationale Bank Kontostnde fr internationale Kunden Monatliche Filelieferungen aus unterschiedlichen Lndern Teilweise Korrekturfiles (ersetzen letzte Lieferung des gleichen Monats) Ursprngliche Lsung: Technische LOAD_ID fr jede Kombination von Monat/Land LIST Partitionen auf LOAD_ID Files werden in Stage-Tabelle geladen Partition Exchange mit aktueller Partition2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201310 11. Praxisbeispiel 2: Internationale Bank Problem: Partition Key LOAD_ID ist fr Abfragen ungeeignet Abfragen basieren auf Bilanzdatum2014 Trivadis Lsung: RANGE Partitionen auf Bilanzdatum LIST Subpartitionen auf Lndercode Partition Exchange mit SubpartitionenPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201311 12. Der richtige Partition KeyLaden durch AustauschenRollende ZeitfensterGlobale Statistiken2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201312 13. Partition Exchange Vorgehensweise1. Daten in Work-Tabelle laden2. Indizes auf Work-Tabelle erstellen3. Auf Zieltabelle neue Partition anfgen4. Partition Exchange5. Ev. Work-Tabelle lschen2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201313 14. Partition Exchange Vorgehensweise1. Daten in Work-Tabelle laden2. Indizes auf Work-Tabelle erstellen3. Auf Zieltabelle neue Partition anfgen4. Partition Exchange5. Ev. Work-Tabelle lschen2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201314 15. Partition Exchange Vorgehensweise1. Daten in Work-Tabelle laden2. Indizes auf Work-Tabelle erstellen3. Auf Zieltabelle neue Partition anfgen4. Partition Exchange5. Ev. Work-Tabelle lschen2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201315 16. Interval PartitioningSeit Oracle 11g knnen Partitionen automatisch erstellt werden Erweiterung von RANGE Partitioning INTERVAL Partitioning Beim Einfgen von neuen Daten wird bei Bedarf neue Partition erstellt2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201316 17. Praxisbeispiel 3: Partition Exchange und IntervalPartitioning Problem: Partitionen knnen bei Interval Partitioning nicht explizit erstellt2014 TrivadiswerdenPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201317 18. Praxisbeispiel 3: Partition Exchange und IntervalPartitioning1. Work-Tabelle laden2. Erste Row in Zieltabelle einfgen Neue Partition wird erstellt3. Partition Exchange2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201318 19. Der richtige Partition KeyLaden durch AustauschenRollende ZeitfensterGlobale Statistiken2014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201319 20. Praxisbeispiel 4: Automatische Partitionenverwaltung Anforderungen Monatspartitionen auf allen Faktentabellen, tgliche ETL-Jobs Daten werden 3 Jahre gespeichert (36 Partitionen pro Tabelle) Table Compression, um Full Table Scans zu beschleunigen Backup nur auf jeweils aktuelle PartitionenTS_01 TS_02 TS_03 TS_04 TS_05 TS_06 TS_07 TS_08 TS_09 TS_10 TS_11 TS_12TS_13 TS_14 TS_15 TS_16 TS_17 TS_18 TS_19 TS_20 TS_21 TS_22 TS_23 TS_24TS_25 TS_26 TS_27 TS_28 TS_29 TS_30 TS_31 TS_32 TS_33 TS_34 TS_35 TS_362014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201320Jan 08 Feb 08 Mar 08 Apr 08 Mai 08 Jun 08 Jul 08 Aug 08 Sep 08 Oct 08 Nov 08 Dec 08Jan 09 Feb 09 Mar 09 Apr 09 Mai 09 Jun 09 Jul 09 Aug 09Sep 09Oct 06 Nov 06 Dec 06Jan 07 Feb 07 Mar 07 Apr 07 Mai 07 Jun 07 Jul 07 Aug 07 Sep 07 Oct 07 Nov 07 Dec 07 21. Praxisbeispiel 4: Automatische Partitionenverwaltung1. Set next tablespace to read-writeTS_01 TS_02 TS_03 TS_04 TS_05 TS_06 TS_07 TS_08 TS_09 TS_10 TS_11 TS_12TS_13 TS_14 TS_15 TS_16 TS_17 TS_18 TS_19 TS_20 TS_21 TS_22 TS_23 TS_24TS_25 TS_26 TS_27 TS_28 TS_29 TS_30 TS_31 TS_32 TS_33 TS_34 TS_35 TS_362014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201321Jan 08 Feb 08 Mar 08 Apr 08 Mai 08 Jun 08 Jul 08 Aug 08 Sep 08 Oct 08 Nov 08 Dec 08Jan 09 Feb 09 Mar 09 Apr 09 Mai 09 Jun 09 Jul 09 Aug 09Sep 09Oct 06 Nov 06 Dec 06Jan 07 Feb 07 Mar 07 Apr 07 Mai 07 Jun 07 Jul 07 Aug 07 Sep 07 Oct 07 Nov 07 Dec 07 22. Praxisbeispiel 4: Automatische Partitionenverwaltung1. Set next tablespace to read-write2. Drop oldest partitionTS_01 TS_02 TS_03 TS_04 TS_05 TS_06 TS_07 TS_08 TS_09 TS_10 TS_11 TS_12TS_13 TS_14 TS_15 TS_16 TS_17 TS_18 TS_19 TS_20 TS_21 TS_22 TS_23 TS_24TS_25 TS_26 TS_27 TS_28 TS_29 TS_30 TS_31 TS_32 TS_33 TS_34 TS_35 TS_362014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201322Jan 08 Feb 08 Mar 08 Apr 08 Mai 08 Jun 08 Jul 08 Aug 08 Sep 08 Oct 08 Nov 08 Dec 08Jan 09 Feb 09 Mar 09 Apr 09 Mai 09 Jun 09 Jul 09 Aug 09Sep 09Nov 06 Dec 06Jan 07 Feb 07 Mar 07 Apr 07 Mai 07 Jun 07 Jul 07 Aug 07 Sep 07 Oct 07 Nov 07 Dec 0722 23. Praxisbeispiel 4: Automatische Partitionenverwaltung1. Set next tablespace to read-write2. Drop oldest partition3. Create new partition for next monthTS_01 TS_02 TS_03 TS_04 TS_05 TS_06 TS_07 TS_08 TS_09 TS_10 TS_11 TS_12TS_13 TS_14 TS_15 TS_16 TS_17 TS_18 TS_19 TS_20 TS_21 TS_22 TS_23 TS_24Oct 09TS_25 TS_26 TS_27 TS_28 TS_29 TS_30 TS_31 TS_32 TS_33 TS_34 TS_35 TS_362014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201323Jan 08 Feb 08 Mar 08 Apr 08 Mai 08 Jun 08 Jul 08 Aug 08 Sep 08 Oct 08 Nov 08 Dec 08Jan 09 Feb 09 Mar 09 Apr 09 Mai 09 Jun 09 Jul 09 Aug 09Sep 09Nov 06 Dec 06Jan 07 Feb 07 Mar 07 Apr 07 Mai 07 Jun 07 Jul 07 Aug 07 Sep 07 Oct 07 Nov 07 Dec 0723 24. Praxisbeispiel 4: Automatische Partitionenverwaltung1. Set next tablespace to read-write2. Drop oldest partition3. Create new partition for next month4. Compress current partitionTS_01 TS_02 TS_03 TS_04 TS_05 TS_06 TS_07 TS_08 TS_09 TS_10 TS_11 TS_12TS_13 TS_14 TS_15 TS_16 TS_17 TS_18 TS_19 TS_20 TS_21 TS_22TS_23 TS_24Oct 09Sep 09TS_25 TS_26 TS_27 TS_28 TS_29 TS_30 TS_31 TS_32 TS_33 TS_34 TS_35 TS_362014 TrivadisPartitionierung im DWH: Erkenntnisse aus der Praxis19. Mrz 201324Jan 08 Feb 08 Mar 08 Apr 08 Mai 08 Jun 08 Jul 08 Aug 08 Sep 08 Oct 08 Nov 08 Dec 08Jan 09 Feb 09 Mar 09 Apr 09 Mai 09 Jun 09 Jul 09 Aug 09 Nov 06 Dec 06Jan 07 Feb 07 Mar 07 Apr 07 Mai 07 Jun 07 Jul 07 Aug 07 Sep 07 Oct 07 Nov 07 Dec 0724 25. Praxisbeispiel 4: Automatische Partitionenverwaltung1. Set next tablespace to read-write2. Drop oldest partition3. Create new partition for next month4. Compress current partition5. Set tablespace to read-onlyTS_01 TS_02 TS_03 TS_04 TS_05 TS_06 TS_07 TS_08 TS_09 TS_10 TS_11 TS_12TS_13 TS_14 TS_15 TS_16 TS_17 TS_18 TS_19 TS_20 TS_21 TS_22TS_23 TS_24Oct 09Sep 09TS_25 TS_26 TS_27 TS_28 TS_29 TS_30 TS_31 TS_32 TS_33 TS_34 TS_35 TS_362014 TrivadisPartitionierung im DWH: Erkenntn