The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence
Recognized and respected throughout the world as the most influential leaders in the data warehousing industry, Ralph Kimball and the Kimball Group have written articles covering more than 250 topics that define the field of data warehousing. For the first time, the Kimball Group's incomparable advice, design tips, and best practices have been gathered in this remarkable collection of articles, which spans a decade of data warehousing innovation.
Each group of articles is introduced with original commentaries that explain their role in the overall lifecycle methodology developed by the Kimball Group. These practical, hands-on articles are fully updated to reflect current practices and terminology and cover the complete lifecycleincluding project planning, requirements gathering, dimensional modeling, ETL, and business intelligence and analytics.
This easily referenced collection is nothing less than vital if you are involved with data warehousing or business intelligence in any capacity.
1 The Reader at a Glance.
Setting Up for Success.
1.1 Resist the Urge to Start Coding.
1.2 Set Your Boundaries.
1.3 Data Wrangling.
1.4 Myth Busters.
1.5 Dividing the World.
1.6 Essential Steps for the Integrated Enterprise Data Warehouse.
1.7 Drill Down to Ask Why.
1.8 Slowly Changing Dimensions.
1.9 Judge Your BI Tool through Your Dimensions.
1.10 Fact Tables.
1.11 Exploit Your Fact Tables.
2 Before You Dive In.
2.1 The Database Market Splits.
2.2 Bringing Up Supermarts.
2.3 Brave New Requirements for Data Warehousing.
2.4 Coping with the Brave New Requirements.
2.5 Stirring Things Up.
2.6 Design Constraints and Unavoidable Realities.
2.7 Two Powerful Ideas.
2.8 Data Warehouse Dining Experience.
3 Project/Program Planning.
3.1 Professional Boundaries.
3.2 An Engineer’s View.
3.3 Beware the Objection Removers.
3.4 What Does the Central Team Do?
3.5 Avoid DW/BI Isolation.
3.6 Implementation Analysis Paralysis.
3.7 Habits of Effective Sponsors.
3.8 TCO Starts with the End User.
3.9 Better Business Skills for BI and Data Warehouse Professionals.
3.10 Kimball Lifecycle in a Nutshell.
3.11 Off the Bench.
3.12 The Anti-Architect.
3.13 Think Critically When Applying Best Practices.
3.14 Eight Guidelines for Low Risk Enterprise Data Warehousing.
3.15 Relating to Agile Methodologies.
3.16 Is Agile Enterprise Data Warehousing an Oxymoron?
4 Requirements Definition.
4.1 Alan Alda’s Interviewing Tips for Uncovering.
4.2 More Business Requirements Gathering Dos and Don'ts.
4.3 Overcoming Obstacles When Gathering Business Requirements.
4.4 Surprising Value of Data Profiling.
4.5 Focus on Business Processes, Not Business Departments!
4.6 Identifying Business Processes.
4.7 Business Process Decoder Ring.
4.8 Relationship between Strategic Business Initiatives and Business Processes.
4.9 The Bottom-Up Misnomer.
5 Data Architecture.
Making the Case for Dimensional Modeling.
5.1 Is ER Modeling Hazardous to DSS?
5.2 A Dimensional Modeling Manifesto.
5.3 There Are No Guarantees.
5.4 Divide and Conquer.
5.5 The Matrix.
5.6 The Matrix: Revisited.
5.7 Drill Down into a Detailed Bus Matrix.
Integration Instead of Centralization.
5.8 Integration for Real People.
5.9 Data Stewardship 101: The First Step to Quality and Consistency .
5.10 To Be or Not To Be Centralized.
5.11 Differences of Opinion.
5.12 Don't Support Business Intelligence with a Normalized EDW.
6 Dimensional Modeling Fundamentals.
Basics of Dimensional Modeling.
6.1 Fact Tables and Dimension Tables.
6.2 Drilling Down, Up, and Across.
6.3 The Soul of the Data Warehouse, Part One: Drilling Down.
6.4 The Soul of the Data Warehouse, Part Two: Drilling Across.
6.5 The Soul of the Data Warehouse, Part Three: Handling Time.
6.6 Graceful Modifications to Existing Fact and Dimension Tables.
6.7 Kimball’s Ten Essential Rules of Dimensional Modeling.
6.8 What Not to Do.
6.9 Dangerous Preconceptions.
6.10 Fables and Facts.
7 Dimensional Modeling Tasks and Responsibilities.
7.1 Letting the Users Sleep.
7.2 Staffing the Dimensional Modeling Team.
7.3 Practical Steps for Designing a Dimensional Model.
7.4 The Naming Game.
7.5 When Is the Dimensional Design Done?
7.6 Fistful of Flaws.
7.7 Rating Your Dimensional Data Warehouse.
8 Fact Table Core Concepts.
8.1 Declaring the Grain.
8.2 Keep to the Grain in Dimensional Modeling.
8.3 Warning: Summary Data May Be Hazardous to Your Health.
8.4 No Detail Too Small.
8.5 Fundamental Grains.
8.6 Modeling a Pipeline with an Accumulating Snapshot.
8.7 Combining Periodic and Accumulating Snapshots.
8.8 Modeling Time Spans.
8.9 A Rolling Prediction of the Future, Now and in the Past.
8.10 Factless Fact Tables.
8.11 Factless Fact Tables? Sound Like Jumbo Shrimp?
8.12 What Didn't Happen.
8.13 Managing Your Parents.
8.14 Patterns to Avoid When Modeling Header/Line Item Transactions.
8.15 Fact Table Surrogate Keys.
8.16 Reader Suggestions on Fact Table Surrogate Keys.
8.17 Another Look at Degenerate Dimensions.
8.18 Creating a Reference Dimension for Infrequently Accessed Degenerates.
8.19 Put Your Fact Tables on a Diet.
8.20 Keeping Text Out of the Fact Table.
8.21 Dealing with Nulls in a Dimensional Model.
8.22 Modeling Data as Both a Fact and Dimension Attribute.
8.23 When a Fact Table Can Be Used as a Dimension Table.
8.24 Sparse Facts and Facts with Short Lifetimes.
8.25 Pivoting the Fact Table with a Fact Dimension.
9 Dimension Table Core Concepts.
Dimension Table Keys.
9.1 Surrogate Keys.
9.2 Keep Your Keys Simple.
9.3 It's Time for Time.
9.4 Surrogate Keys for the Time Dimension.
9.5 Latest Thinking on Time Dimension Tables.
9.6 Smart Date Keys to Partition Fact Tables.
9.7 Handling All the Dates.
Miscellaneous Dimension Patterns.
9.8 Data Warehouse Role Models.
9.9 Mystery Dimensions.
9.10 De-Clutter with Junk Dimensions.
9.11 Showing the Correlation Between Dimensions.
9.12 Causal (Not Casual) Dimensions.
9.13 Resist Abstract Generic Dimensions.
9.14 Hot-Swappable Dimensions.
9.15 Accurate Counting with a Dimensional Supplement.
9.16 Perfectly Partitioning History with Type 2 SCD.
9.17 Many Alternate Realities.
9.18 Monster Dimensions.
9.19 When a Slowly Changing Dimension Speeds Up.
9.20 When Do Dimensions Become Dangerous?
9.21 Slowly Changing Dimensions Are Not Always as Easy as 1, 2, and 3.
9.22 Dimension Row Change Reason Attributes.
10 More Dimension Patterns and Case Studies.
Snowflakes, Outriggers, and Bridges.
10.1 Snowflakes, Outriggers, and Bridges.
10.2 A Trio of Interesting Snowflakes.
10.3 Help for Dimensional Modeling.
10.4 Managing Bridge Tables.
10.5 The Keyword Dimension.
10.6 Maintaining Dimension Hierarchies.
10.7 Help for Hierarchies.
10.8 Five Alternatives for Better Employee Dimensional Modeling.
10.9 Alternate Hierarchies.
10.10 Dimension Embellishments.
10.11 Wrangling Behavior Tags.
10.12 Three Ways to Capture Customer Satisfaction.
10.13 Think Globally, Act Locally.
10.14 Warehousing without Borders.
10.15 Spatially Enabling Your Data Warehouse.
10.16 Multinational Dimensional Data Warehouse Considerations.
10.17 An Insurance Data Warehouse Case Study.
10.18 Traveling through Databases.
10.19 Human Resources Dimensional Models.
10.20 Not So Fast.
10.21 The Budgeting Chain.
10.22 Compliance-Enabled Data Warehouses.
10.23 Clicking with Your Customer.
10.24 The Special Dimensions of the Clickstream.
10.25 Fact Tables for Text Document Searching.
10.26 Enabling Market Basket Analysis.
11 Back Room ETL and Data Quality.
Planning the ETL System.
11.1 Surrounding the ETL Requirements.
11.2 The 34 Subsystems of ETL.
11.3 Doing the Work at Extract Time.
11.4 Is Data Staging Relational?
11.5 Staging Areas and ETL Tools.
11.6 Should You Use an ETL Tool?
11.7 Document the ETL System.
11.8 Measure Twice, Cut Once.
11.9 Brace for Incoming.
11.10 Building a Change Data Capture System.
Data Quality Considerations.
11.11 Dealing with Dirty Data.
11.12 An Architecture for Data Quality.
11.13 Indicators of Quality.
11.14 Is Your Data Correct?
11.15 Eight Recommendations for International Data Quality.
11.16 Using Regular Expressions for Data Cleaning.
11.17 Pipelining Your Surrogates.
11.18 Replicating Dimensions Correctly.
11.19 Identify Dimension Changes Using Cyclic Redundancy Checksums.
11.20 Maintaining Back Pointers to Operational Sources.
11.21 Creating Historical Dimension Rows.
11.22 Backward in Time.
11.23 Early-Arriving Facts.
11.24 Slowly Changing Entities.
11.25 Creating, Using, and Maintaining Junk Dimensions.
11.26 Using the SQL MERGE for Slowly Changing Dimensions.
11.27 Being Offline as Little as Possible.
11.28 Working in Web Time.
11.29 Real-Time Partitions.
11.30 The Real-Time Triage.
12 Technical Architecture Considerations.
Overall Technical/System Architecture.
12.1 Can the Data Warehouse Benefit from SOA?
12.2 Picking the Right Approach to MDM.
12.3 Building Custom Tools for the DW/BI System.
12.4 Welcoming the Packaged App.
12.5 ERP Vendors: Bring Down Those Walls.
12.6 Building a Foundation for Smart Applications.
12.7 RFID Tags and Smart Dust.
12.8 The Aggregate Navigator.
12.9 Aggregate Navigation with (Almost) No Metadata.
12.10 Relating to OLAP.
12.11 Dimensional Relational versus OLAP: The Final Deployment Conundrum.
12.12 Dimensional Modeling for Microsoft Analysis Services.
12.13 Architecting Your Data for Microsoft SQL Server 2005.
12.14 Microsoft SQL Server Comes of Age for Data Warehousing.
12.15 The Second Revolution of User Interfaces.
12.16 Designing the User Interface.
12.17 Meta Meta Data Data.
12.18 Creating the Metadata Strategy.
12.19 Watching the Watchers.
12.20 Catastrophic Failure.
12.21 Digital Preservation.
12.22 Creating the Advantages of a 64-Bit Server.
12.23 Server Configuration Considerations.
12.24 Adjust Your Thinking for SANs.
13 Front Room Business Intelligence Applications.
Delivering Value with Business Intelligence.
13.1 The Promise of Decision Support.
13.2 Beyond Paving the Cow Paths.
13.3 Big Shifts Happening in BI.
13.4 Behavior: The Next Marquee Application.
Implementing the Business Intelligence Layer.
13.5 Think Like a Software Development Manager.
13.6 Standard Reports: Basics for Business Users.
13.7 Building and Delivering BI Reports.
13.8 The BI Portal.
13.9 Dashboards Done Right.
13.10 Don’t Be Overly Reliant on Your Data Access Tool’s Metadata.
Mining Data to Uncover Relationships.
13.11 Digging into Data Mining.
13.12 Preparing for Data Mining.
13.13 The Perfect Handoff.
13.14 Get Started with Data Mining Now.
Dealing with SQL.
13.15 Simple Drill Across in SQL.
13.16 The Problem with Comparisons.
13.17 SQL Roadblocks and Pitfalls.
13.18 Features for Query Tools.
13.19 Turbocharge Your Query Tools.
13.20 Smarter Data Warehouses.
14 Maintenance and Growth Considerations.
14.1 Don’t Forget the Owner's Manual.
14.2 Let’s Improve Our Operating Procedures.
14.3 Marketing the DW/BI System.
14.4 Coping with Growing Pains.
Sustaining for Ongoing Impact.
14.5 Data Warehouse Checkups.
14.6 Boosting Business Acceptance.
14.7 Educate Management to Sustain DW/BI Success.
14.8 Getting Your Data Warehouse Back on Track.
14.9 Upgrading Your BI Architecture.
14.10 Four Fixes for Legacy Data Warehouses.
14.11 A Data Warehousing Fitness Program for Lean Times.
Index of Articles.
Margy Ross is President of the Kimball Group and has focused exclusively on DW/BI solutions since 1982.