Data Warehousing Fundamentals for IT Professionals

PAULRAJ PONNIAH, PHD, with over thirty years of experience as an IT consultant, has worked with such organizations as Texaco, Sotheby's, Blue Cross/Blue Shield, NA Philips, New York-Presbyterian Hospital, Panasonic, and Bantam Doubleday Dell. He specializes in the design and implementation of data warehouse and database systems. Dr. Ponniah has published three successful books and, as Adjunct Professor, continues to teach college courses in data warehousing and database design.

"... clearly the author is a long-time practitioner and expert in this area. And if this were not enough, the language is clear and precise, and the associated content is easily internalised." (BCS, August 2010)

• Preface xxvPart 1 Overview and Concepts 11 The Compelling Need For Data Warehousing 3Chapter Objectives 3Escalating Need For Strategic Information 4Failures of Past Decision-Support Systems 9Operational Versus Decision-Support Systems 11Data Warehousing—The Only Viable Solution 13A Simple Concept For Information Delivery 15The Data Warehousing Movement 17Evolution of Business Intelligence 18Chapter Summary 20Review Questions 20Exercises 212 Data Warehouse: The Building Blocks 23Chapter Objectives 23Defining Features 24Data Warehouses and Data Marts 29Architectural Types 32Overview of The Components 34Metadata in The Data Warehouse 41Chapter Summary 42Review Questions 43Exercises 433 Trends in Data Warehousing 45Chapter Objectives 45Continued Growth in Data Warehousing 46Significant Trends 50Emergence of Standards 64Web-Enabled Data Warehouse 66Chapter Summary 69Review Questions 69Exercises 70Part 2 Planning and Requirements 714 Planning and Project Management 73Chapter Objectives 73Planning Your Data Warehouse 74The Data Warehouse Project 79The Development Phases 83The Project Team 85Project Management Considerations 90Chapter Summary 96Review Questions 96Exercises 975 Defining The Business Requirements 99Chapter Objectives 99Dimensional Analysis 100Information Packages—A Useful Concept 103Requirements Gathering Methods 109Requirements Definition: Scope and Content 116Chapter Summary 119Review Questions 119Exercises 1206 Requirements As The Driving Force For Data Warehousing 121Chapter Objectives 121Data Design 122The Architectural Plan 125Data Storage Specifications 131DBMS Selection 132Information Delivery Strategy 133Chapter Summary 136Review Questions 136Exercises 137Part 3 Architecture and Infrastructure 1397 Architectural Components 141Chapter Objectives 141Understanding Data Warehouse Architecture 141Distinguishing Characteristics 143Architectural Framework 146Technical Architecture 148Architectural Types 156Chapter Summary 160Review Questions 160Exercises 1618 Infrastructure As The Foundation For Data Warehousing 163Chapter Objectives 163Infrastructure Supporting Architecture 164Hardware and Operating Systems 166Database Software 181Collection of Tools 184Data Warehouse Appliances 188Chapter Summary 191Review Questions 191Exercises 1929 The Significant Role of Metadata 193Chapter Objectives 193Why Metadata Is Important 193Metadata Types By Functional Areas 203Business Metadata 207Technical Metadata 209How To Provide Metadata 212Chapter Summary 219Review Questions 220Exercises 220Part 4 Data Design and Data Preparation 22310 Principles of Dimensional Modeling 225Chapter Objectives 225From Requirements To Data Design 225The Star Schema 232Star Schema Keys 239Advantages of The Star Schema 241Star Schema: Examples 244Chapter Summary 246Review Questions 247Exercises 24711 Dimensional Modeling: Advanced Topics 249Chapter Objectives 249Updates To The Dimension Tables 250Miscellaneous Dimensions 255The Snowflake Schema 259Aggregate Fact Tables 262Families of Stars 272Chapter Summary 277Review Questions 278Exercises 27812 Data Extraction, Transformation, and Loading 281Chapter Objectives 281ETL Overview 282ETL Requirements and Steps 284Data Extraction 286Data Transformation 295Data Loading 302ETL Summary 308Other Integration Approaches 311Chapter Summary 313Review Questions 313Exercises 31413 Data Quality: A Key To Success 315Chapter Objectives 315Why Is Data Quality Critical? 316Data Quality Challenges 323Data Quality Tools 326Data Quality Initiative 328Master Data Management (Mdm) 335MDM Categories 335MDM Benefits 335MDM and Data Warehousing 336Chapter Summary 336Review Questions 336Exercises 337Part 5 Information Access and Delivery 33914 Matching Information To The Classes of Users 341Chapter Objectives 341Information From The Data Warehouse 342Who Will Use The Information? 349Information Delivery 356Information Delivery Tools 360Information Delivery: Special Topics 366Chapter Summary 371Review Questions 371Exercises 37215 OLAP in The Data Warehouse 373Chapter Objectives 373Demand For Online Analytical Processing 374OLAP is The Answer 379OLAP Definitions and Rules 379OLAP Characteristics 382Major Features and Functions 382OLAP Models 393OLAP Implementation Considerations 398Chapter Summary 404Review Questions 405Exercises 40516 Data Warehousing and The Web 407Chapter Objectives 407Web-Enabled Data Warehouse 408Web-Based Information Delivery 414OLAP and The Web 420Building A Web-Enabled Data Warehouse 421Chapter Summary 426Review Questions 426Exercises 42717 Data Mining Basics 429Chapter Objectives 429What Is Data Mining? 430Major Data Mining Techniques 439Data Mining Applications 452Chapter Summary 459Review Questions 459Exercises 460Part 6 Implementation and Maintenance 46118 The Physical Design Process 463Chapter Objectives 463Physical Design Steps 464Physical Design Considerations 467Physical Storage 473Indexing The Data Warehouse 477Performance Enhancement Techniques 483Chapter Summary 486Review Questions 486Exercises 48719 Data Warehouse Deployment 489Chapter Objectives 489Data Warehouse Testing 490Major Deployment Activities 491Considerations For A Pilot 497Security 502Backup and Recovery 504Chapter Summary 508Review Questions 508Exercises 50920 Growth and Maintenance 511Chapter Objectives 511Monitoring The Data Warehouse 512User Training and Support 515Managing The Data Warehouse 520Chapter Summary 524Review Questions 525Exercises 525Answers To Selected Exercises 527Appendix A: Project Life Cycle Steps and Checklists 531Appendix B: Critical Factors For Success 535Appendix C: Guidelines For Evaluating Vendor Solutions 537Appendix D: Highlights of Vendors and Products 539Appendix E: Real-World Examples of Best Practices 549References 555Glossary 557Index 565