Power Bi Semantic Model

Power BI Semantic Model Builder

Design and build Power BI semantic models following star schema best practices, using PowerBI Modeling MCP tools for all model operations.

Always search Microsoft Learn (microsoft-learn-mcp/microsoft_docs_search) for the latest modeling guidance before making design decisions.

Use PowerBI Modeling MCP (powerbi-modeling-mcp/*) for all model operations — exploring tables, creating relationships, configuring columns, testing queries. Read references/mcp-tool-reference.md for the complete tool mapping.

Quick Reference

Tool Selection Priority

When multiple tools can perform the same operation, follow this priority:

Tier 1 — MCP (powerbi-modeling-mcp): Use MCP tools for all authoring operations when connected to the target model. Fastest, least-error-prone, no file I/O.

Tier 2 — TMDL files + az rest: Use when MCP is unavailable. Edit TMDL files directly and deploy via Fabric REST API. See references/tmdl-reference.md for syntax and references/semantic-model-rest-api.md for API endpoints.

Tier 3 — Fallback: If neither MCP nor TMDL access is available, guide the user to save as PBIP or install MCP.

Must / Prefer / Avoid

MUST

• Understand data source schema before designing the model
• Follow star schema principles (references/star-schema-checklist.md)
• Follow naming conventions (references/naming-conventions.md)
• Follow tool selection priority (MCP → TMDL → Fallback)
• Validate model with DAX queries after structural changes

PREFER

• Star schema over flat/denormalized tables
• Explicit measures over implicit aggregation
• TMDL format for version-controlled model definitions
• INT keys over TEXT keys for relationships

AVOID

• Hardcoded workspace/item IDs — resolve dynamically
• Reading TMDL files when MCP is connected (use MCP operations instead)
• Calculated columns on fact tables (use measures or Power Query)
• Auto Date/Time (use dedicated date dimension)

Workflow

Step 1: Explore Available Data

Before designing, understand what exists:

Exploration Checklist:
□ Use powerbi-modeling-mcp/model_operations to get current model state
□ Use powerbi-modeling-mcp/table_operations to list all tables and columns
□ Use powerbi-modeling-mcp/relationship_operations to see existing relationships
□ Identify which tables are facts (transactions, events) vs. dimensions (descriptive)
□ Check column data types and cardinality
□ Note any existing measures (powerbi-modeling-mcp/measure_operations)
□ Check connection/partition info (powerbi-modeling-mcp/partition_operations)

If connecting to a gold layer or lakehouse:

Data Source Exploration:
□ Use fabric-notebook-mcp/list_artifacts to find available tables
□ Use fabric-notebook-mcp/get_lakehouse_detail for lakehouse schema
□ Use fabric-notebook-mcp/preview_lakehouse_table to inspect data
□ Use fabric-notebook-mcp/get_table_column_stats for column statistics
□ Use ms-mssql.mssql tools to query SQL-based gold layers

Step 2: Design Star Schema

Classify tables then validate with references/star-schema-checklist.md:

Full design rules and validation → references/star-schema-checklist.md

Step 3: Configure Storage Modes

Full decision matrix → references/storage-mode-decision.md

Step 4: Build Relationships

Use powerbi-modeling-mcp/relationship_operations:

□ Cardinality: One-to-Many (dimension → fact) is standard
□ Cross-filter: Single direction (dimension filters fact) is default
□ Active: Only one active relationship between any two tables
□ Inactive: Use USERELATIONSHIP() for role-playing dimensions
□ Referential integrity: Enable for Import mode (performance boost)

Advanced patterns (M:M, weak, role-playing, ambiguity) → references/advanced-relationships.md

Step 5: Optimize the Model

Prioritize in this order (biggest compression impact first):

1. Remove unused columns — hidden columns still consume memory
2. Reduce cardinality — bin dates, round decimals, group rare values
3. Use INT keys over TEXT — value encoding vs hash encoding
4. No calculated columns on fact tables — use measures or Power Query

Model-level:

• Dedicated date dimension (disable Auto Date/Time)
• Remove auto-generated LocalDateTable_* tables
• Aggregation tables for >100M row facts
• Incremental refresh for growing tables
• Target < 1GB model size (Import mode)

Full optimization guide → references/vertipaq-optimization.md

Step 6: Implement Security

RLS Implementation Steps:
1. Define roles → powerbi-modeling-mcp/security_role_operations
2. Write DAX filter expressions per role
3. Test with dax_query_operations (EVALUATE with role context)

All patterns (static, dynamic, hierarchy, time-based, OLS) → references/rls-patterns.md

Step 7: Validate the Model

Use powerbi-modeling-mcp/dax_query_operations to validate:

• EVALUATE INFO.VIEW.RELATIONSHIPS() — verify relationship structure
• Orphaned records check — ISBLANK(RELATED(...)) pattern
• Row count spot-check — COUNTROWS() per table
• RLS propagation test — CALCULATETABLE with role context

Full discovery query catalog → references/discovery-queries.md

Step 8: Prepare for AI (Copilot Readiness)

Optimize the model for Power BI Copilot and Fabric Data Agent:

Prep for AI Checklist:
□ Use descriptive, human-readable names for all tables, columns, measures
□ Add descriptions to tables, columns, and measures (Copilot uses metadata)
□ Hide relationship key columns and unused technical fields
□ Avoid duplicate field names across tables (e.g., "Name" in Customer vs Store)
□ Remove unused objects — fewer objects = less AI ambiguity
□ Configure AI Data Schema (Prep data for AI → Simplify data schema)
□ Add AI Instructions (business context, terminology, domain logic)
□ Set up Verified Answers for common questions
□ Mark model as "Prepped for AI" in semantic model settings
□ Test with Copilot pane → validate responses against expected answers

Copilot folder structure in PBIP → references/tmdl-reference.md §Copilot Folder Full AI readiness guide → references/semantic-model-ai-readiness.md

Common Modeling Scenarios

Slowly Changing Dimensions (SCD)

Type 1 (overwrite): Update dimension row directly. No special modeling.

Type 2 (history): Surrogate key per version + ValidFrom/ValidTo/IsCurrent. Relationship uses surrogate key (not natural key).

Date Table Requirements

Every model MUST have a proper date table — time intelligence fails without it:

5 Non-Negotiable Requirements:
1. Contiguous dates — one row per calendar date, NO gaps
2. Covers full range of all fact table dates (plus buffer year)
3. Date column is DATE type — no time component
4. DateKey column is INT (YYYYMMDD) — use as relationship key
5. Marked as Date Table via powerbi-modeling-mcp/calendar_operations

Note: Calendar-based time intelligence (preview, Sep 2025) relaxes the contiguity requirement for custom calendars (fiscal, 4-5-4, 13-month, lunar). Defines calendars on tables via Column Category mappings. New DAX functions: TOTALWTD, PREVIOUSWEEK, TOTALYTD('CalendarName'). Enable via Preview Features.

Full column requirements → references/star-schema-checklist.md (Section 3)

PBIP / TMDL File Structure

For complete PBIP folder layout, file schemas, and TMDL syntax → read references/tmdl-reference.md and references/pbip.md.

Related Skills