Skip to Content
Author's profile photo Former Member

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.

/wp-content/uploads/2014/07/1_491203.png

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.

/wp-content/uploads/2014/07/2_491471.png

       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.

/wp-content/uploads/2014/07/3_491472.png

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.

/wp-content/uploads/2014/07/4_491473.png

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.

/wp-content/uploads/2014/07/6_491480.png


Thanks & Regards,

Vishakha Nigam

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.