DATA WAREHOUSING PROFESSIONALS - Baylor University

CONTENTSxvSTAR SCHEMA KEYS / 239Primary Keys / 239Surrogate Keys / 240Foreign Keys / 240ADVANTAGES OF THE STAR SCHEMA / 241Easy for Users to Understand / 241Optimizes Navigation / 242Most Suitable for Query Processing / 243STARjoin and STARindex / 244STAR SCHEMA: EXAMPLES / 244Video Rental / 244Supermarket / 244Wireless Phone Service / 244Auction Company / 244CHAPTER SUMMARY / 246REVIEW QUESTIONS / 247EXERCISES / 24711 DIMENSIONAL MODELING: ADVANCED TOPICSCHAPTER OBJECTIVES / 249UPDATES TO THE DIMENSION TABLES / 250Slowly Changing Dimensions / 250Type 1 Changes: Correction of Errors / 251Type 2 Changes: Preservation of History / 252Type 3 Changes: Tentative Soft Revisions / 253MISCELLANEOUS DIMENSIONS / 255Large Dimensions / 255Rapidly Changing Dimensions / 256Junk Dimensions / 258THE SNOWFLAKE SCHEMA / 259Options to Normalize / 259Advantages and Disadvantages / 260When to Snowflake / 262AGGREGATE FACT TABLES / 262Fact Table Sizes / 264Need for Aggregates / 266Aggregating Fact Tables / 266Aggregation Options / 271FAMILIES OF STARS / 272Snapshot and Transaction Tables / 273Core and Custom Tables / 274249

xviCONTENTSSupporting Enterprise Value Chain or Value Circle / 274Conforming Dimensions / 275Standardizing Facts / 276Summary of Family of STARS / 277CHAPTER SUMMARY / 277REVIEW QUESTIONS / 278EXERCISES / 27812 DATA EXTRACTION, TRANSFORMATION, AND LOADINGCHAPTER OBJECTIVES / 281ETL OVERVIEW / 282Most Important and Most Challenging / 282Time Consuming and Arduous / 283ETL REQUIREMENTS AND STEPS / 284Key Factors / 285DATA EXTRACTION / 286Source Identification / 287Data Extraction Techniques / 287Evaluation of the Techniques / 294DATA TRANSFORMATION / 295Data Transformation: Basic Tasks / 296Major Transformation Types / 297Data Integration and Consolidation / 299Transformation for Dimension Attributes / 301How to Implement Transformation / 301DATA LOADING / 302Applying Data: Techniques and Processes / 303Data Refresh Versus Update / 306Procedure for Dimension Tables / 306Fact Tables: History and Incremental Loads / 307ETL SUMMARY / 308ETL Tool Options / 308Reemphasizing ETL Metadata / 309ETL Summary and Approach / 310OTHER INTEGRATION APPROACHES / 311Enterprise Information Integration (EII) / 311Enterprise Application Integration (EAI) / 312CHAPTER SUMMARY / 313REVIEW QUESTIONS / 313EXERCISES / 314281

CONTENTS13 DATA QUALITY: A KEY TO SUCCESSxvii315CHAPTER OBJECTIVES / 315WHY IS DATA QUALITY CRITICAL? / 316What Is Data Quality? / 316Benefits of Improved Data Quality / 319Types of Data Quality Problems / 320DATA QUALITY CHALLENGES / 323Sources of Data Pollution / 323Validation of Names and Addresses / 325Costs of Poor Data Quality / 325DATA QUALITY TOOLS / 326Categories of Data Cleansing Tools / 327Error Discovery Features / 327Data Correction Features / 327The DBMS for Quality Control / 327DATA QUALITY INITIATIVE / 328Data Cleansing Decisions / 329Who Should Be Responsible? / 330The Purification Process / 333Practical Tips on Data Quality / 334MASTER DATA MANAGEMENT (MDM) / 335MDM Categories / 335MDM Benefits / 335MDM and Data Warehousing / 336CHAPTER SUMMARY / 336REVIEW QUESTIONS / 336EXERCISES / 337PART 5INFORMATION ACCESS AND DELIVERY14 MATCHING INFORMATION TO THE CLASSES OF USERSCHAPTER OBJECTIVES / 341INFORMATION FROM THE DATA WAREHOUSE / 342Data Warehouse Versus Operational Systems / 342Information Potential / 344User – Information Interface / 347Industry Applications / 348WHO WILL USE THE INFORMATION? / 349Classes of Users / 349339341

