Fact table facts

A Fact Table is the central table in a star schema of a data warehouse

1. Fact Table Fact Types & Fact less Fact Table Dr. R.Balamurugan

2. Fact table A Fact Table is the central table in a star schema of a data warehouse. It consists of two types of columns. The foreign keys column allows to join with dimension tables . The measure columns contain the data that is being analyzed.

3. Fact Table Columns – Foreign Key Foreign Key – Column Type These are the columns as coming from Dimension Tables Are the primary keys of Dimension Tables

4. Measures OR FACTS Fact tables - Measures Collection of measurements on a specific aspects of business sales amount, order quantity, profit, balance and discount amount.

5. Additivity of Measures A fact table might contain either detail level facts or facts that have been aggregated. The primary purpose of Data warehouse is reporting, and forecasting Many times reports are aggregations such as sum Example: sales by quarter, by region, Many reports are usually aggregations

6. Types of Additivity of measures Types of Additivity of Measures Additive measures Semi-additive measures   Non-additive measures  

7. Additive Measures Additive If a measure can be summed across all dimensions The purpose of this table is to record the sales amount for each product in each store on a daily basis. Date Store Product Sales_Amount

8. Semi-Additive Measures Semi-additive Sometimes, we can sum a measure across all dimensions except for time such as account balance We can’t sum the account balance across the time dimension  Date Account Current_Balance

9. Non-additive measures Some measures can’t ever be summed These are called non-additive measures Such as discount percentages and prices Date Account Profit_margin Profit_Margin  is a non-additive fact, for it does not make sense to add them up for the account level or the day level.

10. Factless Fact Table In the real world, it is possible to have a fact table that contains no measures or facts . These tables are called " Factless Fact tables".  A factless fact table is a fact table that does have only dimensions (keys). It is essentially an intersection of dimensions.

11. EXAMPLE The fact table would consist of 3 dimensions: the student dimension, the time dimension, and the class dimension.