Star Schema Vs Snowflake Schema Vs Fact constellation Schema

Data Warehousing Schemas

1. Star Schema
2. Snowflake Schema
3. Fact Constellation

Star Schema

• A single large central fact table and one table for each dimension
• Every fact points to one tuple in each of the dimensions and has additional attributes
• Does not capture hierarchies directly.

Snowflake Schema

• Variant of star schema model.
• A single, large and central fact table and one or more tables for each dimension.
• Dimension tables are normalized split dimension table data into additional tables.

       Fact Constellation:

• Multiple fact tables share dimension tables.
• This schema is viewed as collection of stars hence called galaxy schema or fact constellation.
• Sophisticated  application requires such schema.

Case Study:

• Afco Foods & Beverages is a new company which produces dairy, bread and meat products with production unit located at Baroda.
• There products are sold in North, North West and Western region of India.
• They have sales units at Mumbai, Pune , Ahmadabad ,Delhi and Baroda.
• The President of the company wants sales information.

Sales Information

• Report: The Number of units sold.
• 113
• Report : The Number of units sold over Time.

Building Data Warehouse

     Data Selection

• Data Preprocessing
  1. Fill missing values
  2. Remove inconsistency
• Data Transformation & Integration
• Data Loading

Data in warehouse is stored in form of fact tables and dimension tables.

Thanks & Regards,

Vishakha Nigam