This book provides comprehensive guidance to analyze and optimize performance in SAP BW Release 3.5. First, the author introduces you to basic SAP BW concepts (architecture, data modeling), key methods and tools for systematic performance analysis, as well as exclusive data design tips. Then, benefit from expert insights on indices, statistics, database optimizers, and critical aspects of reporting performance. Full-length chapters deal with aggregates, as well as extraction and loading performance. In addition, InfoCube partitioning and compression are discussed extensively.
This unique reference gives administrators the background knowledge, practical guidance, and all the tools needed to effectively analyze and tune BW performance immediately. Plus, BW developers benefit from detailed data design descriptions and expert tips that cannot be found elsewhere.

Highlights include

- BW architecture, sizing, and data modeling
- System load analysis
- Indices and database statistics
- Reporting performance: OLAP Cache, query monitor and trace,
- BW Reporting Agent, front-end performance and network, BEx queries and Excel workbooks
- Aggregates
- Compression and partitioning
- Extraction and loading performance: Integration of data sources, data flow, communication and transfer techniques, Persistent Staging Area, performance aspects for load, data extraction, and data transformation
processes
- Update of InfoCubes and ODS objects

About the Author

Thomas Schröder works as a Managing Consultant in the department of Finance Transformation and Application Consulting at Capgemini Germany. He is a renowned specialist with many years of experience in the design and implementation of Business Intelligence solutions. He focuses on Business Intelligence strategy, Business Analysis, Data Modelling, Data Warehouse Architecture, and SAP Business Information Warehouse.

Table of Contents 

1 ... Introduction and Overview ... 11

1.1 ... Structure of the Book ... 12
1.2 ... How to Use This Book ... 14
1.3 ... Acknowledgements ... 15

2 ... Data Warehousing and SAP BW ... 17

2.1 ... Introduction to Data Warehousing ... 17
2.2 ... Differences Between Operational and Decision Support Systems ... 20
2.3 ... Structure of Data Warehousing Systems ... 21
2.4 ... Overview of SAP BW ... 24
... 2.4.1 ... Administration and Customizing ... 25
... 2.4.2 ... Data Extraction in SAP BW ... 26
... 2.4.3 ... Data Storage in SAP BW ... 26
... 2.4.4 ... Metadata Management ... 30
... 2.4.5 ... Analysis and Reporting ... 30

3 ... Basic Principles of the SAP BW Architecture ... 35

3.1 ... Software Components of an SAP BW System ... 35
3.2 ... Overview of the SAP BW Architecture ... 38

4 ... Basic Principles of SAP Memory Configuration ... 47

4.1 ... Terminology Definitions ... 47
4.2 ... Basic Principles of SAP Memory Management ... 48
... 4.2.1 ... Sequence of Memory Usage ... 53
... 4.2.2 ... SAP Profile Parameters ... 56

5 ... SAP BW Sizing ... 61

5.1 ... The SAP Sizing Process ... 62
5.2 ... Size Categories of SAP BW Systems ... 63
5.3 ... Sizing Hard Disk Capacity ... 64
... 5.3.1 ... Calculating InfoCubes ... 65
... 5.3.2 ... Calculating ODS Objects ... 67
... 5.3.3 ... Calculating PSA Tables ... 68
... 5.3.4 ... Calculating the Total Hard Disk Capacity ... 69
5.4 ... CPU Sizing ... 69
... 5.4.1 ... CPU Sizing for Loading and Data Staging Processes ... 71
... 5.4.2 ... CPU Sizing for Queries ... 72
... 5.4.3 ... CPU Load Distribution ... 76
5.5 ... Memory Sizing ... 77
... 5.5.1 ... Memory Sizing for Loading and Data Staging Processes ... 78
... 5.5.2 ... Memory Sizing for Queries ... 80
... 5.5.3 ... Summary ... 83
5.6 ... Operation Mode and Load Distribution ... 84
5.7 ... SAP Quick Sizer ... 86

6 ... The SAP BW Data Model ... 89

6.1 ... The Star Schema Data Model ... 89
6.2 ... The SAP BW Data Model ... 91
... 6.2.1 ... Fact Tables in SAP BW ... 92
... 6.2.2 ... Dimension Tables in SAP BW ... 92
... 6.2.3 ... Master Data Tables in SAP BW ... 95
... 6.2.4 ... SID Tables in SAP BW ... 101
... 6.2.5 ... External Hierarchies in SAP BW ... 105
... 6.2.6 ... Summary ... 110
6.3 ... Performance-Relevant Modeling Aspects ... 112

7 ... Analyzing the Database, Memory, and Hardware ... 117

7.1 ... Analyzing the Database ... 118
... 7.1.1 ... Terminology Definitions ... 118
... 7.1.2 ... Analyzing the Database Buffers ... 120
... 7.1.3 ... Analyzing the Shared SQL Area ... 125
... 7.1.4 ... Analyzing the Table Scans ... 128
... 7.1.5 ... Analyzing the Sorting Processes ... 129
... 7.1.6 ... Analyzing the Database Buffers ... 130
... 7.1.7 ... Analyzing Memory, Tables and Indices ... 131
... 7.1.8 ... Checking the DB Parameters ... 136
... 7.1.9 ... DBA Planning Calendar ... 137
7.2 ... Analyzing the SAP Memory Areas ... 137
... 7.2.1 ... Analyzing the SAP Buffers ... 138
... 7.2.2 ... Analyzing the SAP Memory ... 140
... 7.2.3 ... Analyzing the Allocated Memory and the Main Memory ... 142
... 7.2.4 ... Analyzing the Swap Space ... 144
7.3 ... Analyzing the Hardware ... 145
... 7.3.1 ... Analyzing CPU or Main Memory Bottlenecks ... 146
... 7.3.2 ... Analyzing I/O Problems ... 147
7.4 ... Analyzing the SAP Work Processes ... 148
7.5 ... Analyzing User Sessions ... 152
7.6 ... Analyzing the Memory Utilization of Users and Sessions ... 154

8 ... Analyzing the System Load ... 155

8.1 ... Basic Principles and Terms ... 156
... 8.1.1 ... Processing a Transaction Step in the SAP System ... 156
... 8.1.2 ... Distributing the Response Times ... 159
8.2 ... The Workload Monitor ... 160
8.3 ... SAP System Load Analysis ... 163
... 8.3.1 ... Analyzing the System Load Overview ... 163
8.4 ... Analysis of SAP BW System Load ... 164
... 8.4.1 ... BW Statistics ... 165
... 8.4.2 ... Analysis of Statistical Data with Transaction SE16 ... 170
... 8.4.3 ... Analyzing Statistical Data Using the System Load Monitor (ST03N) ... 173
... 8.4.4 ... Interpreting Statistical Data ... 178
8.5 ... Technical Content ... 181
8.6 ... Transferring Technical Content ... 183
... 8.6.1 ... Technical Content for Extraction ... 184
... 8.6.2 ... Technical Content for SAP BW Objects ... 185

9 ... Indices and Database Statistics ... 189

9.1 ... Principles ... 189
9.2 ... Structure Types of the Indices ... 194
9.3 ... Indexing Schema in SAP BW ... 199
... 9.3.1 ... Indexing the Fact Table in Standard InfoCubes ... 201
... 9.3.2 ... Indexing the Dimension Tables ... 203
... 9.3.3 ... Indexing the Master Data Tables (X/Y Tables) ... 203
... 9.3.4 ... Indexing the SID Tables ... 204
... 9.3.5 ... Indices for Dimension Tables with High Cardinality ... 205
... 9.3.6 ... Star Join Execution Plan ... 207
... 9.3.7 ... Index Schema for Transactional InfoCubes ... 214
... 9.3.8 ... Index Schema for Partitioned InfoCubes ... 215
... 9.3.9 ... Indices on ODS Objects ... 215
9.4 ... Administration of Indices ... 216
... 9.4.1 ... Checking Indices ... 216
... 9.4.2 ... Checking the Quality of Indices ... 222
... 9.4.3 ... Creating New Indices ... 225
9.5 ... Database Optimizer ... 227
9.6 ... Database Statistics ... 228
9.7 ... Administration of Database Statistics ... 231
... 9.7.1 ... Administration of Database Statistics Using BRCONNECT ... 231
... 9.7.2 ... Statistics for InfoCubes ... 233
... 9.7.3 ... Administration of Database Statistics Using Transaction DB20 ... 235
... 9.7.4 ... Automating the Database Statistics in the DBA Planning Calendar ... 239

