EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Head to Head Differences Tutorial Fact Table vs Dimension Table
 

Fact Table vs Dimension Table

Priya Pedamkar
Article byPriya Pedamkar

Updated March 20, 2023

Fact Table vs Dimension Table

 

 

Introduction to Fact Table and Dimension Table

Fact tables comprises of the facts of the system as its data content, and Dimension tables comprises of all the properties or objects of the fact tables that can help to connect fact tables to the respective dimension tables. The data in both the tables can be in normal text format, while fact tables can have numbers along with the texts. In the process of creating the database, dimension tables are created before fact tables due to their own properties.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Fact table

It 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

A dimension table contains the dimensions along which the values of the attributes are taken in the fact table. Dimension tables are small in size, contains only several thousand rows but the size can be increased occasionally. These tables are associated with a fact table through foreign keys. These tables are de-normalized. The dimension table contains hierarchical relationships and grows horizontally.

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

Fact Table vs Dimension Table Infographics

Key Differences Between Fact Table and Dimension Table

Let us discuss some of the major differences between Fact Table vs 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 Table

Let 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.

Conclusion

In 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 Articles

This 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 –

  1. Data Warehouse vs Data Mart
  2. Difference between AUTOCAD vs CATIA
  3. DOS vs Windows – Top Comparison
  4. White Box Testing vs Black Box Testing
  5. Types, Features, and Steps to Implement Data Mart

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW