Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference Between Fact Table and Dimension Table
A Fact Table contains a combination of attributes from completely different dimension table. It helps the users understand and analyze the business dimensions that would help improve the business processes. Fact tables are also known as Reality tables.
Dimension tables, on the other hand, help a Fact Table gather the dimensions on the data that needs to be collected.
Read through this article to find out more about Fact tables and Dimension Tables and how they are different from each other.
What is a Fact Table?
In data warehousing, a Fact Table is one that contains the measurements, metrics, or facts of a business operation. It is surrounded by Dimension Tables and is found at the core of a star or snowflake schema.
When several fact tables are utilized, a "fact constellation schema" is used to organize them.
There are two sorts of columns in a fact table: those that hold facts and those that serve as a foreign key to dimension tables.
A fact table's main key is generally a composite key made up of all of the table's foreign keys.
Fact tables hold several sorts of measurements such as additive, non-additive, and partly additive measures and include the data warehouse's information.
Fact tables are used to evaluate dimensional qualities because they give additive values that behave as independent variables.
Granularity of a Fact Table
The granularity of a fact table is frequently used to characterize it. The grain of a fact table represents the most atomic level by which the facts may be defined. "Sales volume by day by product by shop" may be the grain of a sales fact table. As a result, each entry in this fact table is individually identified by a day, product, and shop.
Other dimensions (such as location/region) may be members of this fact table, but they add nothing to the uniqueness of the fact entries. These "affiliate dimensions" allow for more independent information to be sliced, but they typically give insights at a higher degree of aggregation (a region contains many stores).
What is a Dimension Table?
Dimension Tables contain the descriptions of the objects in a fact table. They aid in the description of dimensions such as dimension values, characteristics, and keys.
Dimension Tables are usually of modest size. The number of rows might range from a few hundreds to thousands.
The term "dimension table" refers to a collection or set of data pertaining to any quantifiable occurrence. They serve as the foundation for dimensional modeling.
Dimension Tables include a column that serves as a primary key, allowing each dimension row or record to be uniquely identified. Through this key, it is linked to the fact tables. When it's made, a system-generated key called a surrogate key is used to uniquely identify the rows in the dimension.
Difference between Fact Table and Dimension Table
The following table highlights major differences between a Fact Table and a Dimension Table.
| Fact Table | Dimension Table |
|---|---|
| The properties of a dimension table are measured in a fact table. | The characteristics are stored in a dimension table, and the metric is calculated using a truth table. |
| A fact table has fewer characteristics than a dimension table. | In comparison to fact tables, dimension tables have more characteristics. |
| There are more records in a fact table than there are in a dimension table. | There are fewer records in a dimension table than in a fact table. |
| A fact table is arranged vertically. | The dimension table, on the other hand, is a horizontal table. |
| A fact table can have both numerical and text attribute formats. | A dimension table's attribute format is text. |
Conclusion
In a schema, the number of fact tables is less in comparison to the number of dimension tables. Fact tables are used for analysis and decision-making process, while Dimension tables help store information about a business and its processes.
8K+ Views
