Data Modeling for Power BI: Star Schema, Fact and Dimension Tables, Surrogate Keys, Relationships, Cardinality, Cross-Filter Direction, Role-Playing Dimensions, Bridge Tables, and Building Models Data Engineers Are Proud Of

Table of Contents

• Why Data Modeling is the Data Engineer’s Job
• Star Schema — The Foundation of Every Power BI Model
• Fact Tables — The Measurements
• Dimension Tables — The Context
• Surrogate Keys vs Natural Keys
• Relationships — How Tables Connect
• Cardinality — One-to-Many, Many-to-Many, One-to-One
• Cross-Filter Direction — How Filters Flow
• Active vs Inactive Relationships
• Role-Playing Dimensions — One Table, Multiple Roles
• Bridge Tables — Resolving Many-to-Many
• Conformed Dimensions — Shared Across Fact Tables
• Snowflake vs Star Schema in Power BI
• Model Optimization for Performance
• Building the Model — A Complete Example
• Common Mistakes
• Interview Questions
• Wrapping Up

In the previous post, we covered DAX — the formula language that powers Power BI calculations. But DAX is only as good as the model it runs on. A clean star schema means simple, fast DAX measures. A messy model means complex, slow DAX that analysts struggle to write. Data modeling is where data engineers have the most impact on Power BI — even more than choosing the right storage mode or writing DAX.

Analogy — The foundation of a house. The data model is the foundation and framing of a house. DAX measures are the furniture. Visualizations are the paint and decor. You can have beautiful furniture (complex DAX) and stunning paint (fancy visuals), but if the foundation is crooked (bad data model), the doors do not close (wrong numbers), the floors creak (slow performance), and the house eventually needs a complete rebuild. Data engineers are the structural engineers — you build the foundation that everything else sits on.

Why Data Modeling is the Data Engineer’s Job

Who builds what:

  Data Engineer:
    - Designs the star schema (fact and dimension tables)
    - Builds gold layer tables in the Lakehouse/Warehouse
    - Defines relationships and cardinality
    - Creates surrogate keys
    - Handles role-playing dimensions and bridge tables
    - Optimizes for VertiPaq compression and Direct Lake

  BI Developer:
    - Connects to the semantic model
    - Writes DAX measures
    - Builds report visuals
    - May add calculated columns for display purposes

  The quality of the model determines the quality of everything above it.
  A well-designed star schema → simple DAX → fast reports → happy users.
  A poorly designed model → complex DAX → slow reports → endless troubleshooting.

Star Schema — The Foundation of Every Power BI Model

A star schema organizes data into two types of tables: fact tables (the measurements) surrounded by dimension tables (the context). When drawn as a diagram, the fact table is in the center with dimension tables radiating outward like points of a star.

Analogy — A sentence. Every business question is a sentence: “How much revenue (fact) did we generate in Ontario (dimension) for Electronics (dimension) in Q2 2026 (dimension)?” The fact table holds the verbs (revenue, quantity, cost). The dimension tables hold the nouns (who, what, where, when). The star schema is the grammar that connects them.

Star schema structure:

                    DimDate
                      |
                      | (DateKey)
                      |
  DimCustomer --- FactSales --- DimProduct
       |              |              |
  (CustomerKey)  (DateKey,      (ProductKey)
                  CustomerKey,
                  ProductKey,
                  Amount,
                  Quantity,
                  Cost)
                      |
                      |
                  DimRegion
                      |
                 (RegionKey)

  FactSales: one row per transaction (millions of rows)
  DimDate: one row per calendar day (~3,650 rows for 10 years)
  DimCustomer: one row per customer (~50,000 rows)
  DimProduct: one row per product (~5,000 rows)
  DimRegion: one row per region (~50 rows)

Fact Tables — The Measurements

Fact tables store the quantitative data — the numbers that analysts aggregate (sum, count, average). Each row represents an event or transaction.

Fact table characteristics:

  - Contains FOREIGN KEYS to dimension tables (DateKey, CustomerKey, ProductKey)
  - Contains MEASURES (Amount, Quantity, Cost, Discount)
  - Typically the LARGEST table in the model (millions to billions of rows)
  - Rows represent events: orders, transactions, page views, pipeline runs
  - Grain: the level of detail each row represents (one row per order line item)

