> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Fact Table

<Info>
  A **Fact Table** is a central table in a [data warehouse](/glossary/data-warehouse) that stores quantitative data (measures) for analysis. It contains foreign keys that link to [dimension tables](/data-modeling/dimension-table), which provide context for the measures. Fact tables are a core component of **dimensional modeling**, enabling efficient querying and analysis of business processes such as sales, transactions, or inventory.
</Info>

## 1. **What is a Fact Table?**

A fact table stores numerical measurements or metrics (facts) related to a specific business process. It is typically surrounded by dimension tables that provide descriptive context. For example, in a sales data warehouse, a fact table might store sales revenue, quantity sold, and profit, linked to dimensions like time, product, and customer.

## 2. **Key Features of Fact Tables**

* **Measures**: Numerical data representing business metrics (e.g., sales amount, quantity sold).
* **Foreign Keys**: Links to dimension tables (e.g., product ID, customer ID, time ID).
* **Granularity**: The level of detail stored in the fact table (e.g., daily sales, monthly sales).
* **Additive, Semi-Additive, or Non-Additive Measures**:
  * **Additive**: Can be summed across all dimensions (e.g., sales revenue).
  * **Semi-Additive**: Can be summed across some dimensions but not all (e.g., inventory levels).
  * **Non-Additive**: Cannot be summed (e.g., profit margin).
* **Sparse Data**: Fact tables often contain sparse data, as not all combinations of dimensions may have associated facts.

## 3. **Types of Fact Tables**

1. **Transaction Fact Tables**:
   * Store data at the transaction level (e.g., individual sales transactions).
   * High granularity and large volume of data.
2. **Snapshot Fact Tables**:
   * Capture data at specific points in time (e.g., daily inventory levels).
   * Lower granularity compared to transaction fact tables.
3. **Accumulating Fact Tables**:
   * Track processes that have a well-defined lifecycle (e.g., order fulfillment).
   * Include dates for each stage of the process.
4. **Factless Fact Tables**:
   * Store only foreign keys without numerical measures (e.g., tracking attendance or events).

## 4. **Structure of a Fact Table**

* **Foreign Keys**: Links to dimension tables (e.g., product ID, customer ID, time ID).
* **Measures**: Numerical data representing business metrics (e.g., sales amount, quantity sold).
* **Degenerate Dimensions**: Attributes stored directly in the fact table (e.g., transaction ID).

### Example: Sales Fact Table

| TransactionID | DateID (FK) | ProductID (FK) | CustomerID (FK) | SalesAmount | QuantitySold | Profit |
| ------------- | ----------- | -------------- | --------------- | ----------- | ------------ | ------ |
| 1001          | 20250101    | 1              | 101             | 1200        | 2            | 200    |
| 1002          | 20250101    | 2              | 102             | 800         | 1            | 150    |
| 1003          | 20250102    | 3              | 103             | 500         | 1            | 100    |

## 5. **Advantages of Fact Tables**

* **Efficient Querying**: Optimized for fast aggregation and analysis of numerical data.
* **Scalability**: Handles large volumes of data efficiently.
* **Flexibility**: Supports complex analytical queries across multiple dimensions.
* **Consistency**: Links to conformed dimensions ensure consistent reporting.
* **Granularity Control**: Allows analysis at different levels of detail.

## 6. **Challenges of Fact Tables**

* **Data Volume**: Can grow very large, requiring efficient storage and indexing.
* **Complexity**: Designing fact tables with the right granularity and measures can be challenging.
* **Performance**: Aggregating large datasets can impact query performance.
* **Data Quality**: Ensuring accurate and consistent data in the fact table is critical.

## 7. **Use Cases of Fact Tables**

* **Sales Analysis**: Analyzing sales revenue, quantity sold, and profit by product, customer, or region.
* **Inventory Management**: Tracking inventory levels and turnover rates.
* **Financial Reporting**: Reporting financial metrics like revenue, expenses, and profit by department or time period.
* **Customer Behavior**: Analyzing customer transactions and purchasing patterns.
* **Operational Metrics**: Monitoring key performance indicators (KPIs) for business processes.

## 8. **Best Practices for Fact Tables**

* **Choose the Right Granularity**: Define the level of detail based on business requirements.
* **Use Surrogate Keys**: Link to dimension tables using surrogate keys for consistency.
* **Optimize for Performance**: Use indexing and partitioning to improve query performance.
* **Handle Sparse Data**: Design the fact table to efficiently store sparse data.
* **Document Measures**: Clearly define the meaning and calculation of each measure.
* **Regular Maintenance**: Update and clean the fact table to ensure data quality.

## 9. **Key Takeaways**

* **Definition**: A fact table stores numerical measures related to a business process, linked to dimension tables for context.
* **Key Features**: Measures, foreign keys, granularity, additive/semi-additive/non-additive measures, sparse data.
* **Types**: Transaction fact tables, snapshot fact tables, accumulating fact tables, factless fact tables.
* **Structure**: Foreign keys, measures, degenerate dimensions.
* **Advantages**: Efficient querying, scalability, flexibility, consistency, granularity control.
* **Challenges**: Data volume, complexity, performance, data quality.
* **Use Cases**: Sales analysis, inventory management, financial reporting, customer behavior, operational metrics.
* **Best Practices**: Choose the right granularity, use surrogate keys, optimize for performance, handle sparse data, document measures, regular maintenance.