Skip to Content

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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply