Fact tables and dimension tables are key components of a schema in a data warehouse. A fact table contains 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. 

Dimension tables, on the other hand, 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.

In this article, we will be discussing both these concepts and also their differences to be able to make better data analytics decisions!

What is a Fact Table?

In a data warehouse, a fact table is 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.

  • When multiple fact tables are used, they can be organized using a "fact constellation schema." 
  • A fact table has two types of columns: those that contain the facts and those that serve as foreign keys linking to dimension tables. 
  • The primary key of a fact table is often a composite key made up of all of the foreign keys in the table. 
  • Fact tables can hold various types of measurements, such as additive, non-additive, and partly additive measures, and store important information in the data warehouse. 
  • They are useful for evaluating dimensional attributes because they provide additive values that can act as independent variables.

Let us look at some of the main characteristics of a Fact Table.

Characteristics of a Fact Table

The following are some of the characteristics of a fact table:

  • Outrigger Dimensions: Outrigger dimensions are dimensions that refer to any other dimension table.
  • 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. A concatenated key is one such key that specifically identifies the row of the fact table.
  • 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. 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.
  • Fact 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.
  • Degenerated Dimensions: A degenerated dimension is any dimension or attribute that is available in the fact table but cannot be added to or is non-additive.
  • 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.
  • Shrunken Rollup Dimensions: The dimensions that are created by subdividing the base dimension's columns and rows are known as shrunken rollup dimensions.

Understanding the Granularity of a Fact Table

The granularity of a fact table is a common way to describe it. The grain of a fact table refers to the most basic level at which the facts can be defined. For example, the grain of a sales fact table might be "sales volume by day by-product by the shop," meaning that each entry in the table is uniquely identified by a day, product, and shop. Other dimensions, such as location or region, may also be included in the fact table, but they do not contribute to the uniqueness of the entries. These "affiliate dimensions" can provide additional information to slice and dice, but they usually provide insights at a higher level of aggregation (since a region contains many stores).

We will now understand the dynamics of a Dimension Table.

What is a Dimension Table?

Dimension tables contain descriptions of the objects in a fact table and provide information about dimensions such as values, characteristics, and keys. 

  • These tables are usually small, with a number of rows ranging from a few hundred to a few thousand. 
  • 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.

To understand this better, let us look at some characteristics of a Dimension Table.

Characteristics of a Dimension Table

The following are some of the characteristics of a dimension table:

  • Relationship Between Attributes: Although they are all included in the same dimension table, the attributes in it typically do not have a direct relationship with one another.
  • Records: The dimension table contains more characteristics than records.
  • Keys: The main key is required for every dimension table in order to help uniquely identify each entry.
  • Normalization: The dimension table is not normalized because doing so splits the data into different tables and makes it more difficult for queries to execute quickly because they must go through these extra tables to retrieve measurements from the fact table for each corresponding attribute in the dimension table.
  • Attributes: Because the dimension table has so many attributes, it looks to be expanding horizontally.
  • Drilling Down, Rolling Up: The presence of attributes in a dimension table enables the extraction of information by drilling down from a higher level to a lower level or by rolling up from a lower level to a higher level of the attributes.
  • Attribute Values: The majority of the values in the dimension table are expressed as text rather than numbers.

Now that we have a general understanding of fact and dimension tables, let us understand the key differences between these two types of tables in a data warehouse.

Fact Table vs. Dimension Table 

Despite being related to each other, the Fact and Dimension Tables consist of many differences. 

Here are some of their major differences:

Basis of Distinction

Fact Table

Dimension Table

Definition

Facts about a business process, such as measurements or metrics.

Descriptive characteristics in the companion table to the fact table can be utilized as query constraints.

Characteristics

Positioned in the middle of a snowflake or star schema, surrounded by dimensions.

The edges of the snowflake or star schema, attached to the fact table,

Design

Defined by their grain or at the atomic level.

It must be extensive, in-depth, and of the highest calibre.

Task

A fact table is a quantifiable event for which data from a dimension table is gathered and used for reporting and analysis.

Gathering of background data about a company.

Data Type

Facts tables may include data about sales in relation to a number of parameters, such as Product and Date.

Each dimension table has attributes that provide information about the specifics of the dimension. For instance, product dimensions may include the product ID, category, etc.

Key

The fact table's primary key is mapped as a foreign key to dimensions.

Each dimension in a dimension table contains a primary key column that uniquely identifies it.

Storage

Helps to save report labels and filter domain values in dimension tables.

