Data Warehouse Toolkit

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.

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