Foundation of business intelligence and analytics
Dimensional Modeling is a data modeling technique optimized for data retrieval in data warehouses and business intelligence systems. Pioneered by Ralph Kimball, it structures data into fact tables (containing measurable events) and dimension tables (containing descriptive context), making complex business data intuitive and performant for analysis.
Unlike normalized operational databases designed for transactional efficiency, dimensional models prioritize query performance and user understanding. They organize data around business processes, making it natural for business users to explore and analyze data without deep technical knowledge.
Core Principle:
Structure data the way business people think about it - around measurable events (facts) with rich descriptive context (dimensions) that answer who, what, when, where, why, and how questions.
Measurable business events
Fact tables store quantitative data about business transactions or events. They contain foreign keys to dimension tables and numeric measures that can be aggregated.
Characteristics:
Descriptive context
Dimension tables provide descriptive attributes that give context to facts. They answer the "who, what, where, when, why" questions about business events.
Characteristics:
One row per business transaction or event. Most detailed level of granularity.
Example: Sales Transactions
Each individual sale creates one row with date, product, customer, store, and measures.
Captures state at regular intervals. One row per period per entity.
Example: Account Balances
Daily snapshot of every customer account balance, even if no transactions occurred.
Tracks process with definite beginning and end. Row updated as process progresses.
Example: Order Fulfillment
One row per order with dates for ordered, shipped, delivered stages.
Slowly Changing Dimensions (SCD) are techniques for tracking changes to dimension attributes over time. Different types balance historical accuracy against complexity and storage.
Simply overwrite old value with new value. No history preserved.
Before:
After:
Use when: History doesn't matter (corrections, no business value in tracking changes)
Create new row with new surrogate key. Track effective dates and current flag.
Use when: Need complete history (most common approach for important attributes)
Keep current and previous value in separate columns. Limited history.
Use when: Only need to track one previous value (rare in practice)
The grain is the most atomic level of detail captured in a fact table. Clearly define what each row represents before designing tables.
Good Grain Statements:
Mixing grains in a single fact table leads to confusion and incorrect aggregations.
Use system-generated integers as primary keys in dimension tables rather than natural business keys. Provides flexibility for slowly changing dimensions and insulates from source system changes.
Surrogate keys are smaller, faster to join, and support Type 2 SCDs naturally.
Include all hierarchical levels in a single dimension table even if it creates redundancy. Trading storage for query simplicity and performance.
Product Dimension includes:
Users can easily filter and group by any level without joins.
Share identical dimensions across multiple fact tables. Enables drill-across queries and consistent reporting across business processes.
Example:
Same Dim_Date used by Fact_Sales, Fact_Inventory, and Fact_Shipments ensures consistent time analysis across all areas.
Critical for enterprise-wide consistency and integrated reporting.