Facts and Dimensions Table

Posted by std588

5 months ago

Facts and Dimensions Table

-       Difference between Facts and Dimensions Table What is a Fact Table? A fact is a piece of information with a specific numerical value. Facts are kept in fact tables, which are linked to several dimension tables by a foreign key. Fact Tables contain numerical data necessary for effective data analysis and decision-making. A fact table is also a table that stores the measurements, metrics, or facts related to a business operation. It is located at the center of a star or snowflake schema and is surrounded by dimension tables. A fact table also contains the records that combine attributes from different dimension tables. These records allow users to analyze different aspects of their business, which can aid in decision-making and improving the business. A fact table has two types of columns: those that contain the facts and those that serve as <a href="https://www.simplilearn.com/tutorials/sql-tutorial/foreign-key" target="_blank" title="foreign keys">foreign keys</a> linking to dimension tables. The <a href="https://www.simplilearn.com/tutorials/sql-tutorial/primary-key-in-sql" target="_blank" title="primary key">primary key</a> of a fact table is often a composite key made up of all of the foreign keys in the table. It can hold various types of measurements, such as additive, non-additive, and partly additive measures, and store important information in the data warehouse. Characteristics of a Fact Table<ol start="1" type="a"> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Keys: Each fact table has a key that is made up of the primary keys from all of the dimension tables connected to that fact table.</li> </ol>b.    Additive Measures: The fact table's attributes may be entirely, partially, or not at all additive. Measures that are applied to all dimensions are referred to as fully addition or additive measures.<ol start="3" type="a"> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Quasi-Measures: are those that store the fundamental unit of measurement for any business process, whereas semi-additive measures add measures to some dimensions but not to all.</li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Sparse Data: Some records in the fact table have characteristics with null values or measurements, which means that the information they contain is not given or provided. </li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Table Grain: The level of detail or depth of the information recorded in a fact table is referred to as the table's grain. A successful fact table must be designed at the highest level. </li> </ol><p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 2.25in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Types of Facts1.    Additive Facts: This states that all dimensions should be multiplied by measures.2.    Non-Additive:  This means that measures may be introduced to some dimensions in this type of data but not to others.3.    Semi-Additive: This keeps track of some fundamental business process metrics. Examples include orders, phone calls, and sales.  What is a Dimension Table?Dimension tables provide the context and background information for the measures recorded in the fact table. One of the main differences between fact tables and dimension tables is that dimension tables contain the attributes that the measures in the fact table are based on. Dimension tables contain descriptions of the objects in a fact table and provide information about dimensions such as values, characteristics, and keys. The term dimension table refers to a set of data related to any quantifiable event and is the foundation for dimensional modeling.  Dimension tables have a column that serves as a primary key, allowing each dimension row or record to be uniquely identified. This key is used to link the dimension table to the fact tables. A surrogate key, which is a system-generated key, is often used to uniquely identify the rows in the dimension table.  <p class="MsoNormal" style="margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Characteristics of a Dimension Table<p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 0.75in; text-indent: -0.5in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">i.               These tables are usually small, with a number of rows ranging from a few hundred to a few thousand. <p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 0.75in; text-indent: -0.5in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">ii.             Records: The dimension table contains more characteristics than records.<p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 0.75in; text-indent: -0.5in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">iii.            Keys: The main key is required for every dimension table in order to help uniquely identify each entry.<p class="MsoListParagraph" style="margin-bottom: 10.5pt; margin-left: 0.75in; text-indent: -0.5in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">iv.            <a href="https://www.simplilearn.com/automated-recruiting-in-companies-article" target="_blank" title="Normalization">Normalization</a>: The dimension table is not normalized because doing so splits the data into different tables and makes it more difficult for queries to execute. <p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 0.75in; text-indent: -0.5in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">v.             Attribute Values: The majority of the values in the dimension table are expressed as text rather than numbers.<p class="MsoNormal" style="margin-bottom: 19.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Types Of Dimensions:<ul type="disc"> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Role-Playing Dimensions</li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Step Dimensions</li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Degenerate Dimensions</li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Conformed Dimensions</li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Junk Dimensions</li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Swappable Dimensions</li> <li class="MsoNormal" style="color: black; margin-bottom: 10.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Dimension-to-Dimension Table Joins</li> </ul><p class="MsoNormal" style="margin-bottom: 19.5pt; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">The key differences between Fact and Dimension Tables are as follows:<p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 51pt; text-indent: -0.25in; line-height: 150%; 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 characterized by its grain or even most atomic level, while the Dimension database includes extensive information, the Fact table is used to hold report labels.<p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 51pt; text-indent: -0.25in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">·       In contrast to the Dimension table, which incorporates hierarchies, the Fact table does not.<p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 51pt; text-indent: -0.25in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">·       The Dimension table is a partner to the fact table and contains descriptive qualities that can be used as query constraints. The fact table includes measurements, metrics, or facts about business operations.<p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 51pt; text-indent: -0.25in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">·       A star and snowflake schema's core is where the table containing it is located, while its edges are where the dimension table is situated. <p class="MsoListParagraph" style="margin-bottom: 10.5pt; text-indent: -0.25in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">-       Difference between Snowflake Schema and Star Schema<p class="MsoNormal" style="margin-bottom: 10.5pt; margin-left: 0.25in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">What is a Star Schema?<p class="MsoNormal" style="margin-bottom: 10.5pt; text-indent: 0.25in; line-height: 150%; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">A star schema is a database schema used to store data in a star format. This schema consists of a central table called the "Fact Table," and a number of directly connected other tables called "Dimension Tables. The fact table contains information about metrics or measures, while the dimension tables contain information about descriptive attributes. The star schema is very simple and easy to understand, making it ideal for business intelligence applications. This schema contains both dimension tables and Fact tables in it. What is a Snowflake Schema? <p style="line-height: 21pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">A snowflake schema is a type of database schema that is used to store data in a more complex format than the star schema. The snowflake schema consists of a central table which is called the fact table and a number of other tables which are called dimension tables.  As with other schemas, the fact table contains information about events or facts while the dimension tables contain information about the dimensions of those events or facts. This schema also contains all the three dimensions, fact tables and the sub-dimension tables. Key Differences Between Star Schema and Snowflake Schema:<p style="margin: 0in 0in 5pt 24.75pt; text-indent: -0.25in; line-height: 21pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">1.     A star schema has denormalized dimension tables, while a snowflake schema has normalized dimension tables.<p style="margin: 0in 0in 5pt 24.75pt; text-indent: -0.25in; line-height: 21pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">2.     A star schema is easier to design and implement than a snowflake schema.<p style="margin: 0in 0in 5pt 24.75pt; text-indent: -0.25in; line-height: 21pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">3.     A star schema can be more efficient to query than a snowflake schema, because there are fewer JOINs between tables.<p style="margin: 0in 0in 5pt 24.75pt; text-indent: -0.25in; line-height: 21pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">4.     A star schema can require more storage space than a snowflake schema, because of the denormalized data. <p style="margin: 0in 0in 5pt 24.75pt; text-indent: -0.25in; line-height: 21pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">5.     A star schema can be more difficult to update than a snowflake schema, because of the denormalized data. <p style="margin: 0in 0in 5pt 24.75pt; text-indent: -0.25in; line-height: 21pt; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">6.     A star schema can be more difficult to troubleshoot than a snowflake schema, because of the denormalized data.

0 Comments
91 Views
Share:

Topic Detail

Posted by std588

Facts and Dimensions Table