Tutorials › Power BI › Star Schema Design

Star Schema Design

Design an efficient data model using star schema — the industry standard for Power BI

The star schema is the recommended data model design for Power BI. It is called a "star" because when drawn in Model View, it looks like a star: a central fact table with dimension tables radiating outward. Why does this matter? Because Power BI's VertiPaq engine is optimised for star schema. Reports built on a star schema are faster, DAX formulas are simpler, and you get fewer incorrect calculation results compared to flat tables or snowflake schemas. Most data analyst interviews in Delhi NCR companies will ask about star schema — knowing it demonstrates you understand data modelling beyond basic report-building.
Star Schema — Industry Standard for Power BI
📅
DIM_Date
DateKey · Month · Quarter · Year
👤
DIM_Customer
CustID · Name · Region
⭐ FACT_Sales
OrderID
DateKey
CustID
ProductID
RegionID
Amount
Quantity
📦
DIM_Product
ProductID · Name · Category
🗺️
DIM_Region
RegionID · City · State · Zone
⭐ Fact table— Transactions — numeric + FK keys
🔷 Dimension tables— Descriptive attributes

Example

A complete star schema for a sales report

          ┌─────────────┐
          │  DIM_Date   │
          │  DateKey    │
          │  Date       │
          │  Month      │
          │  Quarter    │
          │  Year       │
          └──────┬──────┘
                 │
┌──────────┐    │    ┌──────────────┐
│DIM_Cust  │    │    │ DIM_Product  │
│CustID    │    │    │ ProductID    │
│Name      │    │    │ ProductName  │
│Region    ├────┤    │ Category     │
│Segment   │    │    │ Price        │
└──────────┘    │    └──────────────┘
                │
         ┌──────┴───────┐
         │  FACT_Sales  │  ← CENTRE (fact table)
         │  OrderID     │
         │  DateKey     │
         │  CustID      │
         │  ProductID   │
         │  Quantity    │
         │  Amount      │
         └──────────────┘

Rules:
• Fact table has FK columns + numeric measures only
• Dimension tables have descriptive attributes
• All relationships flow FROM dimensions TO fact
💡 The fact table only stores numbers and foreign keys. All descriptive text lives in dimension tables.

Key Points

  • ✓Fact table = transactions (sales, orders, calls, events) with numeric columns and foreign keys
  • ✓Dimension table = descriptive attributes (customer name, product category, region, date attributes)
  • ✓Every dimension connects to the fact table — not to each other (in a pure star schema)
  • ✓A date dimension table is non-negotiable — every good Power BI model has one
  • ✓Snowflake schema (dimensions linked to dimensions) is harder in Power BI — flatten it when possible

Practice Question

In a star schema, which table should contain columns like Product Name, Category, and Price?