The Data Warehouse Toolkit: A Practical Guide to the Book

What is The Data Warehouse Toolkit?

The Data Warehouse Toolkit is a long-standing book on dimensional modelling, written by Ralph Kimball and Margy Ross. It sets out how to organise warehouse data into fact tables and dimension tables, and shows the techniques through many business scenarios.

The third edition appeared in 2013 and is the one most people buy. Earlier editions came out in the 1990s and 2000s, and the third was updated to reflect changes in the field. Check the publisher's page for the current edition before buying.

Practitioners treat it as a reference book more than a tutorial. You dip into the chapters relevant to your project, rather than reading it as a story. If you are still learning the terms, begin with a basic explanation of what a data warehouse is and come back to the book afterwards.

Our data warehouse services draw on dimensional principles like these, and the book is a good way to understand the thinking behind them.

Who are Ralph Kimball and Margy Ross, and what is the Kimball approach?

Ralph Kimball is one of the best-known writers on data warehousing, and Margy Ross is his long-time colleague and co-author. The method they set out is often called the Kimball approach or dimensional modelling.

The approach starts from how the business measures and analyses its work. Data is modelled as business processes, with measurements in fact tables and descriptive context in dimension tables. The result is a model that business users can read, and that reporting tools can query quickly.

It is often contrasted with other styles, such as building a normalised central store first, or using data vault. Each has strengths, and our comparison of data vault and data warehouse approaches explains where they differ.

What does the book cover?

The contents may differ slightly between editions, so check the table of contents for the edition you buy. In broad terms, the book covers the following.

• Dimensional modelling basics. What facts and dimensions are, how star schemas work, and how to choose the grain of a fact table.
• The design process. A repeatable method for turning a business process into a model.
• Bus architecture and the bus matrix. A planning tool for building many subject areas that fit together.
• Conformed dimensions. Shared dimensions that let separate fact tables be analysed together.
• Slowly changing dimensions. Techniques for handling attributes that change over time.
• Case studies. Worked examples from areas such as retail, orders, finance, human resources, healthcare, insurance and more.
• Loading and delivery. Guidance on the back-room processes that extract, clean and load data, and on how data reaches users.

Case studies are a strength. Seeing how an invoice, an insurance claim or a stock movement turns into a model is often more useful than abstract rules.

What is the four-step design process?

The most reusable idea in the book is a simple sequence for designing a dimensional model. It keeps design discussions focused and gives the business something concrete to review.

1. Choose the business process. Pick one activity the organisation measures, such as taking orders or handling support tickets.
2. Declare the grain. State exactly what one row in the fact table represents, such as one order line.
3. Identify the dimensions. List the context that describes each row, such as customer, product, date and location.
4. Identify the facts. List the numeric measures recorded at that grain, such as quantity and amount.

The order matters. Declaring the grain before adding dimensions and facts prevents tables that mix levels of detail. We walk through the same sequence in our guide to data warehouse design, with additional steps for testing, security and loading.

What are the bus matrix and conformed dimensions?

The bus matrix is a simple grid. Rows are business processes, such as sales, shipments or support. Columns are the shared dimensions, such as date, customer or product. A mark in a cell shows that the process uses that dimension.

It is useful for two reasons. It gives the business a one-page view of the whole warehouse plan, and it shows which dimensions are shared and therefore need to be built carefully once. It also helps you prioritise, because you can pick the next process that reuses dimensions you have already built.

A conformed dimension is a dimension with the same meaning and keys wherever it is used. If sales and support both use one customer dimension, you can compare them in a single report. If each has its own version of customer, the numbers will not line up. Agreeing these shared definitions is mostly a business task, not a technical one.

How does the book deal with slowly changing dimensions?

Dimension attributes change. A customer moves, a product is renamed, a sales region is redrawn. The book describes several standard techniques for handling this, usually called slowly changing dimension types. The most common are summarised below.

The book describes further variants and hybrids, so check it for details. In practice, Type 1 and Type 2 cover most needs. The key is to decide, attribute by attribute, whether history matters to the business, and to record that decision.

What does a worked example look like?

Here is a hypothetical example, using the four steps for a support team. It is illustrative only.

From this, a manager can ask how many high-priority tickets each team closed last month, or whether handling time is rising for one customer group. The same customer and date dimensions could later be reused for sales, which is where the bus matrix earns its place.

Notice what the example leaves out. It does not mention a platform, a tool or a pipeline. That is deliberate. The model is agreed with the business first, and the technology is chosen afterwards.

Who should read it?

The book suits several readers, though not all in the same way.

• Data engineers and warehouse developers. It gives a shared vocabulary and tested patterns for modelling.
• BI developers and Power BI report builders. Understanding facts, dimensions and grain helps you build cleaner models and simpler measures.
• Analysts and data leads. Even without building, you can use the ideas to ask better questions of a data team.
• Project sponsors and managers. The chapters on planning, the bus matrix and business engagement help set realistic scope.

It is probably not the right first book for someone with no data background. It assumes some comfort with databases and reporting. Beginners may prefer a short introduction to warehouse basics before tackling a long reference.

Which parts are still useful, and which need adapting for cloud?

The modelling principles are independent of technology, which is why the book has lasted. Choosing the grain, building conformed dimensions and handling history are as relevant on a cloud platform as they were on a server in a back office.

The parts that need adapting are those tied to the technology of the time. Cloud platforms separate storage from compute, handle very large tables, and often load first and transform afterwards. Some performance advice, such as indexing and aggregate tables, may be less central or work differently now.

Treat the book as the source for the model and your platform's documentation as the source for the build. If you are on Microsoft tools, our Azure cloud services team can help you map one to the other.

How do you apply the book in a real project?

Do not try to implement everything. Pick one business process that matters, and follow the four steps. Draw the star schema on a whiteboard with the business owner before anyone writes a pipeline.

1. Draft a bus matrix. List the main business processes and shared dimensions, even if you will only build one first.
2. Pick the first process. Choose one with high value and reachable data, such as sales or support tickets.
3. Declare the grain and review it. Confirm it with the people who know the process.
4. Model one star schema. Keep names and definitions in business language.
5. Build the shared dimensions carefully. Date, customer and product are reused, so invest in them.
6. Deliver and review. Release the first star, collect feedback and extend using the matrix.

Then connect it to reporting. A clean dimensional model is a good foundation for Power BI and other tools. If your current pain is spreadsheets, stopping manual exports into Excel is often a good first outcome to target.

Is the book enough on its own?

No single book is. The Data Warehouse Toolkit teaches modelling very well, but it does not choose a platform for you, set your security rules or run your pipelines. You will also want guidance on data warehouse architecture requirements, current platform documentation and your own data protection duties.

It also reflects one school of thought. Other approaches exist, and a good practitioner knows when each fits. If you want help applying dimensional modelling to your own data, a data warehouse consultancy can review your model or run the design with you.

If you want to test a model with business users before building it, business intelligence consultants can help prototype reports on a small data sample. And if your data prep happens in Excel or Power BI today, Power Query explained shows how that work can feed a dimensional model.

Finally, you do not need to agree with every point in the book to benefit from it. The core ideas are widely used because they make warehouses easier to understand and to use.