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.
Now we can add a dimension table about customers:
Dimension table: Customers
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.
Related Q&A from Mark Whitehorn
The unstructured data types common in big data systems are often better managed by a NoSQL database than relational software, Mark Whitehorn says.continue reading
IT managers should ask cloud providers some pointed questions about the security of data stored in cloud databases, says expert Mark Whitehorn.continue reading
Expert Mark Whitehorn explains what skills are required for predictive modeling -- and whether business users can do the work of data scientists.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.