✨ Key Features

🔍 Detect — 120 Rules Across 4 Fabric Items

| Item | Rules | What's Scanned | |------|-------|----------------| | 🏠 Lakehouse | 29 | SQL Endpoint + OneLake Delta Log (VACUUM history, file sizes, partitioning, retention) | | 🏗️ Warehouse | 39 | Schema, query performance, security (PII, RLS), database config | | 📊 Eventhouse | 20/db | Extent fragmentation, caching/retention/merge/encoding/partitioning policies, ingestion, query performance, materialized views, stored functions | | 📐 Semantic Model | 32 | DAX expression anti-patterns, model structure, COLUMNSTATISTICS BPA | | | 120 total | |

🔧 Fix — 45 Auto-Fixable Issues

| Item | Auto-Fixes | Method | |------|-----------|--------| | 🏗️ Warehouse | 12 fixes | SQL DDL executed directly | | 🏠 Lakehouse | 14 fixes | REST API (3) + Notebook Spark SQL (11) | | 📐 Semantic Model | 12 fixes | model.bim REST API (6) + Notebook sempy (6) | | 📊 Eventhouse | 7 fixes | KQL management commands (with dry-run preview) | | | 45 total | |

📊 Unified Output

Every scan returns a clean results table — only issues shown, passed rules counted in summary:

29 rules — ✅ 18 passed | 🔴 1 failed | 🟡 10 warning

| Rule | Status | Finding | Recommendation |
|------|--------|---------|----------------|
| LH-007 Key Columns Are NOT NULL | 🔴 | 16 key column(s) allow NULL: table.finding_id, ... | Add NOT NULL constraints |
| LH-017 Regular VACUUM Executed | 🟡 | 4 table(s) need VACUUM: table1, table2, ... | Run VACUUM weekly |

🚀 Quick Start

Prerequisites

• Node.js 18+
• Azure CLI with az login completed
• Fabric capacity with items to scan

Install

git clone https://github.com/tmdaidevs/Force-Fabric-MCP-Server.git
cd Force-Fabric-MCP-Server
npm install
npm run build

Configure VS Code

Add to .vscode/mcp.json in your project:

{
  "servers": {
    "fabric-optimization": {
      "type": "stdio",
      "command": "node",
      "args": ["dist/index.js"],
      "cwd": "/path/to/Force-Fabric-MCP-Server"
    }
  }
}

Use

1. "Login to Fabric with azure_cli"
2. "List all lakehouses in workspace <id>"
3. "Scan lakehouse <id> in workspace <id>"
4. "Fix warehouse <id> in workspace <id>"

🔍 Detect & Scan

Available Scan Tools

| Tool | What It Does | |------|-------------| | lakehouse_optimization_recommendations | Scans SQL Endpoint + reads Delta Log files from OneLake | | warehouse_optimization_recommendations | Connects via SQL and runs 39 diagnostic queries | | warehouse_analyze_query_patterns | Focused analysis of slow/frequent/failed queries | | eventhouse_optimization_recommendations | Runs KQL diagnostics on each KQL database | | semantic_model_optimization_recommendations | Executes DAX + MDSCHEMA DMVs for BPA analysis |

Data Sources Used

                          ┌─────────────────────────────────────┐
                          │         Fabric REST API             │
                          │  Workspaces, Items, Metadata        │
                          └──────────────┬──────────────────────┘
                                         │
          ┌──────────────┬───────────────┼───────────────┬──────────────┐
          ▼              ▼               ▼               ▼              ▼
   ┌─────────────┐ ┌──────────┐ ┌──────────────┐ ┌──────────┐ ┌──────────────┐
   │  SQL Client │ │ KQL REST │ │ OneLake ADLS │ │ DAX API  │ │ MDSCHEMA DMV │
   │  (tedious)  │ │   API    │ │  Gen2 API    │ │executeQry│ │  via REST    │
   └──────┬──────┘ └────┬─────┘ └──────┬───────┘ └────┬─────┘ └──────┬───────┘
          │              │              │              │              │
    Lakehouse SQL   Eventhouse    Delta Log JSON   Semantic     Semantic
    Warehouse SQL   KQL DBs       File Metadata    Model DAX    Model Meta

🔧 Auto-Fix

Warehouse Fixes (warehouse_fix)

Run all safe fixes or specify individual rule IDs:

