To continue reading for free, register below or login
To read more you must become a member of SearchDataManagement.com
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.
|
