What is Dimensional Modeling

Dimensional modeling is a data modeling technique in data warehouse design. Dimensional models use facts and dimensions to describe data for the business. Facts are typically numeric values that are additive (can be aggregated). Dimensions are descriptive elements used for grouping, labeling, and filtering facts. In the schema below:

  • units_sold is a fact
  • date, store, and product are dimensions
  • all the other data elements are attributes of a dimension

Dimensional models are typically represented using a star schema.  Below is a very simple star schema.  However, star schemas are typically built for simplicity to aid in understanding.

Star Schema

The benefits of dimensional modeling are:

  1. Understandability – The dimensional model is very understandable by the business. In addition, this model is completely based on business terms, so that the business knows what each fact, dimension, or attribute means.
  2. Query performance – Dimensional models are denormalized and optimized for fast data querying. Many relational database platforms recognize this model and optimize query execution plans to aid in performance.
  3. Extensibility – Dimensional models can easily accommodate change. Dimension tables can have more columns added to them without affection existing business intelligence applications using these tables. Fact tables are also extensible, but need more planning when adding new dimensions so that they do not disrupt existing queries that are using the fact table.

One of the best books on dimensional modeling is The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling.  I highly recommend that you buy a copy if you are interested in learning more about dimensional modeling.  Over the next few months, I will be discussing the process of developing a dimensional model. Feel free to bookmark this site if you are interested in following along or choose one of the follow options listed at the top of the navigation sidebar on the left side of this page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s