In dimensional modeling, fact tables and dimension tables are the core components used to organize data for efficient querying and reporting. Here's a detailed explanation of each:
Fact Table
- Purpose: The fact table stores quantitative data (measures) related to business processes. It contains the metrics that are analyzed and reported on.
- Content: Fact tables typically include numerical values such as sales amounts, quantities, and transaction counts. They also contain foreign keys that link to dimension tables.
- Granularity: The level of detail in a fact table is determined by its granularity. For example, a sales fact table might have a granularity of individual sales transactions or daily sales totals.
- Examples: Sales, orders, revenue, inventory levels.
Example Structure:
| Date Key | Product Key | Customer Key | Store Key | Sales Amount | Quantity Sold |
|---|---|---|---|---|---|
| 20250101 | 1001 | 2001 | 3001 | 500.00 | 5 |
Dimension Table
- Purpose: Dimension tables store descriptive attributes (dimensions) related to the facts. They provide context to the measures in the fact table.
- Content: Dimension tables include textual or categorical data such as product names, customer names, dates, and locations.
- Attributes: Each dimension table contains attributes that describe the dimension. For example, a product dimension table might include product ID, name, category, and brand.
- Examples: Time, product, customer, store, region.
Example Structure: Product Dimension Table:
| Product Key | Product Name | Category | Brand |
|---|---|---|---|
| 1001 | Widget A | Gadgets | BrandX |
| 1002 | Widget B | Gadgets | BrandY |
Customer Dimension Table:
| Customer Key | Customer Name | Region |
|---|---|---|
| 2001 | John Doe | East |
| 2002 | Jane Smith | West |
Summary
- Fact Table: Contains quantitative data (measures) and foreign keys to dimension tables. It represents the core metrics of the business process.
- Dimension Table: Contains descriptive attributes (dimensions) that provide context to the measures in the fact table. It helps in categorizing and filtering the data.
