goethe-universität frankfurt big data lab database systems ... · goethe-universität frankfurt...

Goethe-Universität Frankfurt Big Data Lab

-

Database Systems I

-

Gastvorlesung

Jens Fache

und

Dr. Michael Simora,

Adastra GmbH

Frankfurt, 06.02.2019

2

Adastra Quick Facts

INFORMATION INSIGHT

Daten Management

und Business Analytics

24 BÜROS WELTWEIT

32 Internationale

Auszeichnungen für

Qualität

€175MILLIONEN

Stetiges Wachstum

seit 20 Jahren

2000+ EXPERTEN

360+ abgeschlossene

Projekte

3

Adastra Leistungsportfolio

Business Information

Management

BusinessAnalysis

QualityAssurance

Data Quality

Data Integration

Advanced AnalyticsMaster

DataManage-

ment

Big Data & Analytics

ProjectManage-

ment

SolutionArchitecture

BusinessIntelligence

4

Ziele / Zweck des Enterprise Data Warehouse (EDW)

(grobe) Architektur des EDW

Datenanlieferung

Datenverarbeitung

Datenexport

Konzeptionelles und Physisches Datenmodell

Rückblick

Ausblick

Use Case I: Datenmodell in Banken

5

Ziel:

zentrale Datenintegrationsplattform

Decision Support System

Kein Reporting

Kein Frontend / Graphical User Interface

Nur MS SQL Server

Enterprise Data Warehouse (EDW)

6

EDW Architektur

Roh Daten

Schicht

Inte-grierteDaten

Schicht

Export Daten

Schicht

Informatica

EDW

7

EDW Architektur

Kunden- / Geschäfts-

daten

Trans-aktionen

Töchter

Vorsysteme

RDS

LADERAMPE

IDS EDS

Informatica

8

EDW Architektur


daten

Trans-aktionen

Töchter

Vorsysteme

Data Mart I

Data Mart II

Reporting

Controlling, Buchhaltung

RDS

LADERAMPE

L I EFERRAMPE

IDS EDS

Informatica

9

EDW Architektur


daten

Trans-aktionen

Töchter

Vorsysteme

Data Mart I

Data Mart II

Reporting


RDS

LADERAMPE

L I EFERRAMPE

IDS EDS

Ab Initio

Informatica

Stresstest

10

EDW Architektur


daten

Trans-aktionen

Töchter

Vorsysteme

Data Mart I

Data Mart II

Reporting


RDS

LADERAMPE

L I EFERRAMPE

IDS EDS

Ab Initio

Informatica

Stresstest

Referenzdaten

11

Pull vs. Push Verfahren

Immer Volllieferungen! Delta bei Bedarf aus Historisierung berechnet

Datenanlieferung


daten

Verbriefungsbestand

Sicher-heiten

Risiko-vorsorge

Kredite

Geldhandel Inland

Devisen-handel

Depot-gebühren

Kreditart

Kontotyp

Rechts-formen

Wirtschafts-zweige

Risk Data Warehouse

Global Limit System

Töchter

u.v.m.

EDW LADE-

RAMPE

12

Datenverarbeitung

Roh Daten

Schicht=

Datenmodell Quellsysteme

ETL mit Informatica:Join, Sum, Transform, Deduplicate, etc.

Integrierte Daten

Schicht =

Datenmodell fachl. Sicht

ExportDaten

Schicht=

Datenmodell (anforderung)

Abnehmer-systeme

Tagesverarbeitung vs. Ultimoverarbeitung

LADERAMPE

L I EFERRAMPE

13

Datenverarbeitung II

RDS

Ab Initio:Data Governance, Data Quality

Stresstest:Simulation Kreditausfälle, Währungsverfall,

Leitzinsszenarien, strengere regul. Anforderungen, etc.

LADERAMPE

L I EFERRAMPE

IDS EDS

14

Datenexport

EDW LIEFER-RAMPE

Data Mart I

Data Mart II

Data Mart III

Reporting

Buchhaltung

Controlling

ReferenzdatenPull vs. Push

Verfahren

15

Bislang: Enterprise Data Warehouse Architektur sowie Datenverarbeitung / Datenflüsse (übergeordnet)

Jetzt: Architektur auf granularer Ebene

Im EDW (und anderswo) zahlreiche Entitäten abgebildet

Kunde, Konto, Geschäft, Ratings, Risikopositionen, etc.

