Data Warehousing Fundamentals for IT Professionals, Second Edition

DATA WAREHOUSING FUNDAMENTALS FOR IT PROFESSIONALS......Page 4
CONTENTS......Page 10
PREFACE......Page 28
PART 1 OVERVIEW AND CONCEPTS......Page 32
CHAPTER OBJECTIVES......Page 34
ESCALATING NEED FOR STRATEGIC INFORMATION......Page 35
Technology Trends......Page 37
Opportunities and Risks......Page 39
FAILURES OF PAST DECISION-SUPPORT SYSTEMS......Page 40
Inability to Provide Information......Page 41
OPERATIONAL VERSUS DECISION-SUPPORT SYSTEMS......Page 42
Different Scope, Different Purposes......Page 43
A New Type of System Environment......Page 44
Strategic Information from the Data Warehouse......Page 45
An Environment, Not a Product......Page 46
A Blend of Many Technologies......Page 47
Data Warehousing Milestones......Page 48
EVOLUTION OF BUSINESS INTELLIGENCE......Page 49
BI: Data Warehousing and Analytics......Page 50
REVIEW QUESTIONS......Page 51
EXERCISES......Page 52
CHAPTER OBJECTIVES......Page 54
Subject-Oriented Data......Page 55
Integrated Data......Page 56
Time-Variant Data......Page 57
Nonvolatile Data......Page 58
Data Granularity......Page 59
Top-Down Versus Bottom-Up Approach......Page 60
A Practical Approach......Page 62
Independent Data Marts......Page 63
Hub-and-Spoke......Page 64
Source Data Component......Page 65
Data Staging Component......Page 68
Data Storage Component......Page 70
Information Delivery Component......Page 71
METADATA IN THE DATA WAREHOUSE......Page 72
CHAPTER SUMMARY......Page 73
EXERCISES......Page 74
CHAPTER OBJECTIVES......Page 76
Data Warehousing has Become Mainstream......Page 77
Data Warehouse Expansion......Page 78
Vendor Solutions and Products......Page 79
Multiple Data Types......Page 81
Data Visualization......Page 83
Parallel Processing......Page 85
Query Tools......Page 87
Data Fusion......Page 88
Data Integration......Page 89
Agent Technology......Page 90
Data Warehousing and ERP......Page 91
Data Warehousing and KM......Page 92
Agile Development......Page 94
EMERGENCE OF STANDARDS......Page 95
OLAP......Page 96
WEB-ENABLED DATA WAREHOUSE......Page 97
The Web to the Warehouse......Page 98
REVIEW QUESTIONS......Page 100
EXERCISES......Page 101
PART 2 PLANNING AND REQUIREMENTS......Page 102
CHAPTER OBJECTIVES......Page 104
Key Issues......Page 105
Business Requirements, Not Technology......Page 107
Justifying Your Data Warehouse......Page 108
The Overall Plan......Page 109
How is it Different?......Page 110
The Life-Cycle Approach......Page 112
THE DEVELOPMENT PHASES......Page 114
Adopting Agile Development......Page 115
Organizing the Project Team......Page 116
Roles and Responsibilities......Page 117
Skills and Experience Levels......Page 118
User Participation......Page 119
PROJECT MANAGEMENT CONSIDERATIONS......Page 121
Guiding Principles......Page 122
Success Factors......Page 123
Anatomy of a Successful Project......Page 124
Adopt a Practical Approach......Page 125
REVIEW QUESTIONS......Page 127
EXERCISES......Page 128
CHAPTER OBJECTIVES......Page 130
Usage of Information Unpredictable......Page 131
Dimensional Nature of Business Data......Page 132
Examples of Business Dimensions......Page 133
INFORMATION PACKAGES—A USEFUL CONCEPT......Page 134
Requirements Not Fully Determinate......Page 135
Business Dimensions......Page 136
Dimension Hierarchies and Categories......Page 137
Key Business Metrics or Facts......Page 138
REQUIREMENTS GATHERING METHODS......Page 140
Types of Questions......Page 141
Interview Techniques......Page 142
Adapting the JAD Methodology......Page 144
Review of Existing Documentation......Page 146
REQUIREMENTS DEFINITION: SCOPE AND CONTENT......Page 147
Data Storage......Page 148
Requirements Definition Document Outline......Page 149
REVIEW QUESTIONS......Page 150
EXERCISES......Page 151
CHAPTER OBJECTIVES......Page 152
DATA DESIGN......Page 153
Structure for Business Dimensions......Page 154
Structure for Key Measurements......Page 155
THE ARCHITECTURAL PLAN......Page 156
Composition of the Components......Page 157
Special Considerations......Page 158
Tools and Products......Page 160
DATA STORAGE SPECIFICATIONS......Page 162
Storage Sizing......Page 163
INFORMATION DELIVERY STRATEGY......Page 164
Types of Analysis......Page 165
Decision Support Applications......Page 166
REVIEW QUESTIONS......Page 167
EXERCISES......Page 168
PART 3 ARCHITECTURE AND INFRASTRUCTURE......Page 170
UNDERSTANDING DATA WAREHOUSE ARCHITECTURE......Page 172
Architecture in Three Major Areas......Page 173
DISTINGUISHING CHARACTERISTICS......Page 174
Data Content......Page 175
Flexible and Dynamic......Page 176
Architecture Supporting Flow of Data......Page 177
The Management and Control Module......Page 178
TECHNICAL ARCHITECTURE......Page 179
Data Acquisition......Page 180
Data Storage......Page 183
Information Delivery......Page 185
Independent Data Marts......Page 187
Hub-and-Spoke......Page 190
REVIEW QUESTIONS......Page 191
EXERCISES......Page 192
CHAPTER OBJECTIVES......Page 194
INFRASTRUCTURE SUPPORTING ARCHITECTURE......Page 195
Physical Infrastructure......Page 196
HARDWARE AND OPERATING SYSTEMS......Page 197
Mainframes......Page 198
Platform Options......Page 199
Server Hardware......Page 208
DATABASE SOFTWARE......Page 212
Parallel Processing Options......Page 213
COLLECTION OF TOOLS......Page 215
Data Modeling......Page 217
Scorecards......Page 218
DATA WAREHOUSE APPLIANCES......Page 219
Evolution of DW Appliances......Page 220
Benefits of DW Appliances......Page 221
REVIEW QUESTIONS......Page 222
EXERCISES......Page 223
WHY METADATA IS IMPORTANT......Page 224
A Critical Need in the Data Warehouse......Page 226
Why Metadata Is Vital for End-Users......Page 229
Why Metadata Is Essential for IT......Page 230
Automation of Warehousing Tasks......Page 231
Establishing the Context of Information......Page 233
METADATA TYPES BY FUNCTIONAL AREAS......Page 234
Data Acquisition......Page 235
Data Storage......Page 236
Information Delivery......Page 237
Content Overview......Page 238
Examples of Business Metadata......Page 239
TECHNICAL METADATA......Page 240
Examples of Technical Metadata......Page 241
Who Benefits?......Page 242
Metadata Requirements......Page 243
Sources of Metadata......Page 245
Metadata Repository......Page 246
Metadata Integration and Standards......Page 248
Implementation Options......Page 249
CHAPTER SUMMARY......Page 250
EXERCISES......Page 251
PART 4 DATA DESIGN AND DATA PREPARATION......Page 254
FROM REQUIREMENTS TO DATA DESIGN......Page 256
Dimensional Modeling Basics......Page 257
E-R Modeling Versus Dimensional Modeling......Page 261
Review of a Simple STAR Schema......Page 263
Inside a Dimension Table......Page 265
Inside the Fact Table......Page 267
Data Granularity......Page 269
Primary Keys......Page 270
Foreign Keys......Page 271
Easy for Users to Understand......Page 272
Optimizes Navigation......Page 273
Most Suitable for Query Processing......Page 274
Auction Company......Page 275
CHAPTER SUMMARY......Page 277
EXERCISES......Page 278
CHAPTER OBJECTIVES......Page 280
Slowly Changing Dimensions......Page 281
Type 1 Changes: Correction of Errors......Page 282
Type 2 Changes: Preservation of History......Page 283
Type 3 Changes: Tentative Soft Revisions......Page 284
Large Dimensions......Page 286
Rapidly Changing Dimensions......Page 287
Junk Dimensions......Page 289
Options to Normalize......Page 290
Advantages and Disadvantages......Page 291
AGGREGATE FACT TABLES......Page 293
Fact Table Sizes......Page 295
Aggregating Fact Tables......Page 297
Aggregation Options......Page 302
FAMILIES OF STARS......Page 303
Snapshot and Transaction Tables......Page 304
Supporting Enterprise Value Chain or Value Circle......Page 305
Conforming Dimensions......Page 306
Standardizing Facts......Page 307
CHAPTER SUMMARY......Page 308
EXERCISES......Page 309
CHAPTER OBJECTIVES......Page 312
Most Important and Most Challenging......Page 313
Time Consuming and Arduous......Page 314
ETL REQUIREMENTS AND STEPS......Page 315
Key Factors......Page 316
DATA EXTRACTION......Page 317
Data Extraction Techniques......Page 318
Evaluation of the Techniques......Page 325
DATA TRANSFORMATION......Page 326
Data Transformation: Basic Tasks......Page 327
Major Transformation Types......Page 328
Data Integration and Consolidation......Page 330
How to Implement Transformation......Page 332
DATA LOADING......Page 333
Applying Data: Techniques and Processes......Page 334
Procedure for Dimension Tables......Page 337
Fact Tables: History and Incremental Loads......Page 338
ETL Tool Options......Page 339
Reemphasizing ETL Metadata......Page 340
ETL Summary and Approach......Page 341
Enterprise Information Integration (EII)......Page 342
Enterprise Application Integration (EAI)......Page 343
REVIEW QUESTIONS......Page 344
EXERCISES......Page 345
CHAPTER OBJECTIVES......Page 346
What Is Data Quality?......Page 347
Benefits of Improved Data Quality......Page 350
Types of Data Quality Problems......Page 351
Sources of Data Pollution......Page 354
Costs of Poor Data Quality......Page 356
DATA QUALITY TOOLS......Page 357
The DBMS for Quality Control......Page 358
DATA QUALITY INITIATIVE......Page 359
Data Cleansing Decisions......Page 360
Who Should Be Responsible?......Page 361
The Purification Process......Page 364
Practical Tips on Data Quality......Page 365
MDM Benefits......Page 366
REVIEW QUESTIONS......Page 367
EXERCISES......Page 368
PART 5 INFORMATION ACCESS AND DELIVERY......Page 370
CHAPTER OBJECTIVES......Page 372
Data Warehouse Versus Operational Systems......Page 373
Information Potential......Page 375
User–Information Interface......Page 378
Industry Applications......Page 379
Classes of Users......Page 380
What They Need......Page 383
How to Provide Information......Page 385
INFORMATION DELIVERY......Page 387
Queries......Page 388
Reports......Page 389
Applications......Page 390
The Desktop Environment......Page 391
Methodology for Tool Selection......Page 392
Tool Selection Criteria......Page 395
Information Delivery Framework......Page 396
Business Activity Monitoring (BAM)......Page 397
Dashboards and Scorecards......Page 398
REVIEW QUESTIONS......Page 402
EXERCISES......Page 403
CHAPTER OBJECTIVES......Page 404
Need for Multidimensional Analysis......Page 405
Fast Access and Powerful Calculations......Page 406
Limitations of Other Analysis Methods......Page 408
OLAP Definitions and Rules......Page 410
MAJOR FEATURES AND FUNCTIONS......Page 413
Dimensional Analysis......Page 414
What Are Hypercubes?......Page 417
Drill Down and Roll Up......Page 421
Slice and Dice or Rotation......Page 423
OLAP MODELS......Page 424
The MOLAP Model......Page 425
The ROLAP Model......Page 426
ROLAP Versus MOLAP......Page 428
OLAP IMPLEMENTATION CONSIDERATIONS......Page 429
Data Design and Preparation......Page 430
Administration and Performance......Page 432
OLAP Tools and Products......Page 433
Implementation Steps......Page 434
CHAPTER SUMMARY......Page 435
EXERCISES......Page 436
CHAPTER OBJECTIVES......Page 438
Why the Web?......Page 439
Convergence of Technologies......Page 441
Adapting the Data Warehouse for the Web......Page 442
The Web as a Data Source......Page 443
Clickstream Analysis......Page 444
Expanded Usage......Page 445
New Information Strategies......Page 447
Browser Technology for the Data Warehouse......Page 449
Security Issues......Page 450
Web-OLAP Approaches......Page 451
BUILDING A WEB-ENABLED DATA WAREHOUSE......Page 452
Nature of the Data Webhouse......Page 453
Implementation Considerations......Page 454
Putting the Pieces Together......Page 455
REVIEW QUESTIONS......Page 457
EXERCISES......Page 458
CHAPTER OBJECTIVES......Page 460
WHAT IS DATA MINING?......Page 461
Data Mining Defined......Page 462
The Knowledge Discovery Process......Page 463
OLAP Versus Data Mining......Page 466
Some Aspects of Data Mining......Page 467
Data Mining and the Data Warehouse......Page 469
MAJOR DATA MINING TECHNIQUES......Page 470
Cluster Detection......Page 471
Decision Trees......Page 474
Memory-Based Reasoning......Page 475
Link Analysis......Page 476
Neural Networks......Page 478
Genetic Algorithms......Page 479
Moving into Data Mining......Page 481
DATA MINING APPLICATIONS......Page 483
Benefits of Data Mining......Page 484
Applications in CRM (Customer Relationship Management)......Page 485
Applications in the Retail Industry......Page 486
Applications in the Telecommunications Industry......Page 487
Applications in Biotechnology......Page 488
REVIEW QUESTIONS......Page 490
EXERCISES......Page 491
PART 6 IMPLEMENTATION AND MAINTENANCE......Page 492
CHAPTER OBJECTIVES......Page 494
Develop Standards......Page 495
Determine the Data Partitioning Scheme......Page 496
Assign Storage Structures......Page 497
Physical Design Objectives......Page 498
Physical Model Components......Page 500
Significance of Standards......Page 501
Optimizing Storage......Page 504
Using RAID Technology......Page 507
Indexing Overview......Page 508
B-Tree Index......Page 510
Bitmapped Index......Page 512
Indexing the Fact Table......Page 513
Data Partitioning......Page 514
Parallel Processing......Page 515
Initialization Parameters......Page 516
REVIEW QUESTIONS......Page 517
EXERCISES......Page 518
CHAPTER OBJECTIVES......Page 520
ETL Testing......Page 521
Complete User Acceptance......Page 522
Perform Initial Loads......Page 523
Get User Desktops Ready......Page 524
Complete Initial User Training......Page 525
Deploy in Stages......Page 526
When is a Pilot Data Mart Useful?......Page 528
Types of Pilot Projects......Page 529
Choosing the Pilot......Page 531
Expanding and Integrating the Pilot......Page 532
Managing User Privileges......Page 533
Password Considerations......Page 534
BACKUP AND RECOVERY......Page 535
Backup Strategy......Page 536
Setting up a Practical Schedule......Page 537
Recovery......Page 538
REVIEW QUESTIONS......Page 539
EXERCISES......Page 540
CHAPTER OBJECTIVES......Page 542
Collection of Statistics......Page 543
Using Statistics for Fine-Tuning......Page 545
USER TRAINING AND SUPPORT......Page 546
Preparing the Training Program......Page 547
Delivering the Training Program......Page 549
User Support......Page 550
MANAGING THE DATA WAREHOUSE......Page 551
Managing Data Growth......Page 552
ETL Management......Page 553
Information Delivery Enhancements......Page 554
CHAPTER SUMMARY......Page 555
EXERCISES......Page 556
ANSWERS TO SELECTED EXERCISES......Page 558
APPENDIX A: PROJECT LIFE CYCLE STEPS AND CHECKLISTS......Page 562
APPENDIX B: CRITICAL FACTORS FOR SUCCESS......Page 566
APPENDIX C: GUIDELINES FOR EVALUATING VENDOR SOLUTIONS......Page 568
APPENDIX D: HIGHLIGHTS OF VENDORS AND PRODUCTS......Page 570
APPENDIX E: REAL-WORLD EXAMPLES OF BEST PRACTICES......Page 580
REFERENCES......Page 586
GLOSSARY......Page 588
INDEX......Page 596