In this blog, you will learn about all types of fact tables in data warehousing.
Post Contents
Transaction Fact Table
Periodic snapshot fact table
Accumulating snapshot fact tables
Factless fact tables
Transaction Fact Table:
A transactional fact table records detailed individual business transactions or events.
Characteristics:
- Granular data represents individual events or transactions.
- Timestamps for tracking when events occurred.
- Foreign keys link to dimension tables for context.
Example: FactSales table captures each sales transaction with details like product sold, customer, date, and sales amount.
| TransactionID | DateKey | ProductKey | CustomerKey | SalesAmount |
|---|---|---|---|---|
| 1 | 20240101 | 101 | 201 | 500.00 |
| 2 | 20240102 | 102 | 202 | 750.00 |
| 3 | 20240103 | 103 | 203 | 300.00 |
Periodic snapshot fact table:
A periodic snapshot fact table captures metrics at regular intervals (e.g., daily, weekly, monthly).
Characteristics:
- Contains aggregated data for a specific reporting period.
- Simplifies trend analysis and performance monitoring.
Example: FactDailySales summarizes daily sales metrics like total sales, discounts, and profits.
| Date | Total Sales | Discounts | Profits |
|---|---|---|---|
| 2024-04-01 | 1500 | 200 | 1000 |
| 2024-04-02 | 1800 | 250 | 1100 |
| 2024-04-03 | 1200 | 150 | 800 |
| 2024-04-04 | 2000 | 300 | 1300 |
| 2024-04-05 | 1600 | 180 | 950 |
Accumulating Snapshot Fact Tables:
Accumulating snapshot fact tables track the state or progress of a process over time, capturing key milestones.
Characteristics:
- Contains data related to specific process stages (e.g., order processing).
- Tracks progress with timestamps for each milestone.
Example: FactOrderProcessing tracks order status changes from order creation to fulfillment.
| OrderID | OrderDate | Status | Timestamp | FulfillmentDate |
|---|---|---|---|---|
| 1001 | 2024-04-01 | Created | 2024-04-01 10:00:00 | NULL |
| 1002 | 2024-04-02 | Created | 2024-04-02 11:30:00 | NULL |
| 1001 | 2024-04-02 | Processing | 2024-04-02 10:15:00 | NULL |
| 1003 | 2024-04-03 | Created | 2024-04-03 09:45:00 | NULL |
| 1002 | 2024-04-03 | Processing | 2024-04-03 12:00:00 | NULL |
| 1001 | 2024-04-03 | Shipped | 2024-04-03 11:30:00 | NULL |
| 1002 | 2024-04-04 | Shipped | 2024-04-04 10:45:00 | NULL |
| 1003 | 2024-04-04 | Processing | 2024-04-04 11:20:00 | NULL |
| 1003 | 2024-04-05 | Shipped | 2024-04-05 13:00:00 | 2024-04-05 |
Factless Fact Tables:
Factless fact tables contain only foreign keys and no numeric measures and are used for tracking relationships between dimensions.
Characteristics:
- It helps analyze events or occurrences without quantifiable measures.
- Useful for analyzing relationships and patterns.
Example: FactCustomerActivity tracks customer interactions like website visits without specific measures.
| CustomerID | VisitDate | PageVisited | InteractionType |
|---|---|---|---|
| 101 | 2024-04-01 | Home Page | View |
| 102 | 2024-04-01 | Product Page A | View |
| 103 | 2024-04-01 | Home Page | View |
| 101 | 2024-04-02 | Product Page B | View |
| 104 | 2024-04-02 | Home Page | View |
| 102 | 2024-04-03 | Product Page A | View |
| 101 | 2024-04-03 | Cart Page | Add to Cart |
| 103 | 2024-04-03 | Product Page C | View |
| 102 | 2024-04-03 | Checkout Page | Checkout |
| 101 | 2024-04-04 | Home Page | View |
| 104 | 2024-04-04 | Product Page D | View |
Thank you for reading this post, and I hope you enjoyed it!
