Force-Fabric-MCP-Server-main

<h1 align="center">Force Fabric MCP Server</h1> <p align="center"> <strong>Detect issues. Auto-fix problems. Optimize your Fabric tenant.</strong><br> An MCP server that scans Lakehouses, Warehouses, Eventhouses, and Semantic Models with 120 rules — and can auto-fix 45 of them. </p> <p align="center"> <a href="#-quick-start">Quick Start</a> • <a href="#-detect--scan">Detect</a> • <a href="#-auto-fix">Auto-Fix</a> • <a href="#-rule-reference">Rules</a> • <a href="#-architecture">Architecture</a> </p> --- ## ✨ 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 ```bash 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: ```json { "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** | <details> <summary><strong>🏠 Lakehouse — 29 Rules</strong> (click to expand)</summary> | # | 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 | </details> <details> <summary><strong>🏗️ Warehouse — 39 Rules</strong> (click to expand)</summary> | # | 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 | — | </details> <details> <summary><strong>📊 Eventhouse — 20 Rules per KQL Database</strong> (click to expand)</summary> | # | 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 | — | </details> <details> <summary><strong>📐 Semantic Model — 32 Rules</strong> (click to expand)</summary> | # | 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 | — | </details> --- ## 🏗️ 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