Fachbereich definiert konzeptionelles Datenmodell (Zusammenhänge)

IT definiert / implementiert physisches Datenmodell

Plattformübergreifendes logisches Datenmodell nicht notwendig, da nur SQL Server genutzt wird

Datenmodellierung

16

Konzeptionelles Datenmodell

Kunde

Kundengruppe

Rechtsform

Ausschlussgrund

Land

Organisations-einheit

Kunden-rolle

Kunden-verknüpfung

Risiko-position

Wertpapier-stamm

Geschäft

Sicherungs-geschäft

Sicherheiten-verteilung

Sicherheiten-verteilung HGB

Ratings

Verknüpfungsart Verknüpfungsgrund

Kunden-information

17

Physisches Datenmodell

Kunde:Kunde_SID (PS,bigint,Nicht-NULL);Business_Date (date,Nicht-NULL);

Name (varchar(100),NULL);Adresse (varchar(100),NULL);

ID_jur_Person (varchar(100),NULL);Rechtsform (varchar(100),NULL);

Bilanzsumme (decimal(28,4),NULL);Jahresumsatz (decimal(28,4),NULL);

…

Konto:Konto_SID

(PS,bigint,Nicht-NULL);Kunde_SID

(bigint,Nicht-NULL);Business_Date

(date,Nicht-NULL);Bezeichnung_KTO

(varchar(100),NULL);Internes_Limit_Betrag(varchar(100),NULL);

…

Limite:…

Vermögen:…

Kundenrolle Risikoposition:

…

Kundenrating-intern:

KUNDE_SID;Business_Date;Ratingnote_LC;Ratingnote_FC;

…

Kundenrating-extern:

KUNDE_SID;Business_Date;

Ratingnote_extern;Source_System_ID;

…

Kundenrolle Geschäft:

…

Kunden-information:

…

18

Planung: Aufbau des EDW als zentrale Datenintegrationsplattform der Bank

Schritt 1: Identifizierung der Kerngeschäftsprozesse, der zugehörigen Entitäten sowie deren Relationen

Zunehmende (regulatorische) Anforderungen

Sukzessiv wachsendes Data Warehouse

Top-Down vs. Bottom-Up Ansatz

Generische Modellierung notwendig

Dokumentation!

Rückblick

19

Anbindung der Töchter und deren Datenverarbeitung ins zentrale EDW

Mehr Quellsysteme, Mehr Abnehmersysteme, Mehr Datenverarbeitung

Mehr Datenlast

Performance Probleme bei Tagesverarbeitung absehbar

Clusterlösung

Eingliederung Stresstest

Ausbau der zentralen Data Governance Anwendung

Vereinheitlichung, Datenqualitätssicherung

Ausblick

20

Anwendungsfall: Autohersteller - Qualitätssicherung

Qualitätssicherung:

• Nach wieviel km geht Kupplungsbausatz KBSX-12 durchschnittlich kaputt

• Wieviel Garantieleistungen hat das Modell SUV_3XL verursacht.

• Ist die Motorwarnleuchte im Cabrio_2 bei offenem Dach auffällig genug

• Hat die die Scheinwerferserie ULight+ ein Problem bei längeren Regenperioden

• Welche Garantieleistungen sind für das Modell L-0817 in Schweden zu erwarten

• Wieviel Bremsscheibensätze der Reihe BS_23/4 werden im kommenden Jahr für den Austausch in den USA benötigt

Analyse Plattformum derartige Fragestellungen flexibel beantworten zu könnenSchnelle AntwortzeitenGraphische BedieneroberflächeMobiltauglich….

21


Ausgangssituation:

DB2 IBM Host Konzern Operational Data Store

FTP Client

...

......

Gemeinsame Hostingplattform für konzerneigene Produktionsssysteme

Externe Datenquellen

Excel

ExcelExcel

Excel Excel Access

Access

csv

xml

• Daten können nur in Excel manuell kombiniert/ verbunden werden

• Keine automatische Synchronisation

• Kapazitäts und Performanceprobleme

• Fehleranfällig

22


Zielarchitektur:

Oracle DWH

DB2 IBM Host Konzern Operational Data Store

Cognos BISPSS R Deep

Analysis

Writeback

FTP Client

...

......

IBM

IDAA

Mainframe Jobs

Transformationen DWH Datenmodell

Advanced Analytics:

Predictive analysis

