Difference between Star Schema & Snow Flake Schema?

Thu, 08 May 2014 08:10:31 GMT

Re: Difference between Star Schema & Snow Flake Schema?

its_anandrjs — Thu, 08 May 2014 08:22:21 GMT

In general way if we say

1.When you have one Fact Table and Multiple Dimension Tables is a Star schema.

2.When You have a Fact Table it is connected to dimension table and then sub dimension table is a snow flake schema.

its_anandrjs — Thu, 08 May 2014 08:24:34 GMT

Here are the main difference from the learning website

	Snowflake Schema	Star Schema
Ease of maintenance / change	No redundancy and hence more easy to maintain and change	Has redundant data and hence less easy to maintain/change
Ease of Use	More complex queries and hence less easy to understand	Less complex queries and easy to understand
Query Performance	More foreign keys-and hence more query execution time	Less no. of foreign keys and hence lesser query execution time
Type of Datawarehouse	Good to use for datawarehouse core to simplify complex relationships (many:many)	Good for datamarts with simple relationships (1:1 or 1:many)
Joins	Higher number of Joins	Fewer Joins
Dimension table	It may have more than one dimension table for each dimension	Contains only single dimension table for each dimension
When to use	When dimension table is relatively big in size, snowflaking is better as it reduces space.	When dimension table contains less number of rows, we can go for Star schema.
Normalization/ De-Normalization	Dimension Tables are in Normalized form but Fact Table is still in De-Normalized form	Both Dimension and Fact Tables are in De-Normalized form
Data model	Bottom up approach	Top down approach

Thu, 08 May 2014 08:24:44 GMT

thankyou