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 last published in September 2008

Dig Deeper on Data warehouse software

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Join the conversation

2 comments

Send me notifications when other members comment.

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

Please create a username to comment.

Hi, I have one simple scenario can you please confirm if this should be a fact table or dimension table. A patient went to a hospital for a visit and doctor tracks diagnosis for that visit an suggests a procedure. So in this scenario I have to create some main table like Patient, Visits, Procedures, Event, Medication and Diagnosis table. Among these tables which one should be a fact and which one should be a dimension table. Medication would defiantly be a Fact table because it has the quantity sold but all others does not have any numeric data except cost for visit and cost for procedure. Please confirm.
Cancel
Hi, i think it should be the Patient's Billing Table ('Amt received' table), because billing info captures day today cash transactions that happen in a hospital. I will change the term 'Medication' as you mentioned as 'Pharma purchase' and this will be one of your dimensional table which will track what medication/surgical items that was bought for the procedure.
Hope this clarifies...
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close