Deep learning...

REST API

Transformationen Denormalisierung

Datenintegration in DWHNormalisiertes und Dimensionales Modell

Analytische High Performance DBAbfrageoptimiertes denormalisertes Modell

Verknüpfung und Historisierung der Datenin einem Datawarehouse

23


Welche Datenquellen brauche ich :

• Fahrzeugstammdaten (Steckbrief:Typ, Ausstattung, ProduktionsDatum, Produktionswerk)

• Fahrzeugdetails( Teile, Komponenten, Bausätze)

• Zulassungsdaten(wann, wo)

• Werkstattberichte

• Garantie (Berichte über Garantieleistungen)

• Steuergeräte(Protokolle)

• Geographie ( Karten, GPS)

• Wetterdaten

• …

24

Inhalt (fachlich)

Sind alle Informationen enthalten

Beschreibung Files und Felder

Beschreibung Entities und Attribute

Qualität

Typsicherheit

Vollständigkeit (null Felder)

Manuell gepflegt?

Duplikate?

Quantität

Anzahl Files

Anzahl Felder

Physikalische Größe

Menge Backdata


Beschreibung der Datenquellen / Schnittstellen

25

Aktualisierungsfrequenz

Quellsystem Täglich, Monatlich, Stream

Zielsystem Anforderung

Reihenfolge relevant

Wie passt Frequenz zu den anderen Datenquellen (integrität)

Format (technisch)

Codierung

csv, xml, xls ,fixed with

Header?

Delimiter

Metadaten?

Anwendungsfall: Autohersteller - QualitätssicherungBeschreibung der Datenquellen / Schnittstellen

26

Zugriff/Konnektivität

Remoteverbindung

Adresse

Authentication Methode

Credentials

Verschlüsselung

Komprimierung

Sensitivität

Persönliche Daten (Gesetzliche Regelungen)

Unternehmenskritische Daten( Unternehmensrichtlinien)

Anwendungsfall: Autohersteller - QualitätssicherungBeschreibung der Datenquellen / Schnittstellen

27

Anwendungsfall: Autohersteller - QualitätssicherungBeispiel Schnittstellenbeschreibung KFZ Steckbrief

Interface Name KFZ Steckbrief

Type sftpCount Tables/Files

3

Source System

IBM Host Refstore File Interface

Source Adress sftp://rfs/KFZSteckrief

Authentication sysqsusr, PKI

frequency Dayli

time 03.00 am

encrypted no

compressed .zipsecurity class 3

28


interface KFZ_Steckbrief

FileName SalesInfo_<dd.mm.yy>.zip ProductionInfo_<dd.mm.yy>.zip

DescriptionBeschreibung Verkaufs, Datensatz erzeugt bei Verkauf

Produktionsinformationen Datensatz erzeugt bei Auslieferung

BusinessEntity Link<Fahrzeug,Kunde> resp. Verkauf Fahrzeug

type fw fw

code ASCII ASCII

size_max 10GB 100GB

size_min 256kB 256kB

Header no no

RowDelimiter <CR><LF> <CR><LF>

ColumnDelimiter no no

29


interface KFZ_Steckbrief

fileName SalesInfo_<dd.mm.yy>.zip

description

Datensatz erzeugt bei Verkauf Händler und Kunden Info

FeldName Description DataType Len isnull isconf ismanuell

FIN FahrgestellNr char 17 0 0 0

HNR HändlerNr int 6 0 0 0

AuslID

AuslieferungsID ab

Werk bigint 8 0 0 0

OrderDt

Bestelldatum

Händler date 12 1 0 0

StArrDt Store arrival date date 12 1 0 0

SalesDt Verkaufsdatum date 12 1 0 0

CustNm Kunden Nm char 64 1 1 0

CustCity KundenOrt char 12 1 1 0

CustPCode Kunden PLZ int 5 1 1 0

CustAdr Kunden Adresse char 256 1 1 0

..

30


interface KFZ_SteckbriefFileName ProductionInfo_<dd.mm.yy>.zip

DescriptionProduktionsinformationen Datensatz erzeugt bei Auslieferung

FeldName Description DataType Len isnull isconf ismanuell

FIN FahrgestellNr char 17 0 0 0

AuslID AuslieferungsID ab Werk bigint 8 0 0 0

ProdDt Ab Freigabe Qualitätssicherung date 12 0 0 0

