star & snowflake schema

[maniram23]

hi friends,

what is star schema and snowflake schema. what are the differences?

[arulsettu]

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.

[robert_mika]

One link of many

diffen.com/difference/Snowflake_Schema_vs_Star_Schema

In your data model try use star schema as often as possible.

Snowflake schema is an extension of the star schema, where each point of the star explodes into more points.

It will be harder to read and understand your model

[prma7799]

please check

https://community.qlik.com/thread/102887

Thanks

PM

[#5]

Star Schema

• Each dimension in a star schema is represented with only one-dimension table.
• This dimension table contains the set of attributes.
• The following diagram shows the sales data of a company with respect to the four dimensions, namely time, item, branch, and location.
• There is a fact table at the center. It contains the keys to each of four dimensions.
• The fact table also contains the attributes, namely dollars sold and units sold.

Snowflake Schema

• Some dimension tables in the Snowflake schema are normalized.
• The normalization splits up the data into additional tables.
• Unlike Star schema, the dimensions table in a snowflake schema are normalized. For example, the item dimension table in star schema is normalized and split into two dimension tables, namely item and supplier table.

[robert_mika]

The following diagram shows .....

?

[#7]

Description	Star Schema	Snow Flake 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

Regards

Harsha Nandan

[pratap6699]

STAR schema::one central fact table connected to multiple dimension tables is star.

SNOWFLAKE::it is extension of star,one fact table connected to multiple dimensions with sub dimesion tables this is called snowflake ...if possible try for make snowflake to star because star is very user friendly more understanding manner and star contain less joins so performance is good than snowflake.

[ankitaag]

Hi,

Start schema is where a fact table is directly linked with multiple denormalised dimension tables.

Snowflake scheema is where a fact table is dirctly or indirectly linked with normalised dimension tables. (dimension tables are linked with other dimension tables)

Thanbks and Regards,

Ankita