In the context of data warehousing, fact tables and dimension tables are fundamental components that work together to provide a structure for analytical data. Here's a breakdown of their roles :
Fact Tables :
- Purpose:
- Fact tables store quantitative data, also known as "measures," that represent business events or transactions.
- They record the "how much" of a business, such as sales amounts, quantities, or profits.
- Characteristics:
- Contain numerical data.
- Typically include foreign keys that link to dimension tables.
- Can grow very large, as they store detailed transaction data.
- Focus on recording events or measurements.
- Example:
- A sales fact table might record each sales transaction, including the date, product, customer, and sales amount.
Dimension Tables :
- Purpose:
- Dimension tables store descriptive attributes that provide context to the data in fact tables.
- They answer the "who," "what," "where," and "when" of a business.
- Characteristics:
- Contain textual or categorical data.
- Provide context for the facts.
- Typically smaller than fact tables.
- Focus on describing the dimensions of business.
- Example:
- A customer dimension table might store customer information, such as name, address, and demographics.
- A product dimension table would hold product information like product name, category, and price.
- A time dimension table would hold information about dates, such as day, week, month, and year.
Relationship :
- Fact tables and dimension tables are linked through foreign key relationships.
- This relationship allows users to analyze factual data in the context of various dimensions.
- The most common model for this is the star schema, where a central fact table is surrounded by dimension tables.
In essence, fact tables provide the "what happened," and dimension tables provide the "who, what, where, and when" that give that "what happened" meaning.
