Data Warehouse Management Using SAP BW Alexander Prosser

Data Warehouse Management
Using SAP BW
Alexander Prosser
Overview
What problems does a data warehouse answer ?
SEITE 2
Overview
CEO: “We may have a problem in procurement.
I have a feeling that we employ too many suppliers.
We are not focused enough, and probably the issue is
even growing. Give me a flexible analysis tool to check
that out.
We should have all data in our SAP ECC system.”
You (IT manager): “…..?”
SEITE 3
Overview
What can I expect of a Data Warehouse ?
What not ?
What can I expect of my consultants/IT professionals ?
How can I ensure that I get what I/my organisation needs ?
SEITE 4
Overview
1980-ies: Functional software
MM
SEITE 5
Sales
PPC
Acc.
Overview
1990-ies: Process orientation:
Business Process
Value for
the
customer
MM
SEITE 6
Sales
PPC
Acc.
Overview
Vertical integration
EIS
Decision
Support
Reporting
Reporting,
Analysis, Controlling
Functional applications
Cross-functional base applications
Operational systems
Office automation
Horizontal integration
SEITE 7
Overview
Organisation #2
Organisation #1
Decision
Support
Decision
Support
Reporting,
Analysis, Controlling
Reporting,
Analysis, Controlling
Functional applications
Cross-functional base applications
Office automation
Functional applications
Cross-functional base applications
Office automation
Cross-company integration (e.g., supply chain management)
SEITE 8
Overview
External Sources
Data Mining
KBS
Operational IS
OLAP
DW
SEITE 9
Overview
Operational system
Procurement
Data warehouse
Vendor assessment
Sales and order processing
Analysis of customer behaviour
Production planning and shop floor Analysis of rework/reject and
control
overdue production orders
SEITE 10
Overview
A data warehouse is NOT a list generator.
A data warehouse is NOT an address database for
mail merge operations.
It is an analytical tool for analysis and decision making.
SEITE 11
Overview
U sa ge
O p eratio n al s yste m
T ran sactio n-inten sive
(re ad an d w rite )
U se rs
R elative ly large n um ber
C o ve ra ge
(In m o st cases) current d ata
on ly
SEITE 12
Overview
Usage
Operational system
Transaction-intensive
(read and write)
Data w arehouse
Q uery-intensive (read only)
Users
Relatively large num ber
Relatively sm all num ber,
unless used as a general
reporting tool
Coverage
(In m ost cases) current data
only
Current & historical data;
tim e-dependent
SEITE 13
Operational system
Data is organised according
to a process
Model
Overview
(typical)
Data
structure
SEITE 14
Flat
Model
(typical)
Operational system
Data is organised according
to a process
Data warehouse
Data is organised according to
a subject matter
Flat
Multi-dimensional according to
the subject matter
Overview
Data
structure
items
customer
tomatoes
milk
bananas
oranges
Mc Donalds
K-Mart
Woolworths
01/ 02/ 03/ 04/
01/ 01/ 01/ ....
01/
09 09 09 ..
09
SEITE 15
period
Modeling a Data Warehouse
… and data is multi-dimensional.
I
t
e
m
Total
Item
type
Total
Customer group
Customer
Customer
Item
Day
Month Year
Period
SEITE 16
Modeling
You have to unequivocally specify what you want
… before you sign the contract.
Otherwise, you will not get what you want.
SEITE 17
Modeling
You have to unequivocally specify what you want
… before you sign the contract.
Otherwise, you will not get what you want.
=> Dimensional Fact Modeling as a „language“ to
specify your needs and to assure the quality of
the system delivered.
=> Conceptual system modeling is not an academic
luxury item, but a means to save €€€
SEITE 18
Modeling
Let‘s design a data warehouse:
Please suggest a case from your experience.
SEITE 19
Modeling
STEP 1:
What is the fact I want to analyze ?
What are the key figures representing the fact ?
What do the key figures look like ?
SEITE 20
Modeling
Nominal: numerical coding without meaningful values
Ordinal: coding represents >< relationships,
no meaningful sum
Interval: metric, but have a “beginning” and/or “end”,
hence, no meaningful sum
Rational: metric, any operation
SEITE 21
Modeling
STEP 2:
What are the axes in my analyses ?
What are their aggregation levels (if any) ?
SEITE 22
Modeling
STEP 3:
Are the axes of aggregation independent of one
another ?
Are there any restrictions in aggregation ?
SEITE 23
Modeling
Operator
Nominal
Ordinal
Interval
Sum
No
No
No
Average
No
(
)
Minimum
No
Maximum
No
SEITE 24
Rational
Modeling
*
Additivity
Plant
Σ
Storage_location
*
Material
Stock_
level
Y
M
W
Material_group
Σ
SEITE 25
=> AVG Σ
Modeling
max
Σ
min
x
AVG
Some aggregation operator
All aggregation operators
SEITE 26
Some dimensions All dimensions
Semi-additive
Semi-additive
Semi -additive
Additive
Modeling
STEP 4:
Are there any non-aggregation attributes ?
Do I have parallel hierarchies ?
SEITE 27
Modeling
STEP 5:
Where does the data come from ?
Do I need to reconcile data from different sources ?
SEITE 28
Modeling
Key Integration
Operational IS
Key_1
Key_2
SEITE 29
Example:
• Accounts receivable
• Customer
• Transport destination
DW
one object in
DW
Modeling
Field Integration
Operational IS
DW
Filter:
• Currencies
• Measurements
• Scope of figures (eg, gross/net)
•…?
• All fields available ?
SEITE 30
Modeling
Content Integration
Operational IS
DW
Example MM/Procurement:
• Material classes the same ?
• Account assignment the same ?
• Data maintenance discipline/rules the same ?
•…?
SEITE 31
Data Sources and Info Sources
BW Server
Modeling
Master
InfoCubes
Master
Data
Data
Master Data
InfoSource
Update Rules
Transaction
InfoSource
Communication
Communication
structure
structure
Customer data
Product data
Delivery plant
data
Master Data
 SAP AG)
