The problem
The client runs an online marketplace with a four-person analytics team and several hundred employees who want numbers every day: sales by category, seller performance, refund rates, campaign results. The team estimated that more than half of its week went to ad-hoc requests, many of them variations of questions answered the week before. Leadership wanted people to use AI assistants to answer their own data questions, but the data team wouldn't give an LLM raw SQL access to the warehouse, for good reasons: wrong joins, inconsistent metric definitions, exposure of customer data, and runaway query costs.
What we actually did
A semantic layer as the only way in
We started with the analytics team's own metric definitions and encoded 42 of the most requested metrics (gross merchandise value, net revenue, refund rate, active sellers, and so on) as governed definitions with approved dimensions and filters. The MCP server exposes these definitions, not tables. An assistant asks for "refund rate by category for last quarter"; the server compiles that into SQL from the approved definition, so the number always matches the official dashboard.
Six narrow tools
The server exposes six tools over the Model Context Protocol: list available metrics, describe a metric and its allowed dimensions, query a metric, compare two periods, list saved reports, and explain how a number is calculated. Every query tool enforces row-level security based on the calling user's identity from the company's single sign-on, so a regional manager sees only their region, and customer-level data is never returned at all.
Cost and safety limits
Each query is estimated before it runs. Queries that would scan beyond a set threshold are rejected with a suggestion to narrow the date range. Results are cached for common questions, and every call is logged with the user, the metric, and the generated SQL, so the data team can see exactly what's being asked.
Challenges and tradeoffs
- Questions outside the defined metrics. The server returns a clear "not available" with the closest governed metrics, and those misses are logged. The data team reviews them weekly and adds definitions for the most frequent ones.
- Authentication across clients. Users connect from different assistant apps. We used the protocol's OAuth-based authorization so the server always knows the real user behind each request, rather than a shared service account.
- Explaining numbers. Business users trust numbers more when they can see how they were computed, so every answer can return its metric definition and filters in plain language.
Results
Within two months of rollout, ad-hoc data requests to the analytics team dropped by 80%, measured from their request queue. The team moved that time to forecasting and experimentation work that had been sitting in their backlog. Because every answer comes from the same definitions as the dashboards, there have been no "the AI says a different number" escalations, which was the main risk leadership worried about.
What we'd do differently
We would publish the miss log to business teams, not just the data team. Seeing which questions couldn't be answered helped teams phrase requests better and made the backlog of new metric definitions a shared priority rather than a data-team chore.