Fact Table

A Fact Table is the central table in a data warehouse that stores quantitative business data and connects to dimension tables using foreign keys. Each row represents a business event such as a sale, order, or shipment.

• Stores measurable data such as sales amount and Quantity
• Connected to dimension tables through foreign keys
• Supports fast reporting, analysis, and aggregation

Star Schema of Fact and Dimension Tables

The image illustrates a Star Schema used in a Data Warehouse for storing and analyzing sales data. In this schema, the fact table is placed at the center and is connected to multiple dimension tables.

1. Fact Table – factSales

The factSales table is the central table in the star schema that stores sales transaction data and measurable business information.

It contains:

• Foreign Keys (FK): CustomerKey, ProductKey, TerritoryKey, OrderDateKey, ShipDateKey, PaymentDateKey
• Measures: UnitPrice, SalesAmount, UnitsSold
• Records and tracks sales-related transactions for analysis and reporting.

2. Dimension Tables

Dimension tables store descriptive information related to the data in the fact table and provide context for analysis.

• dimCustomer: Stores customer details such as CustomerFname, CustomerLname, CustomerEmail, and CustomerGender to analyze sales based on customers.
• dimProduct: Stores product information like ProductName, ProductDescription, and ProductType to analyze sales by product category or type.
• dimTerritory & dimDate: Store regional and date-related details such as TerritoryName, Country, Day, Month, and Year to analyze sales by location and time period.

3. Relationship in the Schema

The fact table is connected to all dimension tables using foreign keys, while each dimension table contains a primary key (PK). Together, these tables form a Star Schema structure with the fact table at the center.

Main Purpose of the Schema

• Business Intelligence (BI)
• Reporting and Data Analysis
• Sales Performance Tracking
• Better Decision Making

Types of Fact Tables

There are several types of fact tables, each serving different purposes in a data warehouse:

• Transaction Fact Table: Stores details of each individual business transaction or event. It is mainly used for recording sales, payments, and orders.
• Periodic Snapshot Fact Table: Stores summarized data at regular time intervals such as daily or monthly. It is useful for trend analysis and reporting.
• Accumulating Snapshot Fact Table:Tracks the progress of a process from start to completion. The records are updated as the process moves through different stages.
• Factless Fact Table: Stores information about events without numeric measures. It is mainly used for counting events and tracking relationships.

Structure of a Fact Table

A fact table is the central table in a data warehouse that stores measurable business data along with foreign keys connected to dimension tables.

Main Components of a Fact Table

• Foreign Keys (FK): Connect the fact table to dimension tables such as Customer, Product, and Date.
• Measures/Facts: Numeric values used for analysis such as SalesAmount, Quantity, and Profit.
• Granularity: Defines the level of detail stored in the table, such as one row per transaction.
• Surrogate Keys: System-generated keys used for consistency and better performance.
• Degenerate Dimensions: Attributes like InvoiceNumber or OrderNumber stored directly in the fact table.

Example:

Types of Facts

There are three types of facts in a fact table, classified based on whether the stored measures can be aggregated across different dimensions.