Content
# 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
