(Multi)dimensional Model, often referred to as Ralph Kimball approach, is a data modeling technique, primarily consisting of a Fact object and multiple connected Dimensions, also called a "Star Join" or "Star Schema".

Emphasizes a bottom-up approach, which starts with building the data warehouse iteratively through focused segments of data - data marts - tailored and designed for specific business functions, ensuring scalability, user accessibility and performance for reporting and analysis. And only afterwards integrating into a broader data warehouse, hence called bottom-up.

This iterative or incremental way of building data warehouse/business intelligence system is also called Enterprise Data Warehouse Bus Architecture.

Integration is delivered via standardized conformed dimensions (different data marts can share dimensions) which are reused across processes.

Example

erDiagram
    "ACCOUNT dimension" }|--o{ "TRANSACTION fact" : ""
    "CHANNEL dimension" ||--o{ "TRANSACTION fact" : ""
    "CUSTOMER dimension" }|--o{ "TRANSACTION fact" : ""
    "PAYMENT METHOD dimension" ||--o{ "TRANSACTION fact" : ""
    "DATE dimension" ||--o{ "TRANSACTION fact" : ""

Pros

• Immediate business value, simplest approach to understand
• Best fit for analysis and reporting
• Faster implementation
• Flexibility

Cons

• Dimensional models can be very hard to populate due to requirement to ensure consistency with dimensions
• Complex dimension logic, especially with Slowly Changing Dimension Type 2 and up
• No single source of truth

Concepts

• Fact objects store measurable, quantitive data related to business process
  • Contain Foreign Keys referencing Dimension objects
  • Include numeric metrics
  • Often contain additive, semi-additive (only in some contexts), or non-additive measures (e.g.: averages)
  • Grain (level of details) must be aligned with business need
  • Types:
    • Transactional Facts - record individual business events as they happen
    • Periodic snapshot Facts - capture data at regular intervals
    • Accumulating snapshot Facts - track the progression of a process
• Dimension objects surrounds Fact objects and provide descriptive attributes about business entities
  • Contain textual or categorical information
  • Related to Fact data
  • Often denormalized to optimize query performance
  • Support hierarchical relationships within a single object, instead of being split into multiple related objects
  • Used to filter, group, and aggregate Facts
  • Types:
    • Conformed Dimensions - shared across multiple Fact objects and data marts, ensuring consistency
    • Role-playing Dimensions - used multiple times within the same schema (e.g.: date Dimension being used as order date and ship date)
    • Junk Dimensions - combine unrelated low-cardinality attributes into a single Dimension to reduce clutter in Fact tables

Schema designs

• Star - where Fact object is at the center, connected to multiple Dimensions
• Snowflake - extends Star schema by normalizing Dimension objects into multiple related objects