# 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

## 📋 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:

```bash
# 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

```bash
# 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

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

### DAX Validation

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

### Template Generation

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

### Schema Analysis

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

### System Health Check

```javascript
// 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:

```javascript
// 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:

```javascript
// 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**
   ```bash
   # Check Azure credentials
   az account show
   
   # Re-authenticate if needed
   az login
   ```

2. **XMLA Endpoint Issues**
   ```bash
   # Verify workspace access
   # Ensure Premium capacity
   # Check XMLA endpoint format
   ```

3. **Build Errors**
   ```bash
   # Clean and rebuild
   npm run typecheck
   npm run build
   ```

### Health Check

Use the health check tool to diagnose issues:

```javascript
{
  "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

```bash
# 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! 🎉