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
2. Advantages of a Layered Architecture
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
Staging Area
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
Data Acquisition Layer
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
Transformation
Special Sales
Data
Transformation
Special Sales
Data
7
Sales Order Header Data
Sales Order Item Data
Sale Invoice Header Data
Sale Invoice Item Data
ERP System
Transfor- mation
Data Acquisition + Corporate Memory Sales
Order Header Data
Sales Order
Item Data
Sale Invoice Header Data
Sale Invoice
Item Data
Transfor- mation
Transfor- mation
Transfor- mation
Quality & 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
System-ID added
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
