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
Publishing Metaphor for DW/BI Managers 5
Dimensional Modeling Introduction 7
Star Schemas Versus OLAP Cubes 8
Fact Tables for Measurements 10
Dimension Tables for Descriptive Context 13
Facts and Dimensions Joined in a Star Schema 16
Kimball’s DW/BI Architecture 18
Operational Source Systems 18
Extract, Transformation, and Load System 19
Presentation Area to Support Business Intelligence 21
Business Intelligence Applications 22
Restaurant Metaphor for the Kimball Architecture 23
Alternative DW/BI Architectures 26
Independent Data Mart Architecture 26
Hub-and-Spoke Corporate Information Factory Inmon Architecture 28
Hybrid Hub-and-Spoke and Kimball Architecture 29
Dimensional Modeling Myths 30
Myth 1: Dimensional Models are Only for Summary Data 30
Myth 2: Dimensional Models are Departmental, Not Enterprise 31
Myth 3: Dimensional Models are Not Scalable 31
Myth 4: Dimensional Models are Only for Predictable Usage 31
Myth 5: Dimensional Models Can’t Be Integrated 32
More Reasons to Think Dimensionally 32
Agile Considerations 34
Summary
2 Kimball Dimensional Modeling Techniques Overview 37
Fundamental Concepts 37
Gather Business Requirements and Data Realities 37
Collaborative Dimensional Modeling Workshops 38
Four-Step Dimensional Design Process 38
Business Processes 39
Grain 39
Dimensions for Descriptive Context 40
Facts for Measurements 40
Star Schemas and OLAP Cubes 40
Graceful Extensions to Dimensional Models 41
Basic Fact Table Techniques 41
Fact Table Structure 41
Additive, Semi-Additive, Non-Additive Facts 42
Nulls in Fact Tables 42
Conformed Facts 42
Transaction Fact Tables 43
Periodic Snapshot Fact Tables 43
Accumulating Snapshot Fact Tables 44
Factless Fact Tables 44
Aggregate Fact Tables or OLAP Cubes 45
Consolidated Fact Tables 45
Basic Dimension Table Techniques 46
Dimension Table Structure 46
Dimension Surrogate Keys 46
Natural, Durable, and Supernatural Keys 46
Drilling Down 47
Degenerate Dimensions 47
Denormalized Flattened Dimensions 47
Multiple Hierarchies in Dimensions 48
Flags and Indicators as Textual Attributes 48
Null Attributes in Dimensions 48
Calendar Date Dimensions 48
Role-Playing Dimensions 49
Junk Dimensions 49
Snowflaked Dimensions 50
Outrigger Dimensions 50
Integration via Conformed Dimensions 50
Conformed Dimensions 51
Shrunken Dimensions 51
Drilling Across 51
Value Chain 52
Enterprise Data Warehouse Bus Architecture 52
Enterprise Data Warehouse Bus Matrix 52
Detailed Implementation Bus Matrix 53
Opportunity/Stakeholder Matrix 53
Dealing with Slowly Changing Dimension Attributes 53
Type 0: Retain Original 54
Type 1: Overwrite 54
Type 2: Add New Row 54
Type 3: Add New Attribute 55
Type 4: Add Mini-Dimension 55
Type 5: Add Mini-Dimension and Type 1 Outrigger 55
Type 6: Add Type 1 Attributes to Type 2 Dimension 56
Type 7: Dual Type 1 and Type 2 Dimensions 56
Dealing with Dimension Hierarchies 56
Fixed Depth Positional Hierarchies 56
Slightly Ragged/Variable Depth Hierarchies 57
Ragged/Variable Depth Hierarchies with Hierarchy Bridge Tables 57
Ragged/Variable Depth Hierarchies with Pathstring Attributes 57
Advanced Fact Table Techniques 58
Fact Table Surrogate Keys 58
Centipede Fact Tables 58
Numeric Values as Attributes or Facts 59
Lag/Duration Facts 59
Header/Line Fact Tables 59
Allocated Facts 60
Profit and Loss Fact Tables Using Allocations 60
Multiple Currency Facts 60
Multiple Units of Measure Facts 61
Year-to-Date Facts 61
Multipass SQL to Avoid Fact-to-Fact Table Joins 61
Timespan Tracking in Fact Tables 62
Late Arriving Facts 62
Advanced Dimension Techniques 62
Dimension-to-Dimension Table Joins 62
Multivalued Dimensions and Bridge Tables 63
Time Varying Multivalued Bridge Tables 63
Behavior Tag Time Series 63
Behavior Study Groups 64
Aggregated Facts as Dimension Attributes 64
Dynamic Value Bands 64
Text Comments Dimension 65
Multiple Time Zones 65
Measure Type Dimensions 65
Step Dimensions 65
Hot Swappable Dimensions 66
Abstract Generic Dimensions 66
Audit Dimensions 66
Late Arriving Dimensions 67
Special Purpose Schemas 67
Supertype and Subtype Schemas for Heterogeneous Products 67
Real-Time Fact Tables 68
Error Event Schemas 68
3 Retail Sales 69
Four-Step Dimensional Design Process 70
Step 1: Select the Business Process 70
Step 2: Declare the Grain 71
Step 3: Identify the Dimensions 72
Step 4: Identify the Facts 72
Retail Case Study 72
Step 1: Select the Business Process 74
Step 2: Declare the Grain 74
Step 3: Identify the Dimensions 76
Step 4: Identify the Facts 76
Dimension Table Details 79
Date Dimension 79
Product Dimension 83
Store Dimension 87
Promotion Dimension 89
Other Retail Sales Dimensions 92
Degenerate Dimensions for Transaction Numbers 93
Retail Schema in Action 94
Retail Schema Extensibility 95
Factless Fact Tables 97
Dimension and Fact Table Keys 98
Dimension Table Surrogate Keys 98
Dimension Natural and Durable Supernatural Keys 100
Degenerate Dimension Surrogate Keys 101
Date Dimension Smart Keys 101
Fact Table Surrogate Keys 102
Resisting Normalization Urges 104
Snowflake Schemas with Normalized Dimensions 104
Outriggers 106
Centipede Fact Tables with Too Many Dimensions 108
Summary 109
4 Inventory 111
Value Chain Introduction 111
Inventory Models 112
Inventory Periodic Snapshot 113
Inventory Transactions 116
Inventory Accumulating Snapshot 118
Fact Table Types 119
Transaction Fact Tables 120
Periodic Snapshot Fact Tables 120
Accumulating Snapshot Fact Tables 121
Complementary Fact Table Types 122
Value Chain Integration 122
Enterprise Data Warehouse Bus Architecture 123
Understanding the Bus Architecture 124
Enterprise Data Warehouse Bus Matrix 125
Conformed Dimensions 130
Drilling Across Fact Tables 130
Identical Conformed Dimensions 131
Shrunken Rollup Conformed Dimension with Attribute Subset 132
Shrunken Conformed Dimension with Row Subset 132
Shrunken Conformed Dimensions on the Bus Matrix 134
Limited Conformity 135
Importance of Data Governance and Stewardship 135
Conformed Dimensions and the Agile Movement 137
Conformed Facts 138
Summary 139
5 Procurement 141
Procurement Case Study 141
Procurement Transactions and Bus Matrix 142
Single Versus Multiple Transaction Fact Tables 143
Complementary Procurement Snapshot 147
Slowly Changing Dimension Basics 147
Type 0: Retain Original 148
Type 1: Overwrite 149
Type 2: Add New Row 150
Type 3: Add New Attribute 154
Type 4: Add Mini-Dimension 156
Hybrid Slowly Changing Dimension Techniques 159
Type 5: Mini-Dimension and Type 1 Outrigger 160
Type 6: Add Type 1 Attributes to Type 2 Dimension 160
Type 7: Dual Type 1 and Type 2 Dimensions 162
Slowly Changing Dimension Recap 164
Summary 165
6 Order Management 167
Order Management Bus Matrix 168
Order Transactions 168
Fact Normalization 169
Dimension Role Playing 170
Product Dimension Revisited 172
Customer Dimension 174
Deal Dimension 177
Degenerate Dimension for Order Number 178
Junk Dimensions 179
Header/Line Pattern to Avoid 181
Multiple Currencies 182
Transaction Facts at Different Granularity 184
Another Header/Line Pattern to Avoid 186
Invoice Transactions187
Service Level Performance as Facts, Dimensions, or Both 188
Profit and Loss Facts 189
Audit Dimension 192
Accumulating Snapshot for Order Fulfillment Pipeline 194
Lag Calculations 196
Multiple Units of Measure 197
Beyond the Rearview Mirror 198
Summary 199
7 Accounting 201
Accounting Case Study and Bus Matrix 202
General Ledger Data 203
General Ledger Periodic Snapshot 203
Chart of Accounts 203
Period Close 204
Year-to-Date Facts 206
Multiple Currencies Revisited 206
General Ledger Journal Transactions 206
Multiple Fiscal Accounting Calendars 208
Drilling Down Through a Multilevel Hierarchy 209
Financial Statements 209
Budgeting Process 210
Dimension Attribute Hierarchies 214
Fixed Depth Positional Hierarchies 214
Slightly Ragged Variable Depth Hierarchies 214
Ragged Variable Depth Hierarchies 215
Shared Ownership in a Ragged Hierarchy 219
Time Varying Ragged Hierarchies 220
Modifying Ragged Hierarchies 220
Alternative Ragged Hierarchy Modeling Approaches 221
Advantages of the Bridge Table Approach for Ragged Hierarchies 223
Consolidated Fact Tables 224
Role of OLAP and Packaged Analytic Solutions 226
Summary 227
8 Customer Relationship Management 229
CRM Overview 230
Operational and Analytic CRM 231
Customer Dimension Attributes 233
Name and Address Parsing 233
International Name and Address Considerations 236
Customer-Centric Dates 238
Aggregated Facts as Dimension Attributes 239
Segmentation Attributes and Scores 240
Counts with Type 2 Dimension Changes 243
Outrigger for Low Cardinality Attribute Set 243
Customer Hierarchy Considerations 244
Bridge Tables for Multivalued Dimensions 245
Bridge Table for Sparse Attributes 247
Bridge Table for Multiple Customer Contacts 248
Complex Customer Behavior 249
Behavior Study Groups for Cohorts 249
Step Dimension for Sequential Behavior 251
Timespan Fact Tables 252
Tagging Fact Tables with Satisfaction Indicators 254
Tagging Fact Tables with Abnormal Scenario Indicators 255
Customer Data Integration Approaches 256
Master Data Management Creating a Single Customer Dimension 256
Partial Conformity of Multiple Customer Dimensions 258
Avoiding Fact-to-Fact Table Joins 259
Low Latency Reality Check 260
Summary 261
9 Human Resources Management 263
Employee Profi le Tracking 263
Precise Effective and Expiration Timespans 265
Dimension Change Reason Tracking 266
Profi le Changes as Type 2 Attributes or Fact Events 267
Headcount Periodic Snapshot 267
Bus Matrix for HR Processes 268
Packaged Analytic Solutions and Data Models 270
Recursive Employee Hierarchies 271
Change Tracking on Embedded Manager Key 272
Drilling Up and Down Management Hierarchies 273
Multivalued Skill Keyword Attributes 274
Skill Keyword Bridge 275
Skill Keyword Text String 276
Survey Questionnaire Data 277
Text Comments 278
Summary 279
10 Financial Service 281
Banking Case Study and Bus Matrix 282
Dimension Triage to Avoid Too Few Dimensions 283
Household Dimension 286
Multivalued Dimensions and Weighting Factors 287
Mini-Dimensions Revisited 289
Adding a Mini-Dimension to a Bridge Table 290
Dynamic Value Banding of Facts 291
Supertype and Subtype Schemas for Heterogeneous Products 293
Supertype and Subtype Products with Common Facts 295
Hot Swappable Dimensions 296
Summary 296
11 Telecommunications 297
Telecommunications Case Study and Bus Matrix 297
General Design Review Considerations 299
Balance Business Requirements and Source Realities 300
Focus on Business Processes 300
Granularity 300
Single Granularity for Facts 301
Dimension Granularity and Hierarchies 301
Date Dimension 302
Degenerate Dimensions 303
Surrogate Keys 303
Dimension Decodes and Descriptions 303
Conformity Commitment 304
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
Multiple Fact Table Granularities 312
Linking Segments into Trips 315
Related Fact Tables 316
Extensions to Other Industries 317
Cargo Shipper 317
Travel Services 317
Combining Correlated Dimensions 318
Class of Service 319
Origin and Destination 320
More Date and Time Considerations 321
Country-Specific Calendars as Outriggers 321
Date and Time in Multiple Time Zones 323
Localization Recap 324
Summary 324
13 Education 325
University Case Study and Bus Matrix 325
Accumulating Snapshot Fact Tables 326
Applicant Pipeline 326
Research Grant Proposal Pipeline 329
Factless Fact Tables 329
Admissions Events 330
Course Registrations 330
Facility Utilization 334
Student Attendance 335
More Educational Analytic Opportunities 336
Summary 336
14 Healthcare 339
Healthcare Case Study and Bus Matrix 339
Claims Billing and Payments 342
Date Dimension Role Playing 345
Multivalued Diagnoses 345
Supertypes and Subtypes for Charges 347
Electronic Medical Records 348
Measure Type Dimension for Sparse Facts 349
Freeform Text Comments 350
Images 350
Facility/Equipment Inventory Utilization 351
Dealing with Retroactive Changes 351
Summary 352
15 Electronic Commerce 353
Clickstream Source Data 353
Clickstream Data Challenges 354
Clickstream Dimensional Models 357
Page Dimension 358
Event Dimension 359
Session Dimension 359
Referral Dimension 360
Clickstream Session Fact Table 361
Clickstream Page Event Fact Table 363
Step Dimension 366
Aggregate Clickstream Fact Tables 366
Google Analytics 367
Integrating Clickstream into Web Retailer’s Bus Matrix 368
Profitability Across Channels Including Web 370
Summary 373
16 Insurance 375
Insurance Case Study 376
Insurance Value Chain 377
Draft Bus Matrix 378
Policy Transactions 379
Dimension Role Playing 380
Slowly Changing Dimensions 380
Mini-Dimensions for Large or Rapidly Changing Dimensions 381
Multivalued Dimension Attributes 382
Numeric Attributes as Facts or Dimensions 382
Degenerate Dimension 383
Low Cardinality Dimension Tables 383
Audit Dimension 383
Policy Transaction Fact Table 383
Heterogeneous Supertype and Subtype Products 384
Complementary Policy Accumulating Snapshot 384
Premium Periodic Snapshot 385
Conformed Dimensions 386
Conformed Facts 386
Pay-in-Advance Facts 386
Heterogeneous Supertypes and Subtypes Revisited 387
Multivalued Dimensions Revisited 388
More Insurance Case Study Background 388
Updated Insurance Bus Matrix 389
Detailed Implementation Bus Matrix 390
Claim Transactions 390
Transaction Versus Profile Junk Dimensions 392
Claim Accumulating Snapshot 392
Accumulating Snapshot for Complex Workflows 393
Timespan Accumulating Snapshot 394
Periodic Instead of Accumulating Snapshot 395
Policy/Claim Consolidated Periodic Snapshot 395
Factless Accident Events 396
Common Dimensional Modeling Mistakes to Avoid 397
Mistake 10: Place Text Attributes in a Fact Table 397
Mistake 9: Limit Verbose Descriptors to Save Space 398
Mistake 8: Split Hierarchies into Multiple Dimensions 398
Mistake 7: Ignore the Need to Track Dimension Changes 398
Mistake 6: Solve All Performance Problems with More Hardware 399
Mistake 5: Use Operational Keys to Join Dimensions and Facts 399
Mistake 4: Neglect to Declare and Comply with the Fact Grain 399
Mistake 3: Use a Report to Design the Dimensional Model 400
Mistake 2: Expect Users to Query Normalized Atomic Data 400
Mistake 1: Fail to Conform Facts and Dimensions 400
Summary 401
17 Kimball DW/BI Lifecycle Overview 403
Lifecycle Roadmap 404
Roadmap Mile Markers 405
Lifecycle Launch Activities 406
Program/Project Planning and Management 406
Business Requirements Definition 410
Lifecycle Technology Track 416
Technical Architecture Design 416
Product Selection and Installation 418
Lifecycle Data Track 420
Dimensional Modeling 420
Physical Design 420
ETL Design and Development 422
Lifecycle BI Applications Track 422
BI Application Specification 423
BI Application Development 423
Lifecycle Wrap-up Activities 424
Deployment 424
Maintenance and Growth 425
Common Pitfalls to Avoid 426
Summary 427
18 Dimensional Modeling Process and Tasks 429
Modeling Process Overview 429
Get Organized 431
Identify Participants, Especially Business Representatives 431
Review the Business Requirements 432
Leverage a Modeling Tool 432
Leverage a Data Profiling Tool 433
Leverage or Establish Naming Conventions 433
Coordinate Calendars and Facilities 433
Design the Dimensional Model 434
Reach Consensus on High-Level Bubble Chart 435
Develop the Detailed Dimensional Model 436
Review and Validate the Model 439
Finalize the Design Documentation 441
Summary 441
19 ETL Subsystems and Techniques 443
Round Up the Requirements 444
Business Needs 444
Compliance 445
Data Quality 445
Security 446
Data Integration 446
Data Latency 447
Archiving and Lineage 447
BI Delivery Interfaces 448
Available Skills 448
Legacy Licenses 449
The 34 Subsystems of ETL 449
Extracting: Getting Data into the Data Warehouse 450
Subsystem 1: Data Profiling 450
Subsystem 2: Change Data Capture System 451
Subsystem 3: Extract System 453
Cleaning and Conforming Data 455
Improving Data Quality Culture and Processes 455
Subsystem 4: Data Cleansing System 456
Subsystem 5: Error Event Schema 458
Subsystem 6: Audit Dimension Assembler 460
Subsystem 7: Deduplication System 460
Subsystem 8: Conforming System 461
Delivering: Prepare for Presentation 463
Subsystem 9: Slowly Changing Dimension Manager 464
Subsystem 10: Surrogate Key Generator 469
Subsystem 11: Hierarchy Manager 470
Subsystem 12: Special Dimensions Manager 470
Subsystem 13: Fact Table Builders 473
Subsystem 14: Surrogate Key Pipeline 475
Subsystem 15: Multivalued Dimension Bridge Table Builder 477
Subsystem 16: Late Arriving Data Handler 478
Subsystem 17: Dimension Manager System 479
Subsystem 18: Fact Provider System 480
Subsystem 19: Aggregate Builder 481
Subsystem 20: OLAP Cube Builder 481
Subsystem 21: Data Propagation Manager 482
Managing the ETL Environment 483
Subsystem 22: Job Scheduler 483
Subsystem 23: Backup System 485
Subsystem 24: Recovery and Restart System 486
Subsystem 25: Version Control System 488
Subsystem 26: Version Migration System 488
Subsystem 27: Workflow Monitor 489
Subsystem 28: Sorting System 490
Subsystem 29: Lineage and Dependency Analyzer 490
Subsystem 30: Problem Escalation System 491
Subsystem 31: Parallelizing/Pipelining System 492
Subsystem 32: Security System 492
Subsystem 33: Compliance Manager 493
Subsystem 34: Metadata Repository Manager 495
Summary 496
20 ETL System Design and Development Process and Tasks 497
ETL Process Overview 497
Develop the ETL Plan 498
Step 1: Draw the High-Level Plan 498
Step 2: Choose an ETL Tool 499
Step 3: Develop Default Strategies 500
Step 4: Drill Down by Target Table 500
Develop the ETL Specification Document 502
Develop One-Time Historic Load Processing 503
Step 5: Populate Dimension Tables with Historic Data 503
Step 6: Perform the Fact Table Historic Load 508
Develop Incremental ETL Processing 512
Step 7: Dimension Table Incremental Processing 512
Step 8: Fact Table Incremental Processing 515
Step 9: Aggregate Table and OLAP Loads 519
Step 10: ETL System Operation and Automation 519
Real-Time Implications 520
Real-Time Triage 521
Real-Time Architecture Trade-Offs 522
Real-Time Partitions in the Presentation Server 524
Summary 526
21 Big Data Analytics 527
Big Data Overview 527
Extended RDBMS Architecture 529
MapReduce/Hadoop Architecture 530
Comparison of Big Data Architectures 530
Recommended Best Practices for Big Data 531
Management Best Practices for Big Data 531
Architecture Best Practices for Big Data 533
Data Modeling Best Practices for Big Data 538
Data Governance Best Practices for Big Data 541
Summary 542
Index 543