Three types of fact tables:

  Transaction facts (most common):
    One row per event (one order, one click, one pipeline run)
    Example: FactSales with OrderID, DateKey, CustomerKey, Amount

  Periodic snapshot facts:
    One row per time period (daily inventory, monthly account balance)
    Example: FactDailyInventory with DateKey, ProductKey, UnitsOnHand

  Accumulating snapshot facts:
    One row per lifecycle (order lifecycle from placed to shipped to delivered)
    Example: FactOrderLifecycle with OrderPlacedDate, ShippedDate, DeliveredDate

Example fact table:

  FactSales:
    SalesKey (surrogate key -- identity/auto-increment)
    DateKey (FK → DimDate)
    CustomerKey (FK → DimCustomer)
    ProductKey (FK → DimProduct)
    RegionKey (FK → DimRegion)
    OrderID (natural key from source system)
    Quantity (measure)
    UnitPrice (measure)
    DiscountAmount (measure)
    TotalAmount (measure: Quantity * UnitPrice - Discount)

Dimension Tables — The Context

Dimension tables provide the descriptive context for fact table measures — the who, what, where, when, and how.

Dimension table characteristics:

  - Contains a PRIMARY KEY (surrogate key: DateKey, CustomerKey)
  - Contains DESCRIPTIVE ATTRIBUTES (CustomerName, Region, Category)
  - Typically SMALL relative to fact tables (thousands to low millions of rows)
  - Should be DENORMALIZED (flatten hierarchies into one table)
  - One row per entity (one customer, one product, one date)

Common dimension tables:

  DimDate (required for time intelligence):
    DateKey, Date, Year, Quarter, MonthName, MonthNumber, DayOfWeek,
    IsWeekday, IsHoliday, FiscalYear, FiscalQuarter

  DimCustomer:
    CustomerKey, CustomerID, CustomerName, Email, City, Province,
    Country, Segment, AccountCreatedDate

  DimProduct:
    ProductKey, ProductID, ProductName, Category, SubCategory,
    Brand, UnitCost, ListPrice, IsActive

  DimRegion (or DimGeography):
    RegionKey, RegionName, Country, Province, City, PostalCode

  DimEmployee:
    EmployeeKey, EmployeeID, FullName, Department, Title, ManagerKey

The denormalization rule:
  In OLTP: Customer → City → Province → Country (normalized, 4 tables)
  In Star Schema: Customer has City, Province, Country columns (denormalized, 1 table)
  Power BI's VertiPaq engine compresses denormalized dimensions efficiently.
  Do NOT snowflake in Power BI unless you have a specific reason.

Surrogate Keys vs Natural Keys

Natural key:
  The business identifier from the source system
  Examples: CustomerID = "CUST-001", OrderID = "ORD-2026-0719-001"
  Problems: can change (customer number reassigned), can have gaps,
  composite keys are slow, different systems use different formats

Surrogate key:
  An auto-generated integer key with no business meaning
  Examples: CustomerKey = 1, 2, 3, 4, ...
  Benefits: compact (integer joins are fast), stable (never changes),
  uniform (always integer, always sequential)

Best practice for Power BI:
  - Use INTEGER surrogate keys for all relationships
  - Keep natural keys as descriptive columns (for display and lookup)
  - Generate surrogate keys in your data pipeline (Databricks, ADF)
  - The VertiPaq engine compresses integers better than strings

Example:
  DimCustomer:
    CustomerKey = 42 (surrogate, used in relationships)
    CustomerID = "CUST-001" (natural, kept for display/reference)
    CustomerName = "Acme Corp" (descriptive attribute)

Relationships — How Tables Connect

Relationships in Power BI define how filter context propagates between tables. When a user selects “Ontario” in a slicer on DimRegion, that filter flows through the relationship to FactSales, filtering the fact table to Ontario rows only.

