Summary: The Data Warehouse Toolkit

The GIST

The Data Warehouse Toolkit by Ralph Kimball, written with Margy Ross for its later editions, is the book that introduced dimensional modeling to the data world. First published in 1996, with a third edition from Wiley in 2013, it teaches how to turn the tangled records of a business into a structure that analysts, managers and reporting tools can explore quickly and confidently. Its core move is to split data into fact tables, which hold the measurements a business cares about, and dimension tables, which hold the descriptive context that gives those measurements meaning. Around that simple split Kimball builds a large vocabulary of patterns: conformed dimensions, slowly changing dimensions, bridge tables, audit dimensions and many more. Data engineers and analysts still use these terms every day, often without knowing where they came from.

The book has a reputation in two parts. Fans call it the classic of its field, the volume most data warehousing and business intelligence professionals have read cover to cover. One reader describes finishing it full of "ah-ha" moments after realizing that the database he had queried for years was a textbook Kimball design. Critics, many of whom also admire it, warn that parts have aged. Long stretches cover the optimization details of the relational databases and OLAP cubes of its era, and modern columnar cloud warehouses have made much of that obsolete. Analytics writer Cedric Chin puts it this way: the Toolkit is really two books in one cover. One is a set of lasting principles. The other is an implementation manual tied to older technology. The principles, summarized below, are why the book still matters.

The Big Ideas

Model the Business, Not the Source System

Kimball's starting point is that a data warehouse exists to answer business questions, so it should be organized around business processes rather than around the shape of the operational systems that produced the data. The book is built to match. Instead of presenting ideas in the abstract, each chapter takes up a business activity: retail sales first, then inventory, then procurement, and onward through a long run of increasingly complex case studies. Each chapter introduces one or more new modeling techniques in the context of a real problem. The third edition includes sample bus matrices for 12 case studies.

At the center of the method is Kimball's four-step dimensional modeling design process, the backbone of the book's "Fundamental Concepts." The authors present design as a team activity. The third edition gives guidance on collaborative, interactive modeling sessions with business stakeholders, on the view that the people who run the business must help define how its data is shaped. Modern readers should note that today's teams rarely do this kind of full up-front effort. Cloud tools make models cheap to rebuild, so the common practice now is to start with lightly transformed data and remodel in the Kimball manner once real usage shows what is needed.

The fundamentals also cover how measurements behave. The book separates facts that are fully additive, which can be summed in any direction, from those that are semi-additive. Its inventory example makes the difference clear. Quantity sold can be added up across days without trouble. Quantity on hand and inventory valuation cannot simply be summed over time, because adding up daily balances gives nonsense. To get an average balance, you divide the sum by the number of daily observations. Small distinctions like this decide whether a report is correct or misleading. The book's fact table material identifies three main kinds of fact tables, plus a fourth, rarely used kind, each suited to a different way that business events and states get recorded.

On the dimension side, Kimball covers ideas that have outlasted the technology of his day. A degenerate dimension is an identifier, such as a transaction number, that lives in the fact table without needing its own dimension table. Multiple hierarchies let one dimension roll up along more than one path. The book's short treatment of surrogate, natural, durable and supernatural keys explains why a warehouse should usually assign its own keys instead of trusting the identifiers that source systems hand it. A 32-bit surrogate key, the authors note, can handle about 4 billion values. Readers also get a practical tool: the Kimball Group publishes a sample date dimension spreadsheet to go with Chapter 3, reflecting the book's view that a rich calendar table is basic equipment for any warehouse.

Integration Through Conformed Dimensions

A warehouse built one business process at a time could easily become a set of disconnected islands, with sales data that cannot be compared to inventory data and customer records that disagree across departments. Kimball's answer is conformed dimensions: shared, consistently defined dimensions, such as product, customer or date, used across many fact tables. When different processes use the same dimensions, their measurements can be lined up and compared.

The tool for planning this is the Enterprise Data Warehouse Bus Architecture and its companion, the Bus Matrix. The matrix is a grid with business processes along one side and dimensions along the other, showing which processes share which dimensions. It works as both a blueprint and a roadmap, so a team can deliver one process at a time while knowing everything will fit together. The book pairs it with an Opportunity/Stakeholder Matrix, which maps business processes to the groups that care about them.

Some modern commentators say this architecture is no longer the recommended approach, given how powerful cloud data warehouses have become. They still encourage readers to study it, because it shows the ground the field has already covered and the integration problems that any data team eventually meets.

Slowly Changing Dimensions

The best-known idea in the book, and the one even its sharpest critics call fully relevant today, is the slowly changing dimension. Business descriptions change over time. A customer moves, a product is reassigned to a new category, a salesperson changes territory. The warehouse has to decide what to do with the history. Should old sales be reported under the old category or the new one? Should the change be recorded, overwritten or both?

