DIFFERENT BETWEEN STAR SCHEMA AND SNOWFLAKE SCHEMA

Posted by abubakryrodiat

5 months ago

DIFFERENT BETWEEN STAR SCHEMA AND SNOWFLAKE SCHEMA

STAR SCHEMA                                                                                                                                                                                                                        Star schema is the simplest method for arranging data in a data warehouse. It contains a fact table at the center connected to dimension tables around it. Star schema is most effective for quick and simple data query execution                                                                 Star schema is the simplest method for arranging data in the <a href="https://builtin.com/data-science/data-warehousing" target="_blank" style="text-align: var(--bs-body-text-align); background-color: hsl(var(--white)); text-decoration-line: underline; text-decoration-color: rgb(36, 79, 231); line-height: 1.25rem;">data warehouse</a>. It consists of the fact table at the center connecting to the dimension tables around. The fact table stores information about metrics, while the dimension tables hold information about <a href="https://builtin.com/data-science/descriptive-statistics" target="_blank" style="text-align: var(--bs-body-text-align); background-color: hsl(var(--white)); text-decoration-line: underline; text-decoration-color: rgb(36, 79, 231); line-height: 1.25rem;">descriptive attributes</a>. The schema distinguishes between the fact data related to an organization and the descriptive data attached to it. Star schema contains <a href="https://builtin.com/big-data/denormalization" target="_blank" style="text-decoration-line: underline; text-decoration-color: rgb(36, 79, 231); line-height: 1.25rem;">denormalized data</a>, which refers to the process of adding redundant data to a relational database to improve read performance at the cost of write performance.Examples of the fact table could be employee names, sales price, sales quantity, and distance measures. Dimension tables associated with such tables will include names of particular employees (John, Bob, etc.), and numerical figures for the other parameters in the fact table. SNOWFLAKE SCEMA<ul><li style="padding-left: 1.5rem; position: relative;">Snowflake Schema: Snowflake schema is a more complex method of storing data in which fact tables, dimension tables and sub-dimension tables are connected through foreign keys. Snowflake is most effective for in-depth data query analyses.</li><li></li></ul>Snowflake schema consists of three types of tables: Fact tables, dimension tables and sub-dimension tables. The fact table is the central table in the schema. The dimension table stores details about the facts. Dimension tables of the snowflake schema are further normalized into sub-dimension tables.For example, suppose a school has built a database for enrollment of its students and grades. The data architect might make “Enrollment” the central fact table. Dimension tables connected to the fact table might be the “Students” table, containing data on the students, the “Courses” table storing data regarding the courses on the anvil, and the 'Teachers' table with the data about the teachers.The “Students” dimension table might have sub-dimension tables like “Parental education,” “Family background,” and “Career objectives.” The “Courses” dimension might have sub-dimension tables such as “Language,” “Science,” and “Commerce.” The “Teachers” table might have sub-dimension tables like “Doctorate,” “Sports,” or “Physical education.”The three tables are inter-connected through foreign keys. A foreign key is a column, or columns, in a table whose values need to essentially match values of a column in the related table. For instance, suppose there’s a restaurant database with an orders table and a customers table. If the algorithm creates a column orders.customer_id referencing the customers.id primary key, any value updated or inserted in orders.customer_id must precisely match a value in customers.id.DIFFENT BETWEEN FACT TABLE AND DIMENTION TABLE1.Fact tables and dimension tables play different but important roles in a data warehouse. Fact tables contain numerical data.Dimension tables provide context and background information. Both types of tables are necessary for effective <a href="https://www.simplilearn.com/data-analysis-methods-process-types-article" target="_blank" rel="noopener" title="data analysis" style="cursor: pointer; color: rgb(17, 121, 239) !important;">data analysis</a> and <a href="https://www.simplilearn.com/how-data-driven-decision-making-enables-better-management-article" target="_blank" rel="noopener" title="decision-making" style="cursor: pointer; color: rgb(17, 121, 239) !important;">decision-making</a>. 2. A fact table is one that holds the primary keys of the referenced dimension tables along with some quantitative metrics, i.e. measurements, over which a calculation can be performed. Some common examples of facts tables include orders, logs and time-series financial data.Dimension tables hold the <a href="https://builtin.com/data-science/intro-descriptive-statistics" target="_blank" style="text-decoration-line: underline; text-decoration-color: rgb(36, 79, 231); line-height: 1.25rem;">descriptive information</a> for all related fields that are included in the fact table’s records. A few common examples of dimension tables are physical entities such as “Customer” and “Product” tables or even “Time” tables. In general, dimension tables are expected to be much smaller in size in comparison to fact tables.3.A straightforward approach to differentiating fact tables from dimension tables is to examine whether a table refers to a noun, such as a physical object or person. For instance, a product or a customer may exist independently of any specific business event. Dimension tables, therefore, represent nouns since they represent something that either takes action or has action taken upon it, such as a physical store, customer or product.On the other hand, a verb usually corresponds to a fact table. Each record corresponds to an event in which entries from dimension tables are involved. For example, an order involves a customer and a product (or potentially more). The act of placing an order is made by a specific customer for a specific product.

0 Comments
58 Views
Share:

Topic Detail

Posted by abubakryrodiat

DIFFERENT BETWEEN STAR SCHEMA AND SNOWFLAKE SCHEMA