Creating relationships:

  Power BI Desktop → Model View
  Drag CustomerKey from DimCustomer to CustomerKey in FactSales
  → Creates a one-to-many relationship
  → DimCustomer (one side) filters FactSales (many side)

  Or: Modeling tab → Manage Relationships → New

Relationship properties:
  - Which tables and columns are related
  - Cardinality (one-to-many, many-to-many, one-to-one)
  - Cross-filter direction (single or both)
  - Active or inactive

The golden rule:
  Filters flow FROM the dimension (one side) TO the fact (many side).
  DimDate filters FactSales (what dates to include).
  FactSales does NOT filter DimDate (the calendar exists regardless of sales).

Cardinality — One-to-Many, Many-to-Many, One-to-One

One-to-Many (1:M) -- THE DEFAULT for star schema:
  DimCustomer (one) → FactSales (many)
  Each customer has many sales. Each sale has one customer.
  90%+ of relationships in a well-designed model are 1:M.
  BEST performance. Simplest filter propagation.

Many-to-Many (M:M) -- USE SPARINGLY:
  A customer belongs to multiple segments.
  A segment contains multiple customers.
  Requires a bridge table (see Bridge Tables section).
  Power BI supports native M:M but it is slower and can produce ambiguous results.
  Always prefer bridge table + two 1:M relationships.

One-to-One (1:1) -- RARE:
  DimCustomer (one) → DimCustomerDetails (one)
  Usually means the tables should be merged into one.
  Occasionally useful for: isolating sensitive columns (PII), very wide tables.
  If you have a 1:1, ask: "Should these just be one table?"

Rule of thumb:
  1:M = correct (star schema)
  M:M = needs a bridge table (or model redesign)
  1:1 = probably should be merged

Cross-Filter Direction — How Filters Flow

Single direction (DEFAULT -- use this):
  Filter flows ONE way: dimension → fact
  DimDate filters FactSales but FactSales does NOT filter DimDate
  This is the standard star schema behavior
  Predictable, performant, easy to understand

Both directions (BIDIRECTIONAL -- use cautiously):
  Filter flows BOTH ways: dimension ↔ fact
  FactSales filters DimCustomer AND DimCustomer filters FactSales
  Use case: showing only customers who have orders (hide inactive customers)
  Risk: can create circular dependencies, unpredictable filter behavior
  Performance cost: VertiPaq must materialize intermediate results

When to use bidirectional:
  - Bridge tables in M:M relationships (enable bidi on the bridge side)
  - Showing "only items with data" (e.g., only products that sold)
  - NEVER enable bidirectional "just in case" -- it causes subtle bugs

Best practice:
  Start with ALL relationships as single direction.
  Only enable bidirectional when you have a specific, documented reason.
  If a DAX measure produces unexpected results, check for bidirectional filters.

Active vs Inactive Relationships

Power BI allows only one active relationship between any two tables. Additional relationships must be inactive and activated explicitly using USERELATIONSHIP in DAX.

Why you need inactive relationships:

  FactSales has THREE date columns:
    OrderDate (when the order was placed)
    ShipDate (when the order was shipped)
    DeliveryDate (when the order was delivered)

  You want to analyze sales by ALL three dates using ONE DimDate table.
  But Power BI only allows ONE active relationship between FactSales and DimDate.

Solution -- one active + two inactive:

  DimDate ──(active)──── FactSales[OrderDate]     ← default, used automatically
  DimDate ──(inactive)── FactSales[ShipDate]      ← must use USERELATIONSHIP
  DimDate ──(inactive)── FactSales[DeliveryDate]  ← must use USERELATIONSHIP

DAX with USERELATIONSHIP:

  Revenue by Order Date = SUM(FactSales[Amount])
  -- Uses the active relationship automatically (OrderDate)

  Revenue by Ship Date = CALCULATE(
    SUM(FactSales[Amount]),
    USERELATIONSHIP(FactSales[ShipDate], DimDate[DateKey])
  )
  -- Activates the ShipDate relationship for this measure only

  Revenue by Delivery Date = CALCULATE(
    SUM(FactSales[Amount]),
    USERELATIONSHIP(FactSales[DeliveryDate], DimDate[DateKey])
  )