xviiiCONTENTSWhat They Need / 352How to Provide Information / 354INFORMATION DELIVERY / 356Queries / 357Reports / 358Analysis / 359Applications / 359INFORMATION DELIVERY TOOLS / 360The Desktop Environment / 360Methodology for Tool Selection / 361Tool Selection Criteria / 364Information Delivery Framework / 365INFORMATION DELIVERY: SPECIAL TOPICS / 366Business Activity Monitoring (BAM) / 366Dashboards and Scorecards / 367CHAPTER SUMMARY / 371REVIEW QUESTIONS / 371EXERCISES / 37215 OLAP IN THE DATA WAREHOUSECHAPTER OBJECTIVES / 373DEMAND FOR ONLINE ANALYTICAL PROCESSING / 374Need for Multidimensional Analysis / 374Fast Access and Powerful Calculations / 375Limitations of Other Analysis Methods / 377OLAP is the Answer / 379OLAP Definitions and Rules / 379OLAP Characteristics / 382MAJOR FEATURES AND FUNCTIONS / 382General Features / 383Dimensional Analysis / 383What Are Hypercubes? / 386Drill Down and Roll Up / 390Slice and Dice or Rotation / 392Uses and Benefits / 393OLAP MODELS / 393Overview of Variations / 394The MOLAP Model / 394The ROLAP Model / 395ROLAP Versus MOLAP / 397373

CONTENTSxixOLAP IMPLEMENTATION CONSIDERATIONS / 398Data Design and Preparation / 399Administration and Performance / 401OLAP Platforms / 402OLAP Tools and Products / 402Implementation Steps / 403Examples of Typical Implementations / 404CHAPTER SUMMARY / 404REVIEW QUESTIONS / 405EXERCISES / 40516 DATA WAREHOUSING AND THE WEB407CHAPTER OBJECTIVES / 407WEB-ENABLED DATA WAREHOUSE / 408Why the Web? / 408Convergence of Technologies / 410Adapting the Data Warehouse for the Web / 411The Web as a Data Source / 412Clickstream Analysis / 413WEB-BASED INFORMATION DELIVERY / 414Expanded Usage / 414New Information Strategies / 416Browser Technology for the Data Warehouse / 418Security Issues / 419OLAP AND THE WEB / 420Enterprise OLAP / 420Web-OLAP Approaches / 420OLAP Engine Design / 421BUILDING A WEB-ENABLED DATA WAREHOUSE / 421Nature of the Data Webhouse / 422Implementation Considerations / 423Putting the Pieces Together / 424Web Processing Model / 426CHAPTER SUMMARY / 426REVIEW QUESTIONS / 426EXERCISES / 42717 DATA MINING BASICSCHAPTER OBJECTIVES / 429WHAT IS DATA MINING? / 430429

