Fact Table discussion.
A fact table is the central table in a star schema of a data warehouse. A fact table stores quantitative information for analysis and is often deformalized.
In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is located at the center of a star schema or a snowflake schema surrounded by dimension tables. … The grain of a fact table represents the most atomic level by which the facts may be defined.
A fact table works by joining dimension tables. A fact table has a copy of the facts to be analyzed, and a dimension table stores data about the ways in which the data in the fact table can be analyzed. Therefore, the fact table consists of two types of columns. One type is the foreign keys column which allows joins with dimension tables, and the measures columns contain the data that is being analyzed.
In my snicker candy bar example, the Mars, Inc, sells products to Wal-Mart. Every sale is a fact that happens, and the fact table is used to record these facts. For example:
|
Vendor ID |
Product ID |
Customer ID |
Unit Sold |
|
4 |
17 |
W2 |
1 |
|
4 |
17-A |
W3 |
10 |
|
8 |
21 |
A3 |
2 |
|
8 |
4 |
A1 |
1 |
Now we can add a dimension table about customers:
|
CustomerID |
Name |
Region |
Revenue |
Product |
Country |
|
W2 |
Wal-Mart |
Midwest |
11 |
3 |
1 |
|
A3 |
Target |
Midwest |
3 |
5 |
1 |
|
C1 |
Lowes |
Eastern |
5 |
7 |
3 |
In this example, the customer ID column in the fact table is the foreign key that joins with the dimension table. By following the links, you can see that row 3 of the fact table records the fact that customer 2, Target, bought two items on day 8. The company would also have a product table and a time table to determine what Target bought and exactly when.
When building fact tables, there are physical and data limits. The ultimate size of the object as well as access paths should be considered. Adding indexes can help with both. However, from a logical design perspective, there should be no restrictions. Tables should be built based on current and future requirements, ensuring that there is as much flexibility as possible built into the design to allow for future enhancements without having to rebuild the data.
Difference Between Fact Table and Dimension Table
Fact Table:
A fact table is a primary table in a dimensional model. A Fact Table contains
- Measurements/facts
- Foreign key to dimension table
Dimension table:
- A dimension table contains dimensions of a fact.
- They are joined to fact table via a foreign key.
- Dimension tables are de-normalized tables.
- The Dimension Attributes are the various columns in a dimension table
- Dimensions offers descriptive characteristics of the facts with the help of their attributes
- No set limit set for given for number of dimensions
- The dimension can also contain one or more hierarchical relationships
