Data Models in Power BI
Great dashboards start with a great data model. Star schemas, relationships, measures, and dimensions — the foundation of Power BI reporting.
What is a data model?
A data model is the blueprint telling Power BI how tables connect.
Three spreadsheets: Sales, Products, Stores. The model maps “ProductID in Sales matches ProductID in Products.” Without this map, Power BI can’t combine data.
Good model = fast, accurate reports. Bad model = wrong numbers.
Star schema
The most common pattern: a central fact table surrounded by dimension tables.
- Fact table (centre): Measurable events — numeric values and foreign keys
- Dimension tables (points): Descriptive attributes for filtering
Priya’s FreshMart model:
- Sales (fact): DateKey, StoreKey, ProductKey, Quantity, Revenue, Cost
- Date (dim): DateKey, Month, Quarter, Year
- Product (dim): ProductKey, Name, Category, Brand
- Store (dim): StoreKey, StoreName, City, Region
| Feature | Fact Tables | Dimension Tables |
|---|---|---|
| Contains | Measurable events | Descriptive attributes |
| Row count | Very large (millions+) | Small (thousands) |
| Key columns | Foreign keys + measures | Primary key + descriptions |
| Examples | Sales, Orders, Deliveries | Date, Product, Store |
Measures vs calculated columns
| Measures | Calculated Columns | |
|---|---|---|
| When | Query time (dynamic) | Data refresh (stored) |
| Example | Total Revenue = SUM(Sales[Revenue]) | Profit = Revenue - Cost |
| Best for | Aggregations with filters | Row-level calculations |
Hierarchies
Drill-down paths: Year → Quarter → Month → Day. Users click to drill from yearly to monthly.
Exam tip: data model concepts
- “Central table with numeric measures” → Fact table
- “Descriptive attributes for filtering” → Dimension table
- “Year → Quarter → Month” → Hierarchy
- “SUM that changes with filters” → Measure
- “Recommended schema” → Star schema
Flashcards
Knowledge check
Priya's Sales table has DateKey, StoreKey, ProductKey, Quantity, Revenue. What type?
Total revenue changes when a user selects a region filter. This uses:
Next up: Choosing the Right Visualization — which chart tells which story?