Kimball Group Reader

Ralph Kimball, PhD, founded the Kimball Group and is a leading visionary in the data warehousing industry. Margy Ross, President of the Kimball Group and DecisionWorks Consulting, has focused on DW/BI solutions since 1982.

• Introduction xxv1 The Reader at a Glance 1Setting Up for Success 11.1 Resist the Urge to Start Coding 11.2 Set Your Boundaries 4Tackling DW/BI Design and Development 61.3 Data Wrangling 61.4 Myth Busters 91.5 Dividing the World 101.6 Essential Steps for the Integrated Enterprise Data Warehouse 131.7 Drill Down to Ask Why 221.8 Slowly Changing Dimensions 251.9 Judge Your BI Tool through Your Dimensions 281.10 Fact Tables 311.11 Exploit Your Fact Tables 332 Before You Dive In 35Before Data Warehousing 352.1 History Lesson on Ralph Kimball and Xerox PARC 36Historical Perspective 372.2 The Database Market Splits 372.3 Bringing Up Supermarts 40Dealing with Demanding Realities 472.4 Brave New Requirements for Data Warehousing 472.5 Coping with the Brave New Requirements 522.6 Stirring Things Up 572.7 Design Constraints and Unavoidable Realities 602.8 Two Powerful Ideas 642.9 Data Warehouse Dining Experience 672.10 Easier Approaches for Harder Problems 702.11 Expanding Boundaries of the Data Warehouse 723 Project/Program Planning 75Professional Responsibilities 753.1 Professional Boundaries 753.2 An Engineer’s View 783.3 Beware the Objection Removers 823.4 What Does the Central Team Do? 863.5 Avoid Isolating DW and BI Teams 903.6 Better Business Skills for BI and Data Warehouse Professionals 913.7 Risky Project Resources are Risky Business 933.8 Implementation Analysis Paralysis 953.9 Contain DW/BI Scope Creep and Avoid Scope Theft 963.10 Are IT Procedures Beneficial to DW/BI Projects? 98Justification and Sponsorship 1003.11 Habits of Effective Sponsors 1003.12 TCO Starts with the End User 103Kimball Methodology 1083.13 Kimball Lifecycle in a Nutshell 1083.14 Off the Bench1113.15 The Anti-Architect1123.16 Think Critically When Applying Best Practices 1153.17 Eight Guidelines for Low Risk Enterprise Data Warehousing 1184 Requirements Definition 123Gathering Requirements 1234.1 Alan Alda’s Interviewing Tips for Uncovering Business Requirements 1234.2 More Business Requirements Gathering Dos and Don’ts 1274.3 Balancing Requirements and Realities 1294.4 Overcoming Obstacles When Gathering Business Requirements 1304.5 Surprising Value of Data Profiling 133Organizing around Business Processes 1344.6 Focus on Business Processes, Not Business Departments! 1344.7 Identifying Business Processes 1354.8 Business Process Decoder Ring 1374.9 Relationship between Strategic Business Initiatives and Business Processes 138Wrapping Up the Requirements 1394.10 The Bottom-Up Misnomer 1404.11 Think Dimensionally (Beyond Data Modeling) 1444.12 Using the Dimensional Model to Validate Business Requirements 1455 Data Architecture 147Making the Case for Dimensional Modeling 1475.1 Is ER Modeling Hazardous to DSS? 1475.2 A Dimensional Modeling Manifesto 1515.3 There are No Guarantees 159Enterprise Data Warehouse Bus Architecture 1635.4 Divide and Conquer 1635.5 The Matrix 1665.6 The Matrix: Revisited 1705.7 Drill Down into a Detailed Bus Matrix 174Agile Project Considerations 1765.8 Relating to Agile Methodologies 1765.9 Is Agile Enterprise Data Warehousing an Oxymoron? 1775.10 Going Agile? Start with the Bus Matrix 1795.11 Conformed Dimensions as the Foundation for Agile Data Warehousing 180Integration Instead of Centralization 1815.12 Integration for Real People 1815.13 Build a Ready-to-Go Resource for Enterprise Dimensions 1855.14 Data Stewardship 101: The First Step to Quality and Consistency 1865.15 To Be or Not To Be Centralized 189Contrast with the Corporate Information Factory 1925.16 Differences of Opinion 1935.17 Much Ado about Nothing 1985.18 Don’t Support Business Intelligence with a Normalized EDW 1995.19 Complementing 3NF EDWs with Dimensional Presentation Areas 2016 Dimensional Modeling Fundamentals 203Basics of Dimensional Modeling 2036.1 Fact Tables and Dimension Tables 2036.2 Drilling Down, Up, and Across 2076.3 The Soul of the Data Warehouse, Part One: Drilling Down 2106.4 The Soul of the Data Warehouse, Part Two: Drilling Across 2136.5 The Soul of the Data Warehouse, Part Three: Handling Time 2166.6 Graceful Modifications to Existing Fact and Dimension Tables 219Dos and Don’ts 2206.7 Kimball’s Ten Essential Rules of Dimensional Modeling 2216.8 What Not to Do 223Myths about Dimensional Modeling 2266.9 Dangerous Preconceptions 2266.10 Fables and Facts 2287 Dimensional Modeling Tasks and Responsibilities 233Design Activities 2337.1 Letting the Users Sleep 2337.2 Practical Steps for Designing a Dimensional Model 2407.3 Staffing the Dimensional Modeling Team 2437.4 Involve Business Representatives in Dimensional Modeling 2447.5 Managing Large Dimensional Design Teams 2467.6 Use a Design Charter to Keep Dimensional Modeling Activities on Track 2487.7 The Naming Game 2497.8 What’s in a Name? 2507.9 When is the Dimensional Design Done? 253Design Review Activities 2547.10 Design Review Dos and Don’ts 2557.11 Fistful of Flaws 2577.12 Rating Your Dimensional Data Warehouse 2608 Fact Table Core Concepts 267Granularity 2678.1 Declaring the Grain 2678.2 Keep to the Grain in Dimensional Modeling 2708.3 Warning: Summary Data May Be Hazardous to Your Health 2728.4 No Detail Too Small 273Types of Fact Tables 2768.5 Fundamental Grains 2778.6 Modeling a Pipeline with an Accumulating Snapshot 2808.7 Combining Periodic and Accumulating Snapshots 2828.8 Complementary Fact Table Types 2848.9 Modeling Time Spans 2868.10 A Rolling Prediction of the Future, Now and in the Past 2898.11 Timespan Accumulating Snapshot Fact Tables 2938.12 Is it a Dimension, a Fact, or Both? 2948.13 Factless Fact Tables 2958.14 Factless Fact Tables? Sounds Like Jumbo Shrimp? 2988.15 What Didn’t Happen 2998.16 Factless Fact Tables for Simplification 302Parent-Child Fact Tables 3048.17 Managing Your Parents 3048.18 Patterns to Avoid When Modeling Header/Line Item Transactions 307Fact Table Keys and Degenerate Dimensions 3098.19 Fact Table Surrogate Keys 3098.20 Reader Suggestions on Fact Table Surrogate Keys 3108.21 Another Look at Degenerate Dimensions 3128.22 Creating a Reference Dimension for Infrequently Accessed Degenerates 313Miscellaneous Fact Table Design Patterns 3148.23 Put Your Fact Tables on a Diet 3148.24 Keeping Text Out of the Fact Table 3168.25 Dealing with Nulls in a Dimensional Model 3178.26 Modeling Data as Both a Fact and Dimension Attribute 3188.27 When a Fact Table Can Be Used as a Dimension Table 3198.28 Sparse Facts and Facts with Short Lifetimes 3218.29 Pivoting the Fact Table with a Fact Dimension 3238.30 Accumulating Snapshots for Complex Workflows 3249 Dimension Table Core Concepts 327Dimension Table Keys 3279.1 Surrogate Keys 3279.2 Keep Your Keys Simple 3319.3 Durable “Super-Natural” Keys 333Date and Time Dimension Considerations 3349.4 It’s Time for Time 3359.5 Surrogate Keys for the Time Dimension 3379.6 Latest Thinking on Time Dimension Tables 3399.7 Smart Date Keys to Partition Fact Tables 3419.8 Updating the Date Dimension 3429.9 Handling All the Dates 343Miscellaneous Dimension Patterns 3459.10 Selecting Default Values for Nulls 3459.11 Data Warehouse Role Models 3479.12 Mystery Dimensions 3509.13 De-Clutter with Junk Dimensions 3539.14 Showing the Correlation between Dimensions 3549.15 Causal (Not Casual) Dimensions 3569.16 Resist Abstract Generic Dimensions 3599.17 Hot-Swappable Dimensions 3609.18 Accurate Counting with a Dimensional Supplement 361Slowly Changing Dimensions 3639.19 Perfectly Partitioning History with Type 2 SCD 3639.20 Many Alternate Realities 3649.21 Monster Dimensions 3679.22 When a Slowly Changing Dimension Speeds Up 3709.23 When Do Dimensions Become Dangerous? 3729.24 Slowly Changing Dimensions are Not Always as Easy as 1, 2, and 3 3739.25 Slowly Changing Dimension Types 0, 4, 5, 6 and 7 3789.26 Dimension Row Change Reason Attributes 38210 More Dimension Patterns and Considerations 385Snowflakes, Outriggers, and Bridges 38510.1 Snowflakes, Outriggers, and Bridges 38510.2 A Trio of Interesting Snowflakes 38810.3 Help for Dimensional Modeling 39210.4 Managing Bridge Tables 39510.5 The Keyword Dimension 39910.6 Potential Bridge (Table) Detours 40310.7 Alternatives for Multi-Valued Dimensions 40510.8 Adding a Mini-Dimension to a Bridge Table 407Dealing with Hierarchies 40910.9 Maintaining Dimension Hierarchies 40910.10 Help for Hierarchies 41410.11 Five Alternatives for Better Employee Dimensional Modeling 41710.12 Avoiding Alternate Organization Hierarchies 42510.13 Alternate Hierarchies 426Customer Issues 42710.14 Dimension Embellishments 42710.15 Wrangling Behavior Tags 42910.16 Three Ways to Capture Customer Satisfaction 43110.17 Extreme Status Tracking for Real-Time Customer Analysis 435Addresses and International Issues 43910.18 Think Globally, Act Locally 43910.19 Warehousing without Borders 44310.20 Spatially Enabling Your Data Warehouse 44810.21 Multinational Dimensional Data Warehouse Considerations 452Industry Scenarios and Idiosyncrasies 45310.22 Industry Standard Data Models Fall Short 45310.23 An Insurance Data Warehouse Case Study 45510.24 Traveling through Databases 46010.25 Human Resources Dimensional Models 46310.26 Managing Backlogs Dimensionally 46710.27 Not So Fast 46810.28 The Budgeting Chain 47110.29 Compliance-Enabled Data Warehouses 47510.30 Clicking with Your Customer 47710.31 The Special Dimensions of the Clickstream 48210.32 Fact Tables for Text Document Searching 48510.33 Enabling Market Basket Analysis 48911 Back Room ETL and Data Quality 495Planning the ETL System 49511.1 Surrounding the ETL Requirements 49511.2 The 34 Subsystems of ETL 50011.3 Six Key Decisions for ETL Architectures 50411.4 Three ETL Compromises to Avoid 50811.5 Doing the Work at Extract Time 51011.6 Is Data Staging Relational? 51311.7 Staging Areas and ETL Tools 51711.8 Should You Use an ETL Tool? 51811.9 Call to Action for ETL Tool Providers 52111.10 Document the ETL System 52211.11 Measure Twice, Cut Once 52311.12 Brace for Incoming 52711.13 Building a Change Data Capture System 53011.14 Disruptive ETL Changes 53111.15 New Directions for ETL 533Data Quality Considerations 53511.16 Dealing With Data Quality: Don’t Just Sit There, Do Something! 53511.17 Data Warehouse Testing Recommendations 53711.18 Dealing with Dirty Data 53911.19 An Architecture for Data Quality 54511.20 Indicators of Quality: The Audit Dimension 55311.21 Adding an Audit Dimension to Track Lineage and Confi dence 55611.22 Add Uncertainty to Your Fact Table 55911.23 Have You Built Your Audit Dimension Yet? 56011.24 Is Your Data Correct? 56211.25 Eight Recommendations for International Data Quality 56511.26 Using Regular Expressions for Data Cleaning 568Populating Fact and Dimension Tables 57211.27 Pipelining Your Surrogates 57211.28 Unclogging the Fact Table Surrogate Key Pipeline 57611.29 Replicating Dimensions Correctly 57911.30 Identify Dimension Changes Using Cyclic Redundancy Checksums 58011.31 Maintaining Back Pointers to Operational Sources 58111.32 Creating Historical Dimension Rows 58211.33 Facing the Re-Keying Crisis 58511.34 Backward in Time 58711.35 Early-Arriving Facts 59011.36 Slowly Changing Entities 59111.37 Using the SQL MERGE Statement for Slowly Changing Dimensions 59311.38 Creating and Managing Shrunken Dimensions 59511.39 Creating and Managing Mini-Dimensions 59711.40 Creating, Using, and Maintaining Junk Dimensions 59911.41 Building Bridges 60111.42 Being Offl ine as Little as Possible 605Supporting Real Time 60611.43 Working in Web Time 60611.44 Real-Time Partitions 61011.45 The Real-Time Triage 61312 Technical Architecture Considerations 617Overall Technical/System Architecture 61712.1 Can the Data Warehouse Benefi t from SOA? 61712.2 Picking the Right Approach to MDM 61912.3 Building Custom Tools for the DW/BI System 62512.4 Welcoming the Packaged App 62612.5 ERP Vendors: Bring Down Those Walls 62912.6 Building a Foundation for Smart Applications 63212.7 RFID Tags and Smart Dust 63712.8 Is Big Data Compatible with the Data Warehouse? 64012.9 The Evolving Role of the Enterprise Data Warehouse in the Era of Big Data Analytics 64112.10 Newly Emerging Best Practices for Big Data 65912.11 The Hyper-Granular Active Archive 670Presentation Server Architecture 67212.12 Columnar Databases: Game Changers for DW/BI Deployment 67212.13 There is no Database Magic 67312.14 Relating to OLAP 67612.15 Dimensional Relational versus OLAP: The Final Deployment Conundrum 67912.16 Microsoft SQL Server Comes of Age for Data Warehousing 68212.17 The Aggregate Navigator 68612.18 Aggregate Navigation with (Almost) No Metadata 690Front Room Architecture 69712.19 The Second Revolution of User Interfaces 69712.20 Designing the User Interface 700Metadata 70412.21 Meta Meta Data Data 70412.22 Creating the Metadata Strategy 70812.23 Leverage Process Metadata for Self-Monitoring DW Operations 709Infrastructure and Security Considerations 71212.24 Watching the Watchers 71212.25 Catastrophic Failure 71612.26 Digital Preservation 71912.27 Creating the Advantages of a 64-Bit Server 72212.28 Server Configuration Considerations 72312.29 Adjust Your Thinking for SANs 72613 Front Room Business Intelligence Applications 729Delivering Value with Business Intelligence 72913.1 The Promise of Decision Support 73013.2 Beyond Paving the Cow Paths 73313.3 BI Components for Business Value 73613.4 Big Shifts Happening in BI 73813.5 Behavior: The Next Marquee Application 740Implementing the Business Intelligence Layer 74313.6 Three Critical Components for Successful Self-Service BI 74313.7 Leverage Data Visualization Tools, But Avoid Anarchy 74513.8 Think Like a Software Development Manager 74713.9 Standard Reports: Basics for Business Users 74813.10 Building and Delivering BI Reports 75313.11 The BI Portal 75713.12 Dashboards Done Right 75913.13 Don’t Be Overly Reliant on Your Data Access Tool’s Metadata 76013.14 Making Sense of the Semantic Layer 762Mining Data to Uncover Relationships 76413.15 Digging into Data Mining 76413.16 Preparing for Data Mining 76613.17 The Perfect Handoff 77013.18 Get Started with Data Mining Now 77413.19 Leverage Your Dimensional Model for Predictive Analytics 77813.20 Does Your Organization Need an Analytic Sandbox? 779Dealing with SQL 78113.21 Simple Drill Across in SQL 78113.22 An Excel Macro for Drilling Across 78313.23 The Problem with Comparisons 78513.24 SQL Roadblocks and Pitfalls 78913.25 Features for Query Tools 79213.26 Turbocharge Your Query Tools 79413.27 Smarter Data Warehouses 79814 Maintenance and Growth Considerations 805Deploying Successfully 80514.1 Don’t Forget the Owner’s Manual 80514.2 Let’s Improve Our Operating Procedures 80914.3 Marketing the DW/BI System 81114.4 Coping with Growing Pains 812Sustaining for Ongoing Impact 81614.5 Data Warehouse Checkups 81614.6 Boosting Business Acceptance 82214.7 Educate Management to Sustain DW/BI Success 82514.8 Getting Your Data Warehouse Back on Track 82814.9 Upgrading Your BI Architecture 82914.10 Four Fixes for Legacy Data Warehouses 83114.11 A Data Warehousing Fitness Program for Lean Times 83514.12 Enjoy the Sunset 83915 Final Thoughts 841Key Insights and Reminders 84115.1 Final Word of the Day: Collaboration 84115.2 Tried and True Concepts for DW/BI Success 84315.3 Key Tenets of the Kimball Method 845A Look to the Future 84715.4 The Future is Bright 847Article Index 853Index 861