Fabric MCP Agent

# fabric-mcp-agent **🆕 Enhanced MVP with Multi-Stage Intelligence** - A complete two-layer system combining an MCP-compliant server with advanced multi-stage agentic AI reasoning for Microsoft Fabric Data Warehouse access. ## 🎯 MVP Status: **ENHANCED** ✅ **🆕 Major Update**: Now features intelligent multi-stage execution with discovery → analysis → evaluation workflows for complex business intelligence queries. ## 🔷 Architecture Overview ### Layer 1: Fabric DW MCP Server Standards-compliant MCP server with 4 complete tools providing clean abstractions over Fabric Data Warehouse operations with full Azure AD authentication. ### Layer 2: **🆕 Multi-Stage Agentic Reasoning Engine** Advanced intelligent system with **3 execution strategies**: - **Single-Stage**: Simple queries → Standard tool chain - **🆕 Multi-Stage**: Complex queries → Discovery → Analysis → Evaluation - **🆕 Iterative**: Advanced queries → Refinement loops (future) **🆕 Separation of Concerns Architecture**: - **Intent Templates**: Domain-agnostic execution patterns (`agentic_layer/prompts/intent/`) - **Persona Modules**: Business domain expertise (`agentic_layer/prompts/personas/`) - **Runtime Integration**: Dynamic combination for context-aware execution ## 🚀 Production Features ### ✅ Complete MCP Tools - **`run_sql_query`**: Execute SQL from natural language questions or direct SQL with full error handling - **`get_metadata`**: Retrieve comprehensive table schemas, sample data, and relationships - **`summarize_results`**: Generate business-friendly summaries with actionable insights - **`generate_visualization`**: Create formatted data tables and chart configurations ### ✅ **🆕 Advanced Multi-Stage Intelligence** - **Intelligent Execution Strategy**: Automatic selection between single-stage and multi-stage workflows - **🆕 3-Stage Discovery Process**: Discovery → Analysis → Evaluation with AI-driven transitions - **🆕 Domain-Agnostic Templates**: Reusable execution patterns that work across all business domains - **🆕 Persona-Driven Context**: Business expertise modules for domain-specific knowledge - **🆕 Pure Business Analysis**: Stage 3 provides structured insights without SQL execution - **Enhanced JSON Parsing**: Robust handling of complex business responses with intelligent fallbacks - **Azure OpenAI Caching**: Automatic response optimization for repeated queries ### ✅ Enterprise Features - **🆕 Token Usage Optimization**: Data compression reducing token usage by 50-80% - **🆕 Session-Based Logging**: Complete session traces in `logs/sessions/` for easy debugging - **Performance Monitoring**: Real-time cost tracking and compression statistics - **Error Tracking**: Full error context with automated recovery mechanisms - **Security**: Azure AD authentication with read-only database access ## 🔄 **🆕 Multi-Stage Execution Flow** **Enhanced intelligent query processing with adaptive execution strategies:** ### Single-Stage Flow (Simple Queries) ``` User: "Show me specifications for MRH-011C" ↓ Intent Classification → Single-Stage Strategy ↓ Load Persona: product_planning.md ↓ SQL Generation + Execution → Results ``` ### **🆕 Multi-Stage Flow (Complex Queries)** ``` User: "Replace BD Luer-Lock Syringe 2.5mL with equivalent domestic product and pricing" ↓ Intent Classification → Multi-Stage Strategy + spt_sales_rep persona ↓ Stage 1: Discovery Template: stage1_discovery.md + Persona Context → Find candidate products matching criteria ↓ AI Intermediate Processing → Analyze Stage 1 results → Select best matches ↓ Stage 2: Analysis Template: stage2_analysis.md + Selected Candidates → Get detailed pricing and specifications ↓ Stage 3: Evaluation Template: stage3_evaluation.md + All Previous Data → Pure business analysis (NO SQL) → Structured insights ``` **🆕 Key Innovation**: Domain-agnostic templates + business personas = context-aware execution ## 📋 API Endpoints ### MCP Standard Endpoints - **`GET /list_tools`** - Returns all available MCP tools with schemas - **`POST /call_tool`** - Execute specific MCP tool with arguments ### Agentic Intelligence Endpoint - **`POST /mcp`** - Full agentic reasoning with intent classification and tool chaining ## 🧪 Quick Start & Testing ### 1. Start the Server ```bash python main.py ``` (Ensure `.env` is configured with Azure credentials) ### 2. Test MCP Tools Discovery ```bash curl http://localhost:8000/list_tools ``` ### 3. Test Individual MCP Tools ```bash # Get table metadata curl -X POST http://localhost:8000/call_tool -H "Content-Type: application/json" \ -d '{"tool": "get_metadata", "args": {"table_name": "JPNPROdb_ps_mstr"}}' # Execute SQL query curl -X POST http://localhost:8000/call_tool -H "Content-Type: application/json" \ -d '{"tool": "run_sql_query", "args": {"question": "Show me active products"}}' ``` ### 4. **🆕 Test Multi-Stage Intelligence (Recommended)** ```bash # Simple query (single-stage execution) curl -X POST http://localhost:8000/mcp -H "Content-Type: application/json" \ -d '{"question": "tell me the components in MRH-011C"}' # 🆕 Complex query (multi-stage execution) curl -X POST http://localhost:8000/mcp -H "Content-Type: application/json" \ -d '{"question": "Replace BD Luer-Lock Syringe 2.5mL with equivalent domestic product and pricing"}' # 🆕 Multi-stage product analysis curl -X POST http://localhost:8000/mcp -H "Content-Type: application/json" \ -d '{"question": "Analyze components and pricing for MRH-011C and recommend optimization opportunities"}' ``` ### 5. **🆕 Session Debugging & Monitoring** ```bash # View recent session logs with optimization stats python view_session.py # View detailed session trace (compression, tokens, cost) python view_session.py 1 # List all session files ls logs/sessions/ ``` ### 6. Access the Web UI ```bash # Open your browser and visit: http://localhost:8000 ``` ## 🎯 **🆕 Enhanced Response Examples** ### Single-Stage Response (Simple Query) ```json { "classification": { "intent": "product_specification_lookup", "persona": "product_planning", "execution_strategy": "single_stage", "confidence": 0.95 }, "tool_chain_results": { "run_sql_query": {"results": [...]}, "summarize_results": {...} }, "final_response": "**Product MRH-011C specifications:**..." } ``` ### **🆕 Multi-Stage Response (Complex Query)** ```json { "classification": { "intent": "competitive_replacement_analysis", "persona": "spt_sales_rep", "execution_strategy": "multi_stage", "confidence": 0.92 }, "tool_chain_results": { "stage1_query": {"results": [...]}, "intermediate_analysis": {"selected_items": ["08-139-NPR"]}, "stage2_query": {"results": [...]}, "stage3_evaluation": { "business_answer": "Equivalent product identified: 08-139-NPR...", "key_findings": ["22-37% cost savings", "Multiple kit options"], "recommended_action": "Recommend 08-139-NPR as primary replacement...", "confidence": "high" } }, "final_response": "**Equivalent products identified with 22-37% cost savings...**" } ``` ## 🌐 **🆕 Enhanced Production Web UI** - **🆕 Multi-Stage Result Rendering**: Structured business analysis display with confidence indicators - **🆕 Business Analysis Section**: Clear presentation of Stage 3 evaluation with findings and recommendations - **🆕 Progressive Disclosure**: Primary insights first, detailed data on demand - **🆕 Smart Result Detection**: Automatic detection of single-stage vs multi-stage responses - **Enhanced Data Tables**: Interactive SQL results with sortable columns and hover effects - **Prompt Management**: Live editing of persona modules with automatic backup - **Real-time Testing**: All execution strategies accessible through responsive interface - **Quick Test Buttons**: Pre-built queries for both simple and complex business scenarios ## Configuration The server requires the following environment variables in a `.env` file located in the project root: | Variable | Description | |-------------------------|------------------------------------------------------------------| | FABRIC_SQL_SERVER | Fully qualified Fabric Data Warehouse server hostname | | FABRIC_SQL_DATABASE | Target database name in Fabric | | AZURE_CLIENT_ID | Azure Service Principal client ID (for AAD authentication) | | AZURE_CLIENT_SECRET | Azure Service Principal secret | | AZURE_TENANT_ID | Azure tenant (directory) ID | | AZURE_OPENAI_KEY | API key for your Azure OpenAI deployment | | AZURE_OPENAI_ENDPOINT | Endpoint URL for Azure OpenAI (e.g., https://xxxx.openai.azure.com) | | AZURE_OPENAI_DEPLOYMENT | Deployment name (e.g., "gpt-4o") | ### Sample `.env` ```env FABRIC_SQL_SERVER=jzd3bvvlcs5udln5rq47r4qvqi-qdrgdhglbgcezlr5igxskwv6ki.datawarehouse.fabric.microsoft.com FABRIC_SQL_DATABASE=unified_data_warehouse AZURE_CLIENT_ID=<your-azure-service-principal-client-id> AZURE_CLIENT_SECRET=<your-azure-service-principal-secret> AZURE_TENANT_ID=<your-azure-tenant-id> AZURE_OPENAI_KEY=<your-azure-openai-key> AZURE_OPENAI_ENDPOINT=https://<your-resource>.openai.azure.com AZURE_OPENAI_DEPLOYMENT=gpt-4o ``` ## 📊 **🆕 Enhanced Performance Monitoring** ### **🆕 Multi-Stage Performance Analysis** **Current Baseline**: 40.7s total execution time | Stage | Duration | Operations | Optimization Target | |-------|----------|------------|-------------------| | Intent Classification | 3.4s (8.3%) | LLM routing | Caching patterns | | Stage 1: Discovery | 14.4s (35.4%) | SQL generation + execution | **50%+ reduction** | | Stage 2: Analysis | 15.7s (38.5%) | SQL generation + execution | **50%+ reduction** | | Stage 3: Evaluation | 7.1s (17.4%) | Pure LLM analysis | Prompt optimization | ### Real-time Dashboard ```bash python performance_dashboard.py ``` ### **🆕 Enhanced Metrics Output** ``` MCP AGENT PERFORMANCE DASHBOARD - MULTI-STAGE ANALYTICS ================================================================================ EXECUTION STRATEGY BREAKDOWN Single-Stage Queries: 60% (avg 12.8s) Multi-Stage Queries: 40% (avg 40.7s) STAGE-LEVEL PERFORMANCE Stage 1 Discovery: 14.4s avg Stage 2 Analysis: 15.7s avg Stage 3 Evaluation: 7.1s avg SQL Operations: 74% of total time OPTIMIZATION OPPORTUNITIES High Impact: SQL generation caching (60-70% reduction potential) Medium Impact: Parallel processing (20-30% reduction) ``` ## 🚀 **🆕 Enhanced Production Deployment** This enhanced MVP is ready for production deployment with: - ✅ **🆕 Multi-stage intelligent execution** with adaptive strategy selection - ✅ **🆕 Structured business analysis** with confidence indicators and recommendations - ✅ **🆕 Domain-agnostic architecture** for rapid business domain expansion - ✅ **🆕 Enhanced UI rendering** with progressive disclosure and business insights - ✅ Full error handling and recovery with intelligent JSON parsing fallbacks - ✅ Comprehensive logging and monitoring with stage-level performance analytics - ✅ Performance optimization with AI caching and clear optimization roadmap - ✅ Security best practices implemented - ✅ Scalable architecture for extension ## 📚 **🆕 Comprehensive Documentation** - **[DESIGN_ARCHITECTURE.md](DESIGN_ARCHITECTURE.md)** - Complete system architecture with multi-stage workflow details - **[CLAUDE.md](CLAUDE.md)** - Development guide with enhanced testing commands and prompt structure - **[agentic_layer/prompts/intent/README.md](agentic_layer/prompts/intent/README.md)** - Intent template framework documentation - **[UI_DOCUMENTATION.md](UI_DOCUMENTATION.md)** - Enhanced web interface with multi-stage result rendering - **[API_RESPONSE_EXAMPLES.md](API_RESPONSE_EXAMPLES.md)** - Complete API response examples for all execution strategies - **[PERFORMANCE_OPTIMIZATION.md](PERFORMANCE_OPTIMIZATION.md)** - Detailed optimization roadmap with specific targets and implementation phases **🎯 Ready for Enterprise**: Complete documentation, performance analysis, and optimization roadmap for production scaling.