A dimensional model organizes business data so it can be analyzed efficiently. Two of its most important components are fact tables and dimension tables.
Fact tables
A fact table stores measurable business events. In a sales model, each row might represent one product sold during a transaction.
SELECT
sale_date_key,
product_key,
customer_key,
quantity,
sales_amount
FROM fact_sales;
Dimension tables
Dimension tables provide descriptive context for the facts. A product dimension might include the product name, category, brand, and supplier.
How the tables work together
The fact table contains keys that connect each business event to its related dimensions. This structure makes it easier to analyze measures from different perspectives.
- The fact table answers what happened.
- The date dimension answers when it happened.
- The product dimension identifies what was sold.
- The customer dimension describes who purchased it.
This approach is commonly associated with Ralph Kimball's dimensional modeling methodology.