10 ... Reporting Performance ... 243

10.1 ... OLAP Processor ... 243
10.2 ... OLAP Cache ... 245
10.3 ... OLAP Cache Monitor ... 247
... 10.3.1 ... Cache Structure ... 250
... 10.3.2 ... Global Cache Parameters ... 252
... 10.3.3 ... OLAP Properties for InfoProviders ... 257
... 10.3.4 ... Cache Purging and Swapping ... 259
10.4 ... Query Monitor ... 260
... 10.4.1 ... Query Properties ... 261
... 10.4.2 ... Debugging Options ... 268
... 10.4.3 ... Performance Information in the Query Monitor ... 272
10.5 ... Query Trace ... 273
10.6 ... SAP BW Reporting Agent ... 277
... 10.6.1 ... Printing Queries ... 277
... 10.6.2 ... Precalculating Web Templates ... 280
10.7 ... Frontend Performance and Network ... 282
... 10.7.1 ... SAP BEx Analyzer ... 282
... 10.7.2 ... Web Reporting ... 286
... 10.7.3 ... Hardware and Software Recommendations for the SAP BW Frontend ... 288
10.8 ... Performance Aspects Relevant to SAP BEx Queries and Excel Workbooks ... 290
... 10.8.1 ... Using Cell Editors (Exception Cells) ... 291
... 10.8.2 ... Using Formulas in Excel Workbooks ... 291
... 10.8.3 ... Using Restricted Key Figures, Filters, and Selections in Queries ... 292
... 10.8.4 ... Query Read Mode ... 292
... 10.8.5 ... Calculated Key Figures in Queries ... 293
... 10.8.6 ... Queries on MultiProviders ... 294

11 ... Aggregates ... 301

11.1 ... Basic Principles ... 303
... 11.1.1 ... Aggregates on Characteristics ... 303
... 11.1.2 ... Aggregates on Navigation Attributes ... 306
... 11.1.3 ... Aggregates on Hierarchy Nodes ... 311
... 11.1.4 ... Aggregates on Fixed Values ... 313
... 11.1.5 ... Exception Aggregations in InfoCubes ... 315
... 11.1.6 ... Line Item Aggregates (Flat Aggregates) ... 317
11.2 ... Automatic Creation of Aggregates ... 319
... 11.2.1 ... Proposing Aggregates from BW Statistics ... 321
... 11.2.2 ... Proposing Aggregates from the Query Definition ... 323
11.3 ... Manual Creation of Aggregates ... 325
... 11.3.1 ... Analyzing the Query Using the Query Monitor (RSRT) ... 326
... 11.3.2 ... Creating the Aggregate ... 329
... 11.3.3 ... Checking and Evaluating Aggregates ... 331
11.4 ... Maintaining Aggregates ... 332
... 11.4.1 ... Aggregate Rollup ... 332
... 11.4.2 ... Checking the Aggregate Tree (Rollup Hierarchy) ... 340
... 11.4.3 ... Summarizing Aggregates/Optimization ... 342
... 11.4.4 ... Switching Off Aggregates ... 343
... 11.4.5 ... Analyzing and Monitoring the Filling and Rollup of Aggregates ... 344
... 11.4.6 ... Hierarchy and Attribute Changes ... 346
... 11.4.7 ... Parameterizing the Hierarchy and Attribute Change Runs ... 348
... 11.4.8 ... Analyzing and Monitoring a Hierarchy and Attribute Change Run ... 350
... 11.4.9 ... Delta Process/Rebuild ... 351
... 11.4.10 ... Aggregate Block Sizes ... 352
... 11.4.11 ... Parallel and Serial Change Runs ... 353

