Table of Contents

Introduction

Intended Audience and Goals
Preview of Contents
Navigation Aids
Terminology Notes

Chapter 1: The Reader at a Glance

Setting Up for Success
Tackling DW/BI Design and Development

Chapter 2: Before You Dive In

Before Data Warehousing
Historical Perspective
Dealing with Demanding Realities

Chapter 3: Project/Program Planning

Professional Responsibilities
Justification and Sponsorship
Kimball Methodology

Chapter 4: Requirements Definition

Gathering Requirements
Organizing around Business Processes
Wrapping Up the Requirements

Chapter 5: Data Architecture

Making the Case for Dimensional Modeling
Enterprise Data Warehouse Bus Architecture
Agile Project Considerations
Integration Instead of Centralization
Contrast with the Corporate Information Factory

Chapter 6: Dimensional Modeling Fundamentals

Basics of Dimensional Modeling
Dos and Don'ts
Myths about Dimensional Modeling

Chapter 7: Dimensional Modeling Tasks and Responsibilities

Design Activities
Design Review Activities

Chapter 8: Fact Table Core Concepts

Granularity
Types of Fact Tables
Parent-Child Fact Tables
Fact Table Keys and Degenerate Dimensions
Miscellaneous Fact Table Design Patterns

Chapter 9: Dimension Table Core Concepts

Dimension Table Keys
Date and Time Dimension Considerations
Miscellaneous Dimension Patterns
Slowly Changing Dimensions

Chapter 10: More Dimension Patterns and Considerations

Snowflakes, Outriggers, and Bridges
Dealing with Hierarchies
Customer Issues
Addresses and International Issues
Industry Scenarios and Idiosyncrasies

Chapter 11: Back Room ETL and Data Quality

Planning the ETL System
Data Quality Considerations
Populating Fact and Dimension Tables
Supporting Real Time

Chapter 12: Technical Architecture Considerations

Overall Technical/System Architecture
Presentation Server Architecture
Front Room Architecture
Metadata
Infrastructure and Security Considerations

Chapter 13: Front Room Business Intelligence Applications

Delivering Value with Business Intelligence
Implementing the Business Intelligence Layer
Mining Data to Uncover Relationships
Dealing with SQL

Chapter 14: Maintenance and Growth Considerations

Deploying Successfully
Sustaining for Ongoing Impact

Chapter 15: Final Thoughts

Key Insights and Reminders
A Look to the Future

Article Index

Index

End User License Agreement

List of Illustrations

Chapter 1: The Reader at a Glance

Figure 1.1 Drill-across report combining data from three subject area fact tables.
Figure 1.2 Bus matrix for a manufacturer's EDW.
Figure 1.3 Drill-across report with a dimension version mismatch.
Figure 1.4 Employee dimension designed for type 2 SCD.
Figure 1.5 Adding dimension attributes and numeric facts through simple user interface gestures.
Figure 1.6 Sample drill-across report.
Figure 1.7 The three different types of fact tables.

Chapter 2: Before You Dive In

Figure 2.1 Dimensional schema example.
Figure 2.2 Conformed dimensions used by multiple fact tables.
Figure 2.3 The enterprise data warehouse, showing the back room ETL staging area and the front room presentation area.
Figure 2.4 The data warehouse bus architecture, showing a series of data marts connecting to the conformed dimensions of the enterprise.

Chapter 3: Project/Program Planning

Figure 3.1 The Kimball Lifecycle diagram.
Figure 3.2 Impact versus feasibility prioritization.

Chapter 4: Requirements Definition

Figure 4.1 The intersection of business requirements and realities.
Figure 4.2 Matrix of business processes mapped to strategic business initiatives.
Figure 4.3 Simplified enterprise data warehouse bus matrix.
Figure 4.4 Opportunity matrix.
Figure 4.5 Prioritization grid.

Chapter 5: Data Architecture

Figure 5.1 A dimensional model for grocery store sales.
Figure 5.2 Two representations of a product dimension—the top snowflaked version is normalized to 3NF, and the bottom is denormalized into a flat dimension.
Figure 5.3 A high level 3NF model.
Figure 5.4 A detailed dimensional model for retail point of sales. Numbers 1 through 4 show places where the design may be gracefully extended.
Figure 5.5 The suppliers and parts database.
Figure 5.6 Enterprise data warehouse bus matrix for a telecommunications company.
Figure 5.7 A detailed data warehouse bus matrix.
Figure 5.8 Independent data marts/warehouses.
Figure 5.9 Dimensional data warehouse.
Figure 5.10 Normalized data warehouse with departmental data marts.
Figure 5.11 Hybrid of normalized data warehouse and dimensional data warehouse.

