Building an AI Analyst Agent

Trustworthy self-service reporting is sometimes called the holy grail: someone asks a question in plain English and gets a number the organization already stands behind. The chat window is the easy part. The hard part is a short, governed path from that question to a measure that already has a definition, a grain, and a permission check.

This is a vendor-neutral field guide for that path. Claude (including Claude Projects), the Model Context Protocol (MCP), Power BI semantic models, and Microsoft Fabric appear as concrete examples of each role. Use the equivalents you already run. Nothing here is a case study, a benchmark, or a description of any one deployment.

The reference architecture

Four hops are enough. A person talks to an assistant. The assistant may only fetch numbers by calling tools. Those tools talk to a semantic model. The semantic model talks to the data platform, under the caller's permissions.

Put policy in the assistant and access in the server. Project instructions can say which subject area is in scope, which measure wins when two names sound alike, and the shape of a good answer. They cannot keep a secret. If a tool can see a row, assume the model can say it.

The semantic layer matters more than the model

A strong language model on a messy schema will sound sure and be wrong. A modest model on a clean semantic layer will reuse definitions the business already argued about and wrote down. Spend the effort on the layer.

The layer is doing four jobs the assistant should not invent:

• Meaning. "Revenue" is a specific measure, not a vibe.
• Grain. One row per order line is not one row per customer.
• Relationships. Which date, which product, which organization the filters apply to.
• Permission. Who is allowed to see which slice.

Treat descriptions as part of the product. The agent chooses a measure by reading its name and description. A column that exists only so a report could be built in 2014 should be hidden. If a human analyst would not drag it onto a canvas, the agent should not see it either.

Name measures so the agent picks the right one

The agent does not know your tribal vocabulary. It knows the strings you published. Names that made sense inside a modeling tool become a bug the moment a person says "how were sales last month?"

Write the definition next to the measure

A description the agent can read should settle five things:

1. The business event (invoice, shipment, order, payment).
2. What is included and what is subtracted (discounts, returns, tax, freight).
3. The date the filter uses (invoice date is not ship date).
4. Words people actually say, including the synonyms.
5. The sibling measure it is easy to confuse it with.

A usable description sounds like this: "Invoiced amount after discounts and returns. Excludes tax and freight. Filtered on invoice date. This is not bookings and not shipments." That sentence prevents more bad answers than a clever prompt.

When two measures are both legitimate, pick a default for the ambiguous question and say so in the instructions: if someone says "sales" and does not specify, use Net Sales and name it in the answer. Offer the other measure. Do not blend them.

When the semantic layer is split

Plenty of environments are mid-migration. Some definitions still live on a legacy operational system, such as an ERP. Others live on the curated tables of a modern warehouse, the layer often called gold. Pretending those are one model, on day one, is how the agent stitches together two truths and calls it an answer.

A few rules keep the split honest:

• Route by subject area. Bind each MCP tool to one model. The tool's description is the map.
• Name the authority. If both sides can produce a number called revenue, the description states which one answers which question, and why.
• Do not join in the chat. If a question needs both sources, either the semantic layer already combines them, or the assistant declines and says what is missing.
• Leave a gap on purpose. A first version can expose one area completely and teach the assistant to refuse the other. A refusal is a better result than a blended guess.

That gap is not a failure of ambition. A pragmatic first version is a valid starting point. Expand the map when the definitions are ready, not when the demo would look more impressive with more topics.

Pass security and row-level permissions through

The agent must not become a side door. Row-level security and object-level security belong in the semantic model, applied to the person who asked. The assistant does not reimplement them in a prompt.

• Call as the user. The MCP server should use that person's identity, or a token constrained to it. A shared service account that can see everything, plus an instruction to "only show their region," is not a control.
• Hide measures the person cannot use. If the model already hides a measure, the tool result and the error text should hide it too. Samples, query plans, and "did you mean" suggestions are still data.
• Do not trust conversation memory. A later message does not grant a row the model refused. Each tool call is checked again.
• Keep arbitrary query off the tool list until you are ready to govern it. A semantic-query tool that only accepts known measures is a smaller blast radius than a SQL box.
• Log the caller, the tool, and the question. Do not log credentials. Logs are for "why did this answer look like that," not for a second copy of the warehouse.

