Difference Between Fact Table and Dimension Table

Key Difference between a Fact table and a Dimension table

The fact table contains measurements, metrics, and facts about a business process, while the Dimension table is a companion to the fact table, which contains descriptive attributes to be used as query constraining.
The fact table is located at the center of a star or snowflake schema, whereas the Dimension table is located at the edges of the star or snowflake schema.
A fact table is defined by its grain or most atomic level, whereas a Dimension table should be wordy, descriptive, complete, and of assured quality.
The fact table helps to store report labels, whereas Dimension table contains detailed data.
The fact table does not contain a hierarchy, whereas the Dimension table contains hierarchies.

What is Fact Table?

A fact table is a primary table in a dimensional model.

A Fact Table contains

Measurements/facts
Foreign key to dimension table

What is a Dimension Table?

A dimension table contains dimensions of a fact.
They are joined to fact table via a foreign key.
Dimension tables are de-normalized tables.
The Dimension Attributes are the various columns in a dimension table
Dimensions offers descriptive characteristics of the facts with the help of their attributes
No set limit set for given for number of dimensions
The dimension can also contain one or more hierarchical relationships

Fact Table vs Dimension Table

Below is difference between Fact Table and Dimension Table

Parameters	Fact Table	Dimension Table
Definition	Measurements, metrics or facts about a business process.	Companion table to the fact table contains descriptive attributes to be used as query constraining.
Characteristic	Located at the center of a star or snowflake schema and surrounded by dimensions.	Connected to the fact table and located at the edges of the star or snowflake schema
Design	Defined by their grain or its most atomic level.	Should be wordy, descriptive, complete, and quality assured.
Task	Fact table is a measurable event for which dimension table data is collected and is used for analysis and reporting.	Collection of reference information about a business.
Type of Data	Facts tables could contain information like sales against a set of dimensions like Product and Date.	Evert dimension table contains attributes which describe the details of the dimension. E.g., Product dimensions can contain Product ID, Product Category, etc.
Key	Primary Key in fact table is mapped as foreign keys to Dimensions.	Dimension table has a primary key columns that uniquely identifies each dimension.
Storage	Helps to store report labels and filter domain values in dimension tables.	Load detailed atomic data into dimensional structures.
Hierarchy	Does not contain Hierarchy	Contains Hierarchies. For example Location could contain, country, pin code, state, city, etc.

Type of facts

Type of facts	Explanation
Additive	Measures should be added to all dimensions.
Semi-Additive	In this type of facts, measures may be added to some dimensions and not with others.
Non-Additive	It stores some basic unit of measurement of a business process. Some real-world examples include sales, phone calls, and orders.

Types of Dimensions

Types of Dimensions	Definition
Conformed Dimensions	Conformed dimensions is the very fact to which it relates. This dimension is used in more than one-star schema or Datamart.
Outrigger Dimensions	A dimension may have a reference to another dimension table. These secondary dimensions called outrigger dimensions. This kind of Dimensions should be used carefully.
Shrunken Rollup Dimensions	Shrunken Rollup dimensions are a subdivision of rows and columns of a base dimension. These kinds of dimensions are useful for developing aggregated fact tables.
Dimension-to-Dimension Table Joins	Dimensions may have references to other dimensions. However, these relationships can be modeled with outrigger dimensions.
Role-Playing Dimensions	A single physical dimension helps to reference multiple times in a fact table as each reference linking to a logically distinct role for the dimension.
Junk Dimensions	It a collection of random transactional codes, flags or text attributes. It may not logically belong to any specific dimension.
Degenerate Dimensions	Degenerate dimension is without corresponding dimension. It is used in the transaction and collecting snapshot fact tables. This kind of dimension does not have its dimension as it is derived from the fact table.
Swappable Dimensions	They are used when the same fact table is paired with different versions of the same dimension.
Step Dimensions	Sequential processes, like web page events, mostly have a separate row in a fact table for every step in a process. It tells where the specific step should be used in the overall session.