What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Snowflak Schema as PDF --


Snowflake Schema
Extension of Star Schema where the Dimension Tables are normalized. E.g. The dimension table Product has manufacturing plant , location of the plant etc. as attributes. Instead these attributes relating to the plant can form a new dimension. The key of this dimension will be a foreign key of product table.

With the snowflake schema the dimensions are represented by more than one dimension table in other words its takes multiple dimension tables to define a dimension. Not all dimension tables are linked or related to the fact table because some of the dimensions will just be related to other dimensions.

So you can see in the diagram we have three dimension tables that together represent one dimension, they represent the pPoduct dimension. So with the star schema approach all the information in the three dimension tables shown here would actually be in only one dimension table, this is where the snowflake schema really differs in approach from the star. Also note since the dimension is represented by more than one dimension table not all the dimension tables are related to the fact table. So in the next diagram a few more tables have been added to get a better picture of what the schema will look like and the name snowflake comes from the idea somehow resembles a snowflake. At any rate there are some very key differences between this and the star schema we just pointed out.

Which way you choose to go has in my mind has a lot to do with your personal preference and there’s an ongoing debate that continues to discuss which of these is better, honestly there are advantages and disadvantages of both and my experience I’ve seen the star schema used the most and preferred by many it’s simple to work with, but really the choice is up to you and both will work in analysis services.

 

Courtesy: http://www.nenit.net/articles/sql-server/ssas/schemas.aspx

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

Leave a Reply

Your email address will not be published. Required fields are marked *


*