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
| Task | Approach | Reference |
|---|---|---|
| Explore existing model | model_operations → get model info, table_operations → list tables |
— |
| Discover model metadata | INFO.VIEW.* queries for tables, columns, measures, relationships | references/discovery-queries.md |
| Connect to gold layer | connection_operations → configure data source |
references/connection-binding.md |
| Design star schema | Run Star Schema Checklist | references/star-schema-checklist.md |
| Naming conventions | Tables, columns, measures, hierarchies naming rules | references/naming-conventions.md |
| Choose storage mode | Use Decision Matrix | references/storage-mode-decision.md |
| Direct Lake guide | Framing, guardrails, fallback, composite | references/directlake-guide.md |
| Build relationships | relationship_operations → create with proper cardinality |
references/advanced-relationships.md |
| Optimize columns | column_operations → set data types, remove unused, hide keys |
references/vertipaq-optimization.md |
| DAX coding standards | Variables, comments, query syntax, best practices | references/dax-guidelines.md |
| DAX performance | Optimization framework (21 patterns, tier 1–4) | references/dax-perf-decision-guide.md + references/dax-perf-patterns.md |
| Power Query / ETL | M language, query folding, transformations | references/power-query-reference.md |
| Implement RLS | Dynamic RLS, OLS, DirectLake RLS patterns | references/rls-patterns.md |
| TMDL / PBIP structure | Tables, columns, measures, relationships, roles | references/tmdl-reference.md + references/pbip.md |
| Deploy to workspace | Git integration, CI/CD, Fabric pipelines | references/deployment-alm-guide.md |
| Gateway & refresh | On-prem gateway, scheduled/incremental refresh | references/gateway-refresh-guide.md |
| Fabric REST API | TMDL CRUD, refresh, data sources, permissions | references/semantic-model-rest-api.md |
| Prepare for AI/Copilot | Descriptions, synonyms, AI instructions, verified answers | references/semantic-model-ai-readiness.md |
| Test the model | dax_query_operations → run EVALUATE queries |
references/discovery-queries.md |
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:
| Type | Role | Examples |
|---|---|---|
| Fact | Measurable events, FK to dims, numeric aggregates | Sales, Orders, Production |
| Dimension | Descriptive context, surrogate key, filtering/grouping | Date, Product, Customer |
| Bridge | M:N link, key columns only | CustomerProduct, EmployeeProject |
| Measure Table | No data rows, organizes DAX measures | _Measures, _KPIs |
Full design rules and validation → references/star-schema-checklist.md
Step 3: Configure Storage Modes
| Scenario | Recommended Mode |
|---|---|
| Historical data, < 1GB | Import |
| Historical data, > 1GB with Fabric | Direct Lake |
| Direct Lake + external reference data | Composite (DL + Import) |
| Real-time operational data | DirectQuery |
| Mix of real-time + historical | Composite (DQ + Import) |
| Dimension tables in composite model | Dual |
| Aggregation tables | Import |
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):
- Remove unused columns — hidden columns still consume memory
- Reduce cardinality — bin dates, round decimals, group rare values
- Use INT keys over TEXT — value encoding vs hash encoding
- 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 —
CALCULATETABLEwith 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
| Skill | Relationship | When |
|---|---|---|
power-bi-business-analysis |
Upstream (Phase 1) | Requirements doc defines tables, data sources, and RLS needs |
power-bi-dax-development |
Downstream (Phase 3) | Model schema feeds into measure creation |
power-bi-performance-troubleshooting |
Cross-cutting | VertiPaq optimization, storage mode tuning, cardinality reduction |
power-bi-report-authoring |
Downstream (Phase 4b) | Model schema used during PBIR generation for queryState bindings |