Chapter 6: Dimensional Modeling Fundamentals

Figure 6.1 A dimensional model for grocery store sales.
Figure 6.2 Dragging dimension attributes and facts into a report.
Figure 6.3 A product dimension table with both marketing and finance attributes.
Figure 6.4 Separate fact tables for each business process share common dimensions.
Figure 6.5 Two fact tables with conformed dimensions.
Figure 6.6 Potential changes to an existing dimensional model.

Chapter 7: Dimensional Modeling Tasks and Responsibilities

Figure 7.1 Customer invoice line item fact table.
Figure 7.2 Two subject areas with conformed dimensions.
Figure 7.3 An example of a “bad” fact table.
Figure 7.4 An example of a “good” fact table addressing the issues in Figure 7.3.
Figure 7.5 A customer billing fact table.
Figure 7.6 Example high level bubble chart diagram.

Chapter 8: Fact Table Core Concepts

Figure 8.1 Example procurement accumulating snapshot.
Figure 8.2 A transaction grained insurance fact table.
Figure 8.3 Three fact table grains in insurance.
Figure 8.4 Accumulating snapshot for the student admissions pipeline.
Figure 8.5 Daily student attendance is recorded in a factless fact table.
Figure 8.6 Factless coverage fact table for promotion events.
Figure 8.7 Bad idea #1—The transaction header is a dimension table.
Figure 8.8 Bad idea #2—None of the header dimensionality is directly joined to the line items.
Figure 8.9 Recommended structure for modeling header/line item transactions.
Figure 8.10 Fact table to capture customer information changes.
Figure 8.11 Balance transactions that reference the account surrogate key.

Chapter 9: Dimension Table Core Concepts

Figure 9.1 A sample data warehouse schema.
Figure 9.2 A durable key in an account dimension.
Figure 9.3 Duplicate product entries.
Figure 9.4 A basic dimensional design for order tracking.
Figure 9.5 The specific meaning of the transaction date depends on the transaction type dimension.
Figure 9.6 A dimension table with a date foreign key, as well as a date stamp attribute.
Figure 9.7 Multiple dates on an accumulating snapshot of orders data.
Figure 9.8 Multiple airports on a flight segment fact table.
Figure 9.9 Multiple business entities associated with a phone call.
Figure 9.10 Sample schema with extensive role playing.
Figure 9.11 The logical progression of transforming complex source data into its corresponding dimensional model.
Figure 9.12 A typical dimensional schema for a set of retail sales transactions with a “causal” promotion dimension.
Figure 9.13 Customer dimension with rapidly changing demographic attributes.
Figure 9.14 Separating frequently changing customer attributes in a demographics mini-dimension.
Figure 9.15 A mini-dimension for capturing profile changes with type 1 overwrite in the primary dimension table.
Figure 9.16 Tracking both “as was” and current profile attributes in a single dimension table.
Figure 9.17 One foreign key in the fact table joins to a SCD2 dimension table, while the natural/durable key joins to only the most current version of the dimension.
Figure 9.18 A dimension table with a series of type 3 attributes to track predictable changes.
Figure 9.19 Add a mini-dimension to make SCD type 4.
Figure 9.20 Add a mini-dimension and type 1 outrigger to make SCD type 5.
Figure 9.21 Add type 1 attributes to type 2 dimension to make SCD type 6.
Figure 9.22 Add dual type 1 and type 2 dimensions to make SCD type 7.
Figure 9.23 Slowly changing dimension techniques summary.
Figure 9.24 Customer dimension table with type 2 administration control columns.

Chapter 10: More Dimension Patterns and Considerations

