book

The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence

Name: The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence
ISBN: 9780470563106

by Ralph Kimball, Margy Ross, Warren Thornthwaite, Joy Mundy, Bob Becker

February 2010

Intermediate to advanced

742 pages

25h 22m

English

Wiley

Read now

Unlock full access

Copyright
About the Authors
Credits
Acknowledgments
Introduction
Intended Audience and GoalsPreview of ContentsNavigation AidsTerminology Notes
1. The Reader at a Glance
1.1. Setting Up for Success1.1.1. 1.1 Resist the Urge to Start Coding1.1.2. 1.2 Set Your Boundaries1.2. Tackling DW/BI Design and Development1.2.1. 1.3 Data Wrangling1.2.2. 1.4 Myth Busters1.2.3. 1.5 Dividing the World1.2.4. 1.6 Essential Steps for the Integrated Enterprise Data Warehouse1.2.4.1. What Does an Integrated EDW Deliver?1.2.4.2. Ultimate Litmus Test for Integration1.2.4.3. Organizational Challenges1.2.4.4. Conformed Dimensions and Facts1.2.4.5. Using the Bus Matrix to Communicate with Executives1.2.4.6. Managing the Integrated EDW Backbone1.2.4.7. The Dimension Manager1.2.4.8. The Fact Provider1.2.4.9. Configuring Business Intelligence (BI) Tools1.2.4.10. Joint Responsibilities1.2.5. 1.7 Drill Down to Ask Why1.2.6. 1.8 Slowly Changing Dimensions1.2.6.1. Three Types of Slowly Changing Dimensions1.2.6.1.1. Type 1: Overwrite1.2.6.1.2. Type 2: Add a New Dimension Record1.2.6.1.3. Type 3: Add a New Field1.2.7. 1.9 Judge Your BI Tool through Your Dimensions1.2.8. 1.10 Fact Tables1.2.8.1. Stay True to the Grain1.2.8.2. Build Up from the Lowest Possible Grain1.2.8.3. Three Kinds of Fact Tables1.2.9. 1.11 Exploit Your Fact Tables1.2.9.1. Front Room: Aggregate Navigation1.2.9.2. Front Room: Drilling Across Different Grains1.2.9.3. Front Room: Exporting Constraints to Different Business Processes1.2.9.4. Back Room: Fact Table Surrogate Keys
2. Before You Dive In
2.1. Historical Perspective2.1.1. 2.1 The Database Market Splits2.1.2. 2.2 Bringing Up Supermarts2.1.2.1. The Planning Crisis2.1.2.2. Data Marts with an Architecture2.1.2.3. Importance of Conformed Dimensions2.1.2.4. Designing Conformed Dimensions2.1.2.5. Taking the Pledge2.1.2.6. Permissible Variations of Conformed Dimensions2.1.2.7. Establishing Standard Fact Definitions2.1.2.8. Importance of Granularity2.1.2.9. Higher Level Data Marts2.1.2.10. Rescuing Stovepipes2.1.2.11. When You Don't Need Conformed Dimensions2.1.2.12. Clear Vision2.2. Dealing with Demanding Realities2.2.1. 2.3 Brave New Requirements for Data Warehousing2.2.2. 2.4 Coping with the Brave New Requirements2.2.2.1. Data Marts and Dimensional Modeling2.2.2.2. Plugging Data Marts into the Data Warehouse Bus Architecture2.2.3. 2.5 Stirring Things Up2.2.4. 2.6 Design Constraints and Unavoidable Realities2.2.4.1. Design Constraints2.2.4.2. Unavoidable Realities2.2.4.3. Picking Ourselves Up Off the Floor2.2.5. 2.7 Two Powerful Ideas2.2.5.1. Separate Your Systems2.2.5.2. Symmetrical Stars and Cubes2.2.5.3. The Big Payoff2.2.5.4. What Have We Accomplished?2.2.6. 2.8 Data Warehouse Dining Experience2.2.6.1. The Kitchen2.2.6.2. The Dining Room
3. Project/Program Planning
3.1. Professional Responsibilities3.1.1. 3.1 Professional Boundaries3.1.2. 3.2 An Engineer's View3.1.2.1. The Data Warehouse Mission3.1.2.2. Design Drivers3.1.2.3. Design Constraints3.1.2.4. The Engineer's Response3.1.3. 3.3 Beware the Objection Removers3.1.4. 3.4 What Does the Central Team Do?3.1.4.1. Defining and Publishing Corporate Dimensions3.1.4.2. Providing Cross-Divisional Applications3.1.4.3. Defining a Consistent Data Warehouse Security Architecture3.1.5. 3.5 Avoid DW/BI Isolation3.1.6. 3.6 Implementation Analysis Paralysis3.2. Justification and Sponsorship3.2.1. 3.7 Habits of Effective Sponsors3.2.1.1. Setting Up for Success3.2.1.2. Resist the Path of Least Resistance3.2.1.3. Rally Those Around You3.2.1.4. Patience Is a Virtue3.2.1.5. Remain Focused on the Goal3.2.2. 3.8 TCO Starts with the End User3.2.2.1. Bad Decisions Are Costs3.2.2.2. A Closer Look at the Costs3.2.3. 3.9 Better Business Skills for BI and Data Warehouse Professionals3.2.3.1. Building Business Understanding3.2.3.2. Building Interpersonal Skills3.2.3.3. Building Public Speaking Skills3.2.3.4. Building Written Communication Skills3.2.3.5. Practice, Practice, Practice3.3. Kimball Methodology3.3.1. 3.10 Kimball Lifecycle in a Nutshell3.3.1.1. Program/Project Planning and Management3.3.1.2. Business Requirements3.3.1.3. Technology Track3.3.1.4. Data Track3.3.1.5. Business Intelligence Track3.3.1.6. Deployment, Maintenance, and Growth3.3.2. 3.11 Off the Bench3.3.3. 3.12 The Anti-Architect3.3.4. 3.13 Think Critically When Applying Best Practices3.3.4.1. Take an Enterprise Approach3.3.4.2. Embrace Business Intelligence3.3.4.3. Design Dimensional Schemas3.3.4.4. Use Conformed Dimensions for Integration3.3.4.5. Carefully Plan the ETL Architecture3.3.5. 3.14 Eight Guidelines for Low Risk Enterprise Data Warehousing3.3.5.1. Work on the Right Thing3.3.5.2. Give Business Users Control3.3.5.3. Proceed Incrementally3.3.5.4. Start with Lightweight, Focused Governance3.3.5.5. Build a Simple, Universal Platform3.3.5.6. Integrate Using Conformed Dimensions3.3.5.7. Manage Quality a Few Screens at a Time3.3.5.8. Use Surrogate Keys Throughout3.3.6. 3.15 Relating to Agile Methodologies3.3.7. 3.16 Is Agile Enterprise Data Warehousing an Oxymoron?
4. Requirements Definition
4.1. Gathering Requirements4.1.1. 4.1 Alan Alda's Interviewing Tips for Uncovering Business Requirements4.1.1.1. Be Curious, but Not Too Smart4.1.1.2. Be Conversational4.1.1.3. Listen and Expect to Be Changed4.1.2. 4.2 More Business Requirements Gathering Dos and Don'ts4.1.3. 4.3 Overcoming Obstacles When Gathering Business Requirements4.1.4. 4.4 Surprising Value of Data Profiling4.2. Organizing around Business Processes4.2.1. 4.5 Focus on Business Processes, Not Business Departments!4.2.2. 4.6 Identifying Business Processes4.2.3. 4.7 Business Process Decoder Ring4.2.4. 4.8 Relationship between Strategic Business Initiatives and Business Processes4.3. Wrapping Up the Requirements4.3.1. 4.9 The Bottom-Up Misnomer4.3.1.1. Focus on the Enterprise, Not Departments4.3.1.2. Draft the Enterprise Data Warehouse Bus Matrix4.3.1.3. Prioritize for an Orderly Conclusion4.3.1.4. Achieve an Enterprise Roadmap
5. Data Architecture
5.1. Making the Case for Dimensional Modeling5.1.1. 5.1 Is ER Modeling Hazardous to DSS?5.1.1.1. Dimensional Model versus Normalized Model5.1.2. 5.2 A Dimensional Modeling Manifesto5.1.2.1. What Is 3NF Normalized Modeling?5.1.2.2. What Is DM?5.1.2.3. DM versus 3NF5.1.2.4. The Strengths of DM5.1.2.5. Myths About DM5.1.2.6. In Defense of DM5.1.3. 5.3 There Are No Guarantees5.1.3.1. Does 3NF Modeling Handle Business Rules?5.1.3.2. Early Dimensional Modeling5.2. Enterprise Data Warehouse Bus Architecture5.2.1. 5.4 Divide and Conquer5.2.1.1. Common Labels Wanted?5.2.1.2. Business Process Subject Areas Are Not Departmental5.2.1.3. Conformed Dimensions and Facts5.2.1.4. The Data Warehouse Bus Architecture5.2.1.5. Only for Highly Distributed Systems?5.2.1.6. Netting Out the Benefits5.2.2. 5.5 The Matrix5.2.2.1. Inviting Subject Area Groups to the Conforming Meeting5.2.2.2. Communicating with the Boss5.2.2.3. Second-Level Subject Areas5.2.3. 5.6 The Matrix: Revisited5.2.3.1. Matrix Columns for Reference Data5.2.3.2. Data Stewardship5.2.3.3. Process-Centric Rows5.2.3.4. Associate Columns and Rows5.2.3.5. Common Matrix Mishaps5.2.3.6. Matrix Extensions5.2.4. 5.7 Drill Down into a Detailed Bus Matrix5.3. Integration Instead of Centralization5.3.1. 5.8 Integration for Real People5.3.1.1. Defining Integration5.3.1.2. Integrating Labels5.3.1.3. Integrating Measures5.3.1.4. Responsibilities of the Dimension Manager5.3.1.5. Responsibilities of the Fact Provider5.3.2. 5.9 Data Stewardship 101: The First Step to Quality and Consistency5.3.2.1. Why Stewardship Is Essential5.3.2.2. Stewardship Responsibilities5.3.2.3. Right Stuff for Stewardship5.3.2.4. Communication Tools and Techniques5.3.2.5. How to Get Started5.3.3. 5.10 To Be or Not To Be Centralized5.3.3.1. All That Glitters Is Not Gold5.3.3.2. Be Not Afraid of Greatness5.3.3.3. All's Well That Ends Well5.4. Contrast with the Corporate Information Factory5.4.1. 5.11 Differences of Opinion5.4.1.1. Common Ground5.4.1.2. Kimball Bus Architecture5.4.1.3. Corporate Information Factory5.4.1.4. Fundamental Differences5.4.1.5. Hybrid Approach?5.4.1.6. Success Criteria5.4.2. 5.12 Don't Support Business Intelligence with a Normalized EDW

