DP-600 - Semantic Models and Data Modeling

Star Schema Design

A star schema is the recommended pattern for Power BI semantic models. It has:

• Fact tables: contain measurable events (sales, orders, transactions). Typically narrow columns: foreign keys + numeric measures.
• Dimension tables: contain descriptive attributes (Date, Customer, Product, Geography). Usually wider with text and categorical columns.

Date dimension requirements: A dedicated Date table must cover all dates in the fact table, have no gaps, and be marked as a Date table (via the Modeling tab). This is required for time intelligence functions to work correctly.

Normalization Forms

For Power BI, dimension tables are often denormalized (1NF, not 2NF/3NF) for performance, but source systems should be normalized to 3NF.

Relationships

Cross-filter direction: Single (default) - filter flows from 1 side to * side. Both - filter flows both ways (use carefully; can cause ambiguity). In DAX use CROSSFILTER() to override per-measure.

Active vs Inactive relationships: Only one relationship between two tables can be active at a time. Additional relationships are inactive and must be activated via USERELATIONSHIP() inside CALCULATE.

Storage Modes

Direct Lake Mode - Deep Dive

When you create a semantic model from a Fabric Lakehouse or Warehouse, the default mode is Direct Lake. Key concepts:

• Framing: The process of loading Delta column segments into VertiPaq memory. Happens when a report is first queried after data changes.
• Fallback: If framing cannot complete (data too large for capacity), the query falls back to DirectQuery against the Lakehouse SQL endpoint.
• No gateway required: Data lives in OneLake, not on-premises.
• Requirements: Fabric capacity (F-SKU or P-SKU); tables must be Delta format in the Tables section of a Lakehouse.
• Direct Lake on SQL endpoint: Queries the SQL analytics endpoint of a Lakehouse rather than reading raw Delta files - used when V-Order optimization applies.

Row-Level Security (RLS)

RLS restricts which data rows a user can see:

1. In Power BI Desktop: Modeling tab -> Manage Roles -> create role -> add DAX filter expression
2. Publish to the Service
3. In the Service: dataset Settings -> Row-level security -> assign users/groups to roles

// Static RLS filter  -  East region role
[Region] = "East"

// Dynamic RLS  -  user sees only their own data
[Email] = USERPRINCIPALNAME()

Dynamic RLS uses USERPRINCIPALNAME() or USERNAME() to filter rows based on the logged-in user's email. No separate role assignment needed per user - one role, one filter expression handles all users via a lookup table.

Object-Level Security (OLS)

OLS restricts visibility of entire tables or columns. Users assigned to a role with OLS restrictions cannot see those objects in reports at all - the field is hidden from the field list and any query referencing it returns an error.

OLS is configured in Tabular Editor (not directly in Power BI Desktop UI). Set the table or column permission to None for the role.

Composite Models

A composite model mixes Import and DirectQuery tables in the same semantic model. Use cases:

• Import small dimension tables for speed; DirectQuery large fact tables from a data warehouse
• Connect a Power BI dataset (another shared semantic model) alongside local Import tables
• Combine Fabric Lakehouse (Direct Lake) with an on-premises DirectQuery source

Aggregations: Composite models support automatic aggregations - a summarized Import table speeds up common queries, with detailed DirectQuery as fallback. Query matching decides which layer to use based on the requested granularity.