Enhanced Power BI DAX Server

🚀 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

📋 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! 🎉