Alternative approach -- duplicate the Date table:
  Create DimOrderDate, DimShipDate, DimDeliveryDate (3 copies of DimDate)
  Each has an active relationship to its respective column
  Simpler DAX but larger model (3x the Date table size)
  Use inactive relationships approach for most models (cleaner)

Role-Playing Dimensions — One Table, Multiple Roles

A role-playing dimension is a dimension table used in multiple roles — like DimDate playing the role of OrderDate, ShipDate, and DeliveryDate simultaneously. The active/inactive relationship pattern above is how Power BI handles role-playing dimensions.

Common role-playing dimensions:

  DimDate:
    OrderDate, ShipDate, DeliveryDate, InvoiceDate, PaymentDate

  DimEmployee:
    SalesRep, Manager, ApprovedBy

  DimLocation:
    ShipFrom, ShipTo, BillTo

  DimAccount:
    DebitAccount, CreditAccount

Bridge Tables — Resolving Many-to-Many

When two dimensions have a many-to-many relationship, use a bridge table to resolve it into two one-to-many relationships.

Problem: A customer can belong to multiple segments.
  Customer "Acme" → Segment "Enterprise" AND Segment "Healthcare"
  Customer "Beta" → Segment "SMB" AND Segment "Healthcare"

  DimCustomer (M) ←→ DimSegment (M)  -- M:M is problematic

Solution: Bridge table
  DimCustomer (1) → BridgeCustomerSegment (M) ← DimSegment (1)

  BridgeCustomerSegment:
    CustomerKey | SegmentKey
    42          | 1  (Acme → Enterprise)
    42          | 3  (Acme → Healthcare)
    43          | 2  (Beta → SMB)
    43          | 3  (Beta → Healthcare)

  Relationships:
    DimCustomer (1:M) → BridgeCustomerSegment
    DimSegment (1:M) → BridgeCustomerSegment
    Enable bidirectional on one side of the bridge

  Now filtering by Segment "Healthcare" flows through the bridge
  to find Customers 42 and 43, then flows to FactSales.

Conformed Dimensions — Shared Across Fact Tables

When your model has multiple fact tables (FactSales and FactInventory), they should share the same dimension tables. This is called a conformed dimension.

Constellation schema (multiple fact tables, shared dimensions):

                DimDate
               /       \
              /         \
  FactSales ---- DimProduct ---- FactInventory
              \         /
               \       /
              DimRegion

  DimDate is shared by both FactSales and FactInventory
  DimProduct is shared by both
  DimRegion is shared by both

  This ensures that filtering by "2026" in DimDate filters
  BOTH FactSales and FactInventory consistently.

  The dimension tables are "conformed" -- same keys, same attributes,
  same grain across all fact tables that reference them.

Snowflake vs Star Schema in Power BI

Snowflake schema (normalized dimensions):
  FactSales → DimProduct → DimSubCategory → DimCategory
  Three tables where one would suffice

Star schema (denormalized dimensions):
  FactSales → DimProduct (includes SubCategory and Category columns)
  One table with all attributes

For Power BI: ALWAYS use star schema
  - VertiPaq compresses denormalized columns extremely efficiently
  - Space savings from snowflaking are negligible
  - Each additional relationship adds query overhead
  - DAX is simpler with fewer tables to navigate
  - Direct Lake performs better with fewer tables

The only exception:
  Very large dimension tables (10M+ rows) with high-cardinality columns
  that are rarely used. Isolating those columns into a separate table
  can reduce model size. But this is rare.

Model Optimization for Performance

Optimization checklist:

  1. REMOVE unused columns
     Every column stored in VertiPaq consumes memory
     Remove columns that no report uses
     Especially: GUIDs, system columns, audit timestamps not needed in reports

  2. REDUCE cardinality
     High-cardinality columns (unique values) compress poorly
     Example: timestamps with seconds → round to minutes or hours
     Example: full address → split into City and Province

  3. USE integer keys for relationships
     Integer joins are 3-5x faster than string joins
     Replace CustomerID = "CUST-001" with CustomerKey = 42

  4. AVOID bidirectional filtering
     Each bidirectional relationship adds query complexity
     Use single direction unless specifically needed

  5. LIMIT calculated columns
     Each calculated column is stored in memory
     Convert to measures where possible

  6. DISABLE Auto date/time
     File → Options → Data Load → uncheck "Auto date/time"
     Power BI creates hidden date tables for every date column
     With a proper DimDate, these are redundant and waste memory

  7. SET data types correctly
     Use Integer instead of Decimal when possible
     Use Date instead of DateTime when time is not needed
     Smaller data types compress better

