In the design of data warehouses and Business Intelligence (BI) systems, fact and dimension tables are fundamental components that work together to enable efficient data analysis and meaningful insights. Fact tables store quantitative, measurable data related to business processes, while dimension tables provide descriptive, contextual information to enrich and categorize those measurements.
Fact Tables: Core Quantitative Data
Fact tables are the central tables in dimensional models such as star and snowflake schemas. They hold the core business metrics or facts generated by operational processes.
Contents: Fact tables contain numeric measures like sales revenue, quantity ordered, transaction counts, or profit margins.
Keys: They include foreign keys linking to associated dimension tables, enabling context for the facts.
Granularity: The granularity of the fact table defines the level of detail captured—for example, sales data might be recorded at the transaction level or daily summary level.
Characteristics
1. Contain additive or semi-additive metrics, which can be aggregated for reporting.
2. Usually have a large volume of data compared to dimension tables.
3. Serve as the primary source for analytical queries and aggregations.
Dimension Tables: Descriptive Context
Dimension tables provide textual or categorical attributes that describe the facts, allowing users to analyze and segment data in meaningful ways.
Contents: Dimensions contain descriptive attributes such as customer name, product category, date, region, or salesperson.
Keys: Each dimension table has a primary key, which is referenced as a foreign key in the fact table.
Characteristics
1. Typically, smaller and less frequently updated than fact tables.
2. Support filtering, grouping, and slicing of data in reports and dashboards.
3. Often denormalized in star schemas for simplicity, but may be normalized in snowflake schemas.
How Fact and Dimension Tables Work Together
In dimensional modeling, facts and dimensions complement each other to support fast, intuitive analysis. The following points outline how they integrate:
Example
Consider a sales data warehouse:
| Table | Example Data | Role |
| Fact_Sales | Sale_ID, Date_Key, Product_Key, Customer_Key, Units_Sold, Revenue | Stores sales metrics and foreign keys linking to dimensions |
| Dim_Date | Date_Key, Date, Month, Quarter, Year | Provides time context for sales data |
| Dim_Product | Product_Key, Product_Name, Brand, Category | Provides product details for sales aggregation |
| Dim_Customer | Customer_Key, Customer_Name, Location, Segment | Describes customer attributes |