TypID Interne TypID bigint 8 0 0 0

VarID VariantenID bigint 8 0 0 0

QSRecID QualityRecordID char 64 0 1 0

OrderID Order ID bigint 8 1 0 0

ColorCd Color Code bigint 8 0 0 0

CMod1 Custom Module 1 char 24 1 1 0



…

31

Anwendungsfall: Autohersteller - QualitätssicherungBeispiel Schnittstellenbeschreibung Fahrzeugteile

Interface Name FahrzeugteileType database jdbcCount Tables/Files 7Source System BeschaffungsDWHSource Adress 175.124.8.3:212Authentication sysqsUserfrequency continouslytime on demandencrypted nocompressedsecurity class 4 | 5

32


interface CarParts

TableName Core.Assembly Core.Parts.Price Core.PartsByVariant

Description Teilinformation als Parent Child Table

Preisinformation für Teile und Baugruppen mit zeitlicher Eingrenzung

Zuordnung von Teilen zu Autovarianten

BusinessEntity Part Part Link(Part,TypVariante)

type database database database

DifferenzialLoad

size_max 200GB/Month 300GB/Month 5GB/Month

size_min 140MB/Month 270GB/Month 4GB/Month

33


FeldName Description DataType isnull isconf ismanuell

PartID TeileID char(32) 0 0 0

ParentID Teilegruppe,modul char(32) 1 0 0

PartTypID Typreferenz bigint 0 0 0

ProdDate Herstellungsdatum timestamp 0 0 0

ManufacturerCD Hersteller Code char(6) 0 0 0

ChargeCD chargenNummer varchar(24) 1 0 0

QualityConstraint

Qualitätsmangelbericht

Link varchar(128) 1 1 0

interface CarParts

TableName Core.Assembly

Description Teilinformation als Parent Child Table

34


interface CarParts

TableName Core.Parts.Price

Description Preisinformation für Teile und Baugruppen mit zeitlicher Eingrenzung



Price Preis decimal(12,2) 0 1 0

Curr Währungscode char(3) 0 1 0ValidFrom Gültigkeit von timestamp 0 1 0

ModulPriceFlag ob Price nur auf Module-level bool 1 1 0

35


interface

TableName Core.PartsByVariant

Description Preisinformation für Teile und Baugruppen mit zeitlicher Eingrenzung



VariantID Autovariante int 0 0 0ValidFrom Gültigkeit von timestamp 0 0 0

36

Anwendungsfall: Autohersteller - QualitätssicherungBeispiel Schnittstellenbeschreibung Werkstattberichte

Interface Name Werkstattbericht

Type sftp

Count Tables/Files 5

Source SystemIBM Host Refstore File Interface

Source Adress sftp://rfs/WerkstattnetzAuthentication sysqsUser, PKIfrequency weekly

time Friday 5pmencrypted yes

compressed .zip

security class 5

37


interface Werkstattberichte

fileName Auftraege_<ww.yy>.zip Teile_<ww.yy>.zip Garantie_<ww.yy.zip>

description

AngenommeneAufträge Eingebaute ErsatzteileGarantieleistung bei einer Reparatur

BusinessEntity Auftrag Link<Part,Reparatur> Position

type csv csv csv

code UTF8 UTF8 UTF8

size_max 500GB 2GB 500MB

size_min 250GB 256MB 100MB

Header yes yes yes

RowDelimiter <CR><LF> <CR><LF> <CR><LF>

ColumnDelimiter Semikolon Semikolon Semikolon

38


interface WerkstattberichtefileName Rechnungen_<ww.yy>.zip Positionen_<ww.yy>.zip

description Rechnungen pro Reparatur

Einzelpositionen auf Rechnungen

BusinessEntity Reparatur Positiontype csv csvcode UTF8 UTF8size_max 50GB 500GBsize_min 12GB 80GBHeader yes yesRowDelimiter <CR><LF> <CR><LF>ColumnDelimiter Semikolon Semikolon

39


FeldName Description DataTypeisnull isconf ismanuell

AuftragsNrAuftrags Nr lt Werkstattbuchungssystem bigint 0 0 1

Werkstattcode eindeutiger Werkstattcode nchar(6) 0 0 1

KundenNrKunden Nr lt Werkstattbuchungssystem int 0 1 1

FIN FGST Nr nchar(17) 0 0 1Kennzeichen KFZ nchar(16) 1 1 1

