Post reply

star schema or snowflake schema

  • June 22, 2010 at 11:23 am

    #225890

    Hi,

    I am new to SSAS. I want to know, how do we decide if we have to use star-schema or snow-flake schema ? Star's advantage over snow-flake and vice-versa.

    Can you also provide me with good books/links for a beginner.

    Thanks in advance!

  • June 22, 2010 at 11:58 am

    #1183520

    this is outside my area of experience, but my Google-Fu skills are strong! I searched "ssas star or snowflake" and found some stuff.

    i found this article about why snowflake is better according to the author:

    http://www.ssas-info.com/analysis-services-articles/62-design/913-star-vs-snowflake-in-olap-land

    hope that helps, I saw you posted the same question a day or two ago, with no replies; at least this might stir up the nest a bit.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 22, 2010 at 12:22 pm

    #1183535

    Good beginner's book: Ralph Kimball, "The Data Warehouse Toolkit". The 2nd edition is ISBN 0-471-20024-7, don't know if there are later ones.

    In any case, pages 54 -57 of the 2nd edition addresses your questions in detail. Short story, Kimball acknowleges snowflake has appropriate uses ("when a clump of correlated attributes is used repeatedly in various independent roles") which seems to apply to the sample behind the link Lowell posted (I suspect there is a lot of natural functional dependence between advertisers and the nationality of campaigns), but encourages the avoidance of snowflake in general.

  • June 22, 2010 at 12:54 pm

    #1183553

    It is not completely a star vs. snowflake choice.

    You could have a snowflake schema that is used to maintain the data correctly, but have a star schema based on rollups of the snowflake hierarchy if you feel it provides better performance or is simpler for query development. You can also rollup the snowflake dimension via a view to create a virtual star.

  • June 22, 2010 at 2:37 pm

    #1183591

    Thank you guys!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply