"Bi Thing" Data Warehouse Modeling: Star-mode, snowflake-mode

In the original: "Bi Thing" Data Warehouse Modeling: Star mode, snowflake pattern

Data Warehouse Modeling-Star schema
Example of Star Schema

Data warehousing Modeling-snowflake mode
Example of Snowflake Schema

• Save storage space
• A certain degree of paradigm

Star vs. Snowflake type

• Which one is better?

1. A long debate
2. There are supporters of both views
3. The argument continues ...

• For now, most of them are more likely to be star-shaped

Arguments that support star dimensions

• The fact table is always big, and the space saved on the dimension table is relatively small.
• Increased complexity of the data model
• Query operations are conceptually more complex
• Load time from Data Warehouse to multidimensional database is longer
• Therefore, the Snowflake dimension is considered only when the dimension table is extremely large and the storage space is a problem
• In short, it's best to use the Star dimension

Arguments to support snowflake dimensions

• From the data warehouse to the multi-dimensional database loading process, the snowflake dimension is more efficient;
• The Snowflake dimension describes a clearer hierarchical concept;
• Stars are considered only when the end user is likely to have direct access to the Data warehouse (which is not recommended);

My personal experience

• The star structure is better than the snowflake type in efficiency;
• In most cases, I will choose the star type, but do not rule out the use of snowflake-shaped case;

"Bi Thing" Data Warehouse Modeling: Star-mode, snowflake-mode