12 ... Compression and Partitioning ... 357

12.1 ... Compressing InfoCubes ... 357
12.2 ... Partitioning InfoCubes ... 360
... 12.2.1 ... Partitioning at the Database Level ... 361
... 12.2.2 ... Partitioning at Application Level ... 366

13 ... Extraction and Load Performance ... 371

13.1 ... Data Sources and Their Integration ... 372
13.2 ... Data Flow in SAP BW ... 374
13.3 ... Enabling the Communication Between SAP R/3 and SAP BW ... 376
13.4 ... Transfer Techniques ... 378
... 13.4.1 ... Application Link Enabling (ALE) ... 378
... 13.4.2 ... Intermediate Document (IDoc) ... 378
... 13.4.3 ... Business Application Programming Interface (BAPI) ... 378
13.5 ... Transfer Methods ... 379
13.6 ... Persistent Staging Area ... 380
... 13.6.1 ... Partitioning the PSA ... 382
... 13.6.2 ... Processing Options for the PSA ... 383
13.7 ... Monitoring the Load Processes ... 385
... 13.7.1 ... Header Data of the Load Request ... 386
... 13.7.2 ... Status Information for the Load Request ... 387
... 13.7.3 ... Detail Data of the Load Request ... 389
13.8 ... Performance Aspects for Data Extraction ... 393
... 13.8.1 ... Maintaining the Control Parameters for the Data Transfer ... 393
... 13.8.2 ... Main Memory Requirements for the Data Extraction ... 398
13.9 ... Performance Aspects Relevant to Data Transformation ... 399
... 13.9.1 ... Simulating the Update ... 401
... 13.9.2 ... ABAP Source Code in Transfer and Update Rules ... 404
... 13.9.3 ... Optimization Measures in SQL Programming ... 405
13.10 ... Performance Aspects Relevant to the Update of InfoCubes ... 406
... 13.10.1 ... Deleting Secondary Indices ... 406
... 13.10.2 ... Increasing the Number Range Buffer of Dimension Tables ... 407
... 13.10.3 ... Uploading Transaction Data ... 409
13.11 ... Performance Aspects Relevant to the Update of Standard ODS Objects ... 411
... 13.11.1 ... Structure of a Standard ODS Object ... 411
... 13.11.2 ... Options for Optimizing the Activation Runtime ... 413

14 ... Appendix ... 417

14.1 ... Transparent Tables of SAP BW Statistics ... 417
... 14.1.1 ... Table RSDDSTAT: Statistical BW Data for Aggregate Selection and Accounting ... 417
... 14.1.2 ... Table RSDDSTATAGGR: Statistical BW Data for Aggregate Selection and Accounting ... 419
... 14.1.3 ... Table RSDDSTATWHM: Statistical BW Data for Warehouse Management ... 420
14.2 ... Job Prefixes in SAP BW ... 421
14.3 ... Transactions in the SAP BW System ... 422
14.4 ... Transactions Relevant to BW in the SAP R/3 System ... 426
14.5 ... Processing Steps and Calling Programs in the RSMO Monitor ... 426
14.6 ... SAP R/3 and BW System Tables ... 428
14.7 ... SAP Notes ... 430
... 14.7.1 ... Database Settings ... 430
... 14.7.2 ... System Settings ... 431
... 14.7.3 ... Aggregates ... 431
... 14.7.4 ... Composite SAP Notes and FAQs ... 432
14.8 ... SAP Online Resources ... 432
14.9 ... Optimizing the Performance of Workbooks Containing Excel Formulas .. 433

Index ... 441