Shulou(Shulou.com)05/31 Report--

This article is about the difference between star model and snowflake model in cloud computing. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. Overview

In the business intelligence solution of multidimensional analysis, according to the relationship between fact table and dimension table, the common models can be divided into star model and snowflake model. When designing the model of logical data, we should consider whether the data is organized according to the star model or the snowflake model.

When all dimension tables are directly connected to the fact table, the whole diagram is like a star, so the model is called a star model, as shown in figure 1.

The star architecture is an irregular structure, and each dimension of the cube is directly connected to the fact table, and there is no gradual dimension, so the data has a certain degree of redundancy, such as in the regional dimension table, there are two records of city C of country An and province B and city D of country An and province B, so the information of country An and province B is stored twice, that is, there is redundancy.

Figure 1. Star Model in sales data Warehouse

The star model is generally more efficient than the snowflake model because of the redundancy of the data, so many statistical queries do not need to make external connections. The star structure does not need to consider many regularization factors, and the design and implementation are relatively simple. Snowflake model due to the removal of redundancy, some statistics need to be generated through the join of tables, so the efficiency is not necessarily as high as the star model. Regularization is also a complex process, the corresponding database structure design, data ETL, and later maintenance are more complex. Therefore, under the premise of acceptable redundancy, the star model is used more and more efficiently in practical application.

II. Choice of use

Star model (Star Schema) and snowflake model (Snowflake Schema) are two commonly used methods in data warehouse, and the comparison between them should be discussed from four angles.

1. Data optimization

The snowflake model uses normalized data, that is, the data is organized within the database in order to eliminate redundancy, so it can effectively reduce the amount of data. Through referential integrity, its business levels and dimensions are stored in the data model.

▲ figure 1 snowflake model

In comparison, the star model is useful for de-normalization data. In the star model, dimensions refer directly to fact tables, and business levels are not deployed through referential integrity between dimensions.

▲ figure 2 star model

two。 Business model

The primary key is a single unique key (data attribute) that is selected by special data. In the above example, Advertiser_ID will be a primary key. Foreign keys (reference properties) are simply fields in one table that are used to match primary keys in other dimension tables. In the example we cited, Advertiser_ID will be a foreign key of Account_dimension.

In the snowflake model, the business level of the data model is represented by the primary key-foreign key relationship of a different dimension table. In the star model, all necessary dimension tables have only foreign keys in the fact table.

3. Performance

The third difference lies in performance. The snowflake model has a lot of joins between dimension tables and fact tables, so its performance is relatively low. For example, if you want to know the details of Advertiser, the snowflake model will request a lot of information, such as Advertiser Name, ID, and the addresses of advertisers and customer tables that need to be connected, and then connect to the fact table.

The star model has far fewer connections. In this model, if you need the above information, you only need to connect the dimension table of Advertiser with the fact table.

4.ETL

The snowflake model loads the data Mart, so the ETL operation is more complex in design and cannot be parallelized due to the limitations of the satellite model.

The star model loads the dimension table and does not need to add subsidiary models between dimensions, so ETL is relatively simple and can achieve a high degree of parallelization.

Thank you for reading! This is the end of the article on "what is the difference between star model and snowflake model in cloud computing". I hope the above content can be helpful to you, so that you can learn more knowledge. If you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.