Prove it with two accounts that should not see the same rows. Ask both the same question. If the answers match, the identity is not actually flowing through.

Guardrails against hallucinated numbers

The failure mode that matters is a fluent sentence with a fabricated figure. The rule is simple: if a tool did not return the number, the assistant does not say the number. It may explain, ask a question, or decline. It may not estimate a governed metric to be helpful.

Every numeric answer should show four things:

1. The value, copied from the tool result. Say so if you round it for display.
2. The measure name, exactly as published.
3. The filter context: dates, product, organization, and anything else that changed the number.
4. The query the tool ran, or a faithful citation of it, so a person can replay it in the semantic model.

If the tool returns no rows, say that. If two measures fit, ask one clarifying question. If the number looks surprising, still show it, still name the measure, and let the person who owns the definition decide whether the surprise is real. The assistant's job is not to sand the edges off a governed result.

Start with a narrow first version

The temptation is to point the assistant at every measure you have, because the model can "handle it." It can talk about all of them. It cannot be right about all of them. I would rather ship a narrow version that declines half the questions than a wide one that answers them with the wrong measure.

A solid first version has:

• One subject area, preferably the one whose definitions are already stable.
• A handful of measures, each with a description written for the agent.
• One audience, so row-level behavior can be tested with real roles.
• A written list of questions it will refuse, including anything that crosses the legacy and modern split.

Publish that boundary in the assistant's instructions and in whatever you tell users. "I don't have a governed measure for that yet" is a complete answer. Expand only after the known-answer tests below stay green.

Test with questions that already have answers

Build a small bank of questions before you invite anyone else. For each one, record the expected behavior from the semantic model itself, not from memory and not from the assistant: which measure, which filters, and whether the correct behavior is to answer, to clarify, or to decline. Store the expected value next to the question when an answer is expected. Recompute it in the model when the model changes.

Cover at least these kinds of questions:

• Direct. "What was net sales last month?" should hit one measure and one date basis.
• Synonym. The words in the description, not the words in the measure name.
• Near miss. "Sales" versus "bookings" versus "shipped." The answer should name which one it used.
• Ambiguous. A question that fits two measures should trigger one clarifying question, not an average.
• Out of scope. A topic you have not published should be declined, with no invented figure.
• Cross-source. A question that needs both the legacy area and the gold layer should not be blended in prose.
• Two identities. The same wording, two users, two different permitted slices.

Re-run the bank when you rename a measure, edit a description, change a relationship, or rewrite the project instructions. Those are the edits that silently retarget a question. A green demo from last month is not a test.

Adoption is a product problem

People do not adopt an analyst agent because the architecture diagram was clean. They adopt it when a question they already ask comes back with a number they can defend in a meeting. Plan for that, in this order.

1. Say what the first version answers. A short menu of questions beats a launch note that says the assistant "can answer questions about the business."
2. Keep the existing report. Sit beside it. The report is the appeal path when someone wants to see the same measure on a page they already trust.
3. Teach the citation, not the prompt. Show people where the measure name and the query are. The habit you want is "which measure did it use?" rather than "the AI is wrong."
4. Give wrong numbers a route to the definition owner. If the measure was the right one and the value still looks off, that is a semantic-layer issue. Collect it. Do not hot-fix it inside the prompt.
5. Feed misses back into the system. Questions people actually ask, and questions the agent fumbled, become new names, new descriptions, or new rows in the test bank.
6. Start with the people who already own the definitions. They can tell a wrong measure from a wrong grain. They are the translators for everyone else.

Change management here is mostly honesty about the boundary. A tool that refuses cleanly earns more trust than a tool that answers everything once.