DP-600: optimizing cardinality and relationship performance in semantic models

I will teach how to optimize the cardinality and performance of relationships in a semantic model (Power BI / Fabric) — a useful skill for DP-600 and essential for scalable, agile models. Knowing this helps reduce query latency, lower memory usage and ensure correct results in complex analyses, especially in scenarios with millions of rows or when combining Import and DirectQuery.

What you need to know

In a semantic model, tables are linked by relationships defined between columns. Cardinality (one-to-one, one-to-many, many-to-one, many-to-many) and filter direction (single, both/directions) affect both calculation logic and query performance. Relationships are evaluated by the VertiPaq engine in Import mode and go through delegation mechanisms in DirectQuery mode. An efficient design avoids unnecessary calculations, reduces joins and allows the engine to use columnar compression and indexes effectively.

Simple numeric example: suppose a FactSales with 10 million rows and a DimProduct with 10,000 rows. The correct relationship is DimProduct (1) -> FactSales (many). If DimProduct has duplicates or poorly defined keys, the same query that takes 200 ms with a correct relationship can take several seconds or return incorrect results. In Import mode, whole columns with low cardinality compress much better than long strings or GUIDs.

How it works in practice

Practical steps to optimize relationships:

1. Verify uniqueness in dimension tables: columns used as keys (for example ProductID) must be unique in the dimension. Use DISTINCTCOUNT(DimProduct[ProductID]) to validate in Power BI or a SQL query in the ETL. If duplicates exist, create a surrogate key (sequential integer ID) or resolve duplication in the data preparation stage. In a typical 10k-row dimension, uniqueness is easy to guarantee; in customer dimensions with 2M records, it is critical.
2. Choose the correct cardinality: set one-to-many whenever a dimension describes multiple fact rows. Avoid many-to-many except when strictly necessary — many-to-many often requires a bridge table and increases calculation cost. For example, a many-to-many relationship between 100k customers and 50k products can multiply calculation load and increase query time 5x–10x if not handled correctly.
3. Set minimal filter direction: use single direction filter by default. Only use both direction when necessary for cross-filtering in specific visuals. In large models, both direction can cause costly filter propagation and ambiguity in filter paths, increasing compute and RAM cost.
4. Normalize keys and types: ensure the joining columns have the same type (integer vs text) and format. Implicit conversions at runtime degrade performance. For example, linking a GUID column (36-character text) to an integer prevents efficient compression and increases model size — converting to an integer ID can reduce space by 25–50% and decrease query times by 30–70% in some scenarios.
5. Reduce fact key cardinality: if the fact has very wide keys (e.g. GUID strings or composite keys), consider mapping to sequential integers to improve compression and indexability. Columns with high cardinality (millions of distinct values) reduce the effectiveness of VertiPaq columnar compression.
6. Avoid cross and ambiguous relationships: keep a clear schema (star schema). Chain or cross relationships create alternative filter paths that increase calculation cost and can cause unexpected results. Maintain a star schema with centralized dimensions for fact tables whenever possible.

-- Pseudocode DAX to test uniqueness
EVALUATE
SUMMARIZE(
  DimProduct,
  DimProduct[ProductID],
  "CountRows", COUNTROWS(FILTER(DimProduct, DimProduct[ProductID]=EARLIER(DimProduct[ProductID])))
)
-- Look for ProductID with CountRows > 1

In DirectQuery environments, minimizing joins and delegating aggregations to the source system (for example the data warehouse SQL) is crucial to keep latencies low. For Import mode, optimizing cardinality and types helps the engine use columnar compression and reduce memory, in addition to speeding up DAX calculations.

Common mistakes

• Using both-direction by default: leads to degraded performance and difficulty explaining filter behavior. In a report with 15 visuals, both-direction can double total processing time because filters propagate along multiple paths.
• Ignoring uniqueness: establishing a one-to-many relationship when the dimension is not unique causes incorrect results and confusion in calculations. Detect this with uniqueness tests and automated validation in ETL.
• Mixing types in relationship columns: text vs integer keys force conversions and prevent engine optimizations. This can not only increase latency, but also cause failures in certain DAX functions that expect specific types.
• Snowflake models when not needed: over-normalizing can require multiple joins and penalize performance. Prefer a star schema for reporting.

How to practice

Practice with real and synthetic datasets: create a large Fact (for example 5–20 million rows) and multiple Dimensions (5k–50k rows); test different cardinality and filter direction configurations; measure refresh time and query time in Power BI Desktop or in the Fabric environment. Use the Performance Analyzer in Power BI to capture DAX query durations per visual. Additional tools like DAX Studio and VertiPaq Analyzer help analyze plans and compression.

Suggested exercise: import a FactSales of 10M rows with ProductID as GUID and measure file size and query time; then transform the GUID into a sequential integer, re-establish the one-to-many relationship and record the reduction in model size and latency improvement. Note numbers (MB, ms) for comparison.

For formal preparation, use the OFFICIAL Microsoft Practice Assessment free of charge and the official study guide (also free). These resources help you validate knowledge in the areas measured by DP-600 without resorting to prohibited material.

In summary

• Set cardinality correctly (one-to-many when the dimension is unique) to ensure correct results and better performance.
• Use minimal filter direction (single) and avoid both-direction except when necessary.
• Ensure uniqueness and type compatibility in join columns to avoid conversions and errors.
• Test performance with real data and with the Performance Analyzer; use DAX Studio/VertiPaq Analyzer for diagnostics; and consult Microsoft's official resources (Practice Assessment and study guide free of charge) to structure your study.