Figure 10.1 Denormalized and snowflaked versions of the same dimension table.
Figure 10.2 A date dimension outrigger joined to the employee dimension.
Figure 10.3 A bridge table resolves a many-valued dimension attribute.
Figure 10.4 A bridge table models a hierarchical variable-depth relationship.
Figure 10.5 Shopper dimension with both visitor and customer attributes.
Figure 10.6 A shopper dimension with a snowflake for customer attributes.
Figure 10.7 A financial product dimension with a subdimension for each product type.
Figure 10.8 A calendar dimension with a higher cardinality subdimension.
Figure 10.9 A typical dimensional model for ATM transactions.
Figure 10.10 A healthcare billing fact table.
Figure 10.11 A bridge table to handle multiple diagnoses.
Figure 10.12 Resolving the relationship of multiple customers associated with an account using a bridge table.
Figure 10.13 The fact table for a letter archive data warehouse showing the keyword bridge dimension table.
Figure 10.14 The same fact table showing the keyword dimension containing a variable-length text string keyword list.
Figure 10.15 A banking example showing an account-to-customer bridge table.
Figure 10.16 A sales fact table capturing multiple sales reasons.
Figure 10.17 Multiple sales reasons modeled as a pivoted sales reason dimension.
Figure 10.18 The account-to-customer bridge table without the mini-dimension.
Figure 10.19 The account-to-customer bridge table with the demographics mini-dimension.
Figure 10.20 Graphical representation of multiple date hierarchies.
Figure 10.21 Sample source data for a product dimension.
Figure 10.22 Normalized hierarchies in the source or ETL staging area.
Figure 10.23 A fact table whose grain is the individual invoice line item.
Figure 10.24 A schematic diagram of customer organizations that Big Nine Consultants sells consulting services to.
Figure 10.25 A bridge table between the fact and dimension table to navigate the organizational hierarchy.
Figure 10.26 Basic structure of the employee dimension and reports-to hierarchy.
Figure 10.27 Classic relational structure for a reports-to hierarchy.
Figure 10.28 Separate employee and reports-to (or job) dimensions.
Figure 10.29 Tracking history in the reports-to relationship with a natural key bridge table.
Figure 10.30 Forced fixed-depth reports-to hierarchy.
Figure 10.31 Sample org chart with pathstring values.
Figure 10.32 Using a bridge table to support multiple alternate hierarchies.
Figure 10.33 Treating satisfaction metrics as facts.
Figure 10.34 Using a bridge table between the transaction fact table and a satisfaction dimension.
Figure 10.35 Design for real-time customer status tracking with historical and current perspectives.
Figure 10.36 A typical fact table recording sales made in different time zones, with GMT as the absolute time stamp.
Figure 10.37 Primary and supplementary calendar dimensions for organizations that must track multiple national calendars.
Figure 10.38 A fact table simultaneously recording sales in many different currencies. The currency conversion table is only used when converting to a third currency.
Figure 10.39 A claims transaction dimensional model.
Figure 10.40 A claims monthly snapshot dimensional model.
Figure 10.41 Inappropriate way to handle the facts unique to each line of business.
Figure 10.42 Monthly snapshot for automobile coverages.
Figure 10.43 The flight segment dimensional model.
Figure 10.44 A human resources monthly snapshot with a type 2 slowly changing employee dimension table.
Figure 10.45 This value chain represents a product flow where each process is a fact table.
Figure 10.46 The budget chain showing the budget, commitments, and payments fact tables.
Figure 10.47 Compliance-enabled fact and dimension tables.
Figure 10.48 The dimensional model for clickstream events.
Figure 10.49 A market basket fact table based on atomic retail sales data.

Chapter 11: Back Room ETL and Data Quality

Figure 11.1 Simplified conceptual ETL plan.
Figure 11.2 Target customer dimension table.
Figure 11.3 Sample source rows.
Figure 11.4 Typical architecture to support FTP-based integration.
Figure 11.5 The error event schema.
Figure 11.6 A sample audit dimension attached to a shipments invoice fact table.
Figure 11.7 Normal and instrumented reports using the audit dimension.
Figure 11.8 An insurance fact table augmented with an exposure confidence fact and uncertainty dimension.
Figure 11.9 A simple audit dimension.
Figure 11.10 Normal and instrumented reports using the audit dimension.
Figure 11.11 Sample source data.
Figure 11.12 Standardized company names for the sample source data.
Figure 11.13 The original loading of a dimension where surrogate keys are assigned sequentially to every input record and the natural key becomes an ordinary attribute.
Figure 11.14 The lookup table for a typical dimension with as many rows as there are unique natural keys.
Figure 11.15 Dimension processing logic for all refreshes of a dimension table after the original load.
Figure 11.16 The pipelined fact table processing logic for replacing all natural keys (designated as IDs) with surrogate keys.
Figure 11.17 Sample source data.
Figure 11.18 Sample junk dimension rows.
Figure 11.19 Source transaction data and associated diagnosis group table.
Figure 11.20 Diagnosis Bridge table and associated ICD10_Diagnosis dimension.
Figure 11.21 The web-intensive data warehouse architecture, showing the hot response cache and the application server that creates its contents.

