It uses normalization which splits up the data into additional tables. The splitting results in the reduction of redundancy and prevention from memory wastage. A snowflake schema is more easily managed but complex to design and understand. It can also reduce the efficiency of browsing since more joins will be required to execute a query.
In the snowflake schema, we are taking the same example as we have taken in the star schema. Here the sales fact table is identical to that of the star schema, but the main difference lies in the definition of dimension tables. The single dimension table for the item in the star schema is normalized in the snowflake schema, results in creation of new item and supplier tables. Here state attribute can also further normalized.
Star and Snowflake schema is used for designing the data warehouse. Both have certain merits and demerits where snowflake schema is easy to maintain, lessen the redundancy hence consumes less space but complex to design. Whereas star schema is simple to understand and design, uses less number of joins and simple queries but have some issues such as data redundancy and integrity. However, use of snowflake schema minimizes redundancy, but it is not popular as star schema which is used most of the time in the design of data warehouse.
Your email address will not be published. It is easier to implement and uses less disk space. As it has multiple tables, the performance of the query is reduced. More maintenance is required because there are more lookup tables. Considering the same example as above of refrigerator manufacturing company, in the snowflake schema, the fact table is the same as in star schema, but the major difference is in the definition or layout of dimension tables.
In this schema, the single dimension table of the item has been normalized and has been split, and a new supplier table has been created, including information on the type of supplier.
Similarly, the dimension table of location is normalized, and data is split into a new city table containing details of the particular city. In this article, we discuss the Star Schema vs Snowflake Schema in detail.
These schemas are used to represent the data warehouse. They are similar in some aspects and different in others. Snowflake is the extension of the star schema. When data is more, then snowflake is preferred as it reduces redundancy, but the star is comparatively more popular than the snowflake schema. This is the table containing core information about the business process. For instance sales revenue by product.
This table can have references to many other tables. The type of relationships between tables in a data warehouse is the most important feature that defines the type of data warehouse schema. For star schema, every external field in the fact table is represented by just one reference table. For example, consider the following fact table:. Amount is just a numeric field. The structure of external tables can look like this:.
Reference tables have no relationships with each other: they are linked only by foreign keys ids with the fact table. The visualization of this schema resembles a star:. The important thing to keep in mind is that data is not fully normalized when using star schema. This means that tables such as Products, Departments, Customers, etc. So, information about products is stored solely in the Products table and nowhere else. It is obvious that a lot of data is duplicated not normalized with this schema.
The snowflake schema is an extension of a star schema. The main difference is that in this architecture, each reference table can be linked to one or more reference tables as well. The aim is to normalize the data. Look at the Products table in the previous example. The Product segment field can be repeated many times for many products. But if we create one more table, Segments , we can just reference the Products table to the Segments table using ids — foreign keys.
The same can be done for the Customer location field in the Customers table or the Department region field in the Departments table. If there are a lot of different tables, this structure resembles a snowflake. It has the center fact table , and many reference tables that make up the branching, similar to what snowflakes have. Having more lookup tables allows perfect data normalization because less data is duplicated. Data Split into different Dimension Tables.
Cube processing is faster. Cube processing might be slow because of the complex join. Offers higher performing queries using Star Join Query Optimization. Tables may be connected with multiple dimensions. The Snowflake schema is represented by centralized fact table which unlikely connected with multiple dimensions. What is a Galaxy Schema? What is Star Cluster Schema? Example of Star Cluster Schema Overlapping dimensions can be found as forks in hierarchies. Report a Bug. Previous Prev.
Next Continue. Home Testing Expand child menu Expand. SAP Expand child menu Expand.
0コメント