A dimension table is a key component of a data warehouse that stores descriptive attributes about business entities - such as customers, products, locations, or time. These attributes provide context to the numeric measurements stored in fact tables. Dimension tables help users filter, group, drill down, slice, and summarize data for analytical queries.
Each dimension table contains a primary key that connects to the foreign keys of a fact table, forming the foundation of dimensional modeling.
Features of Dimension Table
- Descriptive Attributes: The textual or descriptive dimension of the data involves the identification of dimension data in terms of names, dates or categories.
- Primary Key: Every dimension table has its primary key that is used to establish a relationship with the primary foreign key of the fact table.
- Denormalized Structure: Sometimes denormalized to speed up the query execution because it requires fewer joins on the normalized tables.
- Hierarchical Data: This may contain the hierarchy data structures to enable the drill-down operations on reports (for instance, year, quarter, and month).
- Slowly Changing Dimensions (SCD): Supports the use of slowly changing dimensions (SCD) to maintain a proper record of dimension attributes that change frequently.
Structure of a Dimension Table
- Columns and Rows: A dimension table is like a regular database table with multiple rows and columns. It often has many columns because the data is usually denormalized for easier analysis. Compared to fact tables, dimension tables typically have fewer rows.
- Primary Key: A unique identifier for each row. Used in fact tables for fast joins.
- Attributes: Additional columns offer descriptive details that help interpret facts.
Example of dimension table
To understand how a dimension table works, let’s look at a practical example. Below is a Product Dimension Table from a retail data warehouse that stores descriptive details about items sold in the store:
Explanation of the Dimension Table Columns in the above image:
- ProductID: Acts as the "primary key" of the dimension table which uniquely identifies each product and links to the fact table.
- ProductName: Describes the name of the product which helps analysts understand what item a fact (sale, order, etc.) refers to.
- Category: Represents the broader classification of the product. Useful for grouping and filtering reports (e.g., Beverages, Bakery, Snacks).
- Brand: Shows the manufacturer or label of the product which allows brand-level comparison such as "Sales by Brand".
- Price: Indicates the retail price of the product. Helps derive metrics such as revenue, margin, and average selling price.
- Supplier: Specifies the company that supplies the product. Supports analyses like "Top suppliers" or "Supplier performance".
Key Types Used in Dimension Tables
1. Natural Key: A key derived from real-world data (e.g., SKU code, SSN). However, natural keys may be:
- Large and complex
- Not stable
- Inconsistent across source systems
2. Surrogate Key (Recommended): An artificial key (usually an integer) generated by the warehouse.
Benefits:
- Faster joins
- Simple and uniform
- Helpful when integrating data from multiple systems
Denormalization in Dimension Tables
Denormalization involves merging related data into a single table to reduce the need for complex joins and significantly increase query performance for read-heavy analytics.
Benefits:
- Fewer joins → Faster query performance
- Simplifies reporting tools
Contrast:
Transactional systems stay normalized to ensure:
- Data consistency
- Efficient insert/update operations