Chapter 12: Technical Architecture Considerations

Figure 12.1 Approach 1: Managing master data in the data warehouse ETL.
Figure 12.2 Approach 2: Implementing MDM via an integration hub.
Figure 12.3 Approach 3: The enterprise MDM system.
Figure 12.4 The extended RDBMS–based architecture for an enterprise data warehouse.
Figure 12.5 Comparison of the storage, metadata, and query layers between conventional RDBMSs and the Hadoop data warehouse stack.
Figure 12.6 HDFS viewed as a data warehouse environment.
Figure 12.7 Capability comparison of conventional RDBMS and Hadoop systems.
Figure 12.8 The aggregate navigator sits between the user application and DBMS.
Figure 12.9 Aggregate records identified by the level attributes, which is not recommended.
Figure 12.10 Aggregate records stored in separate fact and shrunken dimension tables is recommended.
Figure 12.11 A base-level dimensional model.
Figure 12.12 An aggregated dimensional schema derived from Figure 12.11 by aggregating to the category level.
Figure 12.13 The three-way aggregate table formed by aggregating time to month, product to category, and store to region.
Figure 12.14 A typical storage area network configuration.

Chapter 13: Front Room Business Intelligence Applications

Figure 13.1 The analytic application lifecycle.
Figure 13.2 A sample report mockup.
Figure 13.3 Sample SQL to hand off data from the warehouse to a data mining application.
Figure 13.4 The overall data mining process.
Figure 13.5 The primary data sets used by data mining tools.
Figure 13.6 Sample data from the two subqueries and the final drill-across results.
Figure 13.7 A sample report comparing this year's sales to last year.
Figure 13.8 A more interesting and challenging set of comparison metrics versus Figure 13.7.
Figure 13.9 Sample report illustrating several sequential operations.
Figure 13.10 Non-additive calculations, like average price, must be performed where all the underlying calculation components are available.
Figure 13.11 Sample report with rankings.
Figure 13.12 Sample report with product break rows.
Figure 13.13 A special behavior dimension is added to a normal dimensional model to restrict the schema to the context of a behaviorally defined group of products.
Figure 13.14 A sample schema with stock prices.

Chapter 15: Final Thoughts

Figure 15.1 Traditional and Supernova Customer Dimensions.

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

Published by

John Wiley & Sons, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Published simultaneously in Canada

ISBN: 978-1-119-21631-5

ISBN: 978-1-119-23879-9 (ebk)

ISBN: 978-1-119-21659-9 (ebk)

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifi cally disclaim all warranties, including without limitation warranties of fi tness for a particular purpose. No warranty may be created or extended by sales or promotional materials.

The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2015956232

Trademarks: Wiley, and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affi liates, in the United States and other countries, and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not associated with any product or vendor mentioned in this book.

Introduction

The Kimball Group's article and Design Tip archive has been the most popular destination on our website (www.kimballgroup.com). Stretching back twenty years to Ralph's original 1995 DBMS magazine articles, the archive explores more than 250 topics, sometimes in more depth than provided by our books or courses.

With The Kimball Group Reader, Second Edition, we have organized all of the articles in a coherent way. But The Reader is more than merely a collection of our past magazine articles and Design Tips verbatim. We have trimmed the redundancy, made sure all the articles are written with the same consistent vocabulary, and updated many of the figures. This is a new and improved remastered compilation of our writings.

After considerable discussion, we decided to update many time references and edit content throughout the book to provide the perspective of 2015 rather than leaving old dates or outdated concepts in the articles. Thus an article written in 2007 may use 2015 in an example! When articles refer to the number of years that have passed, we have updated these references relative to 2015. For example, if a 2005 article originally said “during the past five years,” the article now reads “during the past fifteen years.” Mentions regarding our years of experience, number of books sold, articles written, or students taught have also been updated to 2015 figures. Finally, we occasionally changed references from outmoded technologies such as “modems” to more modern technologies, especially “internet.” We trust these changes will not mislead or cause confusion, but rather make your reading experience more natural.

