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