Data Models in Power BI | Guided by A Guide to Cloud

What is a data model?

Simple explanation

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

Fact vs dimension tables
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

Question

What is a star schema?

Click or press Enter to reveal answer

Answer

A data model with a central fact table (events + measures) surrounded by dimension tables (descriptive attributes). Foreign keys connect them — forming a star shape.

Click to flip back

Question

Fact table vs dimension table?

Click or press Enter to reveal answer

Answer

Fact: measurable events (revenue, quantity), very large, grows constantly. Dimension: descriptive attributes (product name, date), relatively small, changes rarely.

Click to flip back

Question

What is a measure in Power BI?

Click or press Enter to reveal answer

Answer

A DAX formula calculated at query time that changes with filter context. Example: Total Revenue = SUM(Sales[Revenue]). Computed on demand, not stored.

Click to flip back

Knowledge check

Knowledge Check

Priya's Sales table has DateKey, StoreKey, ProductKey, Quantity, Revenue. What type?

Knowledge Check

Total revenue changes when a user selects a region filter. This uses:

🎬 Video coming soon

Next up: Choosing the Right Visualization — which chart tells which story?