Data modeling · Advanced Modeling

Dimensional modeling vs ER modeling

Mediumdimensional modelingER3NFOLTPOLAP

Interview question

How does dimensional modeling differ from entity-relationship (ER) modeling?

How to answer

The short answer

ER / 3NF modeling focuses on capturing business entities and relationships without redundancy. It is the default for apps: customers, orders, products as separate normalized tables.

Dimensional modeling focuses on making analytical questions easy and fast: facts at a grain, dimensions for slicing. Some controlled redundancy (denormalized dims) is intentional.

Output
ER (app):
  customers -< orders -< order_lines >- products
  many joins for "revenue by category last month"

Dimensional (warehouse):
  fct_order_items surrounded by dim_date, dim_customer, dim_product
  BI-friendly aggregates
ER / 3NFDimensional
GoalWrite integrity, app screensAnalytics & BI
RedundancyAvoidedOften accepted in dims/marts
Central ideaEntities & relationshipsFacts, dimensions, grain
Typical homeOLTP DBWarehouse / lakehouse gold

Interview tip

> "ER for operational systems; dimensional for analytics. Same business, different optimization target."