Andreas BuckenhoferCDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie
TDWI 2016, München
Daimler TSS GmbH
Zur Person
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 2
https://de.linkedin.com/in/buckenhofer
https://twitter.com/ABuckenhofer
https://www.xing.com/profile/Andreas_Buckenhofer2
Andreas Buckenhofer
Senior DB [email protected]
Seit 2009 bei Daimler TSS im Fachgebiet Big Data Business Unit Analytics
Daimler TSS GmbH
Interner IT-Partner für Daimler
Wir sind Spezialist und strategischer Business-Partner für
innovative IT-Gesamtlösungen im Daimler-Konzern.
Als 100%ige Daimler-Tochter leben wir die Kultur der Exzellenz
mit dem Anspruch der Innovations- und Technologieführerschaft.
Mit herausragender Technologie- und Methodenkompetenz
verstehen wir uns als Anbieter von wettbewerbs-
differenzierenden Dienstleistungen und sind
Impulsgeber in anspruchsvollen IT-Fragestellungen,
speziell in den Kernthemen Car IT und Mobility,
Information Security, Analytics und
Shared Services.
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 3
Daimler TSS GmbH
Standorte
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 4
Daimler TSS Deutschland
6 Standorte774 Mitarbeiter
Ulm (Hauptsitz)
Raum Stuttgart
Böblingen, Echterdingen,Leinfelden, Möhringen
Berlin
Daimler TSS India
Hub Bangalore19 Mitarbeiter
Daimler TSS China
Hub Beijing6 Mitarbeiter
Daimler TSS Malaysia
Hub Kuala Lumpur42 Mitarbeiter
Daimler TSS GmbH
DWH für die Bauzustandsdokumentation
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 5
Daimler TSS GmbH
Lösungsarchitektur
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 6
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Agenda – 1.) CDC und Spiegel
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 7
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Agenda – 2.) CDC und DWH Staging Layer
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 8
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Agenda – 3.) DWH Core Layer / Data Vault
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 9
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Agenda – 4.) DWH Mart Layer und Reporting
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 10
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Technische Herausforderungen für Spiegel und DWH
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 11
Quellsystem 6TBkomprimiert
Initial Loadder Datenmenge
Quellsystem hoch verfügbar
(keine Downtime)
Verarbeitung vonLastspitzen
Mehrere Tabellen mit Milliarden
von Datensätzen + 1TB große
Tabelle
Transaktions-muster
(Hibernate!)
Viele Schema-änderungen
Flexible Reaktion auf Änderungen
Komma-separierte Codelisten
End-2-End Monitoring
Daimler TSS GmbH
Verwendete Produkte
Komponente Tool
Betriebssystem Quelle: AIX 7.1; Ziel: Linux SLES 11SP4
Datenbank IBM DB2 LUW 10.5 AESE
Replikation IBM InfoSphere Data Replication (IIDR) 10.2.1
ETL Informatica Powercenter 9.6.1
Frontend IBM Cognos 10.2.1
Datenmodellierung SAP/Sybase Powerdesigner 16.5
DB Monitoring ITGAIN Speedgain 4.8
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 12
Daimler TSS GmbH
Agenda – CDC und Spiegel
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 13
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Warum Datenspiegel?
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 14
Entlastung Quellsystem bei AdHoc-Abfragen
RightTime-fähige Daten-
versorgung
Daten-bereitstellung für Fremdsysteme
Daimler TSS GmbH
Erkennung von Datenänderungen
CDC = Change Data Capture
•Verschiedene Verfahren zur Erkennung von Änderungen in der DB
Verfahren sind z.B.
•Full Load und Delta-Erkennung im DWH•Quellsystem-Trigger•Quellsystem-Tabellen mit Zeitstempel und/oder Statusfeldern (Achtung bei
Deletes)•DB-Log Scanner (logbasiertes CDC)•Standby-DB / DB2 HADR („High Availability Disaster Recovery“)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 15
Daimler TSS GmbH
Logbasiertes CDC
Logbasiertes CDC ist „minimal-invasiv“
•Keine Änderungen der Applikation im Quellsystem•Geringe Belastung des Quellsystems•RightTime-fähig•Zieltabellen können abweichen von Quelltabellen (Indexe, Partitionierung)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 16
Achtung I: keine no-logging/nonrecoverable Operationen in der QuelleAchtung II: CLOBS, BLOBS, User Defined Data Types werden von vielen Tools nur unzureichend unterstützt
Daimler TSS GmbH
Warum eigenständiges Tool wie IIDR und nicht HADR?
HADR („ High Availability Disaster Recovery“) hat viele Vorteile
+ kein weiteres Tool nötig, eingebaut in DB2
+ minimale DB Anpassungen / einfache Konfiguration
+ überträgt Daten und Schemaänderungen (Tabellenänderungen inkl. Indexe, Trigger, etc
sowie weitere Objekte)
+ keine separate Lizenzierung
ABER
Spiegel-DB kann nur im Modus „uncommitted read“ betrieben werden. Dirty Reads
möglich!
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 17
Daimler TSS GmbH
Architektur IBM InfoSphere Data Replication (IIDR)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 18
DB
Management Console (GUI)
Access Server
DB-LogsDB2-ReplEngine DB2-ReplEngine
DataStoreDB-
Dateien DataStoreRefresh
Mirror
DBSubscriptions(Replication
Threads)
TCP/IP durch Firewall
Command Line (CHCCLP)
AccessAgent
AccessAgent
Metadaten
Metadaten
Daimler TSS GmbH
Subscriptions
Definieren Mappings zwischen Quelltabelle und Zieltabelle
Spaltenfilter
Alle Spalten übertragen?
Datenfilter
Alle Daten übertragen?
Transformation
Daten verändern?
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 19
Daimler TSS GmbH
Subscriptions und deren Tabellen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 20
Daimler TSS GmbH
Mapping Methoden
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 21
StandardApply
AdaptiveApply
Live AuditApply
SummarizationApply
ConsolidationApply
Daimler TSS GmbH
Standard Apply Fahrzeug Mapping
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 22
Daimler TSS GmbH
Audit Apply Fahrzeug Mapping
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 23
Daimler TSS GmbH
Standard Apply und Audit Apply: Ausgangssituation
FIN Werknr
WDD1690071J236589 25
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 24
FIN Werknr
WDD1690071J236589 25
FIN Werknr Aud_type Aud_time
WDD1690071J236589 25 Insert 16.05.2016 17:00:10
Standard Apply
Audit Apply
Daimler TSS GmbH
Standard Apply und Audit Apply: insert und update
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 25
FIN Werknr
WDD1690071J236589 26
WDD1690071J227336 333
FIN Werknr Aud_type Aud_time
WDD1690071J236589 25 Insert 16.05.2016 17:00:10
WDD1690071J236589 26 Update After 16.05.2016 17:00:15
WDD1690071J227336 333 Insert 16.05.2016 17:00:18
FIN Werknr
WDD1690071J236589 26
WDD1690071J227336 333
Standard Apply
Audit Apply
Aud_type Spalte in IIDR:PT = Put (Insert)CL = Clear (Truncate)DL = DeleteUB = Update before imageUP = Update after imageRR, RS = Für Refresh
Daimler TSS GmbH
Ablauf Initial Refresh Spiegel
Quelltabellen in Ziel-DB anlegen, Subscriptions anlegen und Commit-Intervall hochsetzen (hier: 20 Mio.)
Generated always Spalten löschen
Ausgewählte Indexe löschen
Alle FKs ausschalten; PKs beibehalten
Start Refresh mit mehren parallelen Subscriptions
Start Mirroring sobald eine Subscriptionfertig ist
Überführung alle Subscriptions in eine Subscription
Gelöschte Indexe anlegen
FKs wieder einschalten
Daimler TSS GmbH
Monitoring Initial Load: CPU bei maximiertem Durchsatz
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 27
Daimler TSS GmbH
Monitoring Initial Load: I/O Requests weisen gute Antwortzeiten auf
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 28
Daimler TSS GmbH
End2End-Monitoring
Wie aktuell ist der Spiegel?
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 29
Daimler TSS GmbH
IIDR Management Console GUI: IIDR Performance Monitoring
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 30
Daimler TSS GmbH
IIDR Management Console Performanz Monitoring
GUI mit vielen Metriken, aber
Keine Verfügbarkeit historischer Performanzdaten
GUI muss zur Aufzeichnung geöffnet sein
Daten nur für begrenzten Zeitraum verfügbar
Latenz (Zeitdifferenz Quelle - Ziel) nur für beendete Transaktionen
Hohe Latenz = Ziel ist nicht
aktuell
Benötigt wird die Latenz für die
aktuell laufende Transaktion
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 31
Daimler TSS GmbH
Eigene Skripte zum Erfassen und Langzeit-Protokollieren
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 32
Daimler TSS GmbH
Eigene Skripte zum Erfassen und Langzeit-Protokollieren
Nutzung IIDR command Line Interface CHCCLP
Befehle zum Auslesen der Performanzdaten in eine Datei
Parsing der Datei und Speichern der Werte in Datenbank-Tabellen
Skript per crontab: Aufruf z.B. alle 5 min
Skript löst noch nicht das Problem, dass IIDR die Latenz für eine laufende Transaktion nicht übermittelt
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 33
Daimler TSS GmbH
Lösung Latenzbestimmung
Heartbeat-Tabelle in der Quelle mit einer Timestamp-Spalte
Timestamp-Spalte wird laufend aktualisiert, z.B. jede Minute
Aktualisierung wird in der Subscription übertagen
Latenz = Differenz aus aktueller Zeit und letzte Aktualisierung der gespiegelten Heartbeat-Tabelle (current_timestamp – Zieltabelle.timestamp)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 34
Daimler TSS GmbH
Zusammenfassung CDC und Spiegel
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 35
Logbasiertes CDC zur minimal-invasiven Erkennung von Änderungen
Initial Load: Parallelisierung der Subscriptions und anschließende Zusammenführung
Standard Apply für DML (insert, update, delete)
Daimler TSS GmbH
Agenda – CDC und DWH Staging Layer
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 36
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Warum Staging Layer?
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 37
Entkopplung QuellDB – ZielDB
bzw weiterer Quellsysteme (Schnittstelle)
Temporäre Pufferung der
Daten-Änderungen aus
der Quell-DB
Berechnung von Hashwerten für Primärschlüssel und Vergleiche
Daimler TSS GmbH
Standard vs Audit Apply: Insert und Update/Delete
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 38
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
INSERT
INSERT
INSERT
INSERT
UPDATE
UPDATE
INSERT
INSERT
Daimler TSS GmbH
Benötigte Daten im DWH verglichen mit Spiegel
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 39
Untermenge der Tabellen
Geringere Datenmenge
Initial Loadkein Problem
Daimler TSS GmbH
Row by Row = Slow by Slow (Tom Kyte)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 40
Daimler TSS GmbH
Row by Row = Slow by Slow (Tom Kyte)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 41
Row by Row
=
Slow by Slow
(Tom Kyte)
Daimler TSS GmbH
Lösung Initial Load DWH Staging Layer
• Audit Apply nutzt keinen DB2 Load für Initialbefüllung �
• IIDR Optimierungsmöglichkeiten für Audit Apply nicht ausreichend
• Workaround
• Standard Apply für Initial Load analog zum Spiegel, danach Audit Apply
• Vor dem Umschalten auf Audit Apply, Zurücksetzen des Bookmarks
Bookmark: Setzen des Lesepointers im Transaktionslog der Quell-DB
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 42
Daimler TSS GmbH
Transaktionsmuster Hibernate / Quellapplikation
CDC macht Transaktionsmuster sichtbar wie z.B.
• Dummy-Insert + danach Update
• Dummy-Insert + danach Update + danach Update
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 43
ID FIN Werknr Aud_type Aud_time
1111 Insert 16.05.2016 17:00:10
1111 WDD1690071J236589 26 Update 16.05.2016 17:00:10
ID FIN Werknr
1111 WDD1690071J236589 26
Audit Apply
Daimler TSS GmbH
Transaktionsmuster Hibernate / Quellapplikation
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 /
• Dummy-inserts/updates sind fachlich nicht relevante Daten
• IIDR: Erkennung des gültigen Datensatzes (Timestamp nur sekundengenau)
• Einfügen von DB2 generated always-Spalten zur Erkennung der Reihenfolge und des
letzten Datensatzes
Seite 44
Daimler TSS GmbH
Tabellen im Staging Layer
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 45
• Keine PKs, FKs• Keine Indexe• Truncate der Daten nach
1 Woche
Daimler TSS GmbH
Indexe, Constraints und Partitionierung
DWH Layer Indexe? Constraints? Partitionierung?
Staging Keine Keine Ja, tageweise (truncate nach 1 Woche)
Core/Data Vault
Mart
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 46
Daimler TSS GmbH
Zusammenfassung CDC und Staging Layer
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 47
Logbasiertes CDC zur minimal-invasiven Erkennung von Änderungen
Initial Load: Standard Apply wegen Nutzung DB2 Load
Mirroring: Audit Apply für DML (insert, update, delete)
Daimler TSS GmbH
Agenda – DWH Core Layer / Data Vault
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 48
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Warum Core / Data Vault Layer?
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 49
HistorisierungIntegration mehrerer
Datenquellen
Laden aller Daten, keine fachlichen
Updates oder Deletes
Daimler TSS GmbH
Modellierungsalternativen im Core Layer
3NF
Star Schema
Head/Version
Data Vault
u.a.
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 50
Daimler TSS GmbH
Data Vault (Daniel Linstedt)
Data Vault ist optimiert für Anforderungen im Core Warehouse Layer mit folgenden
Eigenschaften:
• Skalierbarkeit
• Datenintegration, Automatisierung
• Datenhistorisierung
• Auditierbarkeit
• Flexibilität
• Agilität
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 51
Bill Inmon (DW 2.0: The Architecture for the Next Generation of DWH, 2008): “Data Vault is the optimal approach for modeling the EDW in the DW2.0 framework.”
Daimler TSS GmbH
Data Vault
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 52
Business Key
Beziehungen
KontextundHistorie
Data Vault:HUB
Data Vault:LINK
Data Vault:SAT
FINModellFarbeAusstattungBrancheProduktserieProduktionsbeginnLieferdatumBestelldatumKundeSteuergeräte
Fahrzeugdaten
Daimler TSS GmbH
HUB
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 53
Identifikation
durch
eindeutige
natürliche
Schlüssel
(Business Keys)
Daimler TSS GmbH
Struktur HUB-Tabellen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 54
Daimler TSS GmbH
Typische Merkmale HUB-Tabellen
Business Keys sollten echte Geschäfts-IDs sein (z.B. Fahrzeug ID, Seriennummer des Steuergeräts) = natürliche Schlüssel
Business Keys sollten sich nie ändern
Business Keys, die über mehrere Datenquellen hinweg nicht eindeutig sind, müssen das Quellsystem für die Eindeutigkeit des Business Keys verwenden
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 55
Daimler TSS GmbH
LINK
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 56
Verbindungen
zwischen
Business Keys
(HUBs)
Daimler TSS GmbH
Struktur LINK-Tabellen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 57
Daimler TSS GmbH
Typische Merkmale LINK-Tabellen
Ein LINK modelliert eine Beziehung. Die Beziehung ist immer n:m
Der zusammengesetzte Fremdschlüssel muss eindeutig sein
Eine Fremdschlüsselbeziehung ist führend („driving“)
Satelliten sind optional
Hierarchische Beziehungen durch einen LINK zwischen genau zwei HUBs. Arten von Beziehungen: SAL (same-as LINK) und HAL (parent-child LINK)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 58
Daimler TSS GmbH
SAT
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 59
Beschreibende,
detaillierte,
aktuelle
und
historisierte
Daten
Daimler TSS GmbH
Struktur SAT-Tabellen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 60
Daimler TSS GmbH
Typische Merkmale SAT-Tabellen
Enthält alle nicht-Schlüssel Daten
Ist genau einer HUB-Tabelle oder LINK-Tabelle zugeordnet
HUB-Tabellen oder LINK-Tabellen können (sollen) mehrere SAT-Tabellen enthalten, die nach Quellsystem, Datenänderungsrate, Datentypen, usw. entworfen werden können
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 61
Daimler TSS GmbH
Design von SAT Tabellen
Verschiedene Kriterien, um SAT-Tabellen zu entwerfen, z.B.
• Quellsystem
• Änderungshäufigkeit
• Datentypen (z.B. Auslagerung von CLOBS)
• Zerlegung inperformanter Quellsystemtabellen mit vielen Spalten
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 62
Daimler TSS GmbH
Design von SAT Tabellen
Änderungshäufigkeit der Daten zur Vermeidung redundanter Speicherung bei
Datenänderungen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 63
Daten, die sich häufiger ändernDaten, die sich nicht ändern
Lieferdatum SW-Version Steuergerät
Diebstahlmeldung
Farbe KommentarAusstattungsmerkmale
Daimler TSS GmbH
Hash Keys statt Sequenzen für Primärschlüssel
Berechnung eines HKs aus den natürlichen Schlüsselattributen (Business Keys)
• Keine Lookups nötig: weitere Zunahme der Parallelisierbarkeit beim Load
• Vermeidung von Hot Spots (insb. sinnvoll bei sog. „Converged Systems“ wie Microsoft
APS, Exadata, Teradata, etc)
• Möglichkeit zur Verbindung relationaler DBs und Daten in Hadoop/NoSQL
• Jedoch: Gefahr einer Hash Key-Kollision
Anmerkung: UPPER für den Fall, dass die jeweilige Spalte case-insensitiv ist
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 64
HK = MD5_HASH (UPPER (TRIM (BK1)) || TRENNER || UPPER (TRIM (BK2)) …)
Daimler TSS GmbH
Hash Keys zur Beladung von Satelliten
Berechnung von Hash Keys für Attribute in Satelliten-Tabellen
• Vergleich Hash Keys neue Daten in Staging Layer und geladene Daten in Core Layer
�Effizienter
�Weniger Code
Anmerkung: UPPER für den Fall, dass die jeweilige Spalte case-insensitiv ist
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 65
HK = MD5_HASH (<HK Berechung BKs> || TRENNER || UPPER (TRIM (Spalte1)) || TRENNER || UPPER (TRIM (Spalte2)) || TRENNER ||
…)
Daimler TSS GmbH
Hash Keys und Kollision
Gefahr einer Kollision (jedoch insgesamt geringe Wahrscheinlichkeit)
• 2 Quellwerte erzeugen
den selben Hashwert
• Eingebaute DB2-Hashfunktion
reicht nicht aus
• Lösung:
https://github.com/tessus/db2-hash-routines
• UDF ist kompatibel mit Hash-Funktion
in Informatica
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 66
Daimler TSS GmbH
Wiederholbares Muster: Beladung HUB
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 67
Quelldaten
vorhanden
Business Keys distinkt laden
Business Key bereits im
HUB?
Datensatz in HUB eintragen
Konflikt bei PK HashKey-Kollision!
Nein
Datensatz verwerfen
Daten im HUB
vorhanden
Ja
Daimler TSS GmbH
Wiederholbares Muster: Beladung LINK
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 68
Quelldaten
vorhanden
Business Keys distinkt laden
Hash Key Beziehungen
bereits im LINK?
Datensatz in LINK eintragen
Konflikt bei PK HashKey-Kollision!
Nein
Datensatz verwerfen
Daten im Link
vorhanden
Ja
Daimler TSS GmbH
Wiederholbares Muster: Beladung SAT
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 69
Quelldaten
vorhanden
Quelldaten distinkt laden
MD5-HASH Diff gleich?
Datensatz in SAT eintragen
Nein
Datensatz verwerfen
Daten im SAT
vorhanden
Ja
Aktuellsten Datensatz aus
SAT-Tabelle laden
Daimler TSS GmbH
Parallelisierung der Beladung
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 70
HU
B g
elad
en
LIN
K u
nd H
UB
-
SAT
gela
den
LIN
K-S
ATge
lade
n
Da
ta V
au
lt
Lo
ad
Kla
ssis
ch
er
Lo
ad
?? ?
Integration neuer JobsZeitfenster für Beladungen 00:00-06:00
• Komplex
• Viele Abhängigkeiten
• Viele sequentielle Jobs
• Systematisch
• Wenige, definierte Abhängigkeiten
• Massiv parallel
Daimler TSS GmbH
Vorteile Data Vault
• Systematischer Ansatz
• Kleine, überschaubare Mappings
• Skalierbarkeit
• Flexibler Ansatz
• Automatisierung
• Volle Auditierbarkeit
• Keine fachlichen Update oder Delete-Operationen
• Support durch Tools
• Horizontale und vertikale Partitionierung
• Für relationale und Hadoop/NoSQL-Technologien
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 71
Daimler TSS GmbH
Nachteile Data Vault
• Mehr Tabellen
• Mehr Joins
• Alle Beziehungen sind modelliert als n:m (Dokumentation der Beziehungen nötig!)
• Bei der Beladung der HUBs, SATs, LINKs muss mehrfach auf dieselbe Quelltabelle
lesend zugegriffen werden
• Die Logik zur Befüllung der Data Marts kann komplex sein falls viele HUBs und deren
Satelliten beteiligt sind und die SQL-Logik Gültigkeitsdaten auflösen muss (Point in
Time oder Bridge-Tabellen verwenden!)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 72
Daimler TSS GmbH
Tabellen im Core / Data Vault Layer
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 73
Daimler TSS GmbH
Anzahl Tabellen
Layer Anzahl Tabellen
Staging 57
Core / Data Vault 164
Mart 21+
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 74
Daimler TSS GmbH
Indexe, Constraints und Partitionierung
DWH Layer Indexe? Constraints? Partitionierung?
Staging Keine Keine Ja, tageweise
Core/Data Vault Ja (1) Ja (2) Verschiedene, häufig nach Branche (PKW, LKW, Van, Bus)
Mart
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 75
• (1) so wenige wie möglich
• (2) für Leseperformanz; Constraints generell vorteilhaft für DB Optimierer
Keine Regel ohne Ausnahme! Obige Tabelle wurde in den meisten Fällen eingehalten.
Daimler TSS GmbH
Zusammenfassung DWH Core Layer / Data Vault
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 76
Data Vault zur Modellierung im Core Layer
HUB, LINK und SAT-Tabellen
Hohe Parallelität, robuste ETL-Verarbeitung
Daimler TSS GmbH
Agenda – DWH Mart Layer und Reporting
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 77
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Warum Mart Layer?
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 78
Standard Reporting:
Star Schema für performante
Analysen
AdHocReporting:
Zugriff auf Daten im DWH und im
Spiegel
Umsetzung Security im DWH: User
sehen nur die Daten, für die sie berechtigt sind
Daimler TSS GmbH
Performanz Aufbau Data Marts
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 79
Lange Laufzeiten trotz aktueller Objektstatistiken
Schlechte Ausführungspläne (u.a. Nested Loops)
Join von Tabellen mit Milliarden von Datensätzen
Komplexe SQL für Dimensionen und Fakten
Daimler TSS GmbH
Performanz-Tabellen: PIT und Bridge-Tabellen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 80
DQ Fakt
Steuergerät Fakt
Ausstattung Fakt
Bridge-Tabellen
Fehlercode DimBranche Dim
Produkt DimDimensionen
Daimler TSS GmbH
PIT-Tabellen (Point in Time)
Eine PIT Tabelle beinhaltet Daten für einen performanten Zugriff auf Satelliten.
Eine PIT-Tabelle jeweils für HUBs bzw. LINKs
• HashKey vom HUB oder LINK (PK)
• Snapshot Date / Time (PK)
• Für jeden benötigten Satelliten:
SAT HashKey und (aktuelles = max) Load Date
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 81
Daimler TSS GmbH
Bridge-Tabellen
Eine Brückentabelle beinhaltet Daten für einen performanten Zugriff, um mehrere
HUBs und LINKs zu verknüpfen.
Eine Brückentabelle umfasst mehrere HUBs und LINKs.
• HashKey (PK)
• Snapshot Date / Time (PK)
• HUB und LINK HashKeys
• GGf. berechnete Werte / Aggegrationen für Fakten
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 82
Daimler TSS GmbH
Standard und AdHoc Reports
Standard-Reports basieren auf
• Faktentabellen und Dimensionen im DWH Mart Layer
• Security-Konzept
• Zukünftig Einsatz „spaltenorientierte In-Memory“ Option möglich
AdHoc-Reports basieren auf
• Spiegel
• DWH Mart Layer
• Für Superuser
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 83
Daimler TSS GmbH
Tabellen im Mart Layer: Fakten und Dimensionen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 84
Daimler TSS GmbH
Indexe, Constraints und Partitionierung
DWH Layer Indexe? Constraints? Partitionierung?
Staging Keine Keine Ja, tageweise
Core/Data Vault Ja (1) Ja (2) Verschiedene, häufig nach Branche (PKW, LKW, Van, Bus)
Mart Ja Ja (2) Ja, nach Branche (PKW, LKW, Van, Bus)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 85
• (1) so wenige wie möglich
• (2) für Leseperformanz; Constraints generell vorteilhaft für DB Optimierer
Keine Regel ohne Ausnahme! Obige Tabelle wurde in den meisten Fällen eingehalten.
Daimler TSS GmbH
Zusammenfassung DWH Mart Layer und Reporting
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 86
Bridge und PIT-Tabellen waren notwendig, um Mart zu befüllen
Persistenter Data Mart, zukünftig ggf. Nutzung spaltenorientierter In-Memory Technologie
Standard Reporting (DWH) und Ad-hoc Reporting (DWH + Spiegel)
Daimler TSS GmbH
Zusammenfassung Lösungsarchitektur
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 87
ETL Engine
Frontend
StandardReports
AdHocReportsLogs
TSM
IIDRReplEngine
Source
DatastoreSource
Spiegel DB
OLTPDB
IIDR ReplEngineSpiegel
DatastoreSpiegel
IIDR ReplEngineDWH
DatastoreDWH
DWH DB
Staging Layer
Core Layer / Data Vault
Mart Layer
Daimler TSS GmbH
Datenintegration
“People have forgotten, or never truly understood, how complex data integration
actually is.”
Data Vault
• Robuste ETL Prozesse
• Kleine, wartbare Mappings
• Standardisierbarkeit / Wiederholbarkeit /Automatisierbarkeit
• Flexibilität
• Aber auch:
Viele Modellierungsoptionen = Viele Diskussionen
Quelle Zitat: http://db2portal.blogspot.de/2015/05/a-trip-report-from-2015-idug-db2-tech.html
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 88
Daimler TSS GmbH
Datenmodellierung
“Data modeling is the process of learning about the data, and regardless of
technology, this process must be performed for a successful application.”
Datenmodellierung, um
• Daten zu verstehen
• Performanz zu garantieren
• Entwicklung zu beschleunigen
• Qualität der Software zu verbessern
• Wartungskosten zu reduzieren
• Gemeinsames Verständnis zu fördern
• NoSQL Schema-on-read: Modellversionen auch nach Jahren noch verstehen
Quelle Zitat: Steve Hoberman: Data Modeling for Mongo DB, Technics Publications 2014
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 89
Daimler TSS GmbH
Methoden Know-how vs Produkt Know-how
Datenmodellierung macht jeder.
Methoden-Schulungen?
Fokus leider auf Schulung von Produkten / Tools.
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 90
Vielen Dank!
Daimler TSS GmbH
Wilhelm-Runge-Straße 11, 89081 Ulm / Telefon +49 731 505-06 / Fax +49 731 505-65 [email protected] / Internet: www.daimler-tss.com / Intranet-Portal-Code: @TSSSitz und Registergericht: Ulm / HRB-Nr.: 3844 / Geschäftsführung: Dr. Stefan Eberhardt (Vorsitzender), Steffen Bäuerle
Daimler TSS GmbH
Literatur
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 92
Daimler TSS GmbH
ETL tägliche Läufe: CPU vs logische Lesezugriffe
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 93
Daimler TSS GmbH
Lösung Schemaänderungen Quellsystem
Nur für wenige Änderungen geringer Aufwand, ansonsten
• Stoppen der Subscriptions
• Durchführung der Schemaänderungen im Ziel
• Herauslösung der Tabelle in eine eigene Subscription
• Initial Load
• Überführung der Subscription in die Gesamtsubscription
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 94
Daimler TSS GmbH
Warum IIDR und nicht HADR? 1(4)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 95
Quell-DB Ziel-DB
Session 1
Session 2
Session 3
Ausgangszustand: Session 1 und Session 2 in der Quell-DB sowie Session 3 in der Ziel-DB zeigen die gleichen Daten an.
HADR
Daimler TSS GmbH
Warum IIDR und nicht HADR? 2(4)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 96
Quell-DB Ziel-DB
Session 1
Session 2
Session 3
In der Quell-DB wird eine Änderung (hier im Beispiel insert eines Datensatzes; für andere Operationen wie update oder delete gilt dasselbe) durchgeführt. Die Transaktion läuft noch, d.h. ein commit steht noch aus.
HADR
Daimler TSS GmbH
Warum IIDR und nicht HADR? 3(4)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 97
Quell-DB Ziel-DB
Session 1
Session 2
Session 3
Session 3 zeigt die geänderten Daten von Session 1 bereits an (uncommitted Read Modus bei HADR). Session 2 auf der Quell-DB sieht diese Änderungen korrekterweise noch nicht, d.h. auf der Ziel-DB sind ACID-Transaktionen aufgehoben.
HADR
Daimler TSS GmbH
Warum IIDR und nicht HADR? 4(4)
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 98
Quell-DB Ziel-DB
HADR
Session 1
Session 2
Session 3
Session 1 führt kein commit durch sondern ein rollback z.B. aufgrund eines Fehlers. Die drei Sessions sind danach wieder synchron. Jedoch hatte Session 3 Daten angezeigt und potentiell weiterverarbeitet, die nicht existieren (Dirty Read).
Daimler TSS GmbH
IIDR Management Console: Fehlerauwertung
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 99
Daimler TSS GmbH
Nutzung von Performanz-Tabellen
Aufbau Data Vault-Tabellen
Aufbau Performanz-Tabellen• Schneller Zugriff auf aktuellste
SAT-Daten• Vorberechnete HUB und LINK-
Joins
Aufbau Mart-Tabellen• Änderungserkennung mit Hilfe
der Performanztabellen• Join Performanztabellen mit
Data Vault-Tabellen
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 100
Daimler TSS GmbH
Monitoring: Standardisierung Session Logging
Beladung des DWH mittels
• Informatica Powercenter
• Eigene Logs
• Workflow Monitor
• DB2 SQL PL (Stored Procedures)
• Per Default kein Logging
• SPs wurden gezielt für den Transport großer Tabellen verwendet
Vorgabe: Jedes Mapping und jede Stored Procedure trägt Start, Ende, verarbeitete
Zeilen, Fehler, u.ä. in eine Tabelle im DWH ein.
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 101
Daimler TSS GmbH
Session Logging in Informatica mit unconnected ProcedureTransformation
CDC und Data Vault für den Aufbau eines DWH in der Automobilindustrie / Analytics / 21.06.2016 / Seite 102
Top Related