| Rule ID | What It Fixes | SQL Command | |---------|--------------|-------------| | WH-001 | Missing primary keys | ALTER TABLE ADD CONSTRAINT PK NOT ENFORCED | | WH-008 | Stale statistics (>30 days) | UPDATE STATISTICS [table] | | WH-009 | Disabled constraints | ALTER TABLE WITH CHECK CHECK CONSTRAINT ALL | | WH-016 | Missing audit columns | ALTER TABLE ADD created_at DATETIME2 DEFAULT GETDATE() | | WH-018 | Unmasked sensitive data | ALTER COLUMN ADD MASKED WITH (FUNCTION='...') | | WH-026 | Auto-update statistics off | ALTER DATABASE SET AUTO_UPDATE_STATISTICS ON | | WH-027 | Result set caching off | ALTER DATABASE SET RESULT_SET_CACHING ON | | WH-028 | Snapshot isolation off | ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON | | WH-029 | Page verify not CHECKSUM | ALTER DATABASE SET PAGE_VERIFY CHECKSUM | | WH-030 | ANSI settings off | ALTER DATABASE SET ANSI_NULLS ON; ... | | WH-032 | Missing statistics | UPDATE STATISTICS [table] | | WH-036 | NOT NULL without defaults | ALTER TABLE ADD DEFAULT ... FOR column |

Eventhouse Fixes (eventhouse_fix)

Supports dry-run mode (dryRun: true) to preview commands without executing them.

| Rule ID | What It Fixes | KQL Command | |---------|--------------|-------------| | EH-002 | Fragmented extents | .merge table ['name'] | | EH-004 | Missing caching policy | .alter table/database policy caching hot = 30d | | EH-005 | Missing retention policy | .alter table/database policy retention softdelete = 365d | | EH-006 | Unhealthy materialized views | .enable materialized-view ['name'] | | EH-014 | Missing ingestion batching | .alter table/database policy ingestionbatching ... | | EH-016 | Large tables without partitioning | .alter table policy partitioning ... | | EH-017 | Suboptimal merge policy | .alter table policy merge ... |

Lakehouse Fixes (lakehouse_run_table_maintenance)

| Fix | Parameters | |-----|-----------| | OPTIMIZE with V-Order | optimizeSettings: { vOrder: true } | | Z-Order by columns | optimizeSettings: { zOrderColumns: ["col1", "col2"] } | | VACUUM stale files | vacuumSettings: { retentionPeriod: "7.00:00:00" } |

Semantic Model Fixes (semantic_model_fix)

Downloads model.bim, applies modifications, uploads back:

| Fix ID | What It Fixes | Method | |--------|--------------|--------| | SM-FIX-FORMAT | Add format strings to measures without one | model.bim | | SM-FIX-DESC | Add descriptions to visible tables | model.bim | | SM-FIX-HIDDEN | Set IsAvailableInMDX=false on hidden columns | model.bim | | SM-FIX-DATE | Mark date/calendar tables as Date table | model.bim | | SM-FIX-KEY | Set IsKey=true on PK columns in relationships | model.bim | | SM-FIX-AUTODATE | Remove auto-date tables | model.bim |

📓 Notebook-Based Fixes

For fixes that require Spark SQL, the MCP server creates a temporary Notebook, runs it, and deletes it:

1. POST /notebooks              → Create temp notebook with fix code
2. POST /items/{id}/jobs        → Execute notebook
3. GET  /items/{id}/jobs/{job}  → Poll until complete
4. DELETE /notebooks/{id}       → Clean up

Lakehouse Notebook Fixes

| Rule | Spark SQL Command | |------|------------------| | LH-003 | CONVERT TO DELTA spark_catalog.lakehouse.table | | LH-005 | DROP TABLE lakehouse.table | | LH-009 | ALTER TABLE lakehouse.table RENAME COLUMN old TO new | | LH-014 | ALTER TABLE t ADD COLUMN created_at TIMESTAMP DEFAULT current_timestamp() | | LH-020 | ALTER TABLE t SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite'='true') | | LH-021 | ALTER TABLE t SET TBLPROPERTIES ('delta.logRetentionDuration'='interval 30 days') | | LH-024 | ALTER TABLE t SET TBLPROPERTIES ('delta.dataSkippingNumIndexedCols'='32') | | LH-S04 | ALTER TABLE t ADD COLUMN id BIGINT |

Semantic Model Notebook Fixes (via sempy_labs)

| Fix | sempy Code | |-----|-----------| | Remove Calculated Columns | tom.remove_column(table, column) | | Remove Calculated Tables | tom.remove_table(table) | | Fix Bi-directional Relationships | rel.CrossFilteringBehavior = OneDirection | | Fix RLS Expressions | table_permission.FilterExpression = ... | | Sync DirectLake Schema | labs.update_direct_lake_model_lakehouse_schema() | | Refresh Model | fabric.refresh_dataset(dataset, workspace) |

📋 Rule Reference

Summary

| Category | HIGH | MEDIUM | LOW | INFO | Total | Auto-Fix | |----------|------|--------|-----|------|-------|----------| | 🏠 Lakehouse | 5 | 14 | 9 | 1 | 29 | 14 (3 REST + 11 Notebook) | | 🏗️ Warehouse | 8 | 17 | 12 | 0 | 39 | 12 (SQL DDL) | | 📊 Eventhouse | 4 | 7 | 3 | 3 | 20 | 7 (KQL + dry-run) | | 📐 Semantic Model | 7 | 14 | 9 | 0 | 32 | 12 (6 model.bim + 6 Notebook) | | Total | 24 | 52 | 33 | 4 | 120 | 45 |

| # | Rule | Category | Severity | Auto-Fix | |---|------|----------|----------|----------| | LH-001 | SQL Endpoint Active | Availability | HIGH | — | | LH-002 | Medallion Architecture Naming | Maintainability | LOW | — | | LH-003 | All Tables Use Delta Format | Performance | HIGH | 📓 Notebook | | LH-004 | Table Maintenance Recommended | Performance | MEDIUM | 🔧 REST API | | LH-005 | No Empty Tables | Data Quality | MEDIUM | 📓 Notebook | | LH-006 | No Over-Provisioned String Columns | Performance | MEDIUM | — | | LH-007 | Key Columns Are NOT NULL | Data Quality | HIGH | — | | LH-008 | No Float/Real Precision Issues | Data Quality | MEDIUM | — | | LH-009 | Column Naming Convention | Maintainability | LOW | 📓 Notebook | | LH-010 | Date Columns Use Proper Types | Data Quality | MEDIUM | — | | LH-011 | Numeric Columns Use Proper Types | Data Quality | MEDIUM | — | | LH-012 | No Excessively Wide Tables | Maintainability | LOW | — | | LH-013 | Schema Has NOT NULL Constraints | Data Quality | MEDIUM | — | | LH-014 | Tables Have Audit Columns | Maintainability | LOW | 📓 Notebook | | LH-015 | Consistent Date Types Per Table | Data Quality | LOW | — | | LH-S01 | No Unprotected Sensitive Data | Security | HIGH | — | | LH-S02 | Large Tables Identified | Performance | INFO | — | | LH-S03 | No Deprecated Data Types | Maintainability | HIGH | — | | LH-S04 | All Tables Have Key Columns | Data Quality | MEDIUM | 📓 Notebook | | LH-016 | Large Tables Are Partitioned | Performance | MEDIUM | — | | LH-017 | Regular VACUUM Executed | Maintenance | MEDIUM | 🔧 REST API | | LH-018 | Regular OPTIMIZE Executed | Performance | MEDIUM | 🔧 REST API | | LH-019 | No Small File Problem | Performance | HIGH | 🔧 REST API | | LH-020 | Auto-Optimize Enabled | Performance | MEDIUM | 📓 Notebook | | LH-021 | Retention Policy Configured | Maintenance | LOW | 📓 Notebook | | LH-022 | Delta Log Version Count Reasonable | Performance | LOW | 🔧 REST API | | LH-023 | Low Write Amplification | Performance | MEDIUM | — | | LH-024 | Data Skipping Configured | Performance | LOW | 📓 Notebook | | LH-025 | Z-Order on Large Tables | Performance | MEDIUM | 🔧 REST API |

| # | Rule | Category | Severity | Auto-Fix | |---|------|----------|----------|----------| | WH-001 | Primary Keys Defined | Data Quality | HIGH | 🔧 SQL | | WH-002 | No Deprecated Data Types | Maintainability | HIGH | — | | WH-003 | No Float/Real Precision Issues | Data Quality | MEDIUM | — | | WH-004 | No Over-Provisioned Columns | Performance | MEDIUM | — | | WH-005 | Column Naming Convention | Maintainability | LOW | — | | WH-006 | Table Naming Convention | Maintainability | LOW | — | | WH-007 | No SELECT * in Views | Maintainability | LOW | — | | WH-008 | Statistics Are Fresh | Performance | MEDIUM | 🔧 SQL | | WH-009 | No Disabled Constraints | Data Quality | MEDIUM | 🔧 SQL | | WH-010 | Key Columns Are NOT NULL | Data Quality | HIGH | — | | WH-011 | No Empty Tables | Maintainability | MEDIUM | — | | WH-012 | No Excessively Wide Tables | Maintainability | MEDIUM | — | | WH-013 | Consistent Date Types | Data Quality | LOW | — | | WH-014 | Foreign Keys Defined | Maintainability | MEDIUM | — | | WH-015 | No Large BLOB Columns | Performance | MEDIUM | — | | WH-016 | Tables Have Audit Columns | Maintainability | LOW | 🔧 SQL | | WH-017 | No Circular Foreign Keys | Data Quality | HIGH | — | | WH-018 | Sensitive Data Protected | Security | HIGH | 🔧 SQL | | WH-019 | Row-Level Security | Security | MEDIUM | — | | WH-020 | Minimal db_owner Privileges | Security | MEDIUM | — | | WH-021 | No Over-Complex Views | Maintainability | LOW | — | | WH-022 | Minimal Cross-Schema Dependencies | Maintainability | LOW | — | | WH-023 | No Very Slow Queries | Performance | HIGH | — | | WH-024 | No Frequently Slow Queries | Performance | HIGH | — | | WH-025 | No Recent Query Failures | Reliability | MEDIUM | — | | WH-026 | AUTO_UPDATE_STATISTICS Enabled | Performance | HIGH | 🔧 SQL | | WH-027 | Result Set Caching Enabled | Performance | MEDIUM | 🔧 SQL | | WH-028 | Snapshot Isolation Enabled | Concurrency | MEDIUM | 🔧 SQL | | WH-029 | Page Verify CHECKSUM | Reliability | MEDIUM | 🔧 SQL | | WH-030 | ANSI Settings Correct | Standards | LOW | 🔧 SQL | | WH-031 | Database ONLINE | Availability | HIGH | — | | WH-032 | All Tables Have Statistics | Performance | MEDIUM | 🔧 SQL | | WH-033 | Optimal Data Types | Performance | MEDIUM | — | | WH-034 | No Near-Empty Tables | Maintainability | LOW | — | | WH-035 | Stored Procedures Documented | Maintainability | LOW | — | | WH-036 | NOT NULL Columns Have Defaults | Data Quality | MEDIUM | 🔧 SQL | | WH-037 | Consistent String Types | Maintainability | LOW | — | | WH-038 | Schemas Are Documented | Maintainability | LOW | — | | WH-039 | Query Performance Healthy | Performance | MEDIUM | — |

| # | Rule | Category | Severity | Auto-Fix | |---|------|----------|----------|----------| | EH-001 | Query Endpoint Available | Availability | HIGH | — | | EH-002 | No Extent Fragmentation | Performance | HIGH | 🔧 KQL | | EH-003 | Good Compression Ratio | Performance | MEDIUM | — | | EH-004 | Caching Policy Configured | Performance | MEDIUM | 🔧 KQL | | EH-005 | Retention Policy Configured | Data Management | MEDIUM | 🔧 KQL | | EH-006 | Materialized Views Healthy | Reliability | HIGH | 🔧 KQL | | EH-007 | Data Is Fresh | Data Quality | MEDIUM | — | | EH-008 | No Slow Query Patterns | Performance | HIGH | — | | EH-009 | No Recent Failed Commands | Reliability | MEDIUM | — | | EH-010 | No Ingestion Failures | Reliability | HIGH | — | | EH-011 | Streaming Ingestion Config | Performance | INFO | — | | EH-012 | Continuous Exports Healthy | Reliability | MEDIUM | — | | EH-013 | Hot Cache Coverage | Performance | MEDIUM | — | | EH-014 | Ingestion Batching Configured | Performance | LOW | 🔧 KQL | | EH-015 | Update Policies Configured | Data Management | INFO | — | | EH-016 | Partitioning on Large Tables | Performance | MEDIUM | 🔧 KQL | | EH-017 | Merge Policy Configured | Performance | LOW | 🔧 KQL | | EH-018 | Encoding Policy for Poorly Compressed | Performance | MEDIUM | — | | EH-019 | Row Order Policy | Performance | LOW | — | | EH-020 | Stored Functions Inventory | Data Management | INFO | — |