Communication
Communication
structure
structure
Transfer RulesTransfer Rules
Transfer
Transfer Structure
Structure
Transfer Rules
Data Sources
(replicas )
Transfer
Transfer Structure
Structure
Transfer
Transfer Structure
Structure
DataSources
Sales data
Transaction
Data
Flat File Source System
SEITE 32
Master Data
InfoSource
Communication
Communication
structure
structure
Transfer RulesData Sources
(user-defined)
Transfer
Transfer Structure
Structure
Update Rules
Transfer
Transfer Structure
Structure
Transfer
Transfer Structure
Structure
Extract
Extract Structure
Structure
Extract
Extract Structure
Structure
Transaction
Data
Master
Data
OLTP Source System
Case Study
Case Study Umbrella Sales:
product group
product
day
year
month
nr. transactions
price
qty.
revenue
customer
delivery plant
SEITE 33
region
state
Case Study
Case Study Sailor‘s Wear:
product group
product
quarter
year
month
day
costs
nr. transactions
price
qty.
revenue
region
area
SEITE 34
customer group
customer
cost element
cost character
Case Study
Case Study Sailor‘s Wear:
Transfer
data
Transfer
rules
Info
source
Source system
Update
rules
Update
rules
Transfer
data
SEITE 35
Transfer
rules
Info
source
Info cube
Case Study
Case Study Sailor‘s Wear:
product group
product
quarter
year
month
day
costs
nr. transactions
price
qty.
revenue
region
area
SEITE 36
customer group
customer
cost element
cost character
Kontaktdaten ergänzen
Institut für Produktionsmanagement
Institute of Production Management
Augasse 2-6, 1090 Vienna, Austria
Alexander Prosser
[email protected]
http://prodman.wu.ac.at
http://erp.wu.ac.at
http://e-voting.at
SEITE 37