modeling-dimensional-datalisted

# Modeling Dimensional Data ## When to use - Designing warehouse/mart tables for analytics or BI. - Deciding a table's grain, or whether something is a fact or a dimension. - Tracking attribute history over time (customer moved, product re-priced). - Do NOT use for OLTP/application schema design (normalize instead). ## Workflow ``` - [ ] Pick the business process to model - [ ] Declare the grain (one row = ...) - [ ] Identify the dimensions (context: who/what/where/when) - [ ] Identify the facts (numeric measures at that grain) - [ ] Choose SCD behavior per dimension attribute - [ ] Add surrogate keys and relationships ``` 1. **Choose the process** (orders, sessions, payments) — one star per process. 2. **Declare the grain first** and write it down: "one row per order line." Every fact column must be true at that grain. Never mix grains in one fact table. 3. **Dimensions** carry descriptive context and are the columns users filter/group by. **Facts** are additive numeric measures. 4. **Pick SCD type per attribute** (see below) based on whether history matters. 5. **Use surrogate keys** (warehouse-generated) as primary/foreign keys; keep the source natural key as a separate column. ## Patterns **Star schema** — one central fact table with foreign keys to denormalized dimensions. Prefer this default: fewer joins, faster BI, easier to understand. **Snowflake schema** normalizes dimensions into sub-tables; use only when a dimension is huge and shared, accepting mo