xxCONTENTSData Mining Defined / 431The Knowledge Discovery Process / 432OLAP Versus Data Mining / 435Some Aspects of Data Mining / 436Data Mining and the Data Warehouse / 438MAJOR DATA MINING TECHNIQUES / 439Cluster Detection / 440Decision Trees / 443Memory-Based Reasoning / 444Link Analysis / 445Neural Networks / 447Genetic Algorithms / 448Moving into Data Mining / 450DATA MINING APPLICATIONS / 452Benefits of Data Mining / 453Applications in CRM (Customer Relationship Management) / 454Applications in the Retail Industry / 455Applications in the Telecommunications Industry / 456Applications in Biotechnology / 457Applications in Banking and Finance / 459CHAPTER SUMMARY / 459REVIEW QUESTIONS / 459EXERCISES / 460PART 6IMPLEMENTATION AND MAINTENANCE18 THE PHYSICAL DESIGN PROCESSCHAPTER OBJECTIVES / 463PHYSICAL DESIGN STEPS / 464Develop Standards / 464Create Aggregates Plan / 465Determine the Data Partitioning Scheme / 465Establish Clustering Options / 466Prepare an Indexing Strategy / 466Assign Storage Structures / 466Complete Physical Model / 467PHYSICAL DESIGN CONSIDERATIONS / 467Physical Design Objectives / 467From Logical Model to Physical Model / 469461463

CONTENTSxxiPhysical Model Components / 469Significance of Standards / 470PHYSICAL STORAGE / 473Storage Area Data Structures / 473Optimizing Storage / 473Using RAID Technology / 476Estimating Storage Sizes / 477INDEXING THE DATA WAREHOUSE / 477Indexing Overview / 477B-Tree Index / 479Bitmapped Index / 481Clustered Indexes / 482Indexing the Fact Table / 482Indexing the Dimension Tables / 483PERFORMANCE ENHANCEMENT TECHNIQUES / 483Data Partitioning / 483Data Clustering / 484Parallel Processing / 484Summary Levels / 485Referential Integrity Checks / 485Initialization Parameters / 485Data Arrays / 486CHAPTER SUMMARY / 486REVIEW QUESTIONS / 486EXERCISES / 48719 DATA WAREHOUSE DEPLOYMENTCHAPTER OBJECTIVES / 489DATA WAREHOUSE TESTING / 490Front-End / 490ETL Testing / 490MAJOR DEPLOYMENT ACTIVITIES / 491Complete User Acceptance / 491Perform Initial Loads / 492Get User Desktops Ready / 493Complete Initial User Training / 494Institute Initial User Support / 495Deploy in Stages / 495CONSIDERATIONS FOR A PILOT / 497When is a Pilot Data Mart Useful? / 497489

xxiiCONTENTSTypes of Pilot Projects / 498Choosing the Pilot / 500Expanding and Integrating the Pilot / 501SECURITY / 502Security Policy / 502Managing User Privileges / 502Password Considerations / 503Security Tools / 504BACKUP AND RECOVERY / 504Why Back Up the Data Warehouse? / 505Backup Strategy / 505Setting up a Practical Schedule / 506Recovery / 507CHAPTER SUMMARY / 508REVIEW QUESTIONS / 508EXERCISES / 50920 GROWTH AND MAINTENANCECHAPTER OBJECTIVES / 511MONITORING THE DATA WAREHOUSE / 512Collection of Statistics / 512Using Statistics for Growth Planning / 514Using Statistics for Fine-Tuning / 514Publishing Trends for Users / 515USER TRAINING AND SUPPORT / 515User Training Content / 516Preparing the Training Program / 516Delivering the Training Program / 518User Support / 519MANAGING THE DATA WAREHOUSE / 520Platform Upgrades / 521Managing Data Growth / 521Storage Management / 522ETL Management / 522Data Model Revisions / 523Information Delivery Enhancements / 523Ongoing Fine-Tuning / 524CHAPTER SUMMARY / 524REVIEW QUESTIONS / 525EXERCISES / 525511

CONTENTSxxiiiANSWERS TO SELECTED EXERCISES527APPENDIX A: PROJECT LIFE CYCLE STEPS AND CHECKLISTS531APPENDIX B:535CRITICAL FACTORS FOR SUCCESSAPPENDIX C: GUIDELINES FOR EVALUATING VENDOR SOLUTIONS537APPENDIX D: HIGHLIGHTS OF VENDORS AND PRODUCTS539APPENDIX E:549REAL-WORLD EXAMPLES OF BEST PRACTICESREFERENCES555GLOSSARY557INDEX565

PREFACETHIS BOOK IS FOR YOUAre you an information technology professional watching, with great interest, the massiveunfolding and spreading of the data warehouse movement during the past decade? Areyou contemplating a move into this fast-growing area of opportunity? Are you a systems analyst, programmer, data analyst, database administrator, project leader, or software engineereager to grasp the fundamentals of data warehousing? Do you wonder how many differentbooks you may have to study to learn the underlying principles and the current practices? Areyou lost in the maze of the literature and products on the subject? Do you wish for a singlepublication on data warehousing, clearly and specifically designed for IT professionals? Doyou need a textbook that helps you learn the fundamentals in sufficient depth? If youanswered “yes” to any of the above, this book is written specially for you.This is the one definitive book on data warehousing clearly intended for IT professionals.The organization and presentation of the book are specially tuned for IT professionals. Thisbook does not presume to target anyone and everyone remotely interested in the subject forsome reason or another, but is written to address the specific needs of IT professionals likeyou. It does not tend to emphasize certain aspects and neglect other critical ones. The booktakes you over the entire spectrum of data warehousing.As a veteran IT professional with wide and intensive industry experience, as a successfuldatabase and data warehousing consultant for many years, and as one who teaches data warehousing fundamentals in the college classroom and at public seminars, I have come toappreciate the precise needs of IT professionals. In every chapter I have incorporatedthese requirements of the IT community.xxv

xxviPREFACETHE SCENARIOWhy have companies rushed into data warehousing? Why is there a tremendous surge ininterest? Data warehousing is no longer a purely novel idea just for research and experimentation. It has become a mainstream phenomenon. True, the data warehouse is not in everydoctor’s office yet, but neither is it confined to only high-end businesses. More than halfof all U.S. companies and a large percentage of worldwide businesses have made a commitment to data warehousing.In every industry across the board, from retail chain stores to financial institutions, frommanufacturing enterprises to government departments, and from airline companies to utilitybusinesses, data warehousing has revolutionized the way people perform business analysisand make strategic decisions. Every company that has a data warehouse is realizing the enormous benefits translated into positive results at the bottom line. These companies, now incorporating Web-based technologies, are enhancing the potential for greater and easier deliveryof vital information.Over the past decade, a large number of vendors have flooded the market with numerousdata warehousing products. Vendor solutions and products run the gamut of data warehousing and business intelligence—data modeling, data acquisition, data quality, data analysis,metadata, information delivery, and so on. The market is large, mature, and continuesto grow.CHANGED ROLE OF ITIn this scenario, information technology departments of all progressive companies haveperceived a radical change in their roles. IT is no longer required to create every reportand present every screen for providing information to the end-users. IT is now chargedwith the building of information delivery systems and letting the end-users themselvesretrieve information in innovative ways for analysis and decision making. Data warehousingand business intelligence environments are proving to be just that type of successful information delivery system.IT professionals responsible for building data warehouses had to revise their mindsetsabout building applications. They had to understand that a data warehouse is not a onesize-fits-all proposition. First, they had to get a clear understanding about data extractionfrom source systems, data transformations, data staging, data warehouse architecture, infrastructure, and the various methods of information delivery. In short, IT professionals, likeyou, must get a strong grip on the fundamentals of data warehousing.WHAT THIS BOOK CAN DO FOR YOUThe book is comprehensive and detailed. You will be able to study every significant topic inplanning, requirements, architecture, infrastructure, design, data preparation, informationdelivery, deployment, and maintenance. The book is specially designed for IT professionals;you will be able to follow the presentation easily because it is built upon the foundation ofyour background as an IT professional, your knowledge, and the technical terminology familiar to you. It is organized logically, beginning with an overview of concepts, moving on toplanning and requirements, then to architecture and infrastructure, on to data design, then to

