Content
# mcp-analytics-lab
A local analytics laboratory that bridges a **LangGraph ReAct agent** with **Microsoft SQL Server** via the **Model Context Protocol (MCP)**. The agent (backed by locally-running Ollama) answers natural-language questions by dynamically calling MCP tools.
---
## Prerequisites
- Python 3.11+
- [Ollama](https://ollama.com) running locally with `llama3.2` pulled (`ollama pull llama3.2`)
- Microsoft SQL Server with ODBC Driver 17 installed
- [Docker Desktop](https://www.docker.com/products/docker-desktop/) (for the Docker option)
---
## Setup
```bash
# 1. Copy and fill in your credentials
cp .env.example .env
# 2. Install dependencies
pip install -r requirements.txt
# 3. Create and seed the database (one-time)
python scripts/seed_db.py
```
---
## Running
### Option A — Docker (recommended)
No manual dependency setup needed — ODBC Driver 17 is installed inside the container.
```bash
# Build images (once, or after any code change)
docker compose build
# Start both services
docker compose up
# In a second terminal — attach to the agent REPL
docker attach mcp-analytics-lab-agent-1
# Stop everything
docker compose down
```
> **Note:** Set `DB_INSTANCE` in your `.env` to your SQL Server instance name (e.g. `SQLEXPRESS` or `TAREK20`). Docker uses this to build the connection string `host.docker.internal\<DB_INSTANCE>`.
### Option B — Local (manual)
```bash
# Terminal 1 — start the MCP server (defaults to Streamable HTTP on :8000)
python servers/sql_server.py
# Terminal 2 — run the agent
python agent/agent.py
```
> The server runs over Streamable HTTP by default. Pass `--stdio` to run it over stdio instead.
---
## Architecture
```
User REPL
└─► agent/agent.py (LangGraph ReAct, Ollama llama3.2)
└─► MCP HTTP http://localhost:8000/mcp
└─► servers/sql_server.py (FastMCP)
└─► pyodbc → SQL Server analytics_db
```
See `docs/agent_flow.md` for detailed flow diagrams and architecture notes.
---
## Key Files
| File | Role |
|---|---|
| `agent/agent.py` | LangGraph agent, REPL, streaming, MCP client wiring |
| `servers/sql_server.py` | FastMCP server — exposes `get_database_schema` and `query_sales_db` tools plus `config://kpi-definitions` resource |
| `config/kpi_definitions.json` | 7 pre-defined KPIs with SQL formulas injected into the agent's system prompt |
| `scripts/seed_db.py` | Creates `analytics_db` with `products` (3 rows) and `sales` (18 rows) tables |
| `Dockerfile` | Builds a Linux image with ODBC Driver 17 + Python dependencies |
| `docker-compose.yml` | Runs MCP server and agent as two connected containers |
| `docs/agent_flow.md` | Mermaid diagrams and detailed notes on every architectural decision |
---
## Environment Variables
| Variable | Default | Purpose |
|---|---|---|
| `DB_DRIVER` | — | ODBC driver name |
| `DB_SERVER` | — | SQL Server instance (local dev) |
| `DB_INSTANCE` | — | Instance name only — used by Docker to construct `host.docker.internal\INSTANCE` |
| `DB_NAME` | `analytics_db` | Database name |
| `DB_UID` / `DB_PWD` | — | SQL Server credentials |
| `OLLAMA_MODEL` | `llama3.2` | Local Ollama model name |
| `OLLAMA_BASE_URL` | `http://localhost:11434` | Ollama endpoint (Docker overrides to `host.docker.internal`) |
| `MCP_URL` | `http://localhost:8000/mcp` | MCP server endpoint |
| `MAX_ITERATIONS` | `3` | ReAct loop iteration cap |
| `FASTMCP_HOST` | `127.0.0.1` | MCP server bind address (Docker overrides to `0.0.0.0`) |
| `LANGCHAIN_TRACING_V2` | — | Set to `true` to enable LangSmith traces |
| `LANGCHAIN_API_KEY` | — | LangSmith API key |
| `LANGCHAIN_PROJECT` | — | LangSmith project name |
---
## Debugging
> Examples use PowerShell (Windows). The server runs over Streamable HTTP on `http://localhost:8000/mcp`.
### 1. Is the server listening?
```powershell
Get-NetTCPConnection -LocalPort 8000 -ErrorAction SilentlyContinue
```
No rows = the server isn't running or crashed. Check Terminal 1 for a traceback (most often a DB connection error on the first tool call).
### 2. `406 Not Acceptable` on `GET /mcp` is normal
The Streamable HTTP endpoint only accepts `POST` with the right headers, so opening the URL in a browser *should* return 406 — that is not a bug. Test it properly with the MCP Inspector (below) or `curl.exe` with the handshake:
```powershell
curl.exe -i -X POST http://localhost:8000/mcp `
-H "Content-Type: application/json" `
-H "Accept: application/json, text/event-stream" `
-d '{\"jsonrpc\":\"2.0\",\"id\":1,\"method\":\"initialize\",\"params\":{\"protocolVersion\":\"2025-06-18\",\"capabilities\":{},\"clientInfo\":{\"name\":\"curl\",\"version\":\"1.0\"}}}'
```
A `200` with a `text/event-stream` body = the server is healthy.
### 3. MCP Inspector (interactive debugger)
```bash
npm install -g @modelcontextprotocol/inspector
```
```powershell
$env:DANGEROUSLY_OMIT_AUTH="true"; npx @modelcontextprotocol/inspector
```
Open `http://localhost:6274` → Transport **Streamable HTTP** → URL `http://localhost:8000/mcp` → **Connect**, then:
- **Tools** → run `get_database_schema` (no args) and `query_sales_db` with `SELECT TOP 5 * FROM sales`. Write ops (e.g. `DELETE FROM sales`) are rejected.
- **Resources** → read `config://kpi-definitions` (returns the 7 KPI definitions).
If Inspector shows *Connection error / reason: ""*, the server on 8000 isn't running (see step 1), or switch Connection Type from **Via Proxy** to **Direct**.
### 4. Watch the SQL the server runs
The server logs at `INFO`, so every query and row count prints in Terminal 1:
```
INFO:servers.sql_server:SQL: SELECT region, SUM(revenue) ...
INFO:servers.sql_server:Returned 4 rows
```
### 5. Database connection failures
Tool calls return `{"error": "..."}` or the server throws on first query:
| Error | Cause / fix |
|---|---|
| `IM002 Data source name not found` | `DB_DRIVER` in `.env` doesn't match an installed driver. List them: `Get-OdbcDriver \| Select-Object Name` |
| `Login failed for user 'sa'` | SQL Server Authentication disabled or wrong `DB_PWD` |
| `Cannot open database "analytics_db"` | Run `python scripts/seed_db.py` |
### 6. Agent-side issues
- `ERROR: Could not connect to MCP server at http://localhost:8000/mcp` → start the server (Terminal 1) first; make sure it wasn't started with `--stdio`.
- Agent loops or gives up → it hit `MAX_ITERATIONS` (default 3). Raise it: `$env:MAX_ITERATIONS="5"; python agent/agent.py`.
- With `LANGCHAIN_TRACING_V2=true` in `.env`, full agent traces (every tool call + LLM step) appear in **LangSmith** under project `mcp-analytics-lab` — the best way to see *why* the agent chose a query.
### 7. Clean restart (frees stuck ports)
```powershell
@(8000,6274,6277) | % { Get-NetTCPConnection -LocalPort $_ -EA 0 | % { Stop-Process -Id $_.OwningProcess -Force -EA 0 } }
```
