Infrastructure Solutions: Design, Manage, and Optimize a 60 TB SAP NetWeaver Business Intelligence Data Warehouse

Book description

In order to improve the performance and operational efficiency of businesses worldwide, a customer using SAP® wanted to establish a global business program to define and implement a standardized, group-wide business process architecture and associated master data for the parameterization of the group of software tools.

The expected growth of the number of users and the size of the database would be at a level never reached by other customers, however, so IBM® was asked to undertake the following tasks:

* Test the application to be sure that it can sustain such growth.
* Prove the manageability of the solution.
* Provide recommendations to optimize the infrastructure architecture.

This project illustrates the new near real time business intelligence (BI) context approached by customers who want the ability to rapidly analyze their business data to gain market shares. Data today comes from many diverse global sources and needs to be merged into an intelligent data warehouse.

This IBM Redbooks® publication describes the testing that was done in terms of performance and manageability in an SAP NetWeaver® BI and DB2® environment on IBM System p™ when scaling a client's solution to a data warehouse of 60 terabytes (TB). This book resulted from a joint cooperative effort that included the PSSC, the IBM/SAP International Competency Center, the DB2-SAP Center of Excellence, SAP AG, and a customer. The customer involved in this project is a worldwide company employing more than 250,000 employees with factories and logistics operations in almost every country in the world.

This project involved multiple technical skills and multiple products, as described here:

* Chapter 1, "Project overview: business objectives, architecture, infrastructure, and results" on page 1, summarizes the entire project, starting from the business needs through the description of the environment and options used, to the results achieved. This chapter can be viewed as an executive summary from an IT specialist perspective.

* Chapter 2, "The SAP NetWeaver BI perspective" on page 47; Chapter 3, "The DB2 perspective" on page 127; and Chapter 4, "The IBM System p perspective" on page 287, provide detailed views of the project from the perspectives of SAP specialists, DB2 specialists, and System p and AIX® specialists, respectively.

* Chapter 5, "The system storage perspective" on page 319, and Chapter 6, "The Tivoli Storage Manager perspective" on page 349, describe the storage environment and the manageability issues in such a large environment.

* Finally Appendix A, "The scripts used" on page 395, provides the scripts that we needed to develop for this project.

