Building a Natural Language Business Intelligence Interface with MCP

ramp · Finance · 2025 · source Agentic: yes · promotional bias: low · source-backed · extracted with prompt 0.5.4 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Business spend analytics and natural-language BI

Ramp wanted business users to query spend data in natural language through its developer API without forcing them to understand REST endpoints, raw JSON responses, or custom dashboards.

Constraints:

• directly feeding large API responses to an LLM caused miscalculations, context-window pressure, input size limits, and high token usage
• simple pagination did not scale meaningfully beyond a few hundred transactions
• reporting workloads involve large data volumes and complex queries that can time out
• business finance data access requires least-privilege controls and auditability
• write tools can be unreliable enough that Ramp deferred broad agentic actions pending a safety framework

Architecture

Ramp built an open-source MCP server with FastMCP that exposes its developer API to Claude Desktop through a SQL-shaped interface. Load tools pull data from Ramp APIs, a process_data tool flattens JSON into an in-memory SQLite database, an execute_query tool lets Claude run SQL over the local table, an OLAP-powered reporting API supports heavier spend queries, and OAuth scopes plus audit logging constrain and record access.

Diagram confidence: explicit (topology described by the source).

flowchart LR
  claude-desktop[Claude Desktop] --> ramp-mcp-server[Ramp MCP server]
  ramp-mcp-server --> load-tools[Load tools]
  load-tools --> ramp-developer-api[Ramp Developer API]
  load-tools --> olap-reporting-api[OLAP reporting API]
  ramp-mcp-server --> process-data-tool[Process data tool]
  process-data-tool --> in-memory-sqlite-database[In-memory SQLite database]
  ramp-mcp-server --> execute-query-tool[Execute query tool]
  execute-query-tool --> in-memory-sqlite-database
  ramp-mcp-server --> audit-logging-and-oauth-scopes[Audit logging and OAuth scopes]
  audit-logging-and-oauth-scopes --> ramp-developer-api

Components

Data flow

Claude Desktop receives a natural-language spend question, calls Ramp MCP tools, loads relevant data from the Ramp developer API, transforms REST JSON into SQL rows, stores them in local in-memory SQLite, and then runs SQL queries to aggregate or inspect spend. For heavier reporting cases, the server can use an OLAP-powered API optimized by Ramp's data platform team. Audit logging and scoped OAuth limit what the MCP client can access.

Why this architecture

Ramp moved from direct API-tool exposure to a SQL-oriented MCP architecture because LLMs handled structured queries better than raw math over large JSON blobs. The server-side SQLite and OLAP path reduce context and token pressure by doing computation outside the model, while MCP tools keep the data interface extensible and OAuth/audit controls keep access bounded.

Models used: Claude · LLM · MCP client

Scale

• The direct API prototype struggled beyond a few hundred transactions.
  “together a simple pagination tool to chunk responses into smaller, digestible parts, but we struggled to scale meaningfully beyond a few hundred transactions. Then we asked Claude what it would prefer the data to look like, and it said it preferred predictable data formats that enabled”article, stated
• The SQL-based version handled tens of thousands of spend events.
  “Claude went from struggling with a few hundred data points to accurately analyzing tens of thousands of spend events.”article, stated
• Very large businesses still hit API latency and tool-call timeout bottlenecks.
  “are still limitations. There's the occasional reliability issue, and API latency can be high for very large businesses with a lot of data. There are plenty of optimizations we could implement (concurrent requests, pulling data from the APIs asynchronously, smart caching, using DuckDB, etc.),”article, stated

Alternatives considered (roads not taken)

Expose API endpoints directly as MCP tools and let Claude analyze raw JSON

Rejected because: That worked for small demos but ran into miscalculations, context limits, input-size limits, and high token usage on real data.

Paginate large API responses into smaller chunks

Rejected because: Pagination reduced response size but still did not scale beyond a few hundred transactions.

Skip the separate process_data tool

Rejected because: Ramp notes this could reduce round trips, but kept an explicit transform step to load API data into SQLite.

Release write-capable business actions immediately

Rejected because: Ramp treated write tools as unreliable and deferred them until a safety framework exists.

Patterns

tool-use-orchestration (agent-tooling)

Ramp exposed business-data operations as MCP tools, including load tools, a transform tool, and an SQL execution tool that Claude could invoke from an MCP client.

structured-output-contract (tool-contracts)

FastMCP used function signatures, type hints, and docstrings to generate schemas that tell the LLM how to call each tool.

latency-optimization (performance)

The architecture moved computation into SQL and an OLAP reporting API to reduce context usage, avoid timeouts, and make large spend analysis practical.

safety-policy-enforcement (security)

Ramp constrained MCP access through audit logging and selectable OAuth scopes/tools, and deferred write actions until a safety framework exists.

Tradeoffs

Represent spend data as SQL rather than raw API JSON

Chose A local SQL interface over flattened API responses over Direct JSON analysis inside the model context.

Rationale: SQL made the data predictable, let server-side functions handle computation, and reduced token usage enough to work with free Claude.

Use a separate ETL/process step before query execution

Chose Explicit process_data loading into SQLite over Skipping the transform step to reduce tool round trips.

Rationale: The separate transform step made REST JSON queryable as SQL rows, even though Ramp acknowledged it added round trips.

Keep MCP access constrained and auditable

Chose Audit logging plus constrained OAuth scopes and tools over Broad, unchecked access to all available business data and tools.

Rationale: LLMs can pull large amounts of data from connected sources, so least privilege and audit trails are part of the architecture rather than an afterthought.

Delay agentic write actions

Chose Read/analysis-oriented tools with future safety work for writes over Immediately releasing tools that can perform business actions.

Rationale: Ramp proved write operations were technically possible but found write tools unreliable enough to require a safety framework first.

Gotchas (what bit them)

Raw API responses made the LLM unreliable at real spend-data scale.

Root cause: The model was asked to reason over large JSON payloads and do math inside its context window.

Resolution: Ramp shifted computation into an in-memory SQL database and let Claude query structured tables.

Pagination looked like an easy fix but did not solve the core scale problem.

Root cause: Chunking still left Claude responsible for synthesizing too much transactional data.

Resolution: Use SQL and server-side functions so the model can load less raw data and ask for computations.

Large reporting queries exposed API latency and timeout bottlenecks.

Root cause: Extracting large volumes through ordinary APIs was not optimized for reporting workloads.

Resolution: Ramp added an OLAP-powered reporting API path, while noting further optimizations like concurrency, async pulls, caching, and DuckDB.

Tool-capable LLMs can choose or use tools incorrectly even with the same user prompt.

Root cause: The model's tool-selection behavior was nondeterministic across conversations.

Resolution: Treat prompt engineering as only partial mitigation and defer high-risk write tools until additional safety controls are built.

Evaluation

Ramp tested the MCP server through Claude Desktop on spend-analysis prompts, visualizations, simple analyses, write-operation proof of concept, small demo businesses, and larger real-world transaction volumes.

Metrics: accuracy on tens of thousands of spend events · scaling beyond a few hundred transactions · reduced input token usage · tool-call timeout and API-latency behavior · whether Claude picked and used tools correctly across conversations

Outcomes

• Ramp released an open-source MCP server for natural-language access to Ramp business data.
• The SQL pivot let Claude analyze tens of thousands of spend events instead of only a few hundred transactions.
• The approach enabled curated spend views, cost-savings analysis, and business hierarchy navigation.
• The system still had reliability, API-latency, and write-tool safety limitations.

Ramp reports tens of thousands of spend events after the SQL pivot, with very large businesses still constrained by API latency and timeout behavior.