# SSAS MCP Server

A read-only [Model Context Protocol](https://modelcontextprotocol.io) server for querying
**SQL Server Analysis Services** — both **multidimensional cubes (MDX)** and **tabular
models (DAX)** — with **Windows Integrated Security** (Kerberos/NTLM, no passwords).

Companion to [mssql_mcp_server](https://github.com/dbdave/mssql_mcp_server), but for the
OLAP side of the house.

## Features

- **`list_metadata`** — discover a cube/model's measures, dimensions and hierarchies
  (via `MDSCHEMA_*` DMVs, which work for both model types), so the agent never guesses
  bracketed unique names. The overview stays compact — measures, dimension names and
  multi-level user hierarchies; pass `dimension="[Dim Event]"` to drill into one
  dimension's full attribute hierarchies and levels (a whole cube's attribute list can
  run to tens of thousands of tokens).
- **`execute_query`** — run an MDX (`SELECT`/`WITH`) or DAX (`EVALUATE`/`DEFINE`) query
  and get a flattened JSON table back.
- **`execute_dmv`** — guarded passthrough for `SELECT ... FROM $SYSTEM.*` schema rowsets
  (`MDSCHEMA_*`, `TMSCHEMA_*`, `DISCOVER_*`) for deeper introspection.
- **Read-only by construction** — statements that don't start with
  `SELECT`/`WITH`/`EVALUATE`/`DEFINE` are rejected, XMLA payloads are rejected, and there
  is no DDL/process surface at all.
- **Row cap + query timeout** on every call so a runaway crossjoin can't flood the
  context window.

## Requirements

Windows-only by construction (the server drives ADOMD.NET via pythonnet):

1. **Windows**, domain-joined, with network access to the SSAS instance
   (default instance = TCP 2383).
2. The Windows account running the server has **Read** access to the SSAS database
   (role membership). Integrated auth means "whoever runs the process".
3. **ADOMD.NET client libraries** — ships with SSMS, or install the
   [Analysis Services client libraries](https://learn.microsoft.com/en-us/analysis-services/client-libraries)
   redistributable. Typically lands in `C:\Program Files\Microsoft.NET\ADOMD.NET\160`.
4. **Python 3.11+**

## Installation

```powershell
git clone https://github.com/dbdave/mssas_mcp_server
cd mssas_mcp_server
pip install -e .
```

Smoke-test connectivity before wiring up MCP (if this fails it's network/auth/ADOMD
install, not the server):

```powershell
python test_connection.py <server> <catalog>
```

## Configuration

| Variable | Required | Default | Notes |
|---|---|---|---|
| `SSAS_SERVER` | yes | — | Host, `host\instance`, or an `http(s)://.../msmdpump.dll` URL |
| `SSAS_CATALOG` | yes | — | SSAS database name |
| `SSAS_DEFAULT_CUBE` | no | — | Cube/model `list_metadata` uses when none is given |
| `SSAS_ROW_LIMIT` | no | `10000` | Hard cap on returned rows |
| `SSAS_QUERY_TIMEOUT` | no | `60` | Per-query timeout, seconds |
| `SSAS_ADOMD_DLL_DIR` | no | auto-detected | Folder containing `Microsoft.AnalysisServices.AdomdClient.dll` |

Authentication is always Windows Integrated Security (`Integrated Security=SSPI`) —
no credentials in config.

## MCP registration (Claude Code / Claude Desktop)

```json
{
  "mcpServers": {
    "ssas-cube": {
      "command": "python",
      "args": ["-m", "ssas_mcp.server"],
      "env": {
        "SSAS_SERVER": "my-ssas-host",
        "SSAS_CATALOG": "My_Cube_Db",
        "SSAS_DEFAULT_CUBE": "Sales"
      }
    }
  }
}
```

One registered server targets one SSAS database. To use both a multidimensional and a
tabular instance, register the server twice with different `SSAS_SERVER`/`SSAS_CATALOG`
env blocks — the query language is chosen per call (`execute_query` accepts MDX or DAX,
and the engine itself only accepts the language matching the model type).

## Example usage

Multidimensional (MDX):

```mdx
SELECT NON EMPTY { [Measures].[Tickets Issued] } ON COLUMNS,
       NON EMPTY { [Sales Item Fact Ordered Date].[Calendar].[Year Name].MEMBERS } ON ROWS
FROM [Sales]
```

Tabular (DAX):

```dax
EVALUATE SUMMARIZECOLUMNS('Date'[Year], "Total Sales", [Total Sales])
```

DMV introspection:

```sql
SELECT [MEASURE_NAME], [MEASUREGROUP_NAME] FROM $SYSTEM.MDSCHEMA_MEASURES WHERE [CUBE_NAME] = 'Sales'
SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES  -- tabular models
```

## Result format

Tools return JSON:

```json
{
  "columns": ["[Sales Item Fact Ordered Date].[Calendar].[Year Name].[MEMBER_CAPTION]", "[Measures].[Tickets Issued]"],
  "rows": [["2025", 123456], ["2026", 98765]],
  "row_count": 2,
  "truncated": false
}
```

Cells can be null and columns can be mixed-type — MDX cellsets are flattened
defensively (nulls preserved, `Decimal` → float, `DateTime` → ISO string).

## Tests

```powershell
pip install pytest
pytest tests/
```

## License

MIT