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

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:

MonthlySales Fact Table Data

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.

Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .