# Power Bi Semantic Model

> Design and build Power BI semantic models using star schema principles and the PowerBI Modeling MCP tools. Use this skill whenever the user wants to build a semantic model, create a data model, design a star schema, add tables or columns, create or modify relationships, configure storage modes (Import, DirectQuery, DirectLake, Composite), implement RLS (Row-Level Security), optimize model performance, or explore existing data sources for modeling. Triggers include: "build semantic model", "create data model", "star schema", "add table", "create relationship", "storage mode", "DirectLake", "composite model", "RLS", "optimize model", "model review", "explore data", "connect to gold layer", "extend model", "add dimension", "add fact table". Do NOT use for DAX measure creation (use power-bi-dax-development) or report generation (use power-bi-report-authoring).

- Skill: `datacoolie/power-bi-semantic-model` (Agent Skill, multi-file: 21 files)
- Install (CLI): `npx skillmds@latest add datacoolie/power-bi-semantic-model`
- Raw SKILL.md: https://api.skillmd.com/api/skills/datacoolie/power-bi-semantic-model/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: datacoolie (https://skillmd.com/u/datacoolie)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/datacoolie/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

| 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):

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

| 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 |