Auftragstext verbale Auftragsbeschreibung ntext 1 0 1

Scanlinklink zum jpg scan des Auftrags im zentralen DMS nvarchar(256) 0 0 0


fileName Auftraege_<ww.yy>.zip

40



fileName Teile_<ww.yy>.zip


AuftragsNr

Auftrags Nr ltWerkstattbuchungssystem bigint 0 0 1

Werkstattcodeeindeutiger Werkstattcode nchar(6) 0 0 1

BestellNr bigint 1 0 0

KundenNr

Kunden Nr ltWerkstattbuchungssystem nchar(12) 0 1 1

FIN FGST Nr nchar(17) 0 0 1

Kennzeichen KFZ nchar(16) 1 1 1

PartID Teile Nr nchar(32) 0 0 1

PartSalesPrice

link zum jpg scan des Auftrags im zentralen DMS decimal(8,2) 1 0 1

41

Anwendungsfall: Autohersteller - QualitätssicherungStaging Modell

Importiere Daten „wie erwartet“

• Datentypen lt. Schnittstellenbeschreibung (typ Validierung)

• Isnull Eigenschaft zur Identifikation von ISNullVerletzungen

• PK Definition zur Vermeidung von Duplikaten (abhängig vom DBMS)

• Metadaten (Load info)

• Informationen aus Filenamen

42

Anwendungsfall: Autohersteller - QualitätssicherungStaging Modell

CARPARTS_AssemblyPartID

ParentID

ParTypID

ProdDate

ManufacturerCD

ChargeCD

QualityConstraint

LoadID

CARPARTS_PartsByVariantPartID

VariantID

ValidFrom

LOADID

CARPARTS_PricePartID

Price

Curr

Modulprice

ValidFrom

KFZSB_prodInfoFIN

AuslId

Proddt

TypID

VarID

QRId

OrderID

ColorCode

CMod1

CMod2

CMod3

Day

LOADID

KFZSB_typeinfoFIN

HNR

AuslID

Orderdate

StArrDt

SalesDt

CustNm

CustCity

CustPCode

CustAdress

Day

LOADID

WERKSTBER_AuftraegeAuftragsNr

WerkstattCode

KundenNr

FIN

Kennzeichen

Auftragstext

Scanlink

Week

LoadID

WERKSTBER_TeileAuftragsNr

WerkstattCode

BestellNr

KundenNr

FIN

Kennzeichen

PartID

PartSalesPrice

Week

LoadID

43

Anwendungsfall: Autohersteller - QualitätssicherungDimensionales Modell

Read optimiertes Model

Fakten und Dimensionen

Nicht normalisiert

Identifikation Entities-> Fakten und DimensionPkeys, Surrogate KEysAnpassen der DatentypenHistorisierungFK Referenzen

44

Anwendungsfall: Autohersteller - QualitätssicherungDimensionales Modell

Dim_Kunde

CustID

CustNm

CustCity

CustPCode

CustAdress

validfrom

validto

dimCar

CarID

FIN

AuslId

Proddt

TypID

VarID

QRId

ColorCode

CMod1

CMod2

CMod3

validfrom

validto

dimPart

dimPartID

PartID

ParentID

ParTypID

ProdDate

ManufacturerCD

ChargeCD

QualityConstraint

validfrom

validto

dimPrice

dimPriceID

dimPartID

PartID

Price

Curr

Modulprice

ValidFrom

validto

FACT_Auftraege

AuftragsNr

WerkstattCode

KundeID

CarID

Auftragstext

Scanlink

AuftrDate

FACT_SALES

CarID

HNR

Orderdate

StArrDt

SalesDt

CustID

Fact_Teile

AuftragsNr

BestellNr

KundenID

PartID

PartSalesPrice

Executiondate

MapCarPart

mapID

dimPartID

carID

ValidFrom

validto

LOADID

dimTime

day date

month nchar(10)

week nchar(10)

year int

Column Name Data Type

45

Kontakt

Dr. Michael Simora

Data Scientist / BI Consultant

E-Mail: [email protected]

Jens Fache

Senior Solution Architekt


Adastra Deutschland

Niedenau 36

60325 Frankfurt a.M.

Tel.: 069 – 71 37 79 790


www.de.adastragrp.com

mailto:[email protected]



goethe-universität frankfurt big data lab database systems ... · goethe-universität frankfurt...

Documents