Introduction xxvii
1 Data Warehousing, Business Intelligence, and Dimensional Modeling Primer 1
Different Worlds of Data Capture and Data Analysis 2
Goals of Data Warehousing and Business Intelligence 3
Dimensional Modeling Introduction 7
Kimball's DW/BI Architecture 18
Alternative DW/BI Architectures 26
Dimensional Modeling Myths 30
More Reasons to Think Dimensionally 32
Agile Considerations 34
Summary 35
2 Kimball Dimensional Modeling Techniques Overview 37
Fundamental Concepts 37
Basic Fact Table Techniques 41
Basic Dimension Table Techniques 46
Integration via Conformed Dimensions 50
Dealing with Slowly Changing Dimension Attributes 53
Dealing with Dimension Hierarchies 56
Advanced Fact Table Techniques 58
Advanced Dimension Techniques 62
Special Purpose Schemas 67
3 Retail Sales 69
Four-Step Dimensional Design Process 70
Retail Case Study 72
Dimension Table Details 79
Retail Schema in Action 94
Retail Schema Extensibility 95
Factless Fact Tables 97
Dimension and Fact Table Keys 98
Resisting Normalization Urges 104
Summary 109
4 Inventory 111
Value Chain Introduction 111
Inventory Models 112
Fact Table Types 119
Value Chain Integration 122
Enterprise Data Warehouse Bus Architecture 123
Conformed Dimensions 130
Conformed Facts 138
Summary 139
5 Procurement 141
Procurement Case Study 141
Procurement Transactions and Bus Matrix 142
Slowly Changing Dimension Basics 147
Hybrid Slowly Changing Dimension Techniques 159
Slowly Changing Dimension Recap 164
Summary 165
6 Order Management 167
Order Management Bus Matrix 168
Order Transactions 168
Invoice Transactions 187
Accumulating Snapshot for Order Fulfillment Pipeline 194
Summary 199
7 Accounting 201
Accounting Case Study and Bus Matrix 202
General Ledger Data 203
Budgeting Process 210
Dimension Attribute Hierarchies 214
Consolidated Fact Tables 224
Role of OLAP and Packaged Analytic Solutions 226
Summary 227
8 Customer Relationship Management 229
CRM Overview 230
Customer Dimension Attributes 233
Bridge Tables for Multivalued Dimensions 245
Complex Customer Behavior 249
Customer Data Integration Approaches 256
Low Latency Reality Check 260
Summary 261
9 Human Resources Management 263
Employee Profile Tracking 263
Headcount Periodic Snapshot 267
Bus Matrix for HR Processes 268
Packaged Analytic Solutions and Data Models 270
Recursive Employee Hierarchies 271
Multivalued Skill Keyword Attributes 274
Survey Questionnaire Data 277
Summary 279
10 Financial Services 281
Banking Case Study and Bus Matrix 282
Dimension Triage to Avoid Too Few Dimensions 283
Supertype and Subtype Schemas for Heterogeneous Products 293
Hot Swappable Dimensions 296
Summary 296
11 Telecommunications 297
Telecommunications Case Study and Bus Matrix 297
General Design Review Considerations 299
Design Review Guidelines 304
Draft Design Exercise Discussion 306
Remodeling Existing Data Structures 309
Geographic Location Dimension 310
Summary 310
12 Transportation 311
Airline Case Study and Bus Matrix 311
Extensions to Other Industries 317
Combining Correlated Dimensions 318
More Date and Time Considerations 321
Localization Recap 324
Summary 324
13 Education 325
University Case Study and Bus Matrix 325
Accumulating Snapshot Fact Tables 326
Factless Fact Tables 329
More Educational Analytic Opportunities 336
Summary 336
14 Healthcare 339
Healthcare Case Study and Bus Matrix 339
Claims Billing and Payments 342
Electronic Medical Records 348
Facility/Equipment Inventory Utilization 351
Dealing with Retroactive Changes 351
Summary 352
15 Electronic Commerce 353
Clickstream Source Data 353
Clickstream Dimensional Models 357
Integrating Clickstream into Web Retailer's Bus Matrix 368
Profitability Across Channels Including Web 370
Summary 373
16 Insurance 375
Insurance Case Study 376
Policy Transactions 379
Premium Periodic Snapshot 385
More Insurance Case Study Background 388
Claim Transactions 390
Claim Accumulating Snapshot 392
Policy/Claim Consolidated Periodic Snapshot 395
Factless Accident Events 396
Common Dimensional Modeling Mistakes to Avoid 397
Summary 401
17 Kimball DW/BI Lifecycle Overview 403
Lifecycle Roadmap 404
Lifecycle Launch Activities 406
Lifecycle Technology Track 416
Lifecycle Data Track 420
Lifecycle BI Applications Track 422
Lifecycle Wrap-up Activities 424
Common Pitfalls to Avoid 426
Summary 427
18 Dimensional Modeling Process and Tasks 429
Modeling Process Overview 429
Get Organized 431
Design the Dimensional Model 434
Summary 441
19 ETL Subsystems and Techniques 443
Round Up the Requirements 444
The 34 Subsystems of ETL 449
Extracting: Getting Data into the Data Warehouse 450
Cleaning and Conforming Data 455
Delivering: Prepare for Presentation 463
Managing the ETL Environment 483
Summary 496
20 ETL System Design and Development Process and Tasks 497
ETL Process Overview 497
Develop the ETL Plan 498
Develop One-Time Historic Load Processing 503
Develop Incremental ETL Processing 512
Real-Time Implications 520
Summary 526
21 Big Data Analytics 527
Big Data Overview 527
Recommended Best Practices for Big Data 531
Summary 542
Index 543
