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)

1. powerbi_run_dax - Execute DAX queries with validation and optimization

2. powerbi_validate_dax - Validate DAX syntax and best practices

3. powerbi_generate_template - Generate DAX from predefined templates

4. powerbi_get_schema - Retrieve dataset schema information

5. powerbi_list_datasets - List available datasets with details

6. powerbi_health - System health check and connectivity test

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

Related MCP server: pbix-mcp

📋 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

1. Enhanced Server (server-enhanced.ts) - Main MCP server with all features

2. DAX Validator (validators.ts) - Syntax validation and optimization

3. Template Engine (templates.ts) - DAX template generation

4. PowerBI Service (enhanced-powerbi.ts) - Power BI API interactions

5. Logging System (logging.ts) - Comprehensive logging and telemetry

6. Resource Manager (resources.ts) - Dynamic resource management

7. Prompt System (prompts.ts) - Intelligent DAX assistance

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

1. Authentication Errors

   # Check Azure credentials
   az account show
   
   # Re-authenticate if needed
   az login

2. XMLA Endpoint Issues

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

3. 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:

1. Fork the repository

2. Create a feature branch

3. Make your changes

4. Add tests if applicable

5. 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:

1. Check the troubleshooting section above

2. Review the logs for error details

3. Use the health check tool for diagnostics

4. 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! 🎉