90 ) Types of Facts and Dimensions

 

Types of Facts and Dimensions 

(All 13 Kimball Dimensions)

Fact Tables (Definitions)	Dimension Tables (Definitions - All 13 Types)
1. Additive Facts: Numeric metrics that can be aggregated across all dimensions in a query (e.g., sales amount, quantity sold).	1. Conformed Dimension: A dimension table shared identically across multiple fact tables or data marts, ensuring enterprise-wide reporting consistency.
2. Semi-Additive Facts: Metrics that can be aggregated across some dimensions, but not all—typically time (e.g., account balances or inventory levels over time).	2. Role-Playing Dimension: A single physical dimension table referenced multiple times within the same fact table under different operational aliases (e.g., Order Date, Ship Date).
3. Non-Additive Facts: Numeric metrics that cannot be mathematically summed across any dimension (e.g., ratios, percentages, unit prices).	3. Degenerate Dimension: A dimension attribute stored directly inside a fact table because it has no descriptive attributes of its own and acts purely as a transaction identifier (e.g., Invoice Number).
4. Factless Fact Tables: Tables containing no numeric measures, used primarily to capture many-to-many relationships or track events, activities, and occurrences (e.g., student attendance tracking).	4. Junk Dimension: A single structure created by combining a random collection of low-cardinality flags, indicators, or text attributes into a unified table to prevent cluttering the fact table.
Note: Fact table categories are generally categorized into Additive, Semi-Additive, Non-Additive, and Factless.	5. Outrigger Dimension: A secondary dimension table that attaches directly to another primary dimension table rather than linking to the central fact table.
	6. Shrunken Rollup Dimension: A subset dimension table derived from a more detailed base dimension, containing fewer rows and columns to match a higher summary aggregation level.
	7. SCD Type 0 (Retain Original): Dimension attributes that never change; original values are permanently locked and preserved from updates (e.g., Date of Birth).
	8. SCD Type 1 (Overwrite): Old attribute values are directly overwritten with new values, maintaining zero historical tracking (e.g., correcting an email typo).
	9. SCD Type 2 (Add New Row): A new row version is added to the dimension table whenever an attribute changes, utilizing effective start/end dates to track full history.
	10. SCD Type 3 (Add New Attribute): A new column is added to the dimension table to store the current value alongside the previous value, limiting history to a fixed window.
	11. SCD Type 4 (Add Mini-Dimension): Highly volatile attributes are split off from a massive main dimension table into a separate "mini-dimension" to optimize performance.
	12. SCD Type 5 (Mini-Dimension with Type 1 Outrigger): A hybrid approach combining Type 4 mini-dimensions with a Type 1 outrigger linkage for dual reporting perspectives.
	13. SCD Type 6 (Hybrid: Types 2 + 3 + 1): A hybrid method where historical changes are tracked via Type 2 rows, but current values are also embedded as Type 3/Type 1 columns.