# Local BI Assistant

A fine-tuned local LLM for business intelligence that generates SQL queries from natural language, served via an MCP server.

**No API costs. No data leaves your network. Runs entirely on your laptop.**

## Features

- **Fine-tuned Mistral 7B** trained on your dbt schema and query history
- **MCP Server** with 5 tools: query_data, suggest_visualization, get_insights, explore_schema, execute_sql
- **Local inference** via Ollama - no external API calls
- **CI/CD integration** - automatically retrain when your schema changes
- **Docker support** - one-command setup for training and runtime

## Architecture

![Architecture](assets/architecture.svg)

## Quick Start

### Option 1: Docker (Recommended)

```bash
# Clone the repository
git clone https://github.com/janovincze/local_bi_assistant.git
cd local_bi_assistant

# Start the BI assistant (uses base Mistral model for testing)
docker compose up bi-assistant

# Or with development mode (hot reload)
docker compose --profile dev up
```

### Option 2: Local Setup with uv (Recommended for Mac)

[uv](https://github.com/astral-sh/uv) is a fast Python package installer (10-100x faster than pip).

```bash
# Install uv (if not already installed)
curl -LsSf https://astral.sh/uv/install.sh | sh

# Clone and enter directory
git clone https://github.com/janovincze/local_bi_assistant.git
cd local_bi_assistant

# Create venv and install dependencies (with training support)
uv venv
uv sync --extra train-local

# Activate virtual environment
source .venv/bin/activate

# Build dbt models (creates sample database)
cd dbt_project && dbt build && cd ..

# Prepare training data
python -m training.scripts.prepare_data

# Train the model (QLoRA on Mac M4, ~2 hours)
python -m training.scripts.train_local

# Create Ollama model
ollama create bi-assistant -f output/mlx_lora/Modelfile

# Start MCP server
python -m mcp_server.server
```

### Option 3: Local Setup with pip

```bash
# Clone and enter directory
git clone https://github.com/janovincze/local_bi_assistant.git
cd local_bi_assistant

# Create virtual environment
python -m venv venv
source venv/bin/activate

# Install dependencies
pip install -e ".[train-local]"

# Build dbt models
cd dbt_project && dbt build && cd ..

# Prepare training data
python -m training.scripts.prepare_data

# Train the model (QLoRA on Mac M4)
python -m training.scripts.train_local

# Create Ollama model
ollama create bi-assistant -f output/mlx_lora/Modelfile

# Start MCP server
python -m mcp_server.server
```

## Using with Claude Desktop

Add to your Claude Desktop MCP configuration:

```json
{
  "mcpServers": {
    "bi-assistant": {
      "command": "python",
      "args": ["-m", "mcp_server.server"],
      "cwd": "/path/to/local_bi_assistant",
      "env": {
        "DUCKDB_PATH": "./dbt_project/jaffle_shop.duckdb",
        "BI_MODEL": "bi-assistant:latest"
      }
    }
  }
}
```

## Available Tools

| Tool | Description |
|------|-------------|
| `query_data` | Convert natural language to SQL and execute |
| `suggest_visualization` | Recommend chart types for data |
| `get_data_insights` | Generate automated data insights |
| `explore_schema` | Discover tables and columns |
| `execute_sql` | Run SQL queries directly |

## Training Your Own Model

### Model Options

![Benchmark Results](assets/benchmark_cover.svg)

| Model | Accuracy | Latency | Best For |
|-------|----------|---------|----------|
| Fine-tuned Mistral | 100% | ~5s | General BI queries, complex reasoning |
| Fine-tuned SQLCoder | 94% | ~7s | SQL-heavy workloads |
| Base SQLCoder | 88% | ~2.5s | Quick queries, speed priority |
| "Senior" Data Engineer* | ~80% | ~29 min | Coffee breaks |

*\*Based on 20+ interviews. Your fine-tuned 7B model might be their new colleague.*

*Note: 100% on sample schema; expect 93-95% on complex production schemas.*

### Train Mistral (Default)

```bash
python -m training.scripts.train_local
ollama create bi-assistant -f output/mlx_lora/Modelfile
```

### Train SQLCoder (Alternative)

```bash
python -m training.scripts.train_sqlcoder
# Uses MLX with adapters - no Ollama model needed
# Automatically detected by MCP server
```

### From Query History

Place your query logs in `data/query_history/` in one of these formats:
- JSON: `[{"question": "...", "sql": "..."}, ...]`
- JSONL: One JSON object per line
- SQL comments: `-- Q: question\n-- A:\nSELECT ...`

```bash
python -m training.scripts.prepare_data \
  --dbt-project ./dbt_project \
  --query-logs ./data/query_history \
  --output ./data/training
```

### CI/CD Integration

The included GitHub Actions workflow automatically retrains when:
- dbt models change (`dbt_project/models/**`)
- Training code changes (`training/**`)

Configure secrets:
- `HF_TOKEN`: Hugging Face API token
- `WANDB_API_KEY`: Weights & Biases key (optional)

## Benchmarking

Compare model performance on your queries:

```bash
python scripts/benchmark_models.py
```

This tests all available models (Ollama and MLX) against sample queries and reports accuracy and latency.

## Memory Requirements

| Operation | Memory |
|-----------|--------|
| Inference (4-bit) | ~5 GB |
| QLoRA Training | ~7 GB |
| Full Fine-tuning | ~16 GB (GPU) |

Works on Mac M4 with 24GB RAM.

## License

MIT