| # | Rule | Category | Severity | Auto-Fix | |---|------|----------|----------|----------| | SM-001 | Avoid IFERROR Function | DAX | MEDIUM | 📓 Notebook | | SM-002 | Use DIVIDE Function | DAX | MEDIUM | 📓 Notebook | | SM-003 | No EVALUATEANDLOG in Production | DAX | HIGH | 📓 Notebook | | SM-004 | Use TREATAS not INTERSECT | DAX | MEDIUM | — | | SM-005 | No Duplicate Measure Definitions | DAX | LOW | — | | SM-006 | Filter by Columns Not Tables | DAX | MEDIUM | 📓 Notebook | | SM-007 | Avoid Adding 0 to Measures | DAX | LOW | — | | SM-008 | Measures Have Documentation | Maintenance | LOW | 🔧 model.bim + 📓 | | SM-009 | Model Has Tables | Maintenance | HIGH | — | | SM-010 | Model Has Date Table | Performance | MEDIUM | 🔧 model.bim | | SM-011 | Avoid 1-(x/y) Syntax | DAX | MEDIUM | — | | SM-012 | No Direct Measure References | DAX | LOW | — | | SM-013 | Avoid Nested CALCULATE | DAX | MEDIUM | — | | SM-014 | Use SUM Instead of SUMX | DAX | LOW | — | | SM-015 | Measures Have Format String | Formatting | LOW | 🔧 model.bim | | SM-016 | Avoid FILTER(ALL(...)) | DAX | MEDIUM | — | | SM-017 | Measure Naming Convention | Formatting | LOW | — | | SM-018 | Reasonable Table Count | Performance | LOW | — | | SM-B01 | No High Cardinality Text Columns | Data Types | HIGH | — | | SM-B02 | No Description/Comment Columns | Data Types | HIGH | — | | SM-B03 | No GUID/UUID Columns | Data Types | HIGH | — | | SM-B04 | No Constant Columns | Data Types | MEDIUM | — | | SM-B05 | No Booleans Stored as Text | Data Types | MEDIUM | — | | SM-B06 | No Dates Stored as Text | Data Types | MEDIUM | — | | SM-B07 | No Numbers Stored as Text | Data Types | MEDIUM | — | | SM-B08 | Integer Keys Not String Keys | Data Types | MEDIUM | — | | SM-B09 | No Excessively Wide Tables | Data Types | MEDIUM | — | | SM-B10 | No Extremely Wide Tables | Data Types | HIGH | — | | SM-B11 | No Multiple High-Cardinality Columns | Data Types | HIGH | — | | SM-B12 | No Single Column Tables | Data Types | LOW | — | | SM-B13 | No High-Precision Timestamps | Data Types | MEDIUM | — | | SM-B14 | No Low Cardinality in Fact Tables | Data Types | LOW | — |

🏗️ Architecture

src/
├── index.ts                    MCP server entry point (stdio transport)
├── auth/
│   └── fabricAuth.ts           Azure auth (CLI, browser, device code, SP)
├── clients/
│   ├── fabricClient.ts         Fabric REST API + DAX + model.bim CRUD
│   ├── sqlClient.ts            SQL via tedious (Lakehouse + Warehouse)
│   ├── kqlClient.ts            KQL/Kusto REST API (Eventhouse)
│   ├── onelakeClient.ts        OneLake ADLS Gen2 + Delta Log parser
│   └── xmlaClient.ts           XMLA SOAP client (experimental)
└── tools/
    ├── ruleEngine.ts           Shared RuleResult type + unified renderer
    ├── auth.ts                 auth_login, auth_status, auth_logout
    ├── workspace.ts            workspace_list
    ├── lakehouse.ts            29 rules + table maintenance
    ├── warehouse.ts            39 rules + 12 auto-fixes
    ├── eventhouse.ts           20 rules + 7 auto-fixes (with dry-run)
    └── semanticModel.ts        32 rules + 6 auto-fixes (model.bim)

🔐 Authentication

| Method | Use Case | |--------|----------| | azure_cli | Recommended — uses your az login session | | interactive_browser | Opens browser for interactive login | | device_code | Headless/remote environments | | vscode | Uses VS Code Azure account | | service_principal | CI/CD (requires tenantId, clientId, clientSecret) | | default | Auto-detect best available method |

📄 License

MIT