Intended Audience and Goals

The primary reader of this book should be the analyst, designer, modeler, or manager who is delivering a data warehouse in support of business intelligence. The articles in this book trace the entire lifecycle of DW/BI system development, from original business requirements gathering all the way to final deployment. We believe that this collection of articles serves as a superb reference-in-depth for literally hundreds of issues and situations that arise in the development of a DW/BI system.

The articles range from a managerial focus to a highly technical focus, although in all cases, the tone of the articles strives to be educational. These articles have been accessed thousands of times per day on the Kimball Group website over a span of 20 years, so we're confident they're useful. This book adds significant value by organizing the archive, and systematically editing the articles to ensure their consistency and relevance.

Preview of Contents

Following two introductory chapters, the book's organization will look somewhat familiar to readers of The Data Warehouse Lifecycle Toolkit, Second Edition (Wiley, 2008) because we've organized the articles topically to correspond with the major milestones of a data warehouse/business intelligence (DW/BI) implementation. Not surprisingly given the word “Kimball” is practically synonymous with dimensional modeling, much of The Reader focuses on that topic in particular.

Chapter 1: The Reader at a Glance. We begin the book with a series of articles written by Ralph several years ago for DM Review magazine. This series succinctly encapsulates the Kimball approach in a cohesive manner, so it serves as a perfect overview, akin to CliffsNotes, for the book.
Chapter 2: Before You Dive In. Long-time readers of Ralph's articles will find that this chapter is a walk down memory lane, as many of the articles are historically significant. Somewhat amazingly, the content is still very relevant even though most of these articles were written in the 1990s.
Chapter 3: Project/Program Planning. With an overview and history lesson under your belt, Chapter 3 moves on to getting the DW/BI program and project launched. We consider both the project team's and sponsoring stakeholders' responsibilities, and then delve into the Kimball Lifecycle approach.
Chapter 4: Requirements Definition. It is difficult to achieve DW/BI success in the absence of business requirements. This chapter delivers specific recommendations for effectively eliciting the business's needs. It stresses the importance of organizing the requirements findings around business processes, and suggests tactics for reaching organizational consensus on appropriate next steps.
Chapter 5: Data Architecture. With a solid understanding of the business requirements, we turn our attention to the data (where we will remain through Chapter 11). This chapter begins with the justification for dimensional modeling. It then describes the enterprise data warehouse bus architecture, discusses the agile development approach to support data warehousing, provides rationalization for the requisite integration and stewardship, and then contrasts the Kimball architecture with the Corporate Information Factory's hub-and-spoke.
Chapter 6: Dimensional Modeling Fundamentals. This chapter introduces the basics of dimensional modeling, starting with distinguishing a fact from a dimension, and the core activities of drilling down, drilling across, and handling time in a data warehouse. We also explore familiar fables about dimensional models.
Chapter 7: Dimensional Modeling Tasks and Responsibilities. While Chapter 6 covers the fundamental “what and why” surrounding dimensional modeling, this chapter focuses on the “how, who, and when.” Chapter 7 describes the dimensional modeling process and tasks, with the aim of organizing an effective team, whether starting with a blank slate or revisiting an existing model.
Chapter 8: Fact Table Core Concepts. The theme for Chapter 8 could be stated as “just the facts, and nothing but the facts.” We begin by discussing granularity and the three fundamental types of fact tables, and then turn our attention to fact table keys and degenerate dimensions. The chapter closes with a potpourri of common fact table patterns, including null, textual, and sparsely populated metrics, as well as facts that closely resemble dimension attributes.
Chapter 9: Dimension Table Core Concepts. We shift our focus to dimension tables in Chapter 9, starting with a discussion of surrogate keys and the ever-present time (or date) dimensions. We then explore role playing, junk, and causal dimension patterns, before launching into a thorough handling of slowly changing dimensions, including four new advanced dimension types. Hang onto your hats.
Chapter 10: More Dimension Patterns and Considerations. Chapter 10 complements the previous chapter with more meaty coverage of dimension tables. We describe snowflakes and outriggers, as well as a significantly updated section on bridges for handling both multi-valued dimension attributes and ragged variable hierarchies. We discuss nuances often encountered in customer dimensions, along with internationalization issues. The chapter closes with a series of case studies covering insurance, voyages and networks, human resources, finance, electronic commerce, text searching, and retail; we encourage everyone to peruse these vignettes as the patterns and recommendations transcend industry or application boundaries.
Chapter 11: Back Room ETL and Data Quality. We switch gears from designing the target dimensional model to populating it in Chapter 11. Be forewarned: This is a hefty chapter, as you'd expect given the subject matter. This updated edition of the Reader has a wealth of new material in this chapter. We start by describing the 34 subsystems required to extract, transform, and load (ETL) the data, along with the pros and cons of using a commercial ETL tool. From there, we delve into data quality considerations, provide specific guidance for building fact and dimension tables, and discuss the implications of real-time ETL.
Chapter 12: Technical Architecture Considerations. It's taken us until Chapter 12, but we're finally discussing issues surrounding the technical architecture, starting with server oriented architecture (SOA), master data management (MDM), and packaged analytics. A new section on big data features two in-depth Kimball Group white papers written by Ralph. Final sections in this chapter focus on the presentation server, including the role of aggregate navigation and online analytical processing (OLAP), user interface design, metadata, infrastructure, and security.
Chapter 13: Front Room Business Intelligence Applications. In Chapter 13, we step into the front room of the DW/BI system where business users are interacting with the data. We describe the lifecycle of a typical business analysis, starting with a review of historical performance but not stopping there. We then turn our attention to standardized BI reports before digging into data mining and predictive analytics. The chapter closes by exploring the limitations of SQL for business analysis.
Chapter 14: Maintenance and Growth Considerations. In this penultimate chapter, we provide recommendations for successfully deploying the DW/BI system, as well as keeping it healthy for sustained success.
Chapter 15: Final Thoughts. The Reader concludes with final perspectives on data warehousing and business intelligence from each Kimball Group principal. The insights range from the most important hard won lessons we have learned to some glimpses of what the future of data warehousing may hold.

