Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Urgent About Star schema and Snow flake shema 1
- Thread starter rrdw
- Start date
The only difference between the two is the level of normalization of your dimension table(s); think of star schema as of denormalized snowflake schema.
Here is an example:
Employee dimension is linked to Employee_Earnings fact table by Empl_id.
Let's say each Employee has atributes like Dept, Manager, Division, Region. You can decide to list all these attributes in each Employee record in the Employee Dimension table - than you'll get many repetitive data in this table. And this is perfectly fine with Star schema concept.
Someone else may decide to separate repetitive Dept, Division, Region info into a second dimension table called Dept and provide unique keys for all combinations. Then he would replace Dept info in the Employee table with a reference to the Dept table by Dept keys. Thus, he created two dimension tables linked by Dept key while only Employee is linked to the fact table. He just normilized a Star schema into a Snowflake schema.
Clearly, Snowflake schema is a step back towards to highly normalized OLTP architecture, and away from DW OLAP denormalized architecture.
Hope this helps.
Here is an example:
Employee dimension is linked to Employee_Earnings fact table by Empl_id.
Let's say each Employee has atributes like Dept, Manager, Division, Region. You can decide to list all these attributes in each Employee record in the Employee Dimension table - than you'll get many repetitive data in this table. And this is perfectly fine with Star schema concept.
Someone else may decide to separate repetitive Dept, Division, Region info into a second dimension table called Dept and provide unique keys for all combinations. Then he would replace Dept info in the Employee table with a reference to the Dept table by Dept keys. Thus, he created two dimension tables linked by Dept key while only Employee is linked to the fact table. He just normilized a Star schema into a Snowflake schema.
Clearly, Snowflake schema is a step back towards to highly normalized OLTP architecture, and away from DW OLAP denormalized architecture.
Hope this helps.
-
1
- #3
We can't say Snowflake schema is a stepback.
In a particular project, If the dimensions are going to change rapidly, one can opt SnowFlake coz Handling dimension-change is easy in snowflake.
Moreover practically speaking Perfect Star Schema can't be acheived in a DWH project. There'll be some compramises.
Even if u decide to go for a STAR schema data model, U'll always see a bit of SNOW FLAKE getting mixed up atleast in one dimension.
Perfect STAR schema is only a theory.
Prasad.
In a particular project, If the dimensions are going to change rapidly, one can opt SnowFlake coz Handling dimension-change is easy in snowflake.
Moreover practically speaking Perfect Star Schema can't be acheived in a DWH project. There'll be some compramises.
Even if u decide to go for a STAR schema data model, U'll always see a bit of SNOW FLAKE getting mixed up atleast in one dimension.
Perfect STAR schema is only a theory.
Prasad.
NaughtyVicar
MIS
Often designers end up using a combination snowflake-star approach in which the fields are selectively normalized into seperate tables and at the same time some of these fields are also kept in the lower-level dimension tables (denormed).
Much of the design is dependent on update requirements, query tool requirements, and space requirements. There usually is no totally black & white Star or Snowflake.
Much of the design is dependent on update requirements, query tool requirements, and space requirements. There usually is no totally black & white Star or Snowflake.
- Thread starter
- #5
- Status
Similar threads
- Locked
- Question