For the better part of two years, every BI vendor has been promising the same thing: chat with your data. Type a question, get an answer, skip the dashboard. The demos always work. The pilots rarely do.
The reason they rarely do is not that the language models are weak. The models are extraordinary. The reason is that the layer between the language model and the data product almost never exists in the shape it needs to exist. When it is present, it is usually too thin (paste a CSV into the prompt and pray) or too thick (a vendor-controlled copilot whose prompt you cannot see, whose governance you cannot adjust, and whose failures you cannot debug). The thing that has been missing, in my experience, is a properly designed protocol layer between the model and the semantic model — one that exposes the structure of the business, lets the LLM query it under controlled conditions, and never surfaces a row to the model until the analyst chooses to.
Over the last several months I have been building that layer. The protocol is the Model Context Protocol (MCP), introduced by Anthropic in late 2024 as an open standard for connecting LLMs to external tools, systems, and data sources. The data product is Power BI — specifically the tabular semantic models that live inside Power BI Desktop and Microsoft Fabric workspaces. This piece is a field report on what I learned, what I broke, what surprised me, and why I think MCP is the correct shape of solution for analytics teams that want to put LLMs to work without sacrificing governance.
I am writing this for the practitioner audience: the BI lead, the analytics engineer, the data platform owner. If you are evaluating whether to plug a chatbot on top of your reporting layer, this is the article I wish I had read before I started.
The Promise That Almost Was
The first wave of "LLMs meet BI" products, roughly 2023 through mid-2024, settled into three families. Each one is a coherent idea. None of them is what you actually want.
The first family was paste the data. The analyst (or a thin app) would copy a slice of the warehouse into the prompt, ask the model to reason about it, and trust the response. This works at toy scale and falls apart everywhere else. Tokens are finite, freshness is whatever the analyst last copied, the model has no way to ask follow-up questions of the source, and the governance posture is whatever the analyst happens to remember. Worse, the model never learns the shape of the business — every conversation begins from nothing.
The second family was text-to-SQL. Generate the query, run it against the warehouse, hand the result back. The shape is closer to right. The reality is that SQL against a warehouse is rarely the shape in which the business reasons. The warehouse is normalized; the business thinks in measures, time intelligence, and cohorts. A model that writes plausible SQL routinely produces totals that are off by a factor of two because it joins the wrong fact table, double-counts a transfer row, or misunderstands what "active player" means in your specific shop. The model is fluent; the answers are subtly wrong.
The third family was vendor-native Q&A — Power BI's own Q&A pane, Copilot for Power BI, ThoughtSpot's Sage, Tableau Pulse, and so on. These products are more mature than they were a year ago, and for many shops they are a perfectly reasonable starting point. They share two traits, however, that make them a poor fit for serious analytics work. First, the integration is opaque: you do not write the system prompt, you do not see the tool calls, you cannot easily add a new capability or constraint. Second, the boundary of "what the model is allowed to see and do" is set by the vendor, not by you. If the regulator asks how a particular answer was produced, the audit trail you can produce is the one the vendor lets you produce.
None of these three families is bad. Each one solved a real problem. But the analyst-facing layer I wanted — one in which the LLM is a co-worker that reads my model, asks intelligent questions of it, drafts measures, runs DAX, and respects every row-level rule I already configured — could not be built on any of them.
What MCP Is, Briefly
MCP is an open protocol that standardizes how LLM applications connect to external tools and data sources. Mechanically, it speaks JSON-RPC 2.0 over either local stdio or HTTP, and it borrows its message-flow ideas from the Language Server Protocol — the same pattern that lets a single VS Code installation talk to dozens of language-specific servers without bespoke glue per language. The animating idea is that the model talks to a generic client (Claude Desktop, Claude Code, Cursor, your own host), the client talks to one or more servers, and each server exposes a typed surface of tools (functions the model can call), resources (data the application can pull in), and prompts (templates the user can invoke).
Two things matter for our purposes. First, MCP is a contract. The server publishes a schema; the model knows what it can and cannot do. There is no string-matching, no plausibility-based hallucinating into freeform endpoints. Second, the server is a thing you control. Local stdio servers run on the analyst's machine and never expose data to the network; HTTP servers can be hosted internally and can enforce any auth, audit, or rate-limit policy you want.
For a sensitive data product like a Power BI semantic model, this combination is exactly the shape you need. The model never gets a wide-open ODBC connection; it gets a narrow, typed, observable surface that you have designed.
Where Meaning Lives in Power BI
To explain why MCP is the right place to insert the LLM, I need to dwell briefly on where the meaning of a Power BI report actually lives.
It does not live in the dashboard. The dashboard is presentation. Move the bar chart from the left to the right and the meaning is unchanged.
It does not live in the warehouse. The warehouse holds the facts and dimensions, but it does not know what "net revenue" is, or what counts as a "lapsed customer," or how to convert the deposits table into cash-handle for a given product family. The warehouse has nouns; the business reasons in verbs.
The meaning lives in the semantic model — the Tabular Object Model that sits between the warehouse and the dashboard. It is the place where the analyst has written, in DAX, what the business actually means by each of the hundred or so terms that show up in the leadership reports. Net Sales, Hold %, Active Players (28d), Cohort Retention, Same-Store Growth — those are not columns in a table. They are measures, often built from other measures, and the only complete written-down version of them is the model.
This has two consequences for an LLM integration. The first is that any answer the model produces should be expressed in terms of the model's measures. If you ask "what was net sales last week?" and the system invents its own definition of net sales by summing some warehouse columns, you have a confident answer that does not match the deck the CEO read on Monday. That is the worst possible failure mode: not wrong, but inconsistent.
The second consequence is that the model is also where governance is encoded. Row-level security rules, calculation groups, perspective definitions, format strings — all of these are part of the semantic layer, not the warehouse. An LLM integration that bypasses the semantic model bypasses everything you have spent years configuring.
The conclusion writes itself. The LLM should not talk to the warehouse, and it should not talk to the dashboard. It should talk to the model.
The question is: through what?
Three Approaches That Don't Quite Work, in More Detail
Before I describe what I built, let me sharpen the failure modes of the three approaches I started with. Each one is something I tried — in two cases for several weeks — before concluding it was the wrong shape.
Approach 1: paste the slice. I exported the relevant facts to a CSV, dropped them into the prompt, and asked the model to reason. For one-off, narrow questions, this worked fine. The first time leadership asked a follow-up — "okay, but split that by distributor for the same window" — I either had to re-export and re-paste, or I had to give the model some way to query. The pattern collapses the moment the conversation acquires any depth.
Approach 2: text-to-DAX in the open. Slightly more sophisticated: I let the model write DAX directly, then I executed the DAX. The problem is that LLM training corpora are saturated with SQL and comparatively thin on DAX, and the two languages reason about filter context very differently. CALCULATE is not WHERE. ALL is not DISTINCT. Time intelligence functions implicitly assume a marked date table that the model may or may not have. The result is DAX that looks right, parses, runs, and returns numbers that are subtly wrong — typically by a small enough margin that you only notice when the weekly total disagrees with the deck. I once spent a Friday afternoon debugging a DAX query that summed "Total Bet ($)" alongside a column whose values were already in dollars, while the source table for the other measure was in cents. The model had cheerfully divided by 100 in the wrong place. It was almost right.
Approach 3: vendor copilot. I will not be specific about which copilot, because the issues are largely structural and apply to most of them. The thing I could not get past was that the boundary of the system was not mine. I could not add a tool to look up our internal location dictionary. I could not enforce the rule that any answer about hold percentage must come through a specific measure. I could not produce a clean audit trail for compliance. The product was good. It was not, however, a foundation I could build my analytics function on.
The shape I needed was something between approach two and approach three: the openness of "I write the prompt and the tools," combined with the rigor of "the model cannot just freelance DAX against the warehouse."
The Shape of the Right Solution
What I built — and what I am now arguing more teams should build — is a Power BI MCP server. The server runs as a local stdio process on the analyst's workstation. It connects to either a local Power BI Desktop instance (over the XMLA endpoint of the analysis services that Desktop spins up) or to a remote Microsoft Fabric workspace (over the public XMLA endpoint of the workspace's semantic model). It exposes a small, typed surface of tools to the LLM client.
The tools are intentionally fine-grained. The list looks something like this:
ListLocalInstances— discover the local Desktop sessions and their XMLA ports.ConnectFabric— attach to a remote workspace and semantic model by name.ConnectLocal— attach to a Desktop instance by port.DescribeModel— return tables, columns, relationships, calculation groups, RLS roles, and metadata for the connected model.ListMeasures— return all measures with their display folder, format string, description, and DAX expression.ExecuteDax— run an arbitrary DAX query against the connected model and return the result.CreateMeasure— create a new measure, given a table name, expression, format, and description.UpdateMeasure— modify an existing measure in place.DeleteMeasure— remove a measure.ValidateExpression— parse a DAX expression without executing it, to catch syntax errors cheaply.
This list is small on purpose. There is no "give me the whole table" tool. There is no general-purpose "run any SQL" tool. The model has to describe before it can query, and it has to query before it can write. That sequence — describe, query, write — turned out to be the most important thing the server enforces.
Critically, the model never sees a row of data unless the analyst's question genuinely requires one. DescribeModel returns structure. ListMeasures returns expressions, not values. The model can develop a complete mental picture of the business — what tables exist, how they relate, what measures are defined, what calculation groups are available — without ever pulling actual data. Only ExecuteDax returns values, and that tool's results are scoped to the DAX the model wrote, which is in turn scoped to the model's own RLS rules.
I cannot overstate how different this is from a generic "the LLM has read access to your database" integration. It is the difference between giving someone the keys to your house and inviting them to your office.
The Lessons That Cost Me a Week Each
Here is where the field report becomes practical. The server itself, in its first working version, took me about a week. The next two months were entirely about the operational details I had not anticipated. In rough order of how much they cost me, here are the lessons I would tape to my monitor if I were starting over.
Power BI Desktop hands you a different port every time it restarts.
If your MCP server connects to a local Desktop instance, it has to discover the instance's port at runtime. The port is assigned dynamically by Analysis Services every time Desktop starts; there is no stable address. The fix is mechanical — enumerate the local AS instances and find the one whose database name matches — but the consequence is that every session has to begin with an instance discovery. The very first tool the LLM should call, before anything else, is the one that lists the live instances. Hard-coding a port worked exactly once.
Sessions time out, and the server should reconnect for you.
XMLA sessions are not eternal. After a few minutes of idle time, the connection is dropped. The first time this happened in production was during a long demo, where I had connected at the start, spent forty minutes describing the model and discussing the results with leadership, and then watched the next ExecuteDax call fail. The right behavior is idempotent reconnects: every tool call should verify the connection and re-establish it transparently if needed. The model should not have to think about session lifetime, and the analyst certainly should not.
Batch operations want to be small, and they want to be tolerant.
The single most common mutating operation I run through the server is "create or update a set of measures" — typically when an LLM has drafted a set of variant calculations for me to review and I want to push the survivors into the model. The naive shape is a single transaction containing all the operations. This shape is wrong, for two reasons. First, the analysis services endpoint has a request-size and time budget that I have empirically pegged at about six measure operations before it starts timing out for non-trivial expressions. Second, transactional all-or-nothing is exactly the wrong semantics when the LLM has produced a batch in which one expression has a typo. You want the seven good measures to be created and the one bad one to fail loudly, not the whole batch to be rolled back and a single error message to come back. The recipe that has worked for me is: chunk to about six per batch, set useTransaction: false, set continueOnError: true, and surface per-item errors in the tool response. Resilience over atomicity, in this context, every time.
DAX has parse quirks when it goes through the wire.
I lost the better part of an afternoon, early on, to a CALCULATE expression that worked perfectly in DAX Studio and failed through the MCP. The expression looked like this:
CALCULATE(
[Net Sales],
'Date'[FiscalYear] = "FY2026" && 'Product'[Platform] = "P2P"
)
DAX Studio accepts the && because it is being interpreted as a Boolean operator inside a filter table expression that CALCULATE evaluates with its own machinery. The parser path through my server was stricter, and refused it. The fix is to use separate filter arguments, which CALCULATE will AND for you:
CALCULATE(
[Net Sales],
'Date'[FiscalYear] = "FY2026",
'Product'[Platform] = "P2P"
)
This is the form CALCULATE prefers anyway — it is clearer to read, and it gives the engine room to optimize each filter independently — but the lesson is broader. Wire formats and interactive tools do not always parse the same way. If you build a server like this, fuzz the parser with the common idioms of your team's DAX library before you ship.
Update and Create are asymmetric, and you will be bitten by it.
When you create a measure, you have to tell the server which table it belongs to. When you update a measure, you do not — the measure already has a home, and the operation finds it by name. The first time I parameterized Update with tableName, the server returned a perfectly silent no-op: no error, no change, nothing. The asymmetry is not unreasonable, but it is the kind of small detail that a strongly typed tool surface should ideally make impossible to get wrong. If you are building your own server, lean on schema validation rather than runtime hope.
Introspect first, always.
The single largest improvement in answer quality, by a wide margin, came from a discipline that turned out to be more cultural than technical. I added language to the server's tool descriptions, and to the system prompt of the host application, that pushed the model to always call DescribeModel and ListMeasures before generating DAX. This sounds trivial. The effect on hallucination rate was dramatic. When the model is asked "show me hold by product family for the last four weeks" and it does not first inspect the model, it confidently invents a measure called something like [Hold %] and references a table called Product — both of which may or may not exist, and even if they exist may not mean what the model assumes. When the model first inspects, it finds that the right measure is [Game Hold %], that it lives in the _Measures table, that the product hierarchy is on CrmLocationView, not Product, and that the date table is marked and named Date. The DAX it then produces is, in the overwhelming majority of cases, correct on the first try.
There is a lesson in here about the relationship between LLMs and structured systems. The model is at its best when it has access to ground truth and treats it as ground truth. The same model, asked to reason about a system whose shape it has only inferred, produces fluent fiction. The job of the server is to make ground truth cheap to ask for and impossible to ignore.
Data Boundaries, and Why This Shape Is Right for Governance
A recurring objection I hear from data platform owners, when I describe this architecture, is some version of: "but we cannot let an AI model see our data." The objection is reasonable in spirit and misplaced in detail. What the architecture I have just described actually does, with respect to data boundaries, is the following.
The MCP server runs locally to the analyst, as a stdio subprocess. No data leaves the workstation as a consequence of the server's existence. The LLM client (Claude Desktop, in my case) sends requests to the server; the server returns responses; nothing about that exchange touches the network.
When the LLM client itself runs against a remote API — which it does, because the model weights are not local — the messages exchanged with the API include the tool call request, the tool call response, and the conversational context. This is the boundary that matters. What crosses the network is what the LLM sees, which is what the server returned. If the server returned only model structure (no rows), only model structure crosses. If the server returned the result of a [Net Sales] query summed across a year, a single number crosses. The granularity of what crosses is exactly the granularity of what the server chose to return.
Compare this to a naive "the LLM has read access to the warehouse" integration. There, the entire row set of any query the LLM cares to run crosses the boundary the moment the LLM runs it. The blast radius of a poorly chosen query is the result set of that query. With an MCP server in front, the blast radius is whatever the tool returns, which you control by tool design.
Row-level security is inherited, not bypassed. The XMLA connection the server opens to the semantic model carries the identity of whoever is running the server. If the analyst running the server is restricted to a particular set of regions or distributors under the model's RLS rules, every DAX query the LLM runs through that server is restricted in exactly the same way. The model cannot escape RLS because the connection cannot escape it.
For most analytics use cases, this is the right governance posture: tight where it counts, permissive where it doesn't. The analyst is operating within their already-existing permissions. The LLM is operating within the analyst's. Nothing new has been granted.
There are edge cases. If you host an HTTP-transport MCP server centrally, you take on responsibilities you did not have with a local stdio server: authenticating callers, logging tool calls for audit, rate-limiting mutations, and so on. None of these are protocol-level problems; they are operational problems, and they look exactly like the operational problems of any internal API. The protocol does not solve them for you, but it also does not get in your way.
What MCP Doesn't Solve
It would be unfair to write a field report and not be honest about the things this architecture does not improve.
It does not improve a bad semantic model. If your measures are inconsistent, your relationships are ambiguous, and your date table is not marked, the LLM will reflect those problems back at you with confidence. Garbage in, fluent garbage out. The best argument for MCP is, in some ways, that it makes the quality of your semantic model legible to leadership in a way that dashboards alone do not. The first time the model produces a wrong answer because two measures disagree on the definition of "active," you will have a conversation about the model that you should have had years ago.
It does not give the LLM intuition about which queries are expensive. A model that cheerfully writes SUMX(VALUES('Date'[Date]), ...) over a ten-year date range across every distributor will produce a query that takes seven minutes to return. You can mitigate this with cost estimators, query plan inspection, and timeouts at the server level, but the model itself does not natively reason about cardinality or storage engine vs formula engine in the way an experienced DAX practitioner does. This is, I suspect, the next frontier for tooling.
It does not eliminate the need for human review. The model is a fast, fluent, frequently-correct draftsman. It is not a substitute for the analyst's judgment about whether the right question is being asked. The right pattern, in my experience, is to use the model to compress the time between "I have a hypothesis" and "I have a result," not to use it to short-circuit the loop entirely.
It does not, by itself, give you an audit trail. The protocol carries the message; the server has to log them. If you need to know, six months from now, who asked which question and what DAX the model executed in response, that observability is your responsibility to build into the server. The good news is that the surface is small enough that this is straightforward — log every tool call with its arguments, results, and the calling identity, and you have a complete record.
These are limits, not indictments. The right way to read this section is as a list of things you should plan for, not as reasons to retreat to the previous architecture.
When to Build, When to Borrow
A handful of open-source MCP servers for Power BI already exist, in various states of completeness. They differ in their surface, their transport, and their assumptions about your environment. For a team that is exploring the space, starting with one of them is the right move. The cost of building your own from scratch, in time and in the inevitable lessons-paid-for-in-debugging-time that this article has chronicled, is meaningfully higher than the cost of running an existing implementation.
I would build your own when one of the following becomes true. First, when you need to enforce row-level governance at the tool boundary in a way the open-source implementations do not — for example, when you have downstream regulatory obligations that require you to log every DAX query with its calling identity and the resulting numeric output. Second, when you need to integrate with internal authentication that the off-the-shelf servers do not support. Third, when your team's DAX idioms are unusual enough that the parser path of a generic server keeps biting you. Fourth, and this is the most common case in mature teams, when you have a clear vision for tools beyond the basic "describe, query, write" set — connectors to your data catalog, lineage lookups, deployment pipelines — that are easier to add to your own server than to fork into someone else's.
For most teams reading this, the right path is: start with an existing server, use it for two months, pay attention to what you wish it did differently, and then make a deliberate decision about whether to fork, contribute upstream, or rewrite.
The Thesis
The reason MCP is the missing layer between LLMs and Power BI is not that the protocol is magical, and it is not that LLMs cannot generate DAX without it. The protocol is workmanlike. The DAX would come out one way or another. The thing that changes when you put an MCP server between the model and your semantic layer is what the LLM is talking to.
Without it, the LLM is talking to a dashboard, a warehouse, or a vendor's prompt — three things that are not the business. With it, the LLM is talking to the semantic model: the document in which your team has, over years, written down what the business actually means. The dashboard becomes one of many UIs over the model. The warehouse becomes one of many sources. The conversation becomes a new way of consuming a definition you already had.
That, more than the throughput or the time saved or the questions answered, is the case for this architecture. The semantic model has always been the most underused asset in a mature BI shop — too technical for executives, too dispersed for engineers, written in a language that only a handful of people on the team can read fluently. An MCP server turns it into the contract through which everyone, human or otherwise, talks to your business.
Dashboards are not enough. They never were. The model is.
Next in the series: how I think about the executive briefing layer above the model — the synthesized narrative leadership can act on in under sixty seconds.