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
A common mistake is 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?". Keep this in mind!
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे वर्णन करणारा text (Product Name, City) fact table मध्ये आणि numbers dimensions मध्ये ठेवणं. Facts मध्ये events आणि numbers असतात (Orders: Amount, Quantity); dimensions मध्ये वर्णन असतं (Product, DarkStore, Customer, Date). स्वतःला विचारा, "हे मी मोजतो किंवा बेरीज करतो, की याने filter करतो?". लक्षात ठेवा!
Ravindra Bagale's Tip – हिंदी
एक common गलती है विवरण वाला text (Product Name, City) fact table में और numbers dimensions में रखना. Facts में events और numbers होते हैं (Orders: Amount, Quantity); dimensions में विवरण होता है (Product, DarkStore, Customer, Date). ख़ुद से पूछो, "क्या इसे मैं गिनता या जोड़ता हूँ, या इससे filter करता हूँ?". ध्यान रखना!