Advantages of a Layered Architecture for
Enterprise Data Warehouse Systems
1
Enterprise Data Warehouse Systems
Thorsten Winsemann, Veit Köppen, Gunter Saake
Otto-von-Guericke-Universität, Magdeburg/Germany
Table of contents
1. Characteristics of Enterprise Data Warehouses
2. Traditional Data Warehouse Architecture
1. Reference Architecture
2. Dataflow-Example
3. Architectures for Enterprise Data Warehouses
1. SAP’s Layered, Scalable Architecture
2
1. SAP’s Layered, Scalable Architecture
2. Layers in Detail
3. Dataflow-Example
4. Simple, but Detailed Example
4. Architectural Differences
1. Overview
Characteristics of Enterprise Data Warehouses
(EDW)
• Business DW, thus covering all business areas
• Data basis for several applications, such as BI,
planning, CRM, …
• Single Version of Truth of company’s data
3
• Single Version of Truth of company’s data
• Multiple, heterogeneous source systems
• Huge amount of data (granular, detailed, old)
• World-wide scope, different time zones
• 24*7-hours availability
• …
Traditional Data Warehouse Architecture:
Reference Architecture
User
D
a
ta
W
a
re
h
o
u
s
e
S
ys
te
m
Data Marts
O
p
e
ra
tio
n
a
l D
a
ta
S
to
re
4
Data Sources
D
a
ta
W
a
re
h
o
u
s
e
S
ys
te
m
O
p
e
ra
tio
n
a
l D
a
ta
S
to
re
Basis Data Base
Traditional Data Warehouse Architecture:
Dataflow-Example
Sales
Data
Sales
Data
Year 1
Sales
Data
Year 2
Sales
Data
Year 3
Basis Data Base
Data Marts
5
Staging Area
Sales Order
Header Data
Sales Order
Item Data
Sale Invoice
Header Data
Sale Invoice
Item Data
ERP System
DW System
Transformation
Architecture for Enterprise Data Warehouses:
SAP’s Layered, Scalable Architecture
User
D
a
ta
W
a
re
h
o
u
s
e
S
ys
te
m
Reporting & Analysis Layer
Business Transformation Layer
O
p
e
ra
tio
n
a
l D
a
ta
S
to
re
6
Data Sources
D
a
ta
W
a
re
h
o
u
s
e
S
ys
te
m
Business Transformation Layer
O
p
e
ra
tio
n
a
l D
a
ta
S
to
re
Data Propagation Layer
Quality & Harmonisation Layer
Corporate
Memory
Architecture for Enterprise Data Warehouses:
Dataflow-Example
Sales
Data
Year 1
Sales
Data
Year 2
Sales
Data
Year 3
Reporting & Analysis
Sales
Order
Data
Sale
Invoice
Data
DW System
Sales
Sales
Sale
Sale
Transformation Transformation
Data Propagation
Business Transformation
Transformation Transformation TransformationSpecial
Sales
Data
TransformationSpecial
Sales
Data
7
Sales Order
Header Data
Sales Order
Item Data
Sale Invoice
Header Data
Sale Invoice
Item Data
ERP System
Transfor-mationData Acquisition + Corporate Memory
Sales
Order
Header
Data
Sales
Order
Item
Data
Sale
Invoice
Header
Data
Sale
Invoice
Item
Data
Transfor-mation Transfor-mation Transfor-mationQuality & Harmonization
Sales
Order
Header
Data
Sales
Order
Item
Data
Sale
Invoice
Header
Data
Sale
Invoice
Item
Data
Architecture for Enterprise Data Warehouses:
Layers in Detail (1)
• Data Acquisition Layer
– „DW Inbox“ (temporary)
– Data stored immediately without changes
• Corporate Memory
8
• Corporate Memory
– „DW Life Insurance“ (long-term, granular, complete)
– Data for non-predictable demands („master the unkown“)
• Quality & Harmonization Layer
– Technical and semantical data integration
– Usually no data storage
Architecture for Enterprise Data Warehouses:
Layers in Detail (2)
• Data Propagation Layer
– „Single Version of Truth“
– Harmonized, integrated data without business logic
• Business Transformation Layer
9
• Business Transformation Layer
– Data are transformed according to business‘ needs
– E.g., combination of sales + finance figures
• Reporting & Analysis Layer
– Data are transformed according to requirements for
usage and fast access performance
Architectures for Enterprise Data Warehouses:
Simple, but Detailed Example (1)
Data Aquisition Layer + Corporate Memory
ORDNR ITMNO MATNR QUASU UNITS AMDCO CURRD Char10 Char4 Char15 Dec10,3 Char3 Dec15,2 Char3 0000012345 0001 ABT00471 2,000 BOX 300,00 EUR Sales Order
Sales Order - Item
Harmonization & Quality Layer
(no persistence!)
INVNR DATEI CUSTOM ORDNR DATEP Char10 Char8 Char10 Char10 Char8 IN02085 20100805 0007410000 0000012345 20100820 INVNR ITMNI MATNR QUABU UNITB AMDCI CURRD Char10 Char4 Char15 Dec10,3 Char3 Dec10,2 Char3 IN02085 0001 ABT00471 4,000 PC 285,00 EUR Sale Invoice
Sale Invoice - Item ORDNR DATEO CUSTOM
Char10 Char8 Char10 0000012345 20100730 0007410000
Data types adapted
Homonyms split
Synonyms merged
10
DOCNR ODATE BUYER Numc10 Char10 Char10 12345 30.07.2010 0007410000
DOCNR ITMNR ARTNR QUASU SUNIT AMDCI DCURR Numc10 Numc4 Char15 Dec10,2 Char3 Dec15,2 Char2
DOCNR INVDT PAYER ORDER PDATE Char7 Char6 Char7 Numc10 Char6 IN02085 100805 7410000 12345 100820
DOCNR ITMNR MATNR QUANT BUNIT AMDCI DCURR Char7 Numc3 Char8 Dec10,3 Char2 Dec10,2 Char3 Sales Order
Sales Order - Item
Sale Invoice
Sale Invoice - Item
System A:
Ordering
System B:
Invoicing
DOCNR ITMNR ARTNR QUASU SUNIT AMODC DCURR SYSID Numc10 Numc4 Char15 Dec10,2 Char3 Dec15,2 Char2 Char3 12345 1 ABT00471 2,00 BOX 300,00 EU SAO Sales Order
Sales Order - Item
DOCNR INVDT PAYER ORDER PDATE SYSID Char7 Char6 Char7 Numc10 Char6 Char3 IN02085 100805 7410000 12345 100820 SBI
DOCNR ITMNR ARTNR QUANT BUNIT AMODC DCURR SYSID Char7 Numc3 Char8 Dec10,3 Char2 Dec10,2 Char3 Char3 IN02085 1 ABT00471 4,000 ST 285,00 EUR SBI Sale Invoice
Sale Invoice - Item DOCNR ODATE BUYER SYSID
Numc10 Char10 Char10 Char3 12345 30.07.2010 0007410000 SAO
Synonyms merged
Field names changed
Architectures for Enterprise Data Warehouses:
Simple, but Detailed Example (2)
Sales Orders
Business Transformation Layer
CUSTOM MATNR MATGR DATEO QUABU UNITB DATEP AMDCI CURRD Char10 Char15 Char3 Char8 Dec10,3 Char3 Char8 Dec10,2 Char3 0007410000 ABT00471 ABT 20100730 4,000 PC 20100820 285,00 EUR Sales
Reporting & Analysis Layer
CUSTOM MONTH AMLCI CURRL PRPPC Char10 Char6 Dec10,2 Char3 Dec10,2 0007410000 201008 200,00 GBP 50,00
Report Execution
(
no persistence
!)
Further information added
Data combined
(according to usage)
11
ORDNR ITMNO MATNR QUASU UNITS QUABU UNITB AMODC CURRD Char10 Char4 Char15 Dec10,3 Char3 Dec10,3 Char3 Dec15,2 Char3 0000012345 0001 ABT00471 2,000 BOX 4,000 PC 300,00 EUR Sales Order
Sales Order - Item
INVNR DATEI CUSTOM ORDNR DATEP Char10 Char8 Char10 Char10 Char8 IN02085 20100805 0007410000 0000012345 20100820 INVNR ITMNO MATNR QUABU UNITB AMODC CURRD Char10 Char4 Char15 Dec10,3 Char3 Dec10,2 Char3 IN02085 0001 ABT00471 4,000 PC 285,00 EUR Sale Invoice
Sale Invoice - Item ORDNR DATEO CUSTOM
Char10 Char8 Char10 0000012345 20100730 0007410000
Harmonization & Quality Layer
Data Propagation Layer
ORDNR ITMNO DATEO CUSTOM MATNR QUASU UNITS QUABU UNITB AMDCO CURRD Char10 Char4 Char8 Char10 Char15 Dec10,3 Char3 Dec10,3 Char3 Dec15,2 Char3 0000012345 0001 20100730 0007410000 ABT00471 2,000 BOX 4,000 PC 300,00 EUR
INVNR ITMNI MATNR DATEI CUSTOM ORDNR DATEP QUABU UNITB AMDCI CURRD Char10 Char4 Char15 Char8 Char10 Char10 Char8 Dec10,3 Char3 Dec10,2 Char3 IN02085 0001 ABT00471 20100805 0007410000 0000012345 20100820 4,000 PC 285,00 EUR Sale Invoices
Additional information added
Data configured
Architectural Differences:
Overview
Matter
Reference Architecture
Layered Architecture
Complexity
Medium
High (several layers)
Data volume
High
Very high
Conceptual work
Medium (requirement-driven)
High (overall concept view)
Implementation effort
Medium
High
12
Architectural Differences:
Advantages of a Layered Architecture
Matter
Reference Architecture
Layered Architecture
Change of transformation
rules (e.g., changed
key-figure calculation)
Reload/-build from source
system
Rebuild from propagation
layer
Change of data (e.g., new
key-figure calculation)
Reload/-build from source
system
Rebuild from propagation
layer
13
Need for new data
Dataflow enhancement and
reload/-build
Load from propagation layer
or corporate memory
„Single Version of Truth“
No
Yes
Decoupling of data load and
availability
No/limited
Yes/supported
Appendix
2
3
4
5
CSDM 2011
Poster Layout
Slides‘ Arrangement
on Panel
Panel: 150x125cm
6
7
8
9
1
Panel: 150x125cm
Slides: A3 + A4
12
13
10
11
