![]() ![]() The galaxy schema (also known as fact constellation schema) is a combination of the star and snowflake schema models. However, it does result in slower query performance due to the higher number of JOINs to perform.Īs you can see from the SELECT statement below, there are more JOINs to perform! SELECT r.region, c.country, fam.name AS virus_subfamily_name, t.infect_rate, ath_cnt FROM fact_pandemic AS fact LEFT JOIN dim_country AS c ON fact.location_id = c.id LEFT JOIN dim_region AS r ON r.id = c.region_id LEFT JOIN dim_virus AS vir ON fact.virus_id = vir.id LEFT JOIN dim_virus_family AS fam ON fam.id = vir.family_id LEFT JOIN dim_transmission t ON vir.type_id = t.id LEFT JOIN dim_dates AS d ON fact.dates_id = d.id LEFT JOIN dim_year AS y ON d.year_id = y.id WHERE y.year = 2020 Galaxy Schema (Fact Constellation Schema) □ ![]() Normalization helps for a number of reasons: it helps reduce duplicates in the data, lower the amount of storage space used (typically, dimension tables aren’t as large as fact tables), and avoid performing data deletion or update commands in multiple places. The snowflake model is designed like a star schema except for the fact that the dimension tables are completely normalized. Bill Inmon, data warehouse creator, introduced the snowflake schema model in the early 1990’s. The snowflake schema (or “3rd Normal Form” schema), on the other hand, is considered the predecessor to the star schema. Own chart (created with Lucidchart) SELECT loc.region, loc.country, vir.family as virus_subfamily_name, vir.infect_rate, ath_cnt FROM fact_pandemic AS fact LEFT JOIN dim_location AS loc ON fact.location_id = loc.id LEFT JOIN dim_virus AS vir ON fact.virus_id = vir.id LEFT JOIN dim_dates AS d ON fact.dates_id = d.id WHERE d.year = 2020 Snowflake (“3NF“) Schema ❄️
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |