pbi-dax-mcp-server by vjsr007 - MCP Server

Enhanced Power BI DAX MCP Server v1.0.0

🚀 A comprehensive Model Context Protocol (MCP) server for Power BI DAX query execution, validation, optimization, and intelligent assistance.

🌟 Features

Core Capabilities

DAX Query Execution: Execute DAX queries against Power BI datasets with enhanced error handling
Syntax Validation: Comprehensive DAX syntax checking and validation
Performance Optimization: Advanced query optimization and performance analysis
Template Engine: Pre-built DAX templates for common calculations and patterns
Schema Analysis: Detailed dataset schema introspection and analysis
Intelligent Prompts: AI-powered assistance for DAX development
Error Recovery: Robust error handling with automatic recovery strategies
Comprehensive Logging: Performance monitoring and telemetry collection

Enhanced Tools (7 Total)

powerbi_run_dax - Execute DAX queries with validation and optimization
powerbi_validate_dax - Validate DAX syntax and best practices
powerbi_generate_template - Generate DAX from predefined templates
powerbi_get_schema - Retrieve dataset schema information
powerbi_list_datasets - List available datasets with details
powerbi_health - System health check and connectivity test
powerbi_list_templates - Browse available DAX templates

Resources (2 Categories)

powerbi://docs/{type} - Access documentation and examples
powerbi://templates/{category} - Access categorized DAX templates

Intelligent Prompts (3 Types)

analyze_dax - Analyze DAX queries for insights and improvements
generate_dax_solution - Generate DAX solutions from requirements
explain_dax_concept - Explain DAX concepts with examples

📋 Prerequisites

Node.js 18.0.0 or higher
Access to Power BI Premium workspace
Azure AD authentication credentials

🛠️ Installation & Setup

1. Environment Configuration

Create a .env file with the following variables:

# Required: Power BI XMLA Endpoint
POWERBI_XMLA_ENDPOINT=powerbi://api.powerbi.com/v1.0/myorg/YourWorkspace

# Azure Authentication (choose one method)

# Method 1: Service Principal (Recommended for production)
AZURE_TENANT_ID=your-tenant-id
AZURE_CLIENT_ID=your-client-id
AZURE_CLIENT_SECRET=your-client-secret

# Method 2: User Authentication (for development)
# Use 'az login' before running the server

2. Build and Start

# Install dependencies
npm install

# Build the project
npm run build

# Start the enhanced server
npm run start:enhanced

# Or for development with hot reload
npm run dev:enhanced

🔧 Usage Examples

Basic DAX Execution

// Execute a simple DAX query
{
  "tool": "powerbi_run_dax",
  "parameters": {
    "query": "EVALUATE VALUES(DimProduct[ProductName])",
    "validate": true
  }
}

DAX Validation

// Validate DAX syntax before execution
{
  "tool": "powerbi_validate_dax", 
  "parameters": {
    "query": "CALCULATE(SUM(Sales[Amount]), FILTER(DimDate, DimDate[Year] = 2024))"
  }
}

Template Generation

// Generate Year-to-Date calculation
{
  "tool": "powerbi_generate_template",
  "parameters": {
    "templateType": "year_to_date",
    "tableName": "Sales", 
    "measureName": "YTD Sales"
  }
}

Schema Analysis

// Get dataset schema information
{
  "tool": "powerbi_get_schema",
  "parameters": {
    "datasetId": "your-dataset-id"
  }
}

System Health Check

// Check server health and connectivity
{
  "tool": "powerbi_health",
  "parameters": {
    "testConnectivity": true
  }
}

🎯 Advanced Features

DAX Templates

The server includes pre-built templates for common calculations:

Year to Date (YTD): year_to_date
Previous Year: previous_year
Year over Year Growth: year_over_year_growth
Moving Average: moving_average
ABC Analysis: abc_analysis
Time Intelligence: time_intelligence

Intelligent Prompts

Use AI-powered assistance for DAX development:

// Analyze a DAX query
{
  "prompt": "analyze_dax",
  "parameters": {
    "query": "CALCULATE(SUM(Sales[Amount]), Sales[Year] = 2024)"
  }
}

// Generate DAX from business requirements
{
  "prompt": "generate_dax_solution",
  "parameters": {
    "requirements": "Calculate total sales for the current year compared to last year"
  }
}

// Explain DAX concepts
{
  "prompt": "explain_dax_concept",
  "parameters": {
    "concept": "CALCULATE function"
  }
}

Resource Access

Access documentation and examples:

// Get DAX function documentation
GET powerbi://docs/functions

// Get template examples
GET powerbi://templates/time_intelligence

🏗️ Architecture

Core Components

Enhanced Server (server-enhanced.ts) - Main MCP server with all features
DAX Validator (validators.ts) - Syntax validation and optimization
Template Engine (templates.ts) - DAX template generation
PowerBI Service (enhanced-powerbi.ts) - Power BI API interactions
Logging System (logging.ts) - Comprehensive logging and telemetry
Resource Manager (resources.ts) - Dynamic resource management
Prompt System (prompts.ts) - Intelligent DAX assistance
Error Handling (error-handling.ts) - Robust error recovery

Error Handling

The server includes comprehensive error handling with:

Pattern Matching: Automatic error categorization
Recovery Strategies: Automatic retry and fallback mechanisms
User-Friendly Messages: Clear error explanations and suggestions
Correlation IDs: Error tracking and debugging support

Performance Monitoring

Built-in performance monitoring includes:

Query execution times
Error tracking and categorization
Usage statistics and telemetry
Health metrics and system status

🔍 Troubleshooting

Common Issues

Authentication Errors

# Check Azure credentials
az account show

# Re-authenticate if needed
az login

XMLA Endpoint Issues

# Verify workspace access
# Ensure Premium capacity
# Check XMLA endpoint format

Build Errors

# Clean and rebuild
npm run typecheck
npm run build

Health Check

Use the health check tool to diagnose issues:

{
  "tool": "powerbi_health",
  "parameters": {
    "testConnectivity": true
  }
}

📊 Monitoring & Logging

The server provides comprehensive logging:

Info Level: Normal operations and metrics
Warn Level: Validation warnings and performance issues
Error Level: Errors with correlation IDs and recovery attempts
Debug Level: Detailed execution traces

Log format includes:

Timestamp
Log level
Operation context
Performance metrics
Correlation IDs for error tracking

🔐 Security Considerations

Credential Management: Store credentials securely using environment variables
Access Control: Ensure proper Power BI workspace permissions
Network Security: Use HTTPS for all Power BI API communications
Audit Logging: All operations are logged for security auditing

🚀 Development

Project Structure

src/
├── server.ts              # Original basic server
├── server-enhanced.ts     # Enhanced server with all features
├── powerbi.ts            # Basic Power BI operations
├── enhanced-powerbi.ts   # Advanced Power BI service
├── validators.ts         # DAX validation engine
├── templates.ts          # DAX template engine
├── logging.ts           # Logging and telemetry
├── resources.ts         # Resource management
├── prompts.ts           # Intelligent prompt system
├── error-handling.ts    # Error handling and recovery
├── types.ts             # TypeScript type definitions
└── cli.ts               # Command-line interface

Development Commands

# Development with hot reload
npm run dev:enhanced

# Type checking
npm run typecheck

# Build for production
npm run build

# CLI commands
npm run health
npm run list
npm run validate
npm run template

📈 Performance

The enhanced server includes optimizations for:

Query Caching: Intelligent caching of dataset schemas and metadata
Connection Pooling: Efficient Power BI API connection management
Async Operations: Non-blocking query execution
Memory Management: Efficient resource usage and cleanup

🤝 Contributing

We welcome contributions! Please:

Fork the repository
Create a feature branch
Make your changes
Add tests if applicable
Submit a pull request

Code Style

Follow TypeScript best practices
Use meaningful variable and function names
Add comprehensive comments for complex logic
Maintain consistent formatting

📄 License

MIT License - see LICENSE file for details.

🆘 Support

For support and questions:

Check the troubleshooting section above
Review the logs for error details
Use the health check tool for diagnostics
Open an issue with detailed error information

🗺️ Roadmap

Future enhancements planned:

Advanced Analytics: Statistical analysis of DAX queries
Query Optimization: ML-powered query optimization suggestions
Visual Query Builder: Graphical DAX query construction
Real-time Monitoring: Live performance dashboards
Integration Extensions: Support for additional BI tools

Enhanced Power BI DAX MCP Server v1.0.0 - Transforming DAX development with intelligent assistance and comprehensive tooling! 🎉