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.
* * * * *
Your Comment To AUTHOR
|
|