Building the Data Warehouse

William H. Inmon is the acknowledged "Father of Data Warehousing" and a partner in www.billinmon.com, a Web site featuring information on data warehousing and related technologies. He has written more than 40 books on database and data warehousing technologies, and is a frequent speaker (and often the keynote) at major conferences.

• Preface xixAcknowledgments xxviiChapter 1 Evolution of Decision Support Systems 1The Evolution 2The Advent of DASD 4PC/4GL Technology 4Enter the Extract Program 5The Spider Web 6Problems with the Naturally Evolving Architecture 7Lack of Data Credibility 7Problems with Productivity 9From Data to Information 12A Change in Approach 14The Architected Environment 16Data Integration in the Architected Environment 18Who Is the User? 20The Development Life Cycle 20Patterns of Hardware Utilization 22Setting the Stage for Re-engineering 23Monitoring the Data Warehouse Environment 25Summary 28Chapter 2 The Data Warehouse Environment 29The Structure of the Data Warehouse 33Subject Orientation 34Day 1 to Day n Phenomenon 39Granularity 41The Benefits of Granularity 42An Example of Granularity 43Dual Levels of Granularity 46Exploration and Data Mining 50Living Sample Database 50Partitioning as a Design Approach 53Partitioning of Data 53Structuring Data in the Data Warehouse 56Auditing and the Data Warehouse 61Data Homogeneity and Heterogeneity 61Purging Warehouse Data 64Reporting and the Architected Environment 64The Operational Window of Opportunity 65Incorrect Data in the Data Warehouse 67Summary 69Chapter 3 The Data Warehouse and Design 71Beginning with Operational Data 71Process and Data Models and the Architected Environment 78The Data Warehouse and Data Models 79The Data Warehouse Data Model 81The Midlevel Data Model 84The Physical Data Model 88The Data Model and Iterative Development 91Normalization and Denormalization 94Snapshots in the Data Warehouse 100Metadata 102Managing Reference Tables in a Data Warehouse 103Cyclicity of Data — The Wrinkle of Time 105Complexity of Transformation and Integration 108Triggering the Data Warehouse Record 112Events 112Components of the Snapshot 113Some Examples 113Profile Records 114Managing Volume 115Creating Multiple Profile Records 117Going from the Data Warehouse to the Operational Environment 117Direct Operational Access of Data Warehouse Data 118Indirect Access of Data Warehouse Data 119An Airline Commission Calculation System 119A Retail Personalization System 121Credit Scoring 123Indirect Use of Data Warehouse Data 125Star Joins 126Supporting the ODS 133Requirements and the Zachman Framework 134Summary 136Chapter 4 Granularity in the Data Warehouse 139Raw Estimates 140Input to the Planning Process 141Data in Overflow 142Overflow Storage 144What the Levels of Granularity Will Be 147Some Feedback Loop Techniques 148Levels of Granularity — Banking Environment 150Feeding the Data Marts 157Summary 157Chapter 5 The Data Warehouse and Technology 159Managing Large Amounts of Data 159Managing Multiple Media 161Indexing and Monitoring Data 162Interfaces to Many Technologies 162Programmer or Designer Control of Data Placement 163Parallel Storage and Management of Data 164Metadata Management 165Language Interface 166Efficient Loading of Data 166Efficient Index Utilization 168Compaction of Data 169Compound Keys 169Variable-Length Data 169Lock Management 171Index-Only Processing 171Fast Restore 171Other Technological Features 172DBMS Types and the Data Warehouse 172Changing DBMS Technology 174Multidimensional DBMS and the Data Warehouse 175Data Warehousing across Multiple Storage Media 182The Role of Metadata in the Data Warehouse Environment 182Context and Content 185Three Types of Contextual Information 186Capturing and Managing Contextual Information 187Looking at the Past 187Refreshing the Data Warehouse 188Testing 190Summary 191Chapter 6 The Distributed Data Warehouse 193Types of Distributed Data Warehouses 193Local and Global Data Warehouses 194The Local Data Warehouse 197The Global Data Warehouse 198Intersection of Global and Local Data 201Redundancy 206Access of Local and Global Data 207The Technologically Distributed Data Warehouse 211The Independently Evolving Distributed Data Warehouse 213The Nature of the Development Efforts 213Completely Unrelated Warehouses 215Distributed Data Warehouse Development 217Coordinating Development across Distributed Locations 218The Corporate Data Model — Distributed 219Metadata in the Distributed Warehouse 223Building the Warehouse on Multiple Levels 223Multiple Groups Building the Current Level of Detail 226Different Requirements at Different Levels 228Other Types of Detailed Data 232Metadata 234Multiple Platforms for Common Detail Data 235Summary 236Chapter 7 Executive Information Systems and the Data Warehouse 239EIS — The Promise 240A Simple Example 240Drill-Down Analysis 243Supporting the Drill-Down Process 245The Data Warehouse as a Basis for EIS 247Where to Turn 248Event Mapping 251Detailed Data and EIS 253Keeping Only Summary Data in the EIS 254Summary 255Chapter 8 External Data and the Data Warehouse 257External Data in the Data Warehouse 260Metadata and External Data 261Storing External Data 263Different Components of External Data 264Modeling and External Data 265Secondary Reports 266Archiving External Data 267Comparing Internal Data to External Data 267Summary 268Chapter 9 Migration to the Architected Environment 269A Migration Plan 270The Feedback Loop 278Strategic Considerations 280Methodology and Migration 283A Data-Driven Development Methodology 283Data-Driven Methodology 286System Development Life Cycles 286A Philosophical Observation 286Summary 287Chapter 10 The Data Warehouse and the Web 289Supporting the eBusiness Environment 299Moving Data from the Web to the Data Warehouse 300Moving Data from the Data Warehouse to the Web 301Web Support 302Summary 302Chapter 11 Unstructured Data and the Data Warehouse 305Integrating the Two Worlds 307Text — The Common Link 308A Fundamental Mismatch 310Matching Text across the Environments 310A Probabilistic Match 311Matching All the Information 312A Themed Match 313Industrially Recognized Themes 313Naturally Occurring Themes 316Linkage through Themes and Themed Words 317Linkage through Abstraction and Metadata 318A Two-Tiered Data Warehouse 320Dividing the Unstructured Data Warehouse 321Documents in the Unstructured Data Warehouse 322Visualizing Unstructured Data 323A Self-Organizing Map (SOM) 324The Unstructured Data Warehouse 325Volumes of Data and the Unstructured Data Warehouse 326Fitting the Two Environments Together 327Summary 330Chapter 12 The Really Large Data Warehouse 331Why the Rapid Growth? 332The Impact of Large Volumes of Data 333Basic Data-Management Activities 334The Cost of Storage 335The Real Costs of Storage 336The Usage Pattern of Data in the Face of Large Volumes 336A Simple Calculation 337Two Classes of Data 338Implications of Separating Data into Two Classes 339Disk Storage in the Face of Data Separation 340Near-Line Storage 341Access Speed and Disk Storage 342Archival Storage 343Implications of Transparency 345Moving Data from One Environment to Another 346The CMSM Approach 347A Data Warehouse Usage Monitor 348The Extension of the Data Warehouse across Different Storage Media 349Inverting the Data Warehouse 350Total Cost 351Maximum Capacity 352Summary 354Chapter 13 The Relational and the Multidimensional Models as a Basis for Database Design 357The Relational Model 357The Multidimensional Model 360Snowflake Structures 361Differences between the Models 362The Roots of the Differences 363Reshaping Relational Data 364Indirect Access and Direct Access of Data 365Servicing Future Unknown Needs 366Servicing the Need to Change Gracefully 367Independent Data Marts 370Building Independent Data Marts 371Summary 375Chapter 14 Data Warehouse Advanced Topics 377End-User Requirements and the Data Warehouse 377The Data Warehouse and the Data Model 378The Relational Foundation 378The Data Warehouse and Statistical Processing 379Resource Contention in the Data Warehouse 380The Exploration Warehouse 380The Data Mining Warehouse 382Freezing the Exploration Warehouse 383External Data and the Exploration Warehouse 384Data Marts and Data Warehouses in the Same Processor 384The Life Cycle of Data 386Mapping the Life Cycle to the Data Warehouse Environment 387Testing and the Data Warehouse 388Tracing the Flow of Data through the Data Warehouse 390Data Velocity in the Data Warehouse 391“Pushing” and “Pulling” Data 393Data Warehouse and the Web-Based eBusiness Environment 393The Interface between the Two Environments 394The Granularity Manager 394Profile Records 396The ODS, Profile Records, and Performance 397The Financial Data Warehouse 397The System of Record 399A Brief History of Architecture — Evolving to the Corporate Information Factory 402Evolving from the CIF 404Obstacles 406CIF — Into the Future 406Analytics 406Erp/sap 407Unstructured Data 408Volumes of Data 409Summary 410Chapter 15 Cost-Justification and Return on Investment for a Data Warehouse 413Copying the Competition 413The Macro Level of Cost-Justification 414A Micro Level Cost-Justification 415Information from the Legacy Environment 418The Cost of New Information 419Gathering Information with a Data Warehouse 419Comparing the Costs 420Building the Data Warehouse 420A Complete Picture 421Information Frustration 422The Time Value of Data 422The Speed of Information 423Integrated Information 424The Value of Historical Data 425Historical Data and CRM 426Summary 426Chapter 16 The Data Warehouse and the ODS 429Complementary Structures 430Updates in the ODS 430Historical Data and the ODS 431Profile Records 432Different Classes of ODS 434Database Design — A Hybrid Approach 435Drawn to Proportion 436Transaction Integrity in the ODS 437Time Slicing the ODS Day 438Multiple ODS 439ODS and the Web Environment 439An Example of an ODS 440Summary 441Chapter 17 Corporate Information Compliance and Data Warehousing 443Two Basic Activities 445Financial Compliance 446The “What” 447The “Why” 449Auditing Corporate Communications 452Summary 454Chapter 18 The End-User Community 457The Farmer 458The Explorer 458The Miner 459The Tourist 459The Community 459Different Types of Data 460Cost-Justification and ROI Analysis 461Summary 462Chapter 19 Data Warehouse Design Review Checklist 463When to Do a Design Review 464Who Should Be in the Design Review? 465What Should the Agenda Be? 465The Results 465Administering the Review 466A Typical Data Warehouse Design Review 466Summary 488Glossary 489References 507Articles 507Books 510White Papers 512Index 517