4.1 Star Schema Design
Key Takeaways
- Star schema is the recommended data model design for Power BI, consisting of fact tables surrounded by dimension tables.
- Fact tables contain numeric measures (sales amount, quantity) and foreign keys to dimension tables.
- Dimension tables contain descriptive attributes (product name, category, customer address) and have a unique primary key.
- The star shape emerges because one central fact table connects to multiple surrounding dimension tables.
- Star schema optimizes DAX performance, simplifies report building, and reduces model ambiguity.
Star Schema Design
Quick Answer: Star schema is the gold standard for Power BI data models. A central fact table holds numeric measurements (revenue, quantity, cost) and connects to surrounding dimension tables that provide descriptive context (who, what, when, where). This design maximizes DAX performance and report simplicity.
What is a Star Schema?
A star schema is a data modeling pattern where:
- A central fact table stores quantitative data (measures/metrics)
- Multiple dimension tables surround the fact table, providing descriptive context
- Each dimension connects to the fact table through a one-to-many relationship
- The visual layout resembles a star, with the fact table at the center
Fact Tables
Fact tables contain the numerical measurements that your reports analyze.
Characteristics of Fact Tables
| Property | Description |
|---|---|
| Grain | Each row represents a specific event or transaction |
| Measures | Numeric columns that are aggregated (SUM, AVG, COUNT) |
| Foreign Keys | Columns that link to dimension tables |
| Row Count | Usually the largest table (millions/billions of rows) |
| Narrow | Fewer columns than dimension tables |
Example: Sales Fact Table
| OrderID | DateKey | ProductKey | CustomerKey | StoreKey | Quantity | UnitPrice | TotalAmount |
|---|---|---|---|---|---|---|---|
| 1001 | 20260301 | P-100 | C-500 | S-10 | 3 | 29.99 | 89.97 |
| 1002 | 20260301 | P-205 | C-312 | S-22 | 1 | 149.00 | 149.00 |
Key columns (DateKey, ProductKey, etc.) are the foreign keys that connect to dimension tables. Measure columns (Quantity, UnitPrice, TotalAmount) are the values you analyze.
Types of Fact Tables
| Type | Description | Example |
|---|---|---|
| Transaction | One row per event | Individual sales |
| Periodic Snapshot | One row per period | Monthly account balances |
| Accumulating Snapshot | One row per process lifetime | Order pipeline stages |
| Factless Fact | No measures — tracks events | Student attendance |
Dimension Tables
Dimension tables provide the descriptive context that gives meaning to the numbers in fact tables.
Characteristics of Dimension Tables
| Property | Description |
|---|---|
| Primary Key | Unique identifier for each row |
| Attributes | Descriptive text columns (name, category, address) |
| Hierarchies | Natural levels (Year → Quarter → Month → Day) |
| Row Count | Much smaller than fact tables (thousands to millions) |
| Wide | Many descriptive columns |
Common Dimension Tables
| Dimension | Key Attributes | Used For |
|---|---|---|
| Date | Year, Quarter, Month, Day, Day of Week | Time-based analysis |
| Product | Name, Category, Subcategory, Brand, Color | Product analysis |
| Customer | Name, City, State, Country, Segment | Customer analysis |
| Store/Location | Store Name, City, Region, Manager | Geographic analysis |
| Employee | Name, Department, Title, Hire Date | HR and sales analysis |
Building a Star Schema in Power BI
Step 1: Identify the Grain
Determine what each row in your fact table represents:
- One sale? One order line? One daily summary?
- The grain determines the level of detail in your model
Step 2: Identify Dimensions
For each fact, ask the "who, what, when, where, why, how" questions:
- Who made the sale? → Customer dimension
- What was sold? → Product dimension
- When was it sold? → Date dimension
- Where was it sold? → Store/Location dimension
- How was it paid? → Payment method dimension
Step 3: Create Relationships
In Power BI Model view:
- Drag a key column from a dimension table to the matching column in the fact table
- Set cardinality to One-to-Many (one dimension row → many fact rows)
- Set cross-filter direction to Single (dimension filters fact, not vice versa)
Step 4: Hide Technical Columns
Hide foreign key columns in fact tables from report view:
- Report builders should filter using dimension attributes, not key values
- Right-click column → Hide in report view
Snowflake Schema vs. Star Schema
| Feature | Star Schema | Snowflake Schema |
|---|---|---|
| Dimension structure | Flat (denormalized) | Normalized (sub-dimensions) |
| Number of tables | Fewer | More |
| Query performance | Better (fewer joins) | Slightly worse |
| Storage | Slightly more (duplicate data) | Less (normalized) |
| Complexity | Simpler | More complex |
| Power BI recommendation | Preferred | Avoid when possible |
Best Practice: In Power BI, always flatten (denormalize) snowflake dimensions into a single table. Power BI's VertiPaq engine compresses data efficiently, making normalization unnecessary and even harmful to performance.
Model Organization Best Practices
- Use Model view (diagram view) to visually arrange tables in a star pattern
- Place fact tables in the center and dimensions around them
- Hide all foreign key columns from report view
- Hide all technical/internal columns that users don't need
- Create display folders to organize measures by business area
- Mark your date table as the official date table (Table Tools → Mark as Date Table)
- Disable auto date/time to prevent hidden date table proliferation
On the Exam
The PL-300 frequently tests:
- Identifying fact tables vs. dimension tables from a business scenario
- Understanding one-to-many relationship direction (dimension → fact)
- Knowing when to denormalize a snowflake schema into a star schema
- Recognizing the role of the date dimension in time intelligence
- Hiding technical columns from report view
In a star schema, which table typically has the most rows?
What is the primary reason Power BI recommends star schema over snowflake schema?
Which columns in a fact table should be hidden from report view?