Quick Introduction

Fact and dimension tables are two fundamental concepts in data warehousing and database design. They serve different roles in organizing and storing data for efficient analysis. Here's the difference between them:

Key Differences between Fact and Dimension Tables

Fact Table:

A fact table contains the quantitative data or facts that you want to analyze.
It typically consists of numerical values, measures, or metrics.
Fact tables are often quite large, containing millions or even billions of records.
They are associated with business transactions or events.
Fact tables usually have foreign keys that link to dimension tables.
They store data about what happened, such as sales, orders, or customer interactions.

Dimension Table:

A dimension table contains descriptive attributes that provide context to the facts in the fact table.
It holds textual or categorical data that helps in filtering, grouping, and labeling facts.
Dimension tables are generally smaller compared to fact tables.
They provide additional information for slicing and dicing the data.
Dimension tables often have primary keys that are referenced as foreign keys in the fact table.
They store data about who, what, where, when, and why something happened.

Example - [HumanResource Schema]

Let's take an example of HumanResource Schema from the AdventureWorks2022 Database. Here are the tables and data from the HumanResources schema in the AdventureWorks2022 database:

Department: This table contains information about the departments in the company, such as the department name, the department manager, and the department location.

Column Name	Data Type	Description
DepartmentID	int	The unique identifier for the department.
Name	nvarchar(50)	The name of the department.
ManagerID	int	The employee ID of the department manager.
LocationID	int	The location ID of the department.

Employee: This table contains information about the employees of the company, such as their name, address, and phone number.

Column Name	Data Type	Description
EmployeeID	int	The unique identifier for the employee.
Name	nvarchar(50)	The name of the employee.
Title	nvarchar(50)	The title of the employee.
BirthDate	datetime	The date of birth of the employee.
HireDate	datetime	The date the employee was hired.
DepartmentID	int	The department ID of the employee.
ManagerID	int	The employee ID of the employee's manager.
Phone	nvarchar(25)	The phone number of the employee.
Email	nvarchar(50)	The email address of the employee.
Address	nvarchar(60)	The address of the employee.
City	nvarchar(30)	The city of the employee's address.
StateProvince	nvarchar(2)	The state or province of the employee's address.
CountryRegion	nvarchar(20)	The country of the employee's address.
PostalCode	nvarchar(10)	The postal code of the employee's address.

EmployeeDepartmentHistory: This table contains information about the departments that employees have worked in, such as the department name, the department manager, and the department start and end dates.

Column Name	Data Type	Description
EmployeeID	int	The unique identifier for the employee.
DepartmentID	int	The department ID of the employee.
DepartmentName	nvarchar(50)	The name of the department.
ManagerID	int	The employee ID of the department manager.
StartDate	datetime	The start date of the employee's employment in the department.
EndDate	datetime	The end date of the employee's employment in the department.

EmployeePayHistory: This table contains information about the pay history of employees, such as their salary, bonus, and overtime pay.

Column Name	Data Type	Description
EmployeeID	int	The unique identifier for the employee.
PayDate	datetime	The date the pay was earned.
Rate	money	The hourly rate of pay for the employee.
HoursWorked	int	The number of hours worked by the employee.
RegularPay	money	The regular pay for the employee.
OvertimePay	money	The overtime pay for the employee.
BonusPay	money	The bonus pay for the employee.
TotalPay	money	The total pay for the employee.

JobCandidate: This table contains information about job candidates, such as their name, address, and resume.

Column Name	Data Type	Description
JobCandidateID	int	The unique identifier for the job candidate.
Name	nvarchar(50)	The name of the job candidate.
Address	nvarchar(60)	The address of the job candidate.
City	nvarchar(30)	The city of the job candidate's address.
StateProvince	nvarchar(2)	The state or province of the job candidate's address.
CountryRegion	nvarchar(20)	The country of the job candidate's address.
PostalCode	nvarchar(10)	The postal code of the job candidate's address.
Phone	nvarchar(25)	The phone number of the job candidate.
Email	nvarchar(50)	The email address of the job candidate.
Resume	varbinary(max)	The resume of the job candidate.

So Let's Identify which are Fact and dimension Tables from the HumanResource Schema.

So we have added prefixes to whichever tables are Fact as Fact and Dimension ad Dim.

fig. Data Model of HumanResource Schema

The following tables in the HumanResources schema are fact tables:

EmployeePayHistory: This table contains information about the pay history of employees.
EmployeeDepartmentHistory: This table contains information about the departments that employees have worked in.

The following tables in the HumanResources schema are dimension tables:

Department: This table contains information about the departments in the company.
Employee: This table contains information about the employees of the company.
JobCandidate: This table contains information about job candidates.
Shift: This table contains information about the shifts that employees work.

How tables were categorized as Fact and Dimension tables?

The EmployeePayHistory and EmployeeDepartmentHistory tables are fact tables because they store measurements of business events. The EmployeePayHistory table stores information about the pay of employees, such as their salary, bonus, and overtime pay. The EmployeeDepartmentHistory table stores information about the departments that employees have worked in, such as the department name, the department manager, and the department start and end dates.

The Department, Employee, JobCandidate, and Shift tables are dimension tables because they provide context for the measurements in the fact tables. The Department table provides information about the departments in the company, such as the department name, the department manager, and the department location. The Employee table provides information about the employees of the company, such as their name, address, and phone number. The JobCandidate table provides information about job candidates, such as their name, address, and resume. The Shift table provides information about the shifts that employees work, such as the shift start and end times, and the number of employees on each shift.

Thank you for reading till the end!

Difference between Fact and Dimension tables in SQL/Power BI with example.