Assignment on MySQL. Facts and Dimension Table
<p class="MsoNormal" style="margin-top:5.0000pt;margin-bottom:5.0000pt;mso-margin-top-alt:auto; mso-margin-bottom-alt:auto;background:rgb(240,242,250);"></p><p class="MsoNormal"><b>Fact Table and Dimension Table Explained</b><b></b></p><p class="MsoNormal" style="margin-bottom: 5pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">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.</p><p class="MsoNormal" style="margin-bottom: 5pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Dimension tables hold the descriptive information 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<b> </b>are expected to be much smaller in size in comparison to fact tables.</p><p class="MsoNormal" style="margin-bottom: 5pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;"> </p><p class="MsoNormal"><b>Difference between a Fact Table and Dimension Table</b><b></b></p><p class="MsoNormal" style="margin-bottom: 5pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">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.</p><p class="MsoNormal" style="margin-bottom: 5pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">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.</p><b>Star Schema</b><b></b><p class="p" style="margin-top: 0pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;"><b> Star Schema:</b> in data warehouse, in which the center of the star can have one fact table and a number of associated dimension tables. It is known as star schema as its structure resembles a star. The Star Schema data model is the simplest type of Data Warehouse schema. It is also known as Star Join Schema and is optimized for querying large data sets.</p><b> Snowflake Schema</b><b></b><p class="p" style="margin-top: 0pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;"><b> </b><b>Snowflake Schema:</b> in data warehouse is a logical arrangement of tables in a multidimensional database such that the ER diagram resembles a snowflake shape. A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. The dimension tables are normalized which splits data into additional tables. </p><p class="MsoNormal" style="margin-top: 5pt; margin-bottom: 5pt; margin-left: 36pt; text-indent: -18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">· The star schema is the simplest type of Data Warehouse schema. It is known as star schema as its structure resembles a star.</p><p class="MsoNormal" style="margin-top: 5pt; margin-bottom: 5pt; margin-left: 36pt; text-indent: -18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">· Comparing Snowflake vs Star schema, a Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. It is called snowflake because its diagram resembles a Snowflake.</p><p class="MsoNormal" style="margin-top: 5pt; margin-bottom: 5pt; margin-left: 36pt; text-indent: -18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">· In a star schema, only single join defines the relationship between the fact table and any dimension tables.</p><p class="MsoNormal" style="margin-top: 5pt; margin-bottom: 5pt; margin-left: 36pt; text-indent: -18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">· Star schema contains a fact table surrounded by dimension tables.</p><p class="MsoNormal" style="margin-top: 5pt; margin-bottom: 5pt; margin-left: 36pt; text-indent: -18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">· Snowflake schema is surrounded by dimension table which are in turn surrounded by dimension table</p><p class="MsoNormal" style="margin-top: 5pt; margin-bottom: 5pt; margin-left: 36pt; text-indent: -18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">· A snowflake schema requires many joins to fetch the data.</p><p class="MsoNormal" style="margin-top: 5pt; margin-bottom: 5pt; margin-left: 36pt; text-indent: -18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">· Comparing Star vs Snowflake schema, Star schema has simple DB design, while Snowflake schema has very complex DB design.</p><p class="p" style="margin-top: 0pt; margin-left: 18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Star schema is widely used for modeling data warehouses and dimensional data marts. It’s composed of a single fact table that references any number of dimension tables.</p><p class="p" style="margin-top: 0pt; margin-left: 18pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">This schema is a variant of the Snowflake schema and is typically used to enable simpler query sets. The name “star schema” originates from the fact table’s central position in the schema diagram, surrounded by dimension tables. </p><p class="MsoNormal" style="margin-bottom: 5pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;"> </p><p class="MsoNormal"> </p>
- 0 Comments
- 30 Views
- Share:
Login To Post Your Comment