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.

Become an Expert in Data Analytics!

Professional Certificate Program In Data AnalyticsExplore Now
Become an Expert in Data Analytics!

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.

Learn Data Analytics From IBM Experts!

Data Analyst Master’s ProgramExplore Program
Learn Data Analytics From IBM Experts!

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.

Become a Data Scientist with Hands-on Training!

Data Scientist Master’s ProgramExplore Program
Become a Data Scientist with Hands-on Training!

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.

Enroll in the Professional Certificate Program in Data Analytics to learn over a dozen of data analytics tools and skills, and gain access to masterclasses by Purdue faculty and IBM experts, exclusive hackathons, Ask Me Anything sessions by IBM.

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

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors