Home > Software > Data-Warehouse > Informatica
Interview Questions   Tutorials   Discussions   Programs   

Informatica - What is star and snowflake schema?

asked marvit September 20, 2014 08:03 AM  

What is star and snowflake schema?


1 Answers

answered By Mswami   0  
Star Schema: It has single fact table connected to dimension tables like a star. In star schema only one join establishes the relationship between the fact table and any one of the dimension tables.A star schema has one fact table and is associated with numerous dimensions table and depicts a star.

Snowflake Schema: It is an extension of the star schema.In snowflake schema, very large dimension tables are normalized into multiple tables. It is used when a dimensional table becomes very big.In snow flake schema since there is relationship between the dimensions Tables it has to do many joins to fetch the data.Every dimension table is associated with sub dimension table.

The main difference between star schema and snowflake schema is that:

The star schema is highly denormalized and the snowflake schema is normalized. So the data access latency is less in star schema in comparison to snowflake schema. As the star schema is denormalized, the size of the data warehouse will be larger than that of snowflake schema.

Performance wise, star schema is good. But if memory utilization is a major concern, 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.

The dimensional table itself consists of hierarchies of dimensions in star schema,whereas hierarchies are split into different tables in snow flake schema. The drilling down data from top most hierarchies to the lowermost hierarchies can be done.

   add comment

Your answer

Join with account you already have



 Write A Tutorials
Online-Classroom Classes

  1 person following this question

  2 people following this tag

  Question tags

informatica × 1

Asked 3 years and 5 months ago ago
Number of Views -421
Number of Answers -1
Last updated
3 years and 2 months ago ago

Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!