Table of contents

  1. Notices
    1. Trademarks
  2. Preface
    1. The team that wrote this book
    2. Become a published author
    3. Comments welcome
  3. Chapter 1: Project overview: business objectives, architecture, infrastructure, and results
    1. The scope of the project
      1. The business context and the customer strategy
      2. Test objectives
      3. The required tests
      4. The infrastructure
      5. The performance tools
    2. The online KPIs
      1. The progressive tests
      2. Objectives
      3. The KPI-G results (1/4)
      4. The KPI-G results (2/4)
      5. The KPI-G results (3/4)
      6. The KPI-G results (4/4)
      7. Trends
    3. The infrastructure KPIs
    4. Data compression
  4. Chapter 2: The SAP NetWeaver BI perspective
    1. SAP NetWeaver BI overview
      1. The SAP NetWeaver BI information model
      2. InfoCubes and the extended star schema
      3. The data flow in SAP NetWeaver BI
    2. Our project environment
      1. SAP NetWeaver application servers overview
      2. The SAP data model
      3. The load process (1/2)
      4. The load process (2/2)
      5. The aggregate rollup process
      6. The query process (1/2)
      7. The query process (2/2)
    3. Options and parameters discussions
      1. Parameters affecting the load process
      2. Optimizing the load process
      3. Parameters affecting the aggregate rollup process
      4. Optimizing the aggregate rollup process (1/2)
      5. Optimizing the aggregate rollup process (2/2)
      6. Parameters affecting the query process
      7. Optimizing the query process (1/4)
      8. Optimizing the query process (2/4)
      9. Optimizing the query process (3/4)
      10. Optimizing the query process (4/4)
      11. Impact of query and aggregate processes running in parallel (1/2)
      12. Impact of query and aggregate processes running in parallel (2/2)
      13. Impact of the load and the aggregate processes running together
      14. Impact of the load and the query processes running together
    4. Results from an SAP perspective
      1. Results summary
      2. Resources
    5. Lessons learned
      1. The load process
      2. The aggregate rollup process
      3. The query process
    6. DB2 9 features used by SAP
  5. Chapter 3: The DB2 perspective
    1. Introducing DB2
      1. Instance
      2. Database
      3. Partitioned databases
      4. DB2 9 (1/2)
      5. DB2 9 (2/2)
      6. SAP and DB2 9 (1/2)
      7. SAP and DB2 9 (2/2)
    2. The project environment
      1. Database creation and data redistribution process
      2. DB2 database data grow
      3. DB2 migration
      4. Database servers
      5. Database partitions
      6. Tablespaces and disk layout (1/2)
      7. Tablespaces and disk layout (2/2)
      8. Object distribution across partitions (1/2)
      9. Object distribution across partitions (2/2)
      10. Buffer pools
      11. Monitoring tools (1/2)
      12. Monitoring tools (2/2)
    3. Options and parameters discussions
      1. Registry and environment variables
      2. Instance configuration
      3. Database configuration
    4. Results from a DB2 specialist’s perspective
      1. Stress test results - KPI-G (1/5)
      2. Stress test results - KPI-G (2/5)
      3. Stress test results - KPI-G (3/5)
      4. Stress test results - KPI-G (4/5)
      5. Stress test results - KPI-G (5/5)
      6. Tests with DB2 data row compression (1/2)
      7. Tests with DB2 data row compression (2/2)
      8. REORG and RUNSTATS comparison with and without compression (1/3)
      9. REORG and RUNSTATS comparison with and without compression (2/3)
      10. REORG and RUNSTATS comparison with and without compression (3/3)
      11. Stress test comparison with and without compression (1/4)
      12. Stress test comparison with and without compression (2/4)
      13. Stress test comparison with and without compression (3/4)
      14. Stress test comparison with and without compression (4/4)
      15. Stress test comparison with concurrent I/O and file system cache (1/2)
      16. Stress test comparison with concurrent I/O and file system cache (2/2)
      17. Multi Dimension Clustering tables test results (1/2)
      18. Multi Dimension Clustering tables test results (2/2)
      19. STMM and DPF (SAP NetWeaver BI) (1/2)
      20. STMM and DPF (SAP NetWeaver BI) (2/2)
  6. Chapter 4: The IBM System p perspective
    1. Introducing IBM System p model p595
      1. The System p hardware
      2. Simultaneous multi-threading
      3. POWER Hypervisor
      4. Virtualization features on IBM system p5
      5. Operating systems
    2. The project environment
      1. IBM System p595 servers and logical partitioning
      2. Storage and file systems
      3. Network
    3. Settings and options
      1. General options and recommendations
      2. AIX kernel and network
      3. About lsattr parameters
    4. Lessons learned
      1. Memory affinity and paging
      2. Tcp_NodelayAck
      3. Use of large pages
      4. Power5 virtualization
      5. JFS2 lock contention
      6. DB2 FCM
  7. Chapter 5: The system storage perspective
    1. Introducing the system storage
      1. Disk - IBM System Storage DS8300
      2. Library - IBM System Storage TS3500 Tape Library
      3. Tape - IBM System Storage TS1030 Tape Drive
      4. Storage Area Network
    2. The project environment
      1. Storage area network architecture
      2. Zoning
      3. Disk configuration (1/2)
      4. Disk configuration (2/2)
      5. Tape configuration
    3. Options and parameters discussion
      1. Disk options and parameters
      2. Tape options and parameters
      3. DB2 logs and tape devices
    4. Results
      1. Tape KPI results
  8. Chapter 6: The Tivoli Storage Manager perspective
    1. The project environment
      1. The challenge
      2. Introducing the backup/restore solution
      3. Components and settings of the backup solution (1/2)
      4. Components and settings of the backup solution (2/2)
      5. Backup and restore processes
    2. Options and parameters discussions
      1. Backup: parallel versus sequential
      2. Design consideration
      3. Multiple volume sets
      4. Multiple nodenames configuration
    3. Infrastructure tests
      1. KPI-3A: back up 60 TB in less than 12 hours
      2. KPI-1: FlashCopy restore and rollforward of 500 GB log files
      3. KPI-2: DB restore from tape with rollforward of 2 TB archived logs (1/2)
      4. KPI-2: DB restore from tape with rollforward of 2 TB archived logs (2/2)
  9. Appendix A: The scripts used
    1. For the restore
    2. For the node 7 backup
    3. The Tivoli Data Protection for mySAP configuration file
    4. initEB8.fct
    5. The Tivoli Data Protection ACS configuration file
    6. The main backup script
    7. For the node 7 restore
    8. The script for the backup of node 0
    9. The Tivoli Storage Manager API configuration file
  10. Abbreviations and acronyms (1/2)
  11. Abbreviations and acronyms (2/2)
  12. Related publications
    1. IBM Redbooks
    2. Online resources
    3. How to get IBM Redbooks
    4. Help from IBM
  13. Index (1/2)
  14. Index (2/2)
  15. Back cover

Product information

  • Title: Infrastructure Solutions: Design, Manage, and Optimize a 60 TB SAP NetWeaver Business Intelligence Data Warehouse
  • Author(s): Christian Matthys, Abbas Birjandi, Alexis Gausach, Fabio Hasegawa, Edgar Maniago, Pekka Siekkinen, James Thompson
  • Release date: February 2008
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738488530