Navigation Aids

Given the breadth and depth of the articles in The Kimball Group Reader, we have very deliberately identified over two dozen articles as “Kimball Classics” because they captured a concept so effectively that we, and many others in the industry, have referred to these articles repeatedly over the past twenty years. The classic articles are designated with a special icon that looks like this:

We expect most people will read the articles in somewhat random order, rather than digesting the book from front to back. Therefore, we have put special emphasis on The Reader's index as we anticipate many of you will delve in by searching the index for a particular technique or modeling situation.

Terminology Notes

We are very proud that the vocabulary established by Ralph has been so durable and broadly adopted. Kimball “marker words” including dimensions, facts, slowly changing dimensions, surrogate keys, fact table grains, factless fact tables, and degenerate dimensions, have been used consistently across the industry for more than twenty years. But in spite of our best intentions, a few terms have morphed since their introduction; we have retroactively replaced the old terms with the accepted current ones.

Artificial keys are now called surrogate keys.
Data mart has been replaced with business process dimensional model, business process subject area, or just subject area, depending on the context.
Data staging is now known as extract, transform, and load.
End user applications have been replaced by business intelligence applications.
Helper tables are now bridge tables.

Since most people won't read this book from cover to cover, we need to introduce some common abbreviations up front:

DW/BI is shorthand for the end-to-end data warehouse/business intelligence system. This abbreviation is useful for brevity, but it also explicitly links data warehousing and business intelligence as codependent. Finally, it reflects the shift of emphasis from the data warehouse being an end in itself to business intelligence (BI) really driving everything we do. After all, the data warehouse is the platform for all forms of BI.
Many figures in The Reader include the DD, FK, and PK abbreviations, which stand for degenerate dimension, foreign key, and primary key, respectively.
ETL means extract, transform, and load, the standard paradigm for acquiring data and making it ready for exposure to BI tools.
ER refers to entity-relationship. We frequently use ER when we discuss third normal form (3NF) or normalized data models, as opposed to dimensional data models.
OLAP stands for online analytical processing, typically used to differentiate dimensional models captured in a multidimensional database or cube from dimensional models in a relational DBMS called star schemas. These relational star schemas are sometimes referred to as ROLAP.
SCD is the abbreviation for slowly changing dimension, referring to the techniques we've established for handling dimension attribute changes.

The Kimball Group Reader

Relentlessly Practical Tools for Data Warehousing and Business Intelligence

Remastered Collection

About the Authors

Credits

Acknowledgments

Introduction

Intended Audience and Goals

Preview of Contents

Navigation Aids

Terminology Notes