990 performance tuning in sap bw and data load performance
TRANSCRIPT
Performance in SAP BW
Product Management SAP NetWeaver / BISAP NetWeaver Regional Implementation Group - BISAP AG
© SAP AG 2004, Performance in SAP BW
Contents
OverviewOverview
Query PerformanceQuery Performance
AggregatesAggregates
OLAP CacheOLAP Cache
PrePre--CalculationCalculation
Data Load PerformanceData Load Performance
Content
© SAP AG 2004, Performance in SAP BW
Performance Tuning in BW
Database
Database
Performance Tuning
Application
Application
Performance Tuning
OLTP SystemsApplication Development and performance tuning separatedPerformance tuning by basis experts
Performance Tuning
BWPerformance Tuning as holistic process over application design and database configuration
BW
OLTP
© SAP AG 2004, Performance in SAP BW
Data Modeling Checklist
BW LayersOperational StoreData Warehouse LayerMultidimensional Model
Dimensions of InfoCubesLine Item Dimensions
Logical (MultiProvider) Partitioning
Time-Dependent Master Data
Non-Cumulative Key Figures
© SAP AG 2004, Performance in SAP BW
Contents
OverviewOverview
Query PerformanceQuery Performance
AggregatesAggregates
OLAP CacheOLAP Cache
PrePre--CalculationCalculation
Data Load PerformanceData Load Performance
Content
© SAP AG 2004, Performance in SAP BW
Checklist – Query and Web Performance – Overview
ST03 / Technical ContentST03 / Technical Content
DatabaseDatabase OLAPOLAP FrontendFrontend
1. Data Model
2. Query Definition
3. Aggregates
4. OLAP Cache
5. Pre-Calculated Web Templates
6. Compressing
7. Indices
8. DB Statistics
9. DB and basis (Buffer) Parameter
1. Data Model
2. Query Definition
3. Aggregates
4. OLAP Cache
5. Pre-Calculated Web Templates
6. Compressing
7. Indices
8. DB Statistics
9. DB and basis (Buffer) Parameter
1. Data Model
2. Query Definition (including OLAP features)
3. Aggregates
4. OLAP Cache
5. Virtual Key Figures / Characteristics
6. Authorizations
1. Data Model
2. Query Definition (including OLAP features)
3. Aggregates
4. OLAP Cache
5. Virtual Key Figures / Characteristics
6. Authorizations
1. Network
2. WAN and BEx
3. Client Hardware
4. VBA / Java
5. Documents
6. Formatting
7. ODBO / 3rd party
1. Network
2. WAN and BEx
3. Client Hardware
4. VBA / Java
5. Documents
6. Formatting
7. ODBO / 3rd party
SQL Trace (ST05)
RSRV
RSRT, RSRTRACE
SQL Trace (ST05)
RSRV
RSRT, RSRTRACE
RSRT, RSRTRACE
SQL Trace (ST05)
ABAP Trace (SE30)
RSRT, RSRTRACE
SQL Trace (ST05)
ABAP Trace (SE30)IEMON
RSRT, RSRTRACE
IEMON
RSRT, RSRTRACE
Which component contributes most?
Check these points
Tools
© SAP AG 2004, Performance in SAP BW
OLAP Performance Layers
ReuseReuse
PerformancePerformance
Portal iView Cache
Pre-Calculation /Offline Analysis
OLAP Cache
Aggregates
InfoCubes
© SAP AG 2004, Performance in SAP BW
Aggregates
DefinitionMaterialization of aggregated subsets of InfoCube fact table dataIndependent structures where summary data is stored within separate, transparent InfoCubes
BenefitsTransparency: Users do not notice if aggregate is hit or notImproved query performance by reducing the amount of data to be read from DB
Aggregates can be created … Only on top of basic InfoCubesFor dimension characteristicsFor navigational attributesOn hierarchy levelsUsing time-dependent navigational attributes (as of BW 3.x)Using hierarchy levels where the structure is time-dependent (as of BW 3.x)
InfoCube
Aggregates
Note: Aggregates can improve query performance considerably, but keep in mind that they also impact the load performance.
© SAP AG 2004, Performance in SAP BW
OLAP Cache
Global OLAP Cache in SAP BW 3.xSession- and user-independentStores Query Results
In application bufferIn cluster table or flat file (highly compressed)
BenefitComplete query results with virtually no DB table access as of the second call of the query
Re-useFor equal queries or subsets of cached query results
InvalidationOLAP Cache and actual InfoProvider data always in-syncInvalidation of cache entries e.g. after transaction data load, master data load/change run, meta data change
StrategyWarm-up OLAP Cache via reporting agent/Information Broadcasting
© SAP AG 2004, Performance in SAP BW
Pre-Calculated Web Templates
Pre-Calculated Web Templates and Information Broadcasting:
Pre-calculation is a set of techniques where you can distribute the workload of running the report to off-peak hours, and have the report result set ready for very fast access to the data
HTML pre-calculation can be used for Offline Analysis
Information Broadcasting in SAP BW 3.5Targeting the Information Consumer
Quick access to many reportsStatic reporting, usually little navigationrequirementsActive Reporting Authorisations
NoteNo invalidation when new data is loaded; onlytimestamp is availableRestricted navigation: only filtering possibles
© SAP AG 2004, Performance in SAP BW
Contents
OverviewOverview
Query PerformanceQuery Performance
AggregatesAggregates
OLAP CacheOLAP Cache
PrePre--CalculationCalculation
Data Load PerformanceData Load Performance
Content
© SAP AG 2004, Performance in SAP BW
Checklist – Data Load Performance – Overview 1 –
Technical Content, Data Load MonitorTechnical Content, Data Load Monitor
ExtractionExtraction TransferTransfer Load Into PSALoad Into PSA
Which component contributes most?
1. Customer Exits
2. Resource Utilization
3. Load Balancing
4. Data Package Size
5. Indices on tables
6. Flat File format
7. Content vs. generic extractor
1. Customer Exits
2. Resource Utilization
3. Load Balancing
4. Data Package Size
5. Indices on tables
6. Flat File format
7. Content vs. generic extractor
1. I/O Contention
2. PSA Partition Size
1. I/O Contention
2. PSA Partition Size1. Resource Contraint
2. CPU / Memory Bottleneck
3. Network
4. Application Buffer Synchronization
1. Resource Contraint
2. CPU / Memory Bottleneck
3. Network
4. Application Buffer Synchronization
Check these points
Extractor Checker (RSA3),
ABAP Trace (SE30),
SQL Trace (ST05)
Extractor Checker (RSA3),
ABAP Trace (SE30),
SQL Trace (ST05)
SM50
SQL Trace (ST05)
OS Monitor (ST06)
SM50
SQL Trace (ST05)
OS Monitor (ST06)
ToolsOS Monitor (ST06)
DB Monitor (ST04)
OS Monitor (ST06)
DB Monitor (ST04)
© SAP AG 2004, Performance in SAP BW
Checklist – Data Load Performance – Overview 2 –
Which component contributes most?
Transfer RulesTransfer Rules
Update RulesUpdate RulesLoad Into Data TargetsLoad Into Data Targets
InfoCubesInfoCubes ODS ObjectsODS Objects
Technical Content, Data Load MonitorTechnical Content, Data Load Monitor
Master DataMaster Data
1. Buffering Number Ranges
2. Change Run
1. Buffering Number Ranges
2. Change Run
1. Roll-up
2. Change Run
3. Compression
4. Indices
5. Load Master Data before Transaction Data
6. Buffering Number Ranges
1. Roll-up
2. Change Run
3. Compression
4. Indices
5. Load Master Data before Transaction Data
6. Buffering Number Ranges
1. Transformation Rules / ABAP Coding
2. Transformation Library Formulas
1. Transformation Rules / ABAP Coding
2. Transformation Library Formulas
1. Parallel ODS activation
2. Unique Data Records
3. Flag BExReporting
4. Indices
1. Parallel ODS activation
2. Unique Data Records
3. Flag BExReporting
4. Indices
Check these points
Debugger within Monitor
ABAP Trace (SE30),
SQL Trace (ST05)
Debugger within Monitor
ABAP Trace (SE30),
SQL Trace (ST05)Tools
SQL Trace (ST05)SQL Trace (ST05)
© SAP AG 2004, Performance in SAP BW
Summary
Design your Data Model Prudently
and Consider Regular Reviews
Design your Data Design your Data Model Prudently Model Prudently
and Consider and Consider Regular ReviewsRegular Reviews
Compress Regularly
Compress Compress RegularlyRegularly
Use Aggregates and Design Them
Carefully
Use Aggregates Use Aggregates and Design Them and Design Them
CarefullyCarefully
Use customer-oriented reporting
& analysis features
Use customerUse customer--oriented reporting oriented reporting
& analysis & analysis featuresfeatures
© SAP AG 2004, Performance in SAP BW
Further Information
How-To Papershttp://service.sap.com/BW (Alias ‚BW‘) > Services and Implementation
SAP Service Marketplace:Use ALIAS: “BW” athttp://service.sap.com/BWSee Performance Folder
SAP BW Online Documentationhttp://help.sap.com