Basics: What is a Dimensional Model

In a recent post, I asserted that a star schema is the implementation of a dimensional model in a relational database.  Many readers may have missed this point, because it was embedded in a conversation about the term normalization. 

Here, then, is a clutter-free discussion the dimensional model.

Activities and Conditions

A dimensional model is a structured framework for measurement.   

Usually, a dimensional model describes a process or activity.  For example, a retailer might design a dimensional model of sales transactions.

A dimensional model may also describe conditions, as measured at predefined intervals. A water utility might design a dimensional model of reservoir levels, measured daily.

Facts and Dimensions

A dimensional model describes measurement of a process (or conditions) through facts and dimensions.

Facts are the measurements.  For the retailer's sales activities, facts that measure each sales transaction include the quantity sold and the price paid.  For the utility's reservoir conditions, a single fact is measured: gallons on hand.

Facts are not useful without context.  For example, "one hundred units sold," is a measurement of quantity sold, but it has no context.  One hundred of what?  When were they sold?  Where?

Dimensions are used to describe the context of facts.  For the retailer, dimension values provide context for each measurement.  These include the date and time of each purchase, the product sold, and the store in which it was purchased. 

For the utility's model of reservoir status, dimensions include the date the measurement was taken, the reservoir or facility where the measurement was taken, and the inspector who measured it.

If you can define a process, facts and dimensions, you have the core of a dimensional model.  Of course, there is a bit more to it than that (the concept of grain is also essential), but that's the basic idea.

Uses

The dimensional model is most famous for its role as the basis for a star schema.  Before we get to that, though, its important to recognize that it has other uses.

First, a dimensional model is an excellent way to describe requirements for an analytic system.  A model of process measurement is far more efficient and flexible than a list of of specific business questions.  A single dimensional model may be able to answer thousands of questions, including some that have not yet been thought of.

A dimensional model is also an excellent tool for planning your data warehouse strategy, and for managing the scope of implementation projects.  This is particularly true when the model is translated to a database design and linked to sources of data.  (I've written about this before.)
 
Stars, Snowflakes, Cubes

A dimensional model can serve as the basis for a database design.  This, of course, is what it is famous for.  (If you can even say it is famous, that is.)

• When a dimensional model is implemented in a relational database, it is called a star schema (or sometimes a snowflake schema.)  
• When implemented in a multidimensional database, it is called a cube.  (I touched on this previously, as well.)

If you work with any of these things, you are working with a dimensional model.

- Chris

Image: Slide together Polyhedra by fdecomite

Licensed under Creative Commons 2.0

TDWI Members: Read Chris's Column in Flashpoint

If you are a member of The Data Warehousing Institute, be sure to check out the April 1, 2010 issue of Flashpoint.

My article The Hidden Value of Dimensional Design explains how you can use dimensional design to cultivate a shared understanding of project scope between business and technical personnel.

This publication is only available to TDWI members.

If you are not a member (or if you want to read more on the topic) have a look at this blog post I wrote in 2007:  Drive Warehouse Strategy With A Dimensional Model.

- Chris

Pre-order Chris's New Book

Star Schema: The Complete Reference is now available for pre-order at Amazon.com in the US and in several other countries.

[ UPDATE 8/20/10:  Now shipping!  Use the links below to order. For more information, see this post.]

Use these links to pre-order your copy from Amazon in your country:

• United States at Amazon.com
• Canada at Amazon.ca*
• France at Amazon.fr
  
• Germany at Amazon.de 
• Japan at Amazon.jp
• United Kingdom at Amazon.co.uk  

* At the time of this post, Amazon Canada was not yet taking pre-orders.  However, you can sign up to be notified when pre-ordering is possible.

Star Schema: The Complete Reference
By Christopher Adamson
ISBN 0071744320 / 9780071744324
McGraw-Hill Osborne Media

Star Schema: The Complete Reference is Chris Adamson's comprehensive guide to dimensional modeling, written for anyone who works with star schemas, snowflakes or cubes. Its breadth and depth make it the definitive reference, ideal for beginners and experts alike.

Offering clear and easy to understand explanations of complex topics, it teaches the best practices of dimensional modeling and their underlying rationales, equipping you to make informed design decisions.

This reference is organized around dimensional design topics rather than vertical industry, so you can find what you need when you need it.

Architecture-neutral, the book’s practical advice can be put to use in any type of data warehouse, including those advocated by W.H. Inmon and Ralph Kimball.

Coverage begins with fundamentals, then follows a logical progression through advanced topics that address real-world complexity. All concepts are illustrated through real-world examples.

The book also explores the impact of design decisions on the BI and ETL processes, and discusses situations where it is useful to deviate from best practices.

The final chapter provides a comprehensive set of tasks and deliverables that can be incorporated into any project, regardless of architecture, scope or methodology.