Kimball's answer is a numbered family of techniques. The third edition expands it to cover types 0 through 7, ranging from keeping an attribute fixed forever, to overwriting it, to adding new rows that keep the full history, to hybrid approaches that let analysts see data either as it was or as it is now. The value of the framework is that it names the choices. Instead of improvising every time an attribute changes, a team can ask which type of change it needs and apply a known pattern. A related design tip suggests adding a change reason attribute so the warehouse records why a change happened as well as when. Modern writers have revisited these techniques for cloud warehouses, but the basic choices Kimball laid out still shape how teams handle history.

Taming Awkward Hierarchies and Attributes

Real businesses rarely fit neat tree structures, and the book spends real effort on the messy cases. Its section on dimension hierarchies prepares readers for situations a simple model cannot handle. One example is an employee structure where colleagues can act as each other's managers on particular projects. For ragged, variable-depth hierarchies, where some branches run deeper than others, and for multivalued attributes, where one record links to several values at once, the third edition gives expanded coverage of bridge tables, linking structures that resolve these many-to-many tangles without breaking the dimensional model.

The book also has a set of tools for keeping dimensions manageable. Junk dimensions collect scattered low-cardinality flags and indicators into one tidy table. Mini-dimensions split off frequently changing attributes so they do not bloat a large dimension. Shrunken dimensions are trimmed-down versions of a full dimension for use with summarized data. The Kimball Group later expanded each of these in standalone design tips, a sign of how much practitioners use them. Even readers who never meet a ragged hierarchy come away with a bigger toolbox and a better sense of which odd business situations a dimensional model can absorb.

Advanced Facts and Built-In Data Quality

Later chapters handle the timing and quality problems that come up in production systems. Two patterns stand out because they occur so often. Late arriving facts are measurements that show up after the period they describe. Late arriving dimensions are the reverse case, where a fact appears before its descriptive context exists in the warehouse. The book explains how to load both without corrupting history. Depending on the business, readers may also need its treatment of multiple currency facts and timespan tracking in fact tables.

The ideas that have aged best in this part of the book are about trust. An audit dimension attaches metadata about how and when each row was loaded, so analysts can trace and question the data they are using. Among the special purpose schemas, the error event schema records data quality failures in a structured way, which makes errors something you can measure instead of something that gets swept aside. Commentators point out that these quality-focused ideas carry over well to modern analytics, and that Uber has applied the error event approach in contemporary practice.

The Lifecycle and the Machinery of ETL

The Toolkit also covers delivery. The third edition includes an updated overview of the Kimball DW/BI project lifecycle methodology, its approach to planning and running warehouse projects from requirements through deployment. It also gives a full review of extract, transform and load (ETL) systems, broken down into 34 subsystems and techniques for populating dimensional models. The idea behind this is that a good model is only as reliable as the pipeline that fills it. The newest edition adds recommended best practices for big data analytics, an attempt to connect the classic methods to newer data volumes.

The third edition also changed the book's structure. Readers had long complained that the case-study format buried each technique deep inside chapters full of domain explanation. In response, Kimball and Ross added a new Chapter 2, "Kimball Dimensional Modeling Techniques Overview," which the Kimball Group calls the "official" library of their techniques. It works as an index to ideas spread across the rest of the book, and it is the main reason experienced readers insist on the third edition.

Putting It to Work

• Read the third edition, and start with Chapter 2. Use the techniques overview as a map. Jump to the methods you need, and read a full case-study chapter only when its business domain matters to you.
• Skip the outdated optimization material. Large sections on tuning older relational databases and OLAP cubes matter much less in the era of columnar cloud warehouses. Focus on the principles.
• Model around business processes. Ask which activity you are measuring and what the business needs to know about it before you think about tables.
• Respect additivity. Before summing a measure, check whether it is fully additive or semi-additive. Balances such as inventory on hand need averaging over time, not totaling.
• Own your keys. Use surrogate keys instead of relying on source-system identifiers, and learn the difference between natural, durable and supernatural keys.
• Choose a slowly changing dimension type on purpose. Whenever an attribute can change, decide which of types 0 through 7 fits, and consider recording the reason for each change.
• Plan integration with a bus matrix, even if you don't adopt the full architecture. Mapping processes against shared dimensions shows where conformed dimensions will pay off.
• Build quality into the model. Add audit dimensions and error event schemas so the warehouse can report on its own reliability.
• Plan for late arrivals. Facts and dimensions will show up out of order, so design your loads for it from the beginning.
• Don't over-plan up front with modern tools. Start with lightly transformed data and apply Kimball's patterns as real usage shows where structure is needed.
• Go further with a companion text. Some practitioners recommend Christopher Adamson's Star Schema: The Complete Reference as a clearer, more refined next step once Kimball has given you the foundation.

Who Should Read It

This book is for data engineers, analytics engineers, BI developers and analysts who want to understand where the field's core vocabulary came from. It suits anyone who has queried a star schema and wondered why it was built that way, and it makes a lasting reference for consultants modeling unfamiliar business processes. Expect a demanding, sometimes dated read. Readers who approach it selectively, starting with Chapter 2 and the timeless techniques, will see why it has shaped data modeling for more than a quarter of a century.

This has been a summary of The Data Warehouse Toolkit by Ralph Kimball, giving you the GIST so you can decide if you want the REST.