Load dimensional structures with thorough atomic data.

Hierarchy

Contains no hierarchy.

Hierarchies are present. For instance, Location could include a country, state, city, zip code, and more.

To understand the underlying context of each of these concepts in a better way, we need to understand their various types. In the upcoming section, we will understand the types of facts and dimensions along with their detailed examples.

Types of Facts

The following are the different categories of facts:

Types of Facts

Explanation

Additive

All dimensions should be multiplied by measures.

Non-Additive

Measures may be introduced to some dimensions in this type of data but not to others.

Semi-Additive

It keeps track of some fundamental business process metrics. Examples from the actual world include orders, phone calls, and sales.

Types of Dimensions

The major types of dimensions are categorized as follows:

Types of Dimensions

Explanation

Shrunken Rollup Dimensions

Shrinks the rows and columns of the base dimension are divided into rollup dimensions. For creating aggregate fact tables, several types of dimensions are helpful.

Outrigger Dimensions

There could be a reference in a dimension to some other dimension table. Outrigger dimensions are the name for these extra measurements. Use of these Dimensions should be prudent.

Role-Playing Dimensions

A single specific dimension can be mentioned more than once in a table structure since each mention points to one of the dimension's logically separate roles.

Step Dimensions

Every step in sequential operations, such as web page events, often has its own row in a fact table. It indicates where in the entire session the particular step should be used.

Degenerate Dimensions

Without a comparable dimension, a dimension is degenerate. It is employed in transactions and the gathering of fact tables for snapshots. Since it is taken from the fact table, this type of dimension does not possess its own dimension.

Conformed Dimensions

Refer to the very reality of conformed dimensions. This dimension is utilized in data marts or schemas with more than one star.

Junk Dimensions

It consists of a group of randomly chosen transactional codes, flags, or text properties. It might not logically fall under any one dimension.

Dimension-to-Dimension Table Joins

There may be references to certain other dimensions in a dimension. Outrigger dimensions, however, can be used to model these interactions.

Swappable Dimensions

They are applied when various iterations of a single dimension are paired with the same fact table.

After learning about Fact and Dimension Tables, their major differences, and their types, it's time to summarize the key differences between them to solidify our understanding of them.

Fact Table vs. Dimension Table: Key Differences

The key differences between Fact and Dimension Tables are as follows:

  • 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.
  • 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.
  • A fact table should be lengthy, descriptive, full, and of guaranteed quality, while 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.
  • In contrast to the Dimension table, which incorporates hierarchies, the Table does not.

To understand these key differences even better, let us go through an example that connects the relationship between Fact and Dimension tables.

Example of Fact Table vs. Dimension Table

Let's say a business sells goods to clients. Every transaction is a fact that actually occurs, and the fact table is where these facts are recorded. 

For instance:

Order ID

Good ID

Client ID

Units Sold

142

897

45

9

143

452

76

6

144

16

28

30

We can now include a dimension table for customers:

Client ID

Name

Gender

Income

Education

Region

28

Mariana Jones

F

300000

3

3

45

Christopher Nolan

M

600000

2

2

76

Mia Riana

F

1000000

3

6

The client ID column in the fact table is used to connect to the dimension table. For instance, if you look at row 2 of the fact table, you can see that client 3, Mia Riana, made a purchase of six items with order id 143. To find out more information about what Mia purchased and when the company would also have a product table and a timetable.

When designing fact tables, there are considerations related to both physical constraints and data limitations. It's important to think about the size of the table and the ways in which it will be accessed. Indexes can be helpful for optimizing both of these factors. However, from a logical design standpoint, there should be no limitations on the structure of the table. It should be designed based on current and future needs, with as much flexibility as possible to allow for future updates without needing to completely rebuild the data.

Choose the Right Program

Looking to build a career in the exciting field of data analytics? Our Data Analytics courses are designed to provide you with the skills and knowledge you need to excel in this rapidly growing industry. Our expert instructors will guide you through hands-on projects, real-world scenarios, and case studies, giving you the practical experience you need to succeed. With our courses, you'll learn to analyze data, create insightful reports, and make data-driven decisions that can help drive business success.

Program Name Data Analyst Post Graduate Program In Data Analytics

