Roadmap › ETL & Data Warehousing Concepts › Module: Dimensional Modelling
Lessons in this course (12)
6. Types of facts and dimensions
Intermediate★ Must knowLesson 6 of 12
Fact types:
| Type | Grain | Example |
|---|---|---|
| Transaction | One row per event | Each sale line |
| Periodic snapshot | One row per entity per period | Daily account balance, month-end inventory |
| Accumulating snapshot | One row per process instance, updated as milestones happen | Order: ordered → shipped → delivered dates |
| Factless | Events with no measures | Student attendance, promotion coverage |
Measures: additive (sum across all dimensions: amount), semi-additive (not across time: balance, inventory), non-additive (ratios, percentages — store components instead).
Dimension types: conformed (shared), role-playing (DimDate as OrderDate/ShipDate), junk (low-cardinality flags combined), degenerate (order number stored in the fact), slowly changing (see next lesson), outrigger (dimension referencing another).
Surrogate key — meaningless integer key generated in the warehouse; isolates from source key changes, enables SCD Type 2 history, makes joins faster. Natural/business key — the source system's ID, kept as an attribute.