The Data Warehouse Toolkit (3^rd Ed.)
The Definitive Guide to Dimensional Modeling

Updated new edition of Ralph Kimball's groundbreaking book on dimensional modeling for data warehousing and business intelligence!

The first edition of Ralph Kimball's The Data Warehouse Toolkit introduced the industry to dimensional modeling, and now his books are considered the most authoritative guides in this space. This new third edition is a complete library of updated dimensional modeling techniques, the most comprehensive collection ever. It covers new and enhanced star schema dimensional modeling patterns, adds two new chapters on ETL techniques, includes new and expanded business matrices for 12 case studies, and more.

• Authored by Ralph Kimball and Margy Ross, known worldwide as educators, consultants, and influential thought leaders in data warehousing and business intelligence
• Begins with fundamental design recommendations and progresses through increasingly complex scenarios
• Presents unique modeling techniques for business applications such as inventory management, procurement, invoicing, accounting, customer relationship management, big data analytics, and more
• Draws real-world case studies from a variety of industries, including retail sales, financial services, telecommunications, education, health care, insurance, e-commerce, and more

Design dimensional databases that are easy to understand and provide fast query response with The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition.

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

RALPH KIMBALL, PhD, has been a leading visionary in the data warehouse and business intelligence industry since 1982. The Data Warehouse Toolkit book series have been bestsellers since 1996.

MARGY ROSS is President of the Kimball Group and the coauthor of five Toolkit books with Ralph Kimball. She has focused exclusively on data warehousing and business intelligence for more than 30 years.