Building the Model — A Complete Example

Scenario: E-commerce analytics for a retail company

  Source tables (from gold layer in Lakehouse):
    gold.fact_sales (10M rows)
    gold.dim_date (3,652 rows)
    gold.dim_customer (50,000 rows)
    gold.dim_product (5,000 rows)
    gold.dim_region (50 rows)
    gold.dim_promotion (200 rows)
    gold.bridge_customer_segment (75,000 rows)
    gold.dim_segment (10 rows)

  Relationships:
    DimDate (DateKey) → FactSales (OrderDateKey)        [1:M, single, ACTIVE]
    DimDate (DateKey) → FactSales (ShipDateKey)          [1:M, single, INACTIVE]
    DimCustomer (CustomerKey) → FactSales (CustomerKey)  [1:M, single, ACTIVE]
    DimProduct (ProductKey) → FactSales (ProductKey)     [1:M, single, ACTIVE]
    DimRegion (RegionKey) → FactSales (RegionKey)        [1:M, single, ACTIVE]
    DimPromotion (PromotionKey) → FactSales (PromotionKey) [1:M, single, ACTIVE]
    DimCustomer (CustomerKey) → BridgeCustomerSegment (CustomerKey) [1:M, single]
    DimSegment (SegmentKey) → BridgeCustomerSegment (SegmentKey) [1:M, bidi]

  Key measures:
    Total Revenue = SUM(FactSales[TotalAmount])
    Order Count = COUNTROWS(FactSales)
    Avg Order Value = DIVIDE([Total Revenue], [Order Count])
    Revenue YTD = TOTALYTD([Total Revenue], DimDate[Date])
    Revenue by Ship Date = CALCULATE([Total Revenue],
      USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey]))

  This model is clean, performant, and supports all common analytics patterns.

Common Mistakes

1. Bringing the OLTP schema directly into Power BI. Source databases are normalized for write performance (3NF). Power BI needs denormalized star schemas for read performance. Do not connect Power BI directly to your transactional database — build a transformation layer (Databricks gold layer, Fabric Lakehouse) that produces star schema tables.

2. Using string keys for relationships. Joining FactSales to DimCustomer on CustomerID (string “CUST-001”) instead of CustomerKey (integer 42) makes joins 3-5x slower and compresses poorly. Always use integer surrogate keys for relationships. Keep the natural key as a descriptive column.

3. Enabling bidirectional filtering on all relationships. Bidirectional filters cause circular dependencies, unpredictable results, and performance degradation. Start with all single-direction relationships. Only enable bidirectional on bridge tables where specifically needed, and document why.

4. Not creating a proper Date dimension. Using the date column directly from the fact table means no hierarchy (Year → Quarter → Month), no fiscal year support, and broken time intelligence. Build a dedicated DimDate table with all calendar attributes and mark it as a Date table.

5. Snowflaking dimensions. Creating DimProduct → DimSubCategory → DimCategory adds two unnecessary joins. Flatten into one DimProduct table with SubCategory and Category columns. VertiPaq compresses the repeated values efficiently. Each extra table adds query overhead.

6. Having multiple fact tables at different grains without understanding the impact. A model with FactDailySales and FactMonthlySales sharing the same DimDate causes confusion — which table should a monthly visual query? Keep fact tables at the same grain where possible, or create separate page-level models with clear documentation.

7. Leaving Auto date/time enabled. Power BI creates a hidden date hierarchy for every date column. With 5 date columns, that is 5 hidden tables consuming memory and confusing DAX. Disable Auto date/time in Options and use your explicit DimDate table instead.

8. Not removing unused columns. A fact table with 50 columns where reports use only 10 wastes memory and slows refresh. Remove columns that no report, measure, or relationship needs. Every column stored in VertiPaq has a cost.

