11.2 Fact Tables and Dimension Tables
| Fact table | Dimension table | |
|---|---|---|
| Contains | Events/transactions and numeric values (measures) | Descriptive attributes used to filter and group |
| Examples | Orders, Inventory snapshots, App sessions, Deliveries | Product, Customer, Date, DarkStore, DeliveryPartner |
| Rows | Many (thousands to billions) | Relatively few |
| Columns | Keys (foreign keys) + numbers | A unique key + descriptive text columns |
| Typical question | "How much?" "How many?" | "By what?" "Who?" "When?" "Where?" |
| In our sample | Orders |
Product, Customer, DarkStore, DeliveryPartner, Date |
Keys: a dimension has a primary key (प्रत्येक ओळ ओळखणारा अद्वितीय स्तंभ) – a column with unique values (e.g. Product[Product ID]). The fact table holds that value as a foreign key (दुसऱ्या तक्त्याशी जोडणारा स्तंभ) (Orders[Product ID]). Some models use a surrogate key – an artificial integer key created in the data warehouse.
Ravindra Bagale's Tip
Ek common chuk mhanje putting descriptive text (Product Name, City) in the fact table and numbers in dimensions. Facts hold events and numbers (Orders: Amount, Quantity); dimensions hold descriptions (Product, DarkStore, Customer, Date). Ask "is this something I count or sum, or something I filter by?". Dhyan rakho!