PREFACExxviiinformation delivery, and concluding with deployment and maintenance. This progression istypical of what you are most familiar with in your IT experience and day-to-day work.The book provides an interactive learning experience. It is not just a one-way lecture. Youparticipate through the review questions and exercises at the end of each chapter. For eachchapter, the objectives at the beginning set the theme and the summary at the end highlightsthe topics covered. You can relate each concept and technique presented in the book to thedata warehousing industry and marketplace. You will benefit from the substantial number ofindustry examples. Although intended as a first course on the fundamentals, this book provides sufficient coverage of each topic so that you can comfortably proceed to the next step ofspecialization for specific roles in a data warehouse project.Featuring all the significant topics in appropriate measure, this book is eminently suitableas a textbook for serious self-study, a college course, or a seminar on the essentials. Itprovides an opportunity for you to become a data warehouse expert.ENHANCEMENTS IN THIS SECOND EDITIONThis greatly enhanced edition captures the developments and changes in the data warehousing landscape during the past nearly ten years. The underlying purposes and principles ofdata warehousing have remained the same. However, we notice definitive changes in thedetails, some finer aspects, and in product innovations. Although this edition succeeds inincorporating all the significant revisions, I have been careful not to disturb the overall logical arrangement and sequencing of the chapters.The term “business intelligence” has gained a lot more currency. Many practitioners nowconsider data warehousing to refer to populating the warehouse with data, and business intelligence to refer to using the warehouse data. Data warehousing has made inroads into areassuch as Customer Relationship Management, Enterprise Application Integration, EnterpriseInformation Integration, Business Activity Monitoring, and so on. The size of corporate datawarehouses has been rising higher and higher. Some progressive businesses have reapedenormous benefits from data warehouses that are almost in the 500 terabyte range (fivetimes the size of the U.S. Library of Congress archive). The benefits from data warehousesare no longer limited to a selected core of executives, managers, and analysts. Pervasive datawarehousing has become the operative principle, providing access and usage to staff at multiple levels. Information delivery through traditional reports and queries is being replaced byinteractive dashboards and scorecards.More specifically, among topics on recent trends and changes, this enhanced editionincludes the following:††††††††Evolution of business intelligenceReal-time business intelligenceData warehouse appliancesData warehouse: architectural typesData visualization enhancementsEnterprise application integration (EAI)Enterprise information integration (EII)Agile data warehouse development

CEData warehousing and KM (knowledge management)Data warehousing and ERP (enterprise resource planning)Data warehousing and CRM (customer relationship management)Improved requirements gathering methodsBusiness activity monitoring (BAM)Interactive information delivery through dashboards and scorecardsAdditional STAR schema examplesMaster data managementExamples of typical OLAP (online analytical processing) implementationsData mining applicationsWeb clickstream analysisHighlights of vendors and productsReal-world examples of best practicesACKNOWLEDGMENTSI wish to acknowledge my indebtedness and to express my gratitude to the authors listed inthe reference section at the end of the book. Their insights and observations have helped mecover every topic adequately.I must also express my appreciation to my students and professional colleagues. My interactions with them have enabled me to shape this textbook according to the needs of ITprofessionals.My special thanks are due to the wonderful staff and editors at Wiley, my publishers, whohave worked with me and supported me for more than a decade in the publication and promotion of my books.PAULRAJ PONNIAH, PH.D.Milltown, New JerseyOctober 2009

PART 1OVERVIEW AND CONCEPTS

CHAPTER 1THE COMPELLING NEED FOR DATAWAREHOUSINGCHAPTER OBJECTIVES††††††Understand the desperate need for strategic informationRecognize the information crisis at every ent

Data warehousing fundamentals for IT professionals / Paulraj Ponniah.—2nd ed. p. cm. Previous ed. published under title: Data warehousing fundamentals. Includes bibliographical references and index. ISBN 978-0-470-46207-2 (cloth) 1. Data warehousing. I. Ponniah, Paulraj. Data warehousing