Interview Questions

Q: What is a star schema and why is it important for Power BI? A: A star schema organizes data into fact tables (quantitative measures like revenue, quantity) surrounded by dimension tables (descriptive context like date, customer, product). Fact tables have foreign keys connecting to dimension primary keys, creating one-to-many relationships. Star schemas are important for Power BI because the VertiPaq engine is optimized for this pattern — single-direction filter propagation, efficient compression, and simple DAX. Connecting Power BI directly to a normalized OLTP source without transformation produces slow reports and complex DAX.

Q: What is the difference between a fact table and a dimension table? A: Fact tables contain quantitative, measurable data (revenue, quantity, cost) and foreign keys to dimensions. They are typically large (millions of rows) with each row representing an event or transaction. Dimension tables contain descriptive attributes (customer name, product category, date hierarchy) and a primary key. They are typically small (thousands of rows) with each row representing an entity. Facts are what you measure. Dimensions are how you slice and filter the measurements.

Q: What is a role-playing dimension and how do you handle it in Power BI? A: A role-playing dimension is one dimension table used in multiple roles — like DimDate serving as OrderDate, ShipDate, and DeliveryDate. Power BI allows only one active relationship between any two tables. Create one active relationship (usually OrderDate) and inactive relationships for the others. Use USERELATIONSHIP in DAX measures to activate the inactive relationship for specific calculations. Alternatively, create separate copies of the date table (DimOrderDate, DimShipDate), each with an active relationship.

Q: When would you use a bridge table? A: Use a bridge table when two dimensions have a many-to-many relationship — for example, a customer belonging to multiple segments. The bridge table contains one row per combination (CustomerKey, SegmentKey) and sits between the two dimensions. Create two one-to-many relationships from each dimension to the bridge table, with bidirectional filtering enabled on one side. This resolves the M:M into clean 1:M relationships that Power BI handles efficiently.

Q: Why should you avoid snowflake schemas in Power BI? A: Snowflake schemas add additional tables and joins to the model (DimProduct → DimSubCategory → DimCategory). In Power BI, this adds query overhead because each relationship requires filter propagation. VertiPaq compresses denormalized columns efficiently, so the space savings from snowflaking are negligible. Always flatten dimensions into a single table (star schema) with all attributes as columns. The only rare exception is very large dimension tables with seldom-used high-cardinality columns.

Q: What is cross-filter direction and when should you use bidirectional? A: Cross-filter direction controls how filters propagate through a relationship. Single direction (default) means filters flow from the one side (dimension) to the many side (fact). Bidirectional means filters flow both ways. Use single direction for standard star schema relationships (90%+ of your model). Use bidirectional only for bridge tables in many-to-many patterns or when you specifically need the fact table to filter a dimension (like showing only customers with orders). Overusing bidirectional causes circular dependencies and performance issues.

Q: How does a data engineer’s data model affect Power BI report performance? A: The data model is the primary performance lever. Integer surrogate keys make joins 3-5x faster than string keys. Removing unused columns reduces memory and refresh time. Denormalized dimensions (star schema) reduce join count. Proper data types (Integer vs Decimal, Date vs DateTime) improve compression. Disabling Auto date/time eliminates hidden tables. Single-direction relationships avoid unnecessary filter calculations. A well-optimized model can make the difference between a 2-second and a 20-second report load.

Wrapping Up

Data modeling is the data engineer’s most impactful contribution to Power BI. A clean star schema with integer surrogate keys, denormalized dimensions, proper relationships, and a dedicated Date table creates the foundation for simple DAX, fast reports, and happy analysts. Every hour spent on model design saves ten hours of DAX troubleshooting and report optimization.

In the next post, we will cover the differences between legacy Power BI and Fabric Power BI — what changed, how Direct Lake replaces Import mode, semantic models vs datasets, and the migration path from traditional Power BI to the Fabric-native experience.

Related posts: – DAX for Data Engineers – Power BI Architecture – Star Schema and Normalization (SQL) – Medallion Architecture – SCD Type 1 and Type 2