6. Dimensional Modeling Fundamentals
6.1. Basics of Dimensional Modeling6.1.1. 6.1 Fact Tables and Dimension Tables6.1.1.1. Measurements and Context6.1.1.2. Dimensional Keys6.1.1.3. Relating the Two Modeling Worlds6.1.1.4. Declaring the Grain6.1.1.5. Additive Facts6.1.1.6. Degenerate Dimensions6.1.2. 6.2 Drilling Down, Up, and Across6.1.2.1. Drilling Down6.1.2.2. Drilling Up6.1.2.3. Drilling Across6.1.3. 6.3 The Soul of the Data Warehouse, Part One: Drilling Down6.1.4. 6.4 The Soul of the Data Warehouse, Part Two: Drilling Across6.1.4.1. Implementing Drill Across6.1.4.2. Amazing Magic6.1.5. 6.5 The Soul of the Data Warehouse, Part Three: Handling Time6.1.5.1. Time Validity6.1.5.2. Correct Association6.1.5.3. Natural Grains6.1.5.4. Have You Lived Up to Your Pledges?6.1.6. 6.6 Graceful Modifications to Existing Fact and Dimension Tables6.2. Dos and Don'ts6.2.1. 6.7 Kimball's Ten Essential Rules of Dimensional Modeling6.2.2. 6.8 What Not to Do6.3. Myths about Dimensional Modeling6.3.1. 6.9 Dangerous Preconceptions6.3.2. 6.10 Fables and Facts6.3.2.1. Not All Dimensional Models Are Created Equal6.3.2.2. Focus on Measurement Processes, Not Departmental Reports6.3.2.3. Begin with Atomic Details, Not Summarized Data6.3.2.4. Integration Is the Goal, Not Normalization
7. Dimensional Modeling Tasks and Responsibilities
7.1. Design Activities7.1.1. 7.1 Letting the Users Sleep7.1.2. 7.2 Staffing the Dimensional Modeling Team7.1.3. 7.3 Practical Steps for Designing a Dimensional Model7.1.3.1. Join the Party7.1.3.2. Dive into the Details7.1.3.3. Review the Results7.1.4. 7.4 The Naming Game7.1.4.1. Step 1: Preparation7.1.4.2. Step 2: Creating an Initial Name Set7.1.4.3. Step 3: Building Consensus7.1.5. 7.5 When Is the Dimensional Design Done?7.2. Design Review Activities7.2.1. 7.6 Fistful of Flaws7.2.1.1. What's the Grain?7.2.1.2. Mixed Grain or Textual Facts?7.2.1.3. Dimension Descriptors and Decodes?7.2.1.4. Handling of Hierarchies?7.2.1.5. Explicit Date Dimension?7.2.1.6. Control Numbers as Degenerate Dimensions?7.2.1.7. Surrogate Keys?7.2.1.8. Slowly Changing Dimension Strategies?7.2.1.9. Well Understood Business Requirements?7.2.2. 7.7 Rating Your Dimensional Data Warehouse7.2.2.1. Architecture Criteria7.2.2.2. Administration Criteria7.2.2.3. Expression Criteria7.2.2.4. Are You Dimensional?
8. Fact Table Core Concepts
8.1. Granularity8.1.1. 8.1 Declaring the Grain8.1.1.1. In Business Terms8.1.1.2. Powerful Effects8.1.1.3. Keep Facts True to the Grain8.1.2. 8.2 Keep to the Grain in Dimensional Modeling8.1.3. 8.3 Warning: Summary Data May Be Hazardous to Your Health8.1.4. 8.4 No Detail Too Small8.1.4.1. Accumulating the Atoms8.1.4.2. Consolidating across Processes8.1.4.3. More Performance, Less Dimensionality8.1.4.4. Consolidated Fact Table Example8.1.4.5. Accumulating Snapshot Example8.1.4.6. Details Always Come First8.2. Types of Fact Tables8.2.1. 8.5 Fundamental Grains8.2.1.1. Fundamental Grains8.2.1.2. How Do We Use Each Fact Table Type?8.2.2. 8.6 Modeling a Pipeline with an Accumulating Snapshot8.2.3. 8.7 Combining Periodic and Accumulating Snapshots8.2.4. 8.8 Modeling Time Spans8.2.5. 8.9 A Rolling Prediction of the Future, Now and in the Past8.2.6. 8.10 Factless Fact Tables8.2.7. 8.11 Factless Fact Tables? Sound Like Jumbo Shrimp?8.2.8. 8.12 What Didn't Happen8.2.8.1. Coverage Tables8.2.8.2. Explicit Records for Non-Behavior8.2.8.3. Searching for Nonexistent Facts with NOT EXISTS8.2.8.4. Using NOT EXISTS to Find Non Attributes8.3. Parent-Child Fact Tables8.3.1. 8.13 Managing Your Parents8.3.1.1. Conflicting Allocation Theories8.3.1.2. Tough Allocation Environments8.3.2. 8.14 Patterns to Avoid When Modeling Header/Line Item Transactions8.3.2.1. Bad Idea #1: Retain the Header as a Dimension8.3.2.2. Bad Idea #2: Line Items Don't Inherit Header Dimensionality8.3.2.3. Recommended Structure for Header/Line Item Transactions8.4. Fact Table Keys and Degenerates8.4.1. 8.15 Fact Table Surrogate Keys8.4.2. 8.16 Reader Suggestions on Fact Table Surrogate Keys8.4.3. 8.17 Another Look at Degenerate Dimensions8.4.4. 8.18 Creating a Reference Dimension for Infrequently Accessed Degenerates8.5. Miscellaneous Fact Table Design Patterns8.5.1. 8.19 Put Your Fact Tables on a Diet8.5.2. 8.20 Keeping Text Out of the Fact Table8.5.3. 8.21 Dealing with Nulls in a Dimensional Model8.5.3.1. Nulls as Fact Table Foreign Keys8.5.3.2. Nulls as Facts8.5.3.3. Nulls as Dimension Attributes8.5.4. 8.22 Modeling Data as Both a Fact and Dimension Attribute8.5.5. 8.23 When a Fact Table Can Be Used as a Dimension Table8.5.6. 8.24 Sparse Facts and Facts with Short Lifetimes8.5.7. 8.25 Pivoting the Fact Table with a Fact Dimension
9. Dimension Table Core Concepts
9.1. Dimension Table Keys9.1.1. 9.1 Surrogate Keys9.1.2. 9.2 Keep Your Keys Simple9.2. Date and Time Dimension Considerations9.2.1. 9.3 It's Time for Time9.2.1.1. Basic Time Issues9.2.1.2. Intermediate Time Issues9.2.2. 9.4 Surrogate Keys for the Time Dimension9.2.3. 9.5 Latest Thinking on Time Dimension Tables9.2.4. 9.6 Smart Date Keys to Partition Fact Tables9.2.5. 9.7 Handling All the Dates9.3. Miscellaneous Dimension Patterns9.3.1. 9.8 Data Warehouse Role Models9.3.2. 9.9 Mystery Dimensions9.3.2.1. Find the Obvious Dimension-Related Fields9.3.2.2. Find the Fact Related Fields9.3.2.3. Decide What to Do with the Rest9.3.2.4. Transform Mystery Fields Into Mystery Dimensions9.3.3. 9.10 De-Clutter with Junk Dimensions9.3.4. 9.11 Showing the Correlation Between Dimensions9.3.5. 9.12 Causal (Not Casual) Dimensions9.3.6. 9.13 Resist Abstract Generic Dimensions9.3.7. 9.14 Hot-Swappable Dimensions9.3.8. 9.15 Accurate Counting with a Dimensional Supplement9.4. Slowly Changing Dimensions9.4.1. 9.16 Perfectly Partitioning History with Type 2 SCD9.4.2. 9.17 Many Alternate Realities9.4.2.1. Predictable Multiple Realities9.4.2.2. Unpredictable Multiple Realities9.4.3. 9.18 Monster Dimensions9.4.4. 9.19 When a Slowly Changing Dimension Speeds Up9.4.4.1. Date Stamps in Slowly Changing Dimensions9.4.4.2. SCDs That Aren't Slowly Changing9.4.5. 9.20 When Do Dimensions Become Dangerous?9.4.6. 9.21 Slowly Changing Dimensions Are Not Always as Easy as 1, 2, and 39.4.6.1. Mini Dimension with Current Overwrite9.4.6.2. Type 2 with Current Overwrite9.4.6.3. Type 2 with Durable Keys in the Fact Table9.4.6.4. Series of Type 3 Attributes9.4.6.5. Balance Power against Ease of Use9.4.7. 9.22 Dimension Row Change Reason Attributes
10. More Dimension Patterns and Case Studies
10.1. Snowflakes, Outriggers, and Bridges10.1.1. 10.1 Snowflakes, Outriggers, and Bridges10.1.2. 10.2 A Trio of Interesting Snowflakes10.1.2.1. Classic Snowflake10.1.2.2. Large Customer Dimensions10.1.2.3. Financial Product Dimensions10.1.2.4. Multi-Enterprise Calendar Dimensions10.1.2.5. Permissible Snowflakes10.1.3. 10.3 Help for Dimensional Modeling10.1.4. 10.4 Managing Bridge Tables10.1.4.1. Using Surrogate Keys10.1.4.2. Using Twin Time Stamps10.1.4.3. Updating the Bridge Table10.1.5. 10.5 The Keyword Dimension10.1.5.1. Designing the Keyword Dimension10.1.5.2. The AND/OR Dilemma10.1.5.3. Searching for Substrings10.1.5.4. High Performance Substring Indexes10.2. Dealing with Hierarchies10.2.1. 10.6 Maintaining Dimension Hierarchies10.2.1.1. Start with the Design10.2.1.2. Load Normalized Data10.2.1.3. Maintain True Hierarchies10.2.1.4. Address Dirty Sources10.2.1.5. Make It Perform10.2.2. 10.7 Help for Hierarchies10.2.3. 10.8 Five Alternatives for Better Employee Dimensional Modeling10.2.3.1. Alternative 1: Bridge Table Using Surrogate Keys10.2.3.2. Alternative 2: Bridge Table with Separate Reports-To Dimension10.2.3.3. Alternative 3: Bridge Table with Natural Keys10.2.3.4. Alternative 4: Forced Fixed-Depth Hierarchy Technique10.2.3.5. Alternative 5: The PathString Attribute10.2.3.6. Recommendation10.2.4. 10.9 Alternate Hierarchies10.3. Customer Issues10.3.1. 10.10 Dimension Embellishments10.3.2. 10.11 Wrangling Behavior Tags10.3.3. 10.12 Three Ways to Capture Customer Satisfaction10.3.3.1. Standard Fixed List10.3.3.2. Simultaneous Dimension Attributes and Facts10.3.3.3. The Unpredictable, Chaotic List10.4. Addresses and International Issues10.4.1. 10.13 Think Globally, Act Locally10.4.1.1. Synchronizing Multiple Time Zones10.4.1.2. Supporting Multiple National Calendars10.4.1.3. Collecting Revenue in Multiple Currencies10.4.1.4. Dealing with the Euro10.4.2. 10.14 Warehousing without Borders10.4.2.1. Designing for an International Name and Address Environment10.4.3. 10.15 Spatially Enabling Your Data Warehouse10.4.3.1. Investigating a GIS Vendor10.4.3.2. Going to Boot Camp10.4.3.3. Automatic Address Standardizing10.4.3.4. Geographic Query on Standard Databases10.4.3.5. The Right Fit?10.4.4. 10.16 Multinational Dimensional Data Warehouse Considerations10.5. Industry Scenarios and Idiosyncrasies10.5.1. 10.17 An Insurance Data Warehouse Case Study10.5.2. 10.18 Traveling through Databases10.5.2.1. Walking through the Design10.5.2.2. Adding Dimensions10.5.2.3. Images and Maps10.5.3. 10.19 Human Resources Dimensional Models10.5.4. 10.20 Not So Fast10.5.4.1. Find the Components of Profitability10.5.4.2. Marketing and Finance Need to Help10.5.4.3. Allocations: The Heart of the Profitability Challenge10.5.4.4. If You're in a Hurry10.5.5. 10.21 The Budgeting Chain10.5.5.1. The Grain of the Budgeting Chain Fact Tables10.5.5.2. The Budgeting Chain Dimensions and Facts10.5.5.3. Applications across the Budget Chain10.5.6. 10.22 Compliance-Enabled Data Warehouses10.5.7. 10.23 Clicking with Your Customer10.5.7.1. The Goals of the Clickstream Dimensional Model10.5.7.2. The Clickstream Data Source10.5.7.3. The Fundamental Grain of Clickstream Data10.5.7.4. Identifying the Clickstream's Dimensions and Facts10.5.7.5. Analyzing Clickstream Events10.5.8. 10.24 The Special Dimensions of the Clickstream10.5.8.1. Visitor Dimension10.5.8.2. Page Object Dimension10.5.8.3. Session Type10.5.8.4. Focus on Page Object and Session Dimensions10.5.9. 10.25 Fact Tables for Text Document Searching10.5.9.1. Similarity Metrics10.5.9.2. Fact Tables for Similarity Measures10.5.9.3. Powerful Applications10.5.10. 10.26 Enabling Market Basket Analysis10.5.10.1. Progressive Pruning Algorithm
11. Back Room ETL and Data Quality
11.1. Planning the ETL System11.1.1. 11.1 Surrounding the ETL Requirements11.1.1.1. Business Needs11.1.1.2. Compliance11.1.1.3. Data Quality via Data Profiling11.1.1.4. Security11.1.1.5. Data Integration and the 360 Degree View11.1.1.6. Data Latency11.1.1.7. Archiving and Lineage11.1.1.8. BI User Delivery Interfaces11.1.1.9. Available Skills11.1.1.10. Legacy Licenses11.1.2. 11.2 The 34 Subsystems of ETL11.1.2.1. Extracting: Getting Data into the Data Warehouse11.1.2.2. Cleaning and Conforming Data11.1.2.3. Delivering: Preparing for Presentation11.1.2.4. Managing the ETL Environment11.1.3. 11.3 Doing the Work at Extract Time11.1.3.1. Modeling Events across Multiple Time Zones11.1.3.2. Verbose Calendar Dimensions11.1.3.3. Keeping the Books across Multiple Currencies11.1.3.4. Product Pipeline Measurements11.1.3.5. Physical Completeness of the Profit and Loss11.1.3.6. Heterogeneous Products11.1.3.7. Aggregations in General11.1.3.8. Dimensional Modeling in General11.1.4. 11.4 Is Data Staging Relational?11.1.4.1. Dimension Processing11.1.4.2. Deciding What Has Changed11.1.4.3. Combining from Separate Sources11.1.4.4. Data Cleaning11.1.4.5. Processing Names and Addresses11.1.4.6. Validating One-to-One and One-to-Many Relationships11.1.4.7. Fact Processing11.1.4.8. Aggregate Processing11.1.4.9. The Bottom Line: Is Data Staging Relational?11.1.5. 11.5 Staging Areas and ETL Tools11.1.6. 11.6 Should You Use an ETL Tool?11.1.6.1. Advantages of ETL Tools11.1.6.2. Disadvantages of ETL Tools11.1.6.3. Build a Solid Foundation11.1.7. 11.7 Document the ETL System11.1.8. 11.8 Measure Twice, Cut Once11.1.8.1. Objective: High Level ETL Plan11.1.8.2. Inputs and Data Flows11.1.8.3. Transformation Notes11.1.8.4. Finish Planning Before Cutting11.1.9. 11.9 Brace for Incoming11.1.9.1. Typical Data Integration Process11.1.9.2. Architecture11.1.9.3. Setup Process11.1.9.4. Exception Handling11.1.9.5. Deceptive Simplicity11.1.10. 11.10 Building a Change Data Capture System11.2. Data Quality Considerations11.2.1. 11.11 Dealing with Dirty Data11.2.1.1. Applications Where Good Data Is Critical11.2.1.2. The Science of Data Cleaning11.2.1.3. The Marketplace Opportunity for Data Cleaning11.2.1.4. Data Integrity Drives Business Reengineering11.2.2. 11.12 An Architecture for Data Quality11.2.2.1. Establish a Quality Culture and Reengineer the Processes11.2.2.2. The Role of Data Profiling11.2.2.3. Quality Screens11.2.2.4. Error Event Schema11.2.2.5. Responding to Quality Events11.2.2.6. The Audit Dimension11.2.2.7. Six Sigma Data Quality11.2.3. 11.13 Indicators of Quality11.2.3.1. Start with the Lowest Possible Grain11.2.3.2. Reporting Aggregate Data Quality11.2.3.3. Building the Audit Dimension11.2.4. 11.14 Is Your Data Correct?11.2.4.1. Judging Data Quality with No History11.2.4.2. Compensating for Predictable Changes11.2.5. 11.15 Eight Recommendations for International Data Quality11.2.5.1. Languages and Character Sets11.2.5.2. Cultures, Names, and Salutations11.2.5.3. Geographies and Addresses11.2.5.4. Privacy and Information Transfer11.2.5.5. International Compliance11.2.5.6. Currencies11.2.5.7. Time Zones, Calendars, and Date Formats11.2.5.8. Numbers11.2.5.9. Architectures for International Data Quality11.2.6. 11.16 Using Regular Expressions for Data Cleaning11.2.6.1. Regular Expressions to the Rescue11.2.6.2. Basic Operators11.2.6.3. Finding the Occurrences of "Inc"11.2.6.4. The Final Results11.2.6.5. Where Can You Use RegExps?11.3. Populating Fact and Dimension Tables11.3.1. 11.17 Pipelining Your Surrogates11.3.1.1. Keys for the Dimension Tables11.3.1.2. Keys for the Fact Tables11.3.2. 11.18 Replicating Dimensions Correctly11.3.3. 11.19 Identify Dimension Changes Using Cyclic Redundancy Checksums11.3.4. 11.20 Maintaining Back Pointers to Operational Sources11.3.5. 11.21 Creating Historical Dimension Rows11.3.5.1. Dig for History11.3.5.2. Discuss the Options and Implications11.3.5.3. Build the Dimension11.3.5.4. Choose a Daily or Minute-Second Grain11.3.6. 11.22 Backward in Time11.3.6.1. Late-Arriving Fact Records11.3.6.2. Late-Arriving Dimension Records11.3.7. 11.23 Early-Arriving Facts11.3.8. 11.24 Slowly Changing Entities11.3.9. 11.25 Creating, Using, and Maintaining Junk Dimensions11.3.9.1. Build the Initial Junk Dimension11.3.9.2. Incorporate the Junk Dimension into the Fact Row Process11.3.9.3. Maintain the Junk Dimension11.3.10. 11.26 Using the SQL MERGE for Slowly Changing Dimensions11.3.10.1. Step 1: Overwrite the Type 1 Changes11.3.10.2. Step 2: Handle the Type 2 Changes11.3.11. 11.27 Being Offline as Little as Possible11.4. Supporting Real Time11.4.1. 11.28 Working in Web Time11.4.2. 11.29 Real-Time Partitions11.4.2.1. Requirements for Real-Time Partitions11.4.2.2. Transaction Grain Real-Time Partitions11.4.2.3. Periodic Snapshot Real-Time Partition11.4.2.4. Accumulating Snapshot Real-Time Partition11.4.3. 11.30 The Real-Time Triage
12. Technical Architecture Considerations
12.1. Overall Technical/System Architecture12.1.1. 12.1 Can the Data Warehouse Benefit from SOA?12.1.2. 12.2 Picking the Right Approach to MDM12.1.2.1. Source System Disparities12.1.2.2. The Need for Master Data12.1.2.3. Approach 1: Master Data in the Conformed Data Warehouse12.1.2.4. Approach 2: The MDM Integration Hub12.1.2.5. Approach 3: The Enterprise MDM System12.1.2.6. Four Steps toward MDM12.1.3. 12.3 Building Custom Tools for the DW/BI System12.1.4. 12.4 Welcoming the Packaged App12.1.4.1. Avoid Stovepipe Data Marts12.1.4.2. Conforming at Query Time12.1.4.3. Vendors Do Take Integration Seriously12.1.5. 12.5 ERP Vendors: Bring Down Those Walls12.1.5.1. What Are the New Rules?12.1.5.2. The Role of ERP in the New Webhouse12.1.6. 12.6 Building a Foundation for Smart Applications12.1.6.1. The Quick but Risky Path12.1.6.2. The Right Way to Smart Apps12.1.6.3. When the Infrastructure Falls Short12.1.6.4. Supporting Transactional Workloads12.1.6.5. Pervasive BI: Spreading BI Everywhere12.1.7. 12.7 RFID Tags and Smart Dust12.1.7.1. Lifetime Employment12.1.7.2. The Assault on Privacy12.1.7.3. Beyond RFID to Smart Dust12.2. Presentation Server Architecture12.2.1. 12.8 The Aggregate Navigator12.2.2. 12.9 Aggregate Navigation with (Almost) No Metadata12.2.2.1. High Level Goals and Risks12.2.2.2. Aggregate Navigation Algorithm12.2.2.3. Aggregates for Everyone12.2.3. 12.10 Relating to OLAP12.2.3.1. Desktop versus Server OLAP12.2.3.2. Dimensional Similarities12.2.3.3. Dimensional Differences12.2.3.4. OLAP's Strengths12.2.4. 12.11 Dimensional Relational versus OLAP: The Final Deployment Conundrum12.2.4.1. Dimensional Relational Advantages12.2.4.2. Relational Disadvantages12.2.4.3. OLAP Advantages12.2.4.4. OLAP Disadvantages12.2.4.5. Equally Easy in Either Approach12.2.4.6. Making the Final Choice12.2.5. 12.12 Dimensional Modeling for Microsoft Analysis Services12.2.6. 12.13 Architecting Your Data for Microsoft SQL Server 200512.2.7. 12.14 Microsoft SQL Server Comes of Age for Data Warehousing12.2.7.1. Speed the Queries with Database Compression12.2.7.2. Divide and Conquer with Table Partitioning12.2.7.3. Go Dimensional with Star Schema Optimization12.2.7.4. Up and Coming Opportunities12.3. Front Room Architecture12.3.1. 12.15 The Second Revolution of User Interfaces12.3.1.1. How the Second Revolution Differs from the First12.3.1.2. The User Interface Is Now More Urgent12.3.1.3. Second Generation User Interface Guidelines12.3.2. 12.16 Designing the User Interface12.4. Metadata12.4.1. 12.17 Meta Meta Data Data12.4.2. 12.18 Creating the Metadata Strategy12.5. Infrastructure and Security Considerations12.5.1. 12.19 Watching the Watchers12.5.1.1. Beneficial Uses and Insidious Abuses12.5.1.2. Who Owns Your Personal Data?12.5.1.3. What Is Likely to Happen?12.5.1.4. The Impact on Warehouse Architecture12.5.2. 12.20 Catastrophic Failure12.5.2.1. Catastrophic Failures12.5.2.2. Countering Catastrophic Failures12.5.3. 12.21 Digital Preservation12.5.3.1. Does a Warehouse Even Need to Keep Old Data?12.5.3.2. Media, Formats, Software, and Hardware12.5.3.3. Obsolete Formats and Archaic Formats12.5.3.4. Hard Copy, Standards, and Museums12.5.3.5. Refreshing, Migrating, Emulating, and Encapsulating12.5.4. 12.22 Creating the Advantages of a 64-Bit Server12.5.5. 12.23 Server Configuration Considerations12.5.5.1. Factors Influencing Server Configurations12.5.5.2. Adding Capacity12.5.5.3. Getting Help12.5.5.4. Conclusion12.5.6. 12.24 Adjust Your Thinking for SANs12.5.6.1. A Postscript
13. Front Room Business Intelligence Applications
13.1. Delivering Value with Business Intelligence13.1.1. 13.1 The Promise of Decision Support13.1.1.1. Analytic Applications Lifecycle13.1.1.2. Publish Reports13.1.1.3. Identify Exceptions13.1.1.4. Determine Causal Factors13.1.1.5. Model Alternatives13.1.1.6. Track Actions13.1.1.7. Stepping Back13.1.2. 13.2 Beyond Paving the Cow Paths13.1.2.1. Begin with Reported Results13.1.2.2. Identify Criteria and Threshold Tolerances13.1.2.3. Understand Cause and Effect13.1.2.4. Evaluate the Options13.1.2.5. Track Actions for Future Optimization13.1.3. 13.3 Big Shifts Happening in BI13.1.3.1. Compliance Is a Free Pass for BI13.1.3.2. Sequential Behavior Analysis Is BI's Mount Everest13.1.4. 13.4 Behavior: The Next Marquee Application13.1.4.1. CRM: The Stepping Stone to Behavior13.1.4.2. The New Analytics of Behavior13.2. Implementing the Business Intelligence Layer13.2.1. 13.5 Think Like a Software Development Manager13.2.2. 13.6 Standard Reports: Basics for Business Users13.2.2.1. What Are BI Applications?13.2.2.2. Build versus Buy13.2.2.3. Designing the Reporting System13.2.2.4. In a Nutshell13.2.3. 13.7 Building and Delivering BI Reports13.2.3.1. Set Up Development13.2.3.2. Create the Reports13.2.3.3. Test Accuracy and Performance13.2.3.4. Deploy to Production13.2.3.5. Manage and Maintain13.2.3.6. Extend the Applications13.2.4. 13.8 The BI Portal13.2.4.1. Density13.2.4.2. Structure13.2.4.3. More Structure and Content13.2.5. 13.9 Dashboards Done Right13.2.6. 13.10 Don't Be Overly Reliant on Your Data Access Tool's Metadata13.3. Mining Data to Uncover Relationships13.3.1. 13.11 Digging into Data Mining13.3.1.1. The Roots of Data Mining13.3.1.2. The Categories of Data Mining13.3.2. 13.12 Preparing for Data Mining13.3.2.1. General Data Transformations13.3.2.2. Transformations for All Forms of Data Mining13.3.2.3. Special Tool-Dependent Transformations13.3.3. 13.13 The Perfect Handoff13.3.3.1. The Perfect Observation13.3.3.2. Implications for Database Architecture13.3.4. 13.14 Get Started with Data Mining Now13.3.4.1. The Business Phase13.3.4.2. The Data Mining Phase13.3.4.3. The Operations Phase13.3.4.4. Role of Data Mining Metadata13.4. Dealing with SQL13.4.1. 13.15 Simple Drill Across in SQL13.4.2. 13.16 The Problem with Comparisons13.4.3. 13.17 SQL Roadblocks and Pitfalls13.4.3.1. Fixing the SQL Problem13.4.4. 13.18 Features for Query Tools13.4.5. 13.19 Turbocharge Your Query Tools13.4.6. 13.20 Smarter Data Warehouses13.4.6.1. SQL Scrutinized13.4.6.2. SQL-99 OLAP Extensions13.4.6.3. More Advanced Queries13.4.6.4. The Extension Problem13.4.6.5. OLAP Is an Answer
14. Maintenance and Growth Considerations
14.1. Deploying Successfully14.1.1. 14.1 Don't Forget the Owner's Manual14.1.1.1. Front Room Operations14.1.1.2. Back Room Operations14.1.1.3. Monitor Operations14.1.2. 14.2 Let's Improve Our Operating Procedures14.1.3. 14.3 Marketing the DW/BI System14.1.3.1. Product14.1.3.2. Price14.1.3.3. Placement14.1.3.4. Promotion14.1.4. 14.4 Coping with Growing Pains14.1.4.1. Recognize the Target14.1.4.2. Case Study Scenario14.1.4.3. Not So Fast14.1.4.4. Plan Before You Build14.1.4.5. Develop, Test, Deploy14.1.4.6. Some Final Thoughts14.2. Sustaining for Ongoing Impact14.2.1. 14.5 Data Warehouse Checkups14.2.1.1. Business Sponsor Disorder14.2.1.2. Data Disorder14.2.1.3. Business Acceptance Disorder14.2.1.4. Infrastructure Disorder14.2.1.5. Cultural/Political Disorder14.2.1.6. Early Detection14.2.2. 14.6 Boosting Business Acceptance14.2.2.1. DW/BI Business Realignment14.2.2.2. Choose the Forum14.2.2.3. Identify and Prepare the Interview Team14.2.2.4. Select, Schedule, and Prepare Business Representatives14.2.2.5. Conduct the Interviews14.2.2.6. Document, Prioritize, and Reach Consensus14.2.3. 14.7 Educate Management to Sustain DW/BI Success14.2.3.1. Gathering Evidence14.2.3.2. Educating the Business: The User Forum14.2.3.3. Educating Senior Staff14.2.3.4. Working with Steering Committees14.2.3.5. Conclusion14.2.4. 14.8 Getting Your Data Warehouse Back on Track14.2.5. 14.9 Upgrading Your BI Architecture14.2.6. 14.10 Four Fixes for Legacy Data Warehouses14.2.6.1. Conform the Nonconformed Dimensions14.2.6.2. Create Surrogate Keys14.2.6.3. Deliver the Details14.2.6.4. Reduce Redundancies14.2.6.5. Face the Realities14.2.7. 14.11 A Data Warehousing Fitness Program for Lean Times14.2.7.1. Cut the Flab14.2.7.2. Monitor and Tune to Defer Spending14.2.7.3. Bulk Up the Bottom Line
Article Index

Content preview from The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence

Chapter 9. Dimension Table Core Concepts

Building on your solid understanding of fact tables from Chapter 8, it's time to turn your attention to dimension tables, whose descriptive attributes allow the business users to filter and group information in nearly countless ways. Robust dimension tables filled with interesting attributes enable robust analytics.

The chapter begins by discussing the importance of replacing a dimension's natural operational keys with meaningless integer surrogate keys during the ETL process. From that basic concept, the second section focuses on the time (or date) dimension that you'll find with virtually every fact table.

We then describe other common dimension table patterns, including role-playing, junk, and causal dimensions. We close out the chapter with a deep dive into techniques for handling slowly changing dimension (SCD) attributes.

There are lots of additional topics to discuss when it comes to dimensions; hang onto your hats for more coverage in Chapter 10.

Dimension Table Keys

The first two articles in this chapter focus on the value of using meaningless surrogate keys as the primary keys of your dimension tables.

9.1 Surrogate Keys

Ralph Kimball, DBMS, May 1998

According to the Webster's Unabridged Dictionary, a surrogate is an "artificial or synthetic product that is used as a substitute for a natural product." That's a great definition for the surrogate ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470563106Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design