Spc
Overview of SAP BW
A business of
Spc
1. Agenda
2
PC
Contents SAP BW Overview and Concepts
Introducing the Workbench Data Modeling and Loading
Data Extraction (OLTP and Remote Systems) The ODS and Business Content Production
BEX Reporting
3
PC
Spc
Data Warehousing and the SAP BW Overview and Concepts
4
PC
SAP Business Information Warehouse „
Data Warehouse system with optimized structures for reporting and analysis
„
OLAP engine and tools for BEX Reporting
„
Integrated meta data repository
„
Data extraction and data staging in OLTP „ Preconfigured for data sources from R/3 Systems
„ BAPIs for data sources from non-SAP systems „
Automated Data Warehouse management
„
Workbench for controlling and managing content
5
PC
Business Information Warehouse Architecture
6
PC
Business Content
Financial ing General Ledger Accnts Receivable
Logistics Sales
Controlling
Accnts Payable
Profitability Analysis
Special Ledger
Product Costing
Fixed Assets
Overhead Costing Profit Center Accnt
Purchasing
Human Resources istration
Inventory Management Training & Events Production Time Management
Project Management Payroll ing 7
PC
Close the Loop
Action
Common Transaction Processing
-----------------------------
OLTP
Analytical Applications
Analysis
Meta Data
Extraction
Transformation
External
8
DSS PC
Architecture Overview
3rd party party OLAP OLAP client clients 3rd 3rd party OLAP client Analyzer (hosted by MS Excel)
MDX
Business Explorer
Browser
OLE-DB for OLAP Provider
Workbench istration
OLAP Processor Meta Data Repository
Meta Data Manager
InfoCatalog
Data Manager
InfoCubes
Scheduling Monitor
Business Information Warehouse Server
Staging Engine
Operational Data Store
BAPI
Non R/3 Production Data Extractor Non R/3 OLTP Applications
Production Data Extractor
OLTP Reporting
R/3 OLTP Applications 9
PC
Staging Process
InfoCube
InfoCube
Update Rules Update Rules Communication Structure
Business Information Warehouse Server Source Systems
Update Rules Communication Structure
Info Sources Mapping & Transformation Rules
Mapping & Transformation Rules Transfer Structure
Transfer Structure
Transfer Structure
R/3 standard extractor
3rd party extraction tool
Data extract Sales Europe
Data extract Sales Americas
Market Information
Research Institute R/3 OLTP System
Non R/3 OLTP System
10
PC
DataSource and InfoSource Business Information Warehouse Server Staging Engine
InfoCubes Update Rules
Communication structure
Transfer Rules
(Replicated)
Transfer Rules
Transfer Structure
InfoSource Transfer Rules
Transfer Structure
Transfer Structure
DataSource Transfer Structure
Extract Source Structure
Transfer Structure
Transfer Structure
Extract Source Structure
Extract Source Structure
OLTP System 1
OLTP System 2
11
PC
Extraction, Transformation and Loading Open for any source Flexible set of ETL capabilities Integration on application level
Open to third-party tools of open standards
…to get a complete view of your business 12
PC
Non-SAP Data Sources Business Information Warehouse Server Workbench
Meta Data Repository
Data Base
Staging Engine BAPI
Complementary Extraction & Transformation Tool R/3
Staging BAPIs allow ...
File Mainframe
RDBMS Non SAP
... certified SAP Partners to provide ready-to-run extraction & transformation tools ... customers to integrate their non-SAP data 13
PC
Persistent Staging Area
Business Information Warehouse Server
InfoCube
Update Rules
Validation
PSA InfoSource
InfoSource
BAPI
Data extract OLTP System
Data extract
14
PC
Workbench
Central istration and Control
Modeling Reporting Agent Business Content Monitoring Metadata Repository
15
PC
OLAP Processor Arbitrary drill-downs, horizontally, vertically, hierarchically Built-in functions for ... ... Aggregation: sum, count, count distinct, min / max, first / last, average by period, ... ... Comparison: difference, ratio, percent,... ... Analysis: sort, cumulated sum, time series,... ... Stock value handling ... Financial: currencies, fiscal year variants,... Derived key figures
PC
More OLAP Goodies ... Hierarchies for interactive drill-down Tree-like structures on a characteristic’s domain Structure defined in external hierarchy table (similar to master data) no realignment problem! Flexible versioning on hierarchies Variables Determine set of data for a query at run-time which complex filters, which hierarchies?
Values for variables are calculated by the system or entered by the Values for variables can be used as input for formulae
17
PC
Currency Conversion
Convert during data load and/or during analysis Based on R/3 conversion rates Conversion per fiscal year / fiscal period calendar date / period conversion rate type
Business Explorer
Mixed currencies within columns or rows
multi currency aggregates can be resolved by a simple dilldown by units
convert
OLAP Processor EUR
Staging Engine
R/3
EUR
convert
File
18
PC
Aggregates Speed up query performance by providing pre-aggregated views on InfoCubes
Aggregates are also stored in InfoCube star schema
Fully invisible to the end-
Created by depending on InfoCube semantics and query anticipation
Optimized by OLAP processor selecting best aggregate
Built-in consistency
data package released for queries when aggregate update complete
Zero downtime during load
19
PC
BW Data Model
Customer # Name
Location
13970522
Palo Alto
Brightview, Inc.
Customer dimension
InfoCube
C Customer # Region …
13970522 P C T
Quantity
Revenue
2101004 Product dimension
...
Discount Sales overhead
250 500,000 $
50,000 $
280,000 $
50 100,000 $
7,500 $
60,000 $
…
...
… P Product #
west
…
Product group … displays ...
Master data
T Period 10
Fact table
Fiscal year
…
1997
...
Time dimension
20
PC
21
PC
InfoCube: SAP BW Design Central data stores for reports and evaluations Contains two types of data: Key Figures
Characteristics
1 Fact Table and up to 16 Dimension Tables 3 Dimensions are predefined by SAP
Time
Unit
Info Package
22
PC
Info Cube Multi-Dimensional Analysis
Query Cache
Others Govt Agencies Institutions Retail Whole sale
South
Dept Stores
West
Regions
Bi-products
Chemicals
Furnishings
Plastics
Ceramics
East
Glassware
Customer Group
Division 23
PC
South
Customer group
Dept. Stores North
Region
East
InfoCube: Example
Wholesale Retail
Glass- Ceramics Plastics Pottery Copper Pewter ware
Division 24
PC
InfoCube: Multi-dimensional analysis Product group Customer group Division Area Company code Region Period Profit Center Bus. Area
2 Analysis
South
DeptStores Wholesale Retail
Region
East
of Plastics division and Southern region
Customer group
North
DeptStores Wholesale Retail
Region
South East
3 Analysis
of Plastics division
Customer group
North
Region
Analysis of Ceramics 1 division
East
Division
Customer group
South
DeptStores Wholesale Retail
DeptStores Wholesale Retail
North
East South Glass- Ceramics Plastics ware
InfoCube
Characteristics:
Customer group
North
Region
Query Cache
Glass- Ceramics Plastics ware
Glass- Ceramics Plastics ware
Glass- Ceramics Plastics ware
Division
Division
Division 25
PC
InfoCube: Characteristics and Key Figures
Customer group
Region
1 100 RT-0001
North Plastics Retail Trade
Sales 4,000,000 Hours worked 1,300,000
Characteristics
Key Figures
Division
Key Figures are stored for a unique combination of Characteristic Values
Number of dimensions is degree of granularity / summarization level of the dataset
26
PC
What is an InfoObject ? The various OLTP data models are unified for BW
Business objects / data elements become InfoObjects R/3 OLTP
CO Controlling
HR Human Resources
BW Extractor
Table of cost centers KOSTL
...
DataSource for Cost Center
InfoObject “0COSTCENTER”
Table of employees EMPLO COST_CENTER ...
InfoObjects are unique across application components ! 27
PC
Types of InfoObjects Characteristics: evaluation groups like “Cost Center”, “Product group”, “Material” Have discrete values stored in their master data tables (e.g. the characteristic “Region” has the values “North”, “South”, ... ) Special types of characteristics:
Time characteristics like “Fiscal period”, “Calendar year”, ... Unit characteristics which comprise currencies and units of measure like “Local currency” or “Sales quantity”
Keyfigures: continuously valued numerical fields like amounts and quantities (e.g.: “Revenue” and “Sales quantity”)
28
PC
Reporting Architecture Analyzer defines ... Business Explorer
OLAP server OLAP Processor operates on ...
Database
Query
Database stores ... Aggregates
Star Schema
stored in
InfoCub e 29
PC
Reporting Architecture Business Explorer Analyzer shows ... defines ...
stored in
Query View
Excel Workbook
OLAP server
OLAP Processor operates on ...
Query
Database
Aggregates stored in
Database stores ...
Star Schema
InfoCube 30
PC
Analyzer: Defining Queries
31
PC
Analyzer embedded in Excel Business Explorer Analyzer ...
... implemented as an Add-in for Microsoft Excel
... links query rsults to cells in Excel workbooks (e.g. multiple queries within same worksheet)
... offers all navigation features of OLAP-Processor via mouse-click, context-menus, toolbar etc.
Workbooks as container for queries (store, e-mail)
All rendition and presentation features of Excel available
End-s build on existing Excel and MS Office know how
PC
Spc
Thank You
A business of