FACT Table

---

FACT Table is used to store measurements, metrics or facts of a business process in the Data warehouse. Fact table design should start with declaration of grain. Grain is the base business definition of Fact table that determine measurement of business event. Granularity is an extent of information that represents particular transaction data in the Fact table.

An instance, when we buy any products in the supermarket, they sales person use Scanner to capture data from Product Label and then stores ‘Sales Quantity’ and ‘Extended Price’ in the Database through OLTP Application with relevant information like Store, Product, Promotion, Invoice, Customer and Employee information. In this scenario, ‘Sales Quantity’ and ‘Extended Price’ are grain of Sales event.

After declare the grain in fact table, list out dimensional foreign keys that based on the grain. In the sales event, declare Date, Store, Product, Promotion, Invoice, Customer and Employee foreign keys in the Sales_Fact table based on ‘Sales Quantity’ and ‘Extended Price’ grain of fact.

Fact table must be contain numerical data and should not contain any descriptive data or any other data rather than the numerical measurement attributes. By default, Fact table is designed with Composite Primary Key to identify unique transaction and those attributes are foreign keys of referenced Dimension tables.

The following Sales_Fact table is designed with typical grain and foreign keys that integrates with relevant dimension tables.



Sales_QTY, Extended_Price and Discount_Amount attributes are additive facts that used to stores Sales measurement data.

The combination of Date_Key, Store_Key, Product_Key, Promotion_Key, Customer_Key, Employee_Key, Invoice_Number is composite primary key of Sales_Fact table and those attributes are integrates with Date, Store, Product, Promotion, Customer, Employeee and Invoice dimension tables.

Sales_Fact table and relevant dimension tables are help to generate Date wise, Store wise, Product wise, Customer wise and Employee wise Transactional Sales Analysis Report from Sales Historical Information.

* * * * *

---