Ask the Expert

What are the differences between fact tables and dimension tables in star schemas?

More on data warehouse management

Learn the importance of balancing skills in DBA teams

Check out the latest Gartner Magic Quadrant for data warehouse services

Read about Amazon's Redshift data warehouse in the cloud

What's the difference between dimension tables and fact tables?

These two table types are used in what we call star schemas; these are used for analysis rather than capturing transactions. What is the difference between dimension tables and fact tables?

One very literal (and initially unhelpful) answer is that fact tables store facts. Happily, this definition is only unhelpful until we add an example.

Suppose our company sells products to customers. Every sale is a fact that happens within our company and the fact table is used to record these facts.

Fact table
 

    Requires Free Membership to View

TimeID ProductID CustomerID Unit Sold
4 17 2 1
8 21 3 2
8 4 1 1

Now we can add a dimension table about customers:

Dimension table: Customers
 

CustomerID Name Gender Income Education Region
1 Brian Edge M 2 3 4
2 Fred Smith M 3 5 1
3 Sally Jones F 1 7 3

By following the links we can see that, for example, row 2 in the fact table records the fact that customer 3 (Sally Jones) bought two items on day 8. And, in a complete example, we would also have a product table and a time table so that we know what she bought and exactly when.

The fact table lists events that happen in our company (or at least the events that we want to analyze). The dimension tables list the factors (Customer, Time, Product) by which we want to analyze the data.

Given this fact table and these three dimension tables, we can ask questions like: How many diamond rings have we sold to female customers in region 4 during the first quarter of 2008?

In other words, the difference between dimension tables and fact tables is that fact tables hold the data we want to analyze and the dimension tables hold the information necessary to allow us to query it.

This was first published in September 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: