Q

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

Although both are tables used in star schemas, there are differences between dimension tables and fact tables. Learn more from a data warehouse expert.

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

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.

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

 

This was first published in September 2008

Dig deeper on Data warehouse software

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

2 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close