Manager's Guide to Data Warehousing

LAURA L. REEVES, coauthor of The Data Warehouse Lifecycle Toolkit, has over 23 years of experience in end-to-end data warehouse development focused on developing comprehensive project plans, collecting business requirements, designing business dimensional models and database schemas, and creating enterprise data warehouse strategies and data architectures.

• Introduction xxiiiPart One The Essentials of Data Warehousing 1Chapter 1 Gaining Data Warehouse Success 3The Essentials of Data Warehousing 3What Is a Data Warehouse? 4Differences Between Operational and DW Systems 4The Data Warehousing Environment 4What Is a Data Model? 6Understanding Industry Perspectives 7Design and Development Sequence 8Why Build a Data Warehouse? 11The Value of Data Warehousing 12The Promises of Data Warehousing 15Keys to Success 16Developing and Maintaining Strong Business and Technology Partnerships 17Identifying True Business Requirements 17Shifting to a Global Perspective 18Overcoming Unrealistic Expectations 19Providing Clear Communication 20Treating Data As a Corporate Asset 21Effectively Leveraging Technology 21Roadblocks to Success 22Believing the Myth: ‘‘If You Build It, They Will Come’’ 22Falling into the Project Deadline Trap 23Failing to Uphold Organizational Discipline 23Lacking Business Process Change 24Narrowing the Focus Too Much 25Resting on Your Laurels 27Relying on the Technology Fix 27Getting the Right People Involved 28Finding Lost Institutional Knowledge 29Summary 30Chapter 2 The Executive’s FAQ for Data Warehousing 31Question: What is the business benefit of a data warehouse? 32Answer 32Question: How much will it cost? 33Answer 33Question: How long will it take? 34Answer 35Question: How can I ensure success? 36Answer 36Question: Do other companies really build these in 90 days? 37Answer 37Question: How will we know we are doing this right? 38Answer 38Question: Why didn’t this work last time? What is different this time? 39Answer 39Question: Do we have the right technology in place? 39Answer 40Question: Are we the only company with data warehouse problems? 40Answer 41Question: Will I get one version of the truth? 41Answer 42Question: Why can’t we just use our current systems? 43Answer 44Question: Will the data warehouse replace our old systems? 45Question: Who needs to be involved? 45Question: Do we know where we are going? How will we know when we get there? 46Answer 46Question: How do we get started and stay focused? 47Answer 47Summary 48Part Two The Business Side of Data Warehousing 49Chapter 3 Understanding Where You Are and Finding Your Way 51Assessing Your Current State 51What Is Your Company’s Strategic Direction? 52What Are the Company’s Top Initiatives? 54How Healthy Is Your Data? 55Does the Business Place Value on Analysis? 56Reflecting on Your Data Warehouse History 57Understanding Your Existing Reporting Environment 58Finding the Reporting Systems 59Compiling an Inventory 60Identifying the Business Purpose 61Discovering the Data You Already Have 63Understanding the People 65Tracking Technology and Tools 65Understanding Enterprise Resources 66Netting It All Out 68Introducing the Case Studies 70The Call Center Data Warehouse Project 70In Real Life 70Giant Company 71Agile, Inc. 72Summary 72Chapter 4 Successful IT–Business Partnerships 75What a Partnership Really Means 75What the Business Partners Should Expect to Do 76Business Executives and Senior Management 78The Executive Business Sponsor 78Business Managers 81The Business Champion 82Business Analysts 83Helping the Business Analyst Deal with Change 85Business User Audience 86Project Manager 86What You Should Expect from IT 88CIO/IT Executive Sponsor 89Data Warehouse Manager 89Business Systems Analyst 90Source System Analyst 91Data Modeler/Data Architect 92ETL Developer(s) 93Business Intelligence Application Developer 94Other Supporting Roles 95Tips for Building and Sustaining a Partnership 95Leveraging External Consulting 97Building Strong Project Teams 98Effective Communication 99Netting Out Key Messages 99Presenting in Business Terms 100Meeting Preparation 101Presentation Tips 102When to Communicate 103Partnerships Beyond a Project 104The Decision-Making Process 104Executive Steering Committee 104DW Business Support Team 106Enterprise Considerations 107In Real Life 107A Glimpse into Giant, Co. 107Insight from Agile, Inc. 108Summary 109Chapter 5 Setting Up a Successful Project 111Defining the Project 111Setting Up the Project Charter 112Documenting Project Scope 117Developing a Statement of Work 117How Much Will It Cost? 120Project Approval 122Starting the Project 122Launching the Project 123Managing a Successful Project 124Issue Tracking 124Using Project Change Control 125Discussing Change in Business Terms 126Managing Expectations 128In Real Life 129Structured Projects with Giant 129Freedom for Creativity at Agile, Inc. 130Summary 131Chapter 6 Providing Business Requirements 133What Requirements Are Needed? 134Peeling Back the Layers of Requirements Gathering 134Who Provides Input? 137Who Gathers the Requirements? 137Providing Business Requirements 138Strategic Requirements 138Broad Business Requirements 140Business Analyses 143Business Data Requirements 145Systems and Technical Requirements 147Communicating What You Really Need 149What Else Would Help the Project Team? 150Data Integration Challenges 151Assess Organizational Motivation 151Complete Picture of the Data 152What If No One Is Asking? 152Practical Techniques for Gathering Requirements 153Interview Session Characteristics 153Individual Interviews 153Group Interviews 153Project Team Participation 154Interview Tips 154Who Needs to Be Included? 155Setting a Good Example 156Preparing for Interview Sessions 157Conducting the Interview Sessions 157Capturing Content: Notes vs. Tapes 157Running the Interview 158Concluding the Interview 158Putting the Pieces Together 158Individual Interview Documentation 159Responsibilities 159Business Themes 159Business Data 160Consolidated Requirements Documentation 161Executive Summary 161Consolidated Business Themes 162Candidate Business Analyses 162Consolidated Business Data Requirements 162Identification of Non-Data Warehouse Requirements 163Common Requirements Gathering Challenges 163Sifting Through Reports 163Listing Data Elements 164Developing Functional Specifications 164Moving Beyond Immediate 164Lack of Requirements 165The Cynic 165Setting Attainable Goals 166Exploring Alternatives 167Setting Priorities 168In Real Life 170A Glimpse into Giant Company 170Insight from Agile, Inc. 170Summary 171Part Three Dealing with the Data 173Chapter 7 Modeling the Data for your Business 175The Purpose of Dimensional Models 176Ease of Use 176Query Performance 177Understanding Your Data 177What Is a Dimensional Model? 178Dimensions 178Facts 180Using Both Parts of the Model 180Implementing a Dimensional Model 181Diagramming Your Dimensional Model 182The Business Dimensional Model 182Business Dimensions 183Fact Groups 184A Call Center Case Study 186Call Center Dimensions 187Date Dimension 187Time Dimension 187Customer Dimension 189Employee Dimension 191Call Dimension 191Call Outcome Dimension 194Employee Task Dimension 195Call Center Fact Groups 196Calls Fact Group 196Call Center Time Tracking Fact Group 196Call Forecast Fact Group 198Working with the Model 199Business Dimensional Model Index 200Enterprise Considerations 200Conformed Dimensions 200Conformed Facts 202Practical Guidelines 202Guidelines for a Single Dimension 202Guidelines for a Single Fact Group 203Characteristics of the Model across the Enterprise 204Business Participation in the Modeling Process 205Creating the First Draft 205Preparing for Modeling Sessions 205Brainstorming the Framework 206Drafting the Initial Dimensions 206Drafting the Initial Fact Groups 207Documenting the Model 208Logging Questions and Issues 208Building the Business Measures Worksheet 209Preliminary Source to Target Data Map 211Completing or Fleshing Out the Model 211Working Through the Issues 211Completing the Documentation 212Working Through All the Data Elements 212Refining the Model 213Business Reviews of the Model 213Small Business Reviews 214When Are You Done? 214Gaining Final Commitment 215Expanding Business Data Over Time 215Enhancing Dimensions 215Adding More Fact Groups 215Reflecting on Business Realities: Advanced Concepts 216Supporting Multiple Perspectives: Multiple Hierarchies 216Tracking Changes in the Dimension: Slowly Changing Dimensions 216Depicting the Existence of a Relationship: Factless Fact Tables 218Linking Parts of a Transaction: Degenerate Dimensions 219Pulling Together Components: Junk Dimensions 221Multiple Instances of a Dimension: Role Playing 222Other Notation 224Dimension Connectors 224Clusters of Future Attributes 225Notation Summary 225Taking the Model Forward 225Translating the Business Dimensional Model 226Dimension Table Design 226Translating Fact Groups 227Physical Database Design 228In Real Life 228A Glimpse into Giant Co. 229Insight from Agile, Inc. 229Chapter 8 Managing Data As a Corporate Asset 231What Is Information Management? 232Information Management Example—Customer Data 235IM Beyond the Data Warehouse 239Master Data Management 240Master Data Feeds the Data Warehouse 242Finding the Right Resources 242Data Governance 243Data Ownership 243Who Really Owns the Data? 244Your Responsibilities If You Are ‘‘the Owner’’ 246What are IT’s Responsibilities? 247Challenges with Data Ownership 247Data Quality 248Profiling the Data 249How Clean Does the Data Really Need to Be? 250Measuring Quality 250Quality of Historical Data 251Cleansing at the Source 253Cleaning Up for Reporting 254Managing the Integrity of Data Integration 254Quality Improves When It Matters 256Example: Data Quality and Grocery Checkout Scanners 257Example: Data Quality and the Evaluation of Public Education 257Realizing the Value of Data Quality 258Implementing a Data Dictionary 259The Data Dictionary Application 259Populating the Data Dictionary 261Accessing the Data Dictionary 263Maintaining the Data Dictionary 263Getting Started with Information Management 264Understanding Your Current Data Environment 264What Data Do You Have? 265What Already Exists? 266Where Do You Want to Be? 267Develop a Realistic Strategy 268Sharing the Information Management Strategy 269Setting Up a Sustainable Process 270Enterprise Commitment 270The Data Governance Committee 270Revising the Strategy 271In Real Life 271A Glimpse into Giant, Co. 272Insight from Agile, Inc. 272Summary 274Part Four Building the Project 275Chapter 9 Architecture, Infrastructure, and Tools 277What Is Architecture? 278Why Do We Need Architecture? 278Making Architecture Work 281Data Architecture 282Revisiting DW Goals 283Components of DW Data Architecture 285A Closer Look at Common Data Warehouse Architectures 286Bottom-Up Data Architecture 286Top-Down Data Architecture 290Publish the Data: Data Marts 294Adopting an Architecture 295Technical Architecture 297Technical Architecture Basics 298Components of Technical Architecture 299Infrastructure 300Technical Architecture in Action 300What You Need to Know about Technical Architecture 301Navigating the Technology Jungle 302Weighing Technology Options 303Best of Breed 303End-to-End Solutions 303Deciding Not to Buy a Tool 304Finding the Right Products 304Requests for Information or Proposals 305Business Participation in the Selection Process 305Understanding Product Genealogy 306Understanding Value and Evaluating Your Options 306Cutting through the Marketing Hype 308The Value of References 309Making Architecture Work for You 310Just-In-Time Architecture 311In Real Life 311Architecture at Giant 311Agile Ignores the Need for Architecture 312Summary 313Chapter 10 Implementation: Building the Database 315Extract, Transform, and Load (ETL) Fundamentals 315What Work Is Being Done? 315ETL System Functionality 317Extraction 318Transformation 318Load 322The Business Role in ETL 323Why Does the Business Need to Help? 323Defining Business Rules 324Defining Expected Results—The Test Plan 325Development Support 326Testing the ETL System—Is the data Right? 326Why Does It Take So Long and Cost So Much? 327Balancing Requirements and Data Reality 329Discovering the Flaws in Your Current Systems 330Applying New Business Rules 331Working Toward Long-Term Solutions 332Manually Including Business Data 333Tracking Progress—Are We There Yet? 333What Else Can You Do to Help? 334Encouragement and Support 334Ensuring Continued Business Participation 335Proactive Communication 336In Real Life 337Building the Data Warehouse at Giant, Co. 337Agile, Inc., Builds a Data Warehouse Quickly 338Summary 339Chapter 11 Data Delivery: What you Finally See 341What Is Business Intelligence? 341Business Intelligence without a DW 342BI in Action 343Tabular Reports 343Parameter-Driven Reports 343Interactive Reports—Drilling Down and Across 344Exception Reports 344Other BI Capabilities 345Complex Analysis 345BI Building Blocks 346Data Content—Understanding What You Have 346Navigation—Finding What You Need 347Presentation—How Do You Want to See Results? 347Delivery—How Do You Receive the Results? 351Supporting Different Levels of Use 352Construction of the BI Solution 354Planning for Business Change 354Design—What Needs to Be Delivered? 355Development 357Testing BI Applications and Validating Data 358Additional Responsibilities 359Security—Who Can Look at the Data? 359System Controls—Who Can Change What? 360Planning a Successful Launch 361Marketing the Solution 361Learning to Use the Data without a Technical Degree 362Learning about the Data 362Learning about the BI Tool/Application 362Ensuring That the Right Help Is Available 363In Real Life 364BI at Giant Company 364Agile, Inc. Dives into BI 365Summary 366Part Five Next Steps—Expanding On Success 367Chapter 12 Managing the Production Data Warehouse 369Finishing the Project 369Recapping the BI Application Launch 369Post-Implementation Review 370Looking Back—Did you Accomplish Your Objectives? 371Adopting the Solution 371Tracking Data Warehouse Use 372Getting the Rest of the Business Community on Board 372Business Process Change 374Changing How Data Is Used 374Streamlining Business Processes 374Encouraging Change 375The Production Data Warehouse 375Staffing Production Activities 376Maintaining the Environment 376Keeping Up with Technology 376Monitoring Performance and Capacity Planning 378Maintaining the Data Warehouse 380Maintaining the ETL System 380Maintaining the BI Application 381Tracking Questions and Problems 382Fixing Bugs 384When the Data Warehouse Falls Short 384Common Causes for a Stalled Warehouse 385Jump-Starting a Stalled Data Warehouse 388Conducting an Assessment 388Determining What Can Be Salvaged 389Developing a Plan to Move On 390Aligning DW Objectives with Business Goals 391Getting It Right This Time 392Launching the Improved Data Warehouse and BI Solution 393In Real Life 394Lack of Support for the Production DW at Giant Co. 394Unleashing BI at Agile, Inc. 395Summary 396Chapter 13 Achieving Long-Term Success 397Planning for Expansion and Growth 397Exploring Expansion Opportunities 398Prioritization of Feedback 399Managing Enterprise DW Resources 400Creating an Enterprise Data Warehouse Team 400The Centralized Enterprise Data Warehouse Team 401The Virtual Enterprise Data Warehouse Team 401Enterprise DW Team Responsibilities 403Funding the Enterprise DW Team 404Pushing into the Future 405Embedded Business Intelligence 405Operational Business Intelligence 406Real-Time Data Warehousing 407Unstructured Data 408Monitoring Industry Innovation 409Moving Toward Business Value 410Measuring Success One Step at a Time 410Adjusting Expectations to Reality 412Keeping the Momentum Going 413Celebrating Progress 416Success Can Be Attained 417Conclusion 419Glossary 421Index 429