I will be covering mostly the Crux of the discussion i had with my Client on which to use for a Major Warehouse project.
Planning Plays a very important role in the effieient design and development of the application.
I am not talking about the waterfall model, as it is in-efficient in many circumstances and specially while software is concerned, where things are Ad-Hoc in nature and making one change in the waterfall model, is not possible unless to RIP-Apart the Code.
So the Ad-hoc method of development sometime (always) shows Faster Development and more integrated structure than you may imagine, still keeping the integrity and Scalability in place.
Star Schema is efficient in large Warehouse projects.
Snow Flake on the other hand is efficient in Small/mid warehouse project
Star Schema, as the name suggests is a star like model, with one core (Fact) and branches directly connected to the core, signifying the (dimensions).
SnowFlake Schema has outer branching from the parent dimensions(dimensions connected to parent dimensions) which are connected to facts/Core.
Star requires less Normalization while Snow Flake requires more normalization.
This in turn presents issues with snowflake as the Query needs more joins to retrieve results and in majority of the cases the queries are slow.
Performance consideration is a major factor in choosing a schema, and STAR performs as it should… It SHINES…
Again it depends on the busines needs as well..