Aggregate FACT

---

Fact table might design with transactional level facts or summarization level facts. Aggregate FACT table must be design with summarization level facts. Summarization level facts are used to stores aggregated or grouped or summed up data that based on certain level of hierarchical data of dimension table.

Aggregate FACT table is helps to reduces database memory and improved database query performance, when generate Analytical Reports. Fact table can not design with combination of transactional and summarization level facts that make complications when using the data warehouse.

An instance, the business process is 'SALES' then the Daily Summarization Sales Fact table might be design with Date_Key, Store_Key, Product_Key, Promotion_Key, Employee_Key, Number_of_Sales, Total_Sales_QTY, Total_Sales_Amount and Total_Discount_Amount attributes.



Number_of_Sales, Total_Sales_QTY, Total_Sales_Amount and Total_Discount_Amount are semi-additive facts that used to stores Sales pre-defined summarize data.

The semi-additive facts can be added across some dimensions not all dimension table. An instance, Quantity on hand can be added across Product and Warehouse but not Time dimension table. The Daily_Sales_Fact semi-additive facts can be added across Date, Store, Product and Employee dimensions but not Invoice and Time dimension tables.

The combination of Date_Key, Store_Key, Product_Key, Promotion_Key, Employee_Key is composite primary key of Daily_Sales_Fact table and those attributes are integrates with Date, Store, Product, Promotion and Employee dimension tables.

Daily_Sales_Fact table and relevant dimension tables are used to generate Date wise, Store wise, Product wise and Employee wise Periodical Sales Analysis Report from Sales Historical Information.

* * * * *

---