Tutorials -> Dimensional Modeling Tutorial

Fact Table Schema

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.

Granularity or Grain of Fact Table

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

MonthlySales Fact Table Schema
 

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:

MonthlySales Fact Table Data
 

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.

Next>> Star Schema