Overview of SAP BW
A business of
1. Agenda
2
Contents • SAP BW Overview and Concepts • Introducing the Administrator Workbench • Data Modeling and Loading • Data Extraction (OLTP and Remote Systems) • The ODS and Business Content • Production Support • BEX Reporting
3
Data Warehousing and the SAP BW Overview and Concepts
4
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 support for data sources from R/3 Systems • BAPIs for data sources from non-SAP systems • Automated Data Warehouse management • Administrator Workbench for controlling and managing content 5
Business Information Warehouse Architecture
6
Business Content
Financial Accounting 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 Administration
Inventory Management Training & Events Production Project Management
Time Management Payroll Accounting 7
Close the Loop
Action
Common Transaction Processing
-----------------------------
OLTP
Analytical Applications
Analysis
Meta Data Extraction
Transformation
External
8
DSS
Architecture Overview
3rd party party OLAP OLAP client clients 3rd 3rd party OLAP client MDX
OLE-DB for OLAP Provider
Administrator Workbench Administration Scheduling Monitor
Business Explorer Analyzer (hosted by MS Excel)
OLAP Processor Meta Data Repository
Meta Data Manager
Business Information Warehouse Server
Browser
InfoCatalog
Data Manager
Staging Engine
InfoCubes 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
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
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
Extraction, Transformation and Loading Open for any source Flexible set of ETL capabilities JDBC
XLMA ODBO
Integration on application level Open to third-party tools Support of open standards
…to get a complete view of your business 12
Non-SAP Data Sources Business Information Warehouse Server Administrator Workbench
Meta Data Repository
Data Base
Staging Engine BAPI
Complementary Extraction & Transformation Tool R/3
• Staging BAPIs allow ...
File RDBMS
Mainframe
Non SAP
−
... certified SAP Partners to provide ready-to-run extraction & transformation tools
−
... customers to integrate their non-SAP data 13
Persistent Staging Area
Business Information Warehouse Server InfoCube
Update Rules
Validation
PSA InfoSource
InfoSource
BAPI
Data extract OLTP System
Data extract 14
Administrator Workbench
Central Administration and Control Modeling Reporting Agent Business Content Monitoring Metadata Repository
15
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
Vienna.conf.032499
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 user Values for variables can be used as input for formulae
17
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
•
Mixed currencies within columns or rows − multi currency aggregates can be resolved by a simple dill-down by units
Business Explorer
EUR
convert
OLAP Processor EUR USD
JPY
Staging Engine
NLG DM
R/3
JPY EUR LIT R FF 18
File
convert
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-user
Created by administrator 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
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
21
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
Info Cube Multi-Dimensional Analysis
Query Cache
Others Govt Agencies Institutions Retail Whole sale
South
Dept Stores
West
Bi-products
Chemicals
Furnishings
Plastics
Ceramics
East
Glassware
Customer Group
Division 23
Regions
South
Customer group
Dept. Stores North
Region
East
InfoCube: Example
Wholesale Retail
Glass- Ceramics Plastics Pottery Copper Pewter ware
Division 24
InfoCube: Multi-dimensional analysis Product group Customer group Division Area Company code Region Period Profit Center Bus. Area
2 Analysis
Division
Glass- Ceramics Plastics ware
DeptStores Wholesale Retail
East
East
of Plastics division and Southern region
Customer group
South North
DeptStores Wholesale Retail
Region
South East Glass- Ceramics Plastics ware
3 Analysis
of Plastics division
Customer group
North
Region
Analysis of Ceramics 1 division
Customer group
South
Division
North
DeptStores Wholesale Retail
Region
East Glass- Ceramics Plastics ware
InfoCube
Characteristics:
Customer group
South North
Region
Query Cache
Division
Glass- Ceramics Plastics ware
Division 25
DeptStores Wholesale Retail
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
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
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
Reporting Architecture Analyzer defines ... Business Explorer
OLAP server OLAP Processor operates on ...
Database
Query
Database stores ... Aggregates
InfoCub e
Star Schema
stored in
29
Reporting Architecture
Business Explorer Analyzer 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
Analyzer: Defining Queries
31
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 mouseclick, context-menus, toolbar etc.
Workbooks as container for queries (store, email)
Vienna.conf.032499
All rendition and presentati on features of Excel available
End-users build on existing Excel and MS Office know how
Thank You
A business of