Star and snowflake schemas

Data Modeling in Power BI

Sara Billen

Curriculum Manager at DataCamp

Star schema

  • Fact table(s)
  • Surrounded by dimension tables

Diagram of a star schema with a fact surrounded by five dimensions

Data Modeling in Power BI

Snowflake schema

  • Allows relationships between dimensions
  • Fact table(s) remain the same

Diagram of a snowflake schema with a fact surrounded by five dimensions of which two are connected to other dimensions

Data Modeling in Power BI

A closer look

Star schema

Example table for product hierarchy in a star schema

Snowflake schema

Example tables for product hierarchy in a snowflake schema

Data Modeling in Power BI
Star schema

Star schema diagram

  • Preferred approach
  • Easy for business users to understand
  • Most BI tools optimize for this schema
Snowflake schema

Snowflake schema diagram

  • Used in some data warehouses
  • Less duplication
  • Updating records is more efficient
Data Modeling in Power BI

Stars and snowflakes in Power BI

Data model example in Power BI

  • Both schemas work!
  • But Power BI prefers star schemas
    • Easier to understand
    • Performance is less of a concern
Data Modeling in Power BI

The performance analyzer

Screenshots of the Performance Analyzer in Power BI

  • Built-in performance analysis
  • Each visual has three components
    • How long did the DAX query take?
    • How long did the visual take to render?
    • How long did everything else take?
Data Modeling in Power BI

Performance tuning advice

DAX Query slowness

  • Tune DAX operations
  • Improve data loading performance

Visual display slowness

  • Use less complicated visuals
  • Show less information on the screen

Other slowness

  • Reduce number of visuals on the page
Data Modeling in Power BI

Let's practice!

Data Modeling in Power BI

Preparing Video For Download...