Fact table contains the actual business process measurements or metrics called facts. Usually these facts are numeric.
These facts are generally Additive.
Some times the facts are semi additive such as balances
Some times they are non additive such as unit price
In the above example where you are building a data warehouse of monthly sales in dollars, your fact table will contain the actual sales numbers, one row per month. In addition to the data itself, you will have the foreign keys for the various dimensions in this row.
The level of detail of the fact table is known as the grain of the fact table. In this example the grain of your fact table is monthly sales number per location per product. Your Fact Table will look like this
Field Name | Type |
TM_Dim_Id | INTEGER (4) |
PR_ Dim_Id | INTEGER (4) |
LOC_ Dim_Id | INTEGER (4) |
Sales | INTEGER (4) |
In this table the combination of all three dimension table foreign keys make up the primary key in the fact table. (TM_Dim_Id, PR_ Dim_Id, LOC_ Dim_Id) is our primary key. This is the unique key into our Sales fact table.
The actual data in your MonthlySales Table will look like this:
TM_Dim_Id | PR_ Dim_Id | LOC_ Dim_Id | Sales |
1001 | 1001 | 1003 | 435677 |
1002 | 1002 | 1001 | 451121 |
1003 | 1001 | 1003 | 98765 |
1001 | 1004 | 1001 | 6543 |
A fact table may contain one or more Facts. Usually you create one fact table per business process or event. For example if you want to analyze the sales numbers and also advertising spending, they are two separate business processes. So you will create two separate fact tables, one for sales data and one for advertising cost data. On the other hand if you want to track the sales tax in addition to the sales number, you simply create one more fact column in the Sales fact table called Tax.