DP-600: optimize semantic models with star schema

João Barros 09 de August de 2026 6 min read

This lesson focuses on a key DP-600 skill: implementing and managing semantic models using the star schema pattern. Knowing when and how to structure fact and dimension tables improves performance, facilitates DAX calculations, and is frequently assessed on the exam and used in real-world scenarios. A well-designed model reduces response times, lowers memory consumption, and simplifies report and data governance.

What you need to know

A star schema organizes data into fact tables that contain numeric measures (such as sales, quantities) and dimension tables that contain descriptive attributes (such as product, customer, date). The central idea is to keep the fact table denormalized and the dimensions relatively stable, linked by simple keys. Advantages: faster queries, simpler DAX modeling, and better compression in the VertiPaq engine.

Simple example: a Sales fact table with columns SaleID, ProductKey, CustomerKey, DateKey, Quantity, SalesAmount; and dimensions Product (ProductKey, ProductName, Category), Customer (CustomerKey, Name, Region) and Date (DateKey, Date, Year, Month). In real scenarios, the fact table can reach millions of rows (for example 5–50M rows), while dimensions typically have tens to hundreds of thousands of records (for example Product = 10k, Customer = 120k). Keeping dimensions clean and with integer keys helps compression: replacing textual keys with surrogate keys can reduce the space occupied by those columns by 60–80% in many cases.

How it works in practice

Main elements and steps to implement an effective star schema in Power BI/Fabric:

Identify facts and dimensions: start from the sources; transactional tables (sales, transactions) become fact tables. Tables with descriptive attributes (products, customers, calendar) become dimensions. For example, an orders table with 12 columns and 10M rows should be converted into a fact table with keys and measures and the remaining columns moved to dimensions to reduce duplication.
Choose and maintain joining keys: use integer surrogate keys when possible (e.g.: ProductKey) instead of string combinations. Integer keys optimize relationships and compression. In practice, generate a ProductKey as a sequential integer in the ETL (IDENTITY/auto-increment) and store the natural key (SKU) only in the dimension for reference. This speeds up joins and reduces index size; for example, an int column occupies 4 bytes, while a 20-character SKU can exceed 20 bytes per record.
Denormalize fact, normalize dimensions: avoid deep joins in analytical queries; the fact table should contain only keys and measures. Dimensions can group related attributes (e.g.: ProductCategory in the Product dimension). Denormalizing the fact means, for example, avoiding columns that repeat long text in every sales row; instead, store the reference to the dimension.
Model relationships with correct cardinality: define 1:* relationships (dimension → fact). In Power BI Desktop/Fabric, set relationships activate/bi-directional as needed, but prefer unidirectional filtering in most cases. Unidirectional relationships keep the model predictable and avoid ambiguous contexts; use bi-directional only when you need to filter across without bridge tables, and run integrity tests before publishing.
Manage hierarchies and calculated columns: create hierarchies (Year > Month > Day) in date dimensions. Use calculated columns sparingly: prefer DAX measures for dynamic calculations and better performance. For example, calculating average margin per product should be done as a measure (aggregated at runtime) and not as a calculated column that increases model size; only create calculated columns when you need the column to exist for slicing or relationships.

In practice — DAX and modeling example

Assume you have Sales (fact) and Date (dim) tables. To create a Total Sales measure instead of a calculated column, write:

Total Sales = SUM(Sales[SalesAmount])

YTD Sales = TOTALYTD([Total Sales], Date[Date])

Explanation: SUM aggregates the fact table; TOTALYTD uses the Date dimension to evaluate year-to-date. If the relationship between Sales[DateKey] and Date[DateKey] is correct (1:*), these measures are efficient and reusable in visuals. On a base with 10M fact rows, well-written measures dramatically reduce calculation time: typical sum queries that took 5–10s on poorly designed models can drop to 0.2–2s with compression and correct keys.

Common mistakes

Using many-to-many relationships without need: resorting to *:* cardinality and bi-directional filtering by default can cause incorrect and slow results. Evaluate alternatives (bridge tables or different modeling). For example, multiple direct relationships between fact and several dimensions can lead to result duplication if not properly isolated.
Excess calculated columns in the fact table: creating many derived columns in facts increases model size. Prefer DAX measures, except when you need columns for persistent slicing/categorization. As a practical rule, each additional column in a 20M-row fact adds linear memory costs.
Long textual keys as PK/FK: using strings as joining keys (e.g.: alpha codes) increases space and reduces performance. Replace with integer surrogate keys whenever you can.

How to practice

To practice, build a small project in Power BI Desktop/Fabric: import a sales table and dimensional tables, create the star model, define relationships and implement measures like Total Sales, YTD and Average Price. Try comparing performance between calculated columns and measures using the Performance Analyzer and observing the file size (.pbix/.parquet) before and after. A useful exercise is to duplicate the model with textual keys and with integer keys and compare: many teams observe 30–70% reductions in model size after optimizing keys and removing unnecessary columns.

See the official free Microsoft resources: the Practice Assessment OFICIAL for DP-600 (free practice assessment) and the Microsoft study guide for DP-600. Both are free and help validate your knowledge on the skills measured without relying on unauthorized material.

In summary

Star schema separates fact tables (measures) from dimension tables (attributes) for better performance and clarity.
Use integer surrogate keys, 1:* relationships, and preferably unidirectional filtering.
Prefer DAX measures over calculated columns in the fact table to reduce memory usage and improve performance.
Avoid many-to-many cardinalities and long textual keys; test and compare performance in your environment using tools like Performance Analyzer before publishing.