Data Analytics Bootcamp
Geo All Geos All Geos US
University Simplilearn Purdue Caltech
Course Duration 11 Months 8 Months 6 Months
Coding Experience Required No Basic No
Skills You Will Learn 10+ skills including Python, MySQL, Tableau, NumPy and more
Data Analytics, Statistical Analysis using Excel, Data Analysis Python and R, and more
Data Visualization with Tableau, Linear and Logistic Regression, Data Manipulation and more
Additional Benefits Applied Learning via Capstone and 20+ industry-relevant Data Analytics projects Purdue Alumni Association Membership
Free IIMJobs Pro-Membership of 6 months
Access to Integrated Practical Labs Caltech CTME Circle Membership
Cost $$ $$$$ $$$$
Explore Program Explore Program Explore Program

Learn More About Fact Tables and Dimension Tables With Simplilearn’s PCP Data Analytics Certification Course

Fact tables and dimension tables play different but important roles in a data warehouse. Fact tables contain numerical data, while dimension tables provide context and background information. Both types of tables are necessary for effective data analysis and decision-making

Consider checking out the PCP Data Analytics Certification Training Course provided by Simplilearn if you're looking to learn more about data analytics and how to use these concepts in real-world situations. You may get the skills and information required for success in the industry by enrolling in this thorough course, which covers a wide range of topics, including data warehousing and business intelligence. 

FAQs

1. What is the dimension and fact table with examples?

The primary distinction between a fact table, also known as a reality table, and a dimension table is that the latter includes properties from which actual measurements are made.

2. What are facts and dimensions?

Data warehousing terminology includes facts and dimensions. A fact is a piece of information with a specific numerical value, like a sale or a download. Facts are kept in fact tables, which are linked to several dimension tables by a foreign key. Facts are accompanied by dimensions, which describe the items in a fact table.

3. Why is the fact table larger than the dimension table?

Fact tables have more records and fewer attributes, while dimension tables have more attributes and fewer records. While the dimension table expands horizontally, the fact table expands vertically. While the dimension table has a primary key, the table has a concatenated key.

4. What is the relationship between facts and dimensions?

A single-dimension membership can be connected to many facts in the majority of dimensions, where each fact links to one and only one member of the dimension. This is known as each relationship in relational database jargon. However, connecting a single piece of evidence to several dimension members is frequently helpful. 

5. What are the 5 types of dimensions?

There are 9 types of dimensions:

  • Shrunken Rollup Dimensions
  • Outrigger Dimensions
  • Role-Playing Dimensions
  • Step Dimensions
  • Degenerate Dimensions
  • Conformed Dimensions
  • Junk Dimensions
  • Dimension-to-Dimension Table Joins
  • Swappable Dimensions

6. What are the three types of fact tables?

The three types of fact tables are as follows:

  • Additive
  • Non-Additive
  • Semi-Additive

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Caltech Post Graduate Program in Data Science

Cohort Starts: 23 Jul, 2024

11 Months$ 4,500
Data Analytics Bootcamp

Cohort Starts: 23 Jul, 2024

6 Months$ 8,500
Post Graduate Program in Data Engineering

Cohort Starts: 29 Jul, 2024

8 Months$ 3,850
Post Graduate Program in Data Analytics

Cohort Starts: 1 Aug, 2024

8 Months$ 3,500
Post Graduate Program in Data Science

Cohort Starts: 7 Aug, 2024

11 Months$ 3,800
Applied AI & Data Science

Cohort Starts: 20 Aug, 2024

3 Months$ 2,624
Data Scientist11 Months$ 1,449
Data Analyst11 Months$ 1,449

Get Free Certifications with free video courses

  • Introduction to Data Analytics Course

    Data Science & Business Analytics

    Introduction to Data Analytics Course

    3 hours4.6271K learners
  • Introduction to Data Science

    Data Science & Business Analytics

    Introduction to Data Science

    7 hours4.669.5K learners
prevNext

Learn from Industry Experts with free Masterclasses

  • Career Masterclass: AI Engineer vs. Data Scientist: Skills, Roles, and Opportunities

    Data Science & Business Analytics

    Career Masterclass: AI Engineer vs. Data Scientist: Skills, Roles, and Opportunities

    3rd Jul, Wednesday9:00 PM IST
  • Break into a Rewarding AI & Data Science Career with Brown University

    Data Science & Business Analytics

    Break into a Rewarding AI & Data Science Career with Brown University

    5th Jun, Wednesday8:30 PM IST
  • Data Scientist vs Data Analyst: Breaking Down the Roles

    Data Science & Business Analytics

    Data Scientist vs Data Analyst: Breaking Down the Roles

    21st May, Tuesday9:00 PM IST
prevNext