You are reading the article Fact Table Vs Dimension Table updated in October 2023 on the website Vibergotobrazil.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Fact Table Vs Dimension Table
Introduction to Fact Table and Dimension TableStart Your Free Data Science Course
Hadoop, Data Science, Statistics & others
Fact tableIt is a table that has values of the attributes of the dimension table. It contains quantitative information in a denormalized form. It basically contains the data that needs to be analyzed. Fact tables mostly have two columns, one for foreign keys that helps to join them with a dimension table and others that contains the value or data that need to be analyzed. It mostly contains numeric data. It grows vertically and it contains more records and fewer attributes.
Characteristics of Fact Table
Keys: Fact table consists of a key that is the combination or concatenation of all primary keys of various dimension tables associated with that fact table. Such key is called a concatenated key which uniquely identifies the row of the fact table.
Fact Table Grain: Grain of the table means the level of the detail or the deepness of the information that is stored in the fact table. The level must be the highest for designing an efficient fact table.
Additive Measures: Attributes in the fact table can be fully additive, semi-additive or non- additive. Fully additive or additive measures are those that are added to all dimensions. In semi-additive, measures are added to some dimensions and not to all and non-additive measures are those which stores basic unit of measurement of any business process.
Sparse Data: Some records present in the fact table contain attributes with null values or measures i.e. these records do not give or provide any information.
Degenerated Dimensions: The dimensions or attributes present in the fact table which cannot be added or which are not additive are called a degenerated dimension.
Outrigger Dimensions: The dimensions that have reference to any other dimension table are called as outrigger dimensions.
Shrunken Rollup Dimensions: The dimensions which are the subdivision of columns and rows of the base dimension are called Shrunken Rollup dimensions.
Dimension Table Characteristics of Dimension Table
Keys: Every dimension table needs to have a primary key that helps to uniquely identify each record of the dimension table.
Attributes: Dimension table contains many attributes and therefore the dimension table appears to grow horizontally.
Attribute Values: The values in the dimension table are mostly in textual format and not in numeric format.
Relation Between Attributes: Attributes present in the dimension table are generally not directly related to each other but still are a part of the same dimension table.
Normalization: Dimension table is not normalized because normalization splits the data and creates additional tables which decrease the efficiency of the query execution as it must pass through these additional tables when it wants to recover measurements from the fact table for any corresponding attribute in the dimension table.
Drilling Down, Rolling Up: Attributes present in dimension table permits to derive details through traversing from the higher level to lower level or it also allows rolling up from lower level to the higher level of the attributes.
Records: Dimension table has less number of records and more number of attributes.
Head to Head Comparison Between Fact Table and Dimension Table (Infographics)Below are the Top 12 differences between Fact Table vs Dimension Table
Key Differences Between Fact Table and Dimension Table
Fact Table contains the values or measurements of the attributes of the dimension table.
Fact table comprises of fewer attributes and more records whereas the dimension table comprises of lesser records and more attributes.
The fact table grows vertically while the dimension table grows horizontally.
Fact Table contains a concatenated key while the dimension table contains a primary key.
Dimension tables must be created before the fact table is created.
Any schema contains more dimension tables and less fact tables.
Fact Table vs Dimension Table Comparison TableLet us discuss the topmost differences between Fact Table vs Dimension Table.
Characteristics Fact Table Dimension Table
Basic Definition It contains measurements, facts or metrics of the attributes. It is the companion table that contains attributes using which fact table deduce the facts.
Design It is defined by data grain. It is descriptive, complete and wordy.
Task It contains measures and is used for analysis and decision making. It contains information about a business and its process.
Type of Data It contains data in both numeric as well as textual format. It contains data in only textual format.
Key It has a primary key for each dimension which is acts as a foreign key in the dimension table. It has a foreign key associated with the primary key of the fact table.
Storage It stores the filter domain and reports labels in dimension tables. It stores the detailed atomic data into dimensional structures.
Hierarchy It does not have a hierarchy. It contains a hierarchy.
Attributes It has less attributes More attributes
Records More records Less records.
Table Growth The table grows vertically. The table grows horizontally.
Creation Time A fact table is created after dimension tables are created. The dimension table needs to be created first.
Schema Structure There is less number of fact tables in a schema. There is a number of dimension tables in a schema.
ConclusionIn this article, we read about the fact table vs dimension table and the differences between them in detail. These tables are important for developing a schema. Dimension table is a companion of the fact table and both are necessary for each other.
Recommended ArticlesThis has been a guide to Fact Table vs Dimension Table. Here we also discuss the key differences with infographics and comparison table. You can also go through our other suggested articles to learn more –
You're reading Fact Table Vs Dimension Table
Update the detailed information about Fact Table Vs Dimension Table on the Vibergotobrazil.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!