Difference between Star Schema and Snowflake Schema


Star Schema: In this Scham Design every Dimension table is directly conected with FACT Table directly.


Star Schema


Snowflake Schema : in this Schema Design every  Dimension is not linked with Fact table directly. some dimensions connect to other dimensions which are directly connected to Fact tables.


Snowflake Schema




Note: The Schema Arrangements (Snowflake / Star) affects the dimension tables and NOT the Fact Tables.




Star Schema Vs Snowflake Schema


At the time of Cube processing Snowflex Schema generate more joins then Star Schema, which obviously a processing overhead.
So for optimal performance, its recomended to choose Start Schema over Snowflex Schema


But its not all time that we can choose only Snowflake schema, As in Snowflake Schema Facts are connected with many dimension tables indirectly so there is more flexibilities in Snowflake then Start Schema.
For example: if we were working on retail Showroom chain we might need a report for product  Categories ( Garments, Home appliances, Electronic Gadzets) as well as sub categories Jeans, Fridze , Stove etc. So in this case we could have both Dimensions for Product and Sub Product, however some attribute sub product might not apply to products and vise versa. like you could examine Electricity Cunsomption Attribute would apply to electronic goods only not for Garments. if you wish to aggregate the sales by product you will need to know which sub products should rollup to each product. in this case snowflake schema is good approach.


in summery if i say the difference between Star and Snowflake Schema as follows



Snowflake Schema   
Star Schema
Snowflake Schema
De normalized Data structure
Normalized Data Structure
Simple DB Design
Complex DB Design
Data aggregated in single Dimension Table
Data Split into different Dimension Table
High level of Data redundancy
Very low data redundancy
Simple query execution as Cube Processing
Complex Joins performs at Cube Processing
Query Result faster
Delay in query processing due to complex joins


2 comments:

  1. Conceptually very clear explanations , thanks Sir !

    ReplyDelete
  2. what is a Fridze

    ReplyDelete