I85PREP
⌘ K
DP-500 study materials
DP-500 study resources

DP-500: Design and develop enterprise data models

DP-500 · Work through your exam, one topic at a time.

All guide sections

Design and develop enterprise data models

Domain 3 - Design and develop enterprise data models

This domain tests semantic model design in Power BI and Azure Analysis Services: star schemas, relationships, storage modes, and DAX that stays fast at enterprise scale.

Star schema fundamentals

  • Keep fact tables narrow and dimension tables wide; replace snowflakes with flattened dimensions where practical.
  • Use surrogate integer keys, one-to-many single-direction relationships, and a contiguous date table marked as the date table.
  • Use bridge tables for many-to-many; avoid bidirectional filters except for specific, tested cases because they create ambiguous paths and slow queries.

Storage modes

  • Import: fastest queries, data compressed in memory, limited by model size.
  • DirectQuery: no data copy, queries the source; performance depends on source indexes and query folding.
  • Dual: can act as either, used for dimensions in composite models.
  • Aggregations over a DirectQuery detail table keep common queries fast when the visual grain matches the aggregation grain.

DAX design rules

  • Prefer measures over calculated columns; calculated columns on a large fact table inflate the model and slow refresh.
  • Use variables to evaluate a filter once instead of repeating it.
  • Avoid iterators over the full fact table when a simple CALCULATE with filter arguments achieves the same result.

Worked example

Sales must be reported by product category, but the model snowflakes Product → Subcategory → Category. Build a flattened DimProduct with CategoryName and SubcategoryName columns, and relate Sales to it one-to-many. Reasoning: each extra hop adds a relationship the engine must traverse and makes cross-filtering harder to reason about. If the snowflake must remain, keep relationships single-direction and avoid bidirectional propagation between the levels.

Traps

  • Marking a date table but leaving gaps, which breaks time intelligence.
  • Using bidirectional cross-filtering to "fix" a filter that should be handled with CROSSFILTER in a specific measure only.
  • Assuming an aggregation is used when the visual groups at a finer grain than the aggregation table.
  • Adding high-cardinality calculated columns (GUIDs, timestamps) that dominate model size.