Star Schema and Snow Flake Schema

Friends,

I have never taken and also given an interview on SSAS(even on MSBI) with out the question “What is the difference between STAR and SNOWFLAKE Schema”.  Before going to the differences lets have a look at what a schema is and the types.

Schema in relational database defines the tables, columns in each table, and the relationships between tables. It gives a clear picture about the tables present in the database how the tables are related to each other. There are mainly two types of schema(and ppl are adding one per day like STARFLAKE,GALAXY and so on .. but not to worry much about those) which are listed below.

1) Star

2) SnowFlake.

Lets discuss about each type in detail here.

Star – 

This is the simplest Schema design available and in the star schema design, a single object (the fact table) sits in the middle and is radially connected to other surrounding objects (dimension lookup tables) like a star. Each dimension is represented as a single table. The primary key in each dimension table is related to a foreign key in the fact table. In simple when ALL the DIMENSION TABLES are DIRECTLY related to FACT TABLE then that Schema is called as Star Schema. PFB the Screenshot showing STAR Schema.

Snow Flake –

The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy. When ALL the dimension tables are NOT directly related to FACT table then that type of schema is called as Snow Flake Schema. PFB Screenshot of Snow Flake Schema.

In snow flake schema fact table will be linked directly as well as there will be some intermediate dimension tables or Fact less Fact tables between fact and dimension tables.

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V