Data Warehouse Concepts #2/5
Data warehouse Schemas and it’s type..
What is Schema?
Graphical representation of the data structures . It is the first phase of the implementation cycle.
What is a star schema?
Star schema is a data warehouse schema where there is only one “fact table” and many de-normalized dimension tables.
Fact table contains primary keys from all the dimension tables and other numeric columns columns of additive, numeric facts.
What does it mean by grain of the star schema?
In Data warehousing, grain refers to the level of detail available in a given fact table as well as to the level of detail provided by a star schema.
It is usually given as the number of records per key within the table. In general, the grain of the fact table is the grain of the star schema.
What is a snowflake schema?
Snowflake schema contain normalized dimension tables in a tree like structure with many nesting levels.
Snowflake schema is easier to maintain but queries require more joins because of nested levels.
What is the difference between snowflake and star schema ?
Star schema:
1. The star schema is the simplest data warehouse scheme.
2. In star schema each of the dimensions is represented in a single table .It should not have any hierarchies between dimensions.
3. It contains a fact table surrounded by dimension tables. If the dimensions are de-normalized, we say it is a star schema design.
4. In star schema only one join establishes the relationship between the fact table and any one of the dimension tables.
5. A star schema optimizes the performance by keeping queries simple and providing fast response time. All the information about the each level is stored in one row.
6. It is called a star schema because the diagram resembles a star.
Snowflake Schema:
1. Snowflake schema is a more complex data warehouse model than a star schema.
2. In snowflake schema at least one hierarchy should exists between dimension tables.
3. It contains a fact table surrounded by dimension tables. If a dimension is normalized, we say it is a snowflake design.
4. In snow flake schema since there is relationship between the dimensions tables it has to do many joins to fetch the data.
5. Snowflake schemas normalize dimensions to eliminated redundancy. The result is more complex queries and reduced query performance.
6. It is called a snowflake schema because the diagram resembles a snowflake
Thank you for Reading !! Hit a like ..
— Pawan Kumar
