Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

Which schema is best (Star or Snowflake)?

Hi,

Can you please tell me which schema is best for Fast loading data?

Muthukumar Pandiyan
1 Solution

Accepted Solutions
engishfaque
Valued Contributor III

Re: Which schema is best (Star or Snowflake)?

Dear Muthukumar,

Kindly check the attached image for Star Schema vs Snowflake Schema. I hope this will help you to understand in better way.

Schema.png

For more information, please have a look into listed below similar thread

Re: Star & snowflake schema

Kind regards,

Ishfaque Ahmed

View solution in original post

4 Replies
karmariv82
Contributor III

Re: Which schema is best (Star or Snowflake)?

Hi muthukumar,

Please refer to the page 15 from this document Best Practices for Data Modelling, it's a good reference of the advantages and disadvantages of a snowflake vs a star schema. In my personal opinion, I strongly support the Star Schema model, it provides not only better performance but also it's easier to understand for the user. I also recommend to read some of the Kimball techniques for dimensional modeling.

Hope it helps,

-- Karla

engishfaque
Valued Contributor III

Re: Which schema is best (Star or Snowflake)?

Dear Muthukumar,

Kindly check the attached image for Star Schema vs Snowflake Schema. I hope this will help you to understand in better way.

Schema.png

For more information, please have a look into listed below similar thread

Re: Star & snowflake schema

Kind regards,

Ishfaque Ahmed

View solution in original post

ramasaisaksoft
Valued Contributor III

Re: Which schema is best (Star or Snowflake)?

Hi Muthukumar,

Star Schema is the best loading technique.

Snowflake Schema

Star Schema

Ease of maintenance / changeNo redundancy and hence more easy to maintain and changeHas redundant data and hence less easy to maintain/change
Ease of UseMore complex queries and hence less easy to understandLess complex queries and easy to understand
Query PerformanceMore foreign keys-and hence more query execution timeLess no. of foreign keys and hence lesser query execution time
Type of DatawarehouseGood to use for datawarehouse core to simplify complex relationships (many:many)Good for datamarts with simple relationships (1:1 or 1:many)
JoinsHigher number of JoinsFewer Joins
Dimension tableIt may have more than one dimension table for each dimensionContains only single dimension table for each dimension
When to useWhen 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-NormalizationDimension Tables are in Normalized form but Fact Table is still in De-Normalized formBoth Dimension and Fact Tables are in De-Normalized form
Data modelBottom up approach

Top down approach

The main difference between star schema and snowflake schema is that

  • The star schema is highly denormalized and the snowflake schema is normalized. .
  • Performance wise, star schema is good but if we think about memory then snow flake schema is better than star schema.
  • A dimension table will not have parent table in star schema, whereas
    snow flake schemas have one or more parent tables.
  • Snow Flake Schema has bottom-up appraoch where as Star has Top-down.
  • Star Schema has fewer joins and Snow flake has higher
Partner
Partner

Re: Which schema is best (Star or Snowflake)?

Can anybody explain why Single Table is bad for RAM Consumption and Script Run Time?