AI in BI: why agentic analytics needs a semantic layer first

AI in BI works in the enterprise when the language model queries governed metric definitions instead of raw warehouse tables. A semantic layer (dbt Semantic Layer, LookML, Power BI semantic models, Snowflake semantic views, Databricks metric views or Cube) gives an agent named metrics, approved joins and access rules, so its answers match the dashboards and respect security. Build that layer, an evaluation set and a review loop first, then open the chat box.

The rest of this article covers why raw text-to-SQL breaks, what the current platform options do, and a rollout plan we would put in front of a steering committee.

Why does text-to-SQL on raw warehouse tables fail?

The published numbers are consistent. On Spider 2.0, a benchmark of 632 text-to-SQL workflow problems taken from enterprise database use cases, the authors’ o1-preview-based agent solved 21.3% of tasks, against 91.2% on the older Spider 1.0 and 73.0% on BIRD. A data.world study on an insurance schema found GPT-4 with zero-shot prompts reached 16% accuracy directly on SQL, rising to 54% when the same questions ran over a knowledge graph representation of the database.

Vendors report the same gap from their own evaluations. Snowflake measured GPT-4o at 51% on its internal BI evaluation set and says Cortex Analyst, which works from a semantic model, was nearly twice as accurate. Google says its internal testing found Looker’s semantic layer reduced errors in natural language queries by as much as two thirds. Both are vendor-run tests on vendor-chosen data, so treat them as direction rather than proof. All four sources point the same way.

The failures fall into three groups.

Ambiguous metrics

A warehouse rarely has one revenue. It has gross, net of returns, net of discounts, recognized, booked, in transaction currency and in group currency. The rule that “revenue in the board pack excludes intercompany and uses month-end FX” usually lives in a BI tool, a spreadsheet or someone’s memory. A model reading column names will pick something plausible, and the answer will look right until finance compares it with the monthly report.

Joins, grain and fan-out

Models are good at writing syntactically valid joins and poor at knowing which ones are safe. Joining orders to order lines to shipments and then summing order value multiplies it by the number of shipments. Slowly changing dimensions need a date-effective join. Some tables need a mandatory filter (active records, a source-system flag, test customers excluded) that nothing in the schema announces. These errors return plausible numbers rather than exceptions, which is why they are dangerous.

Governance

A model generating free SQL can reach any table its service account can read. If that account is broad, row-level security designed for dashboards may not apply, PII columns become queryable, and audit logs show one technical user instead of the person who asked. Fixing this after launch is far harder than designing for it.

What is a semantic layer, in agent terms?

A semantic layer is a versioned definition of metrics, dimensions, entities and relationships, held as code (YAML, LookML, DAX) or as database objects, with an engine that compiles requests into SQL. For an agent, the important change is the contract. The model no longer writes arbitrary SQL against hundreds of tables. It picks from a catalog of named, documented metrics and dimensions, and a deterministic engine produces the query.

 user question
      |
      v
 +-----------+   metric + dimensions + filters   +----------------+
 | LLM agent | --------------------------------> | semantic layer |
 +-----------+   (no free-form SQL)              | engine         |
      ^                                          +-------+--------+
      |  result + metric used + SQL shown                | compiled SQL,
      |                                                  | access rules applied
      |                                          +-------v--------+
      +------------------------------------------| warehouse      |
                                                 +----------------+

This narrows what the model can get wrong. It can still pick the wrong metric or misread a time window, but it cannot invent a join path or silently drop the intercompany filter, because those rules live in the definition. When a dashboard and the agent resolve the same definition, their numbers agree by construction.

Which semantic layer options exist in late 2026?

Names and release stages move fast in this area. The table reflects vendor documentation as of November 2026; check the linked pages before you commit.

Option	Where definitions live	AI / agent surface	Access control (as documented)	Status notes
dbt Semantic Layer (MetricFlow)	Semantic models and metrics in the dbt project	dbt MCP server exposes Semantic Layer queries to MCP clients	Semantic Layer access permissions on dbt platform, plus warehouse grants	Semantic Layer on dbt platform Starter, Enterprise, Enterprise+; MetricFlow moved to Apache 2.0 in Oct 2025
Looker (LookML)	LookML models and Explores	Conversational Analytics with Gemini, across up to five Explores	Grounded in the LookML model; validate access filters in your instance	Conversational Analytics GA since Nov 2025
Power BI semantic models	Tables, relationships and DAX measures	Copilot in Power BI; Fabric data agents	RLS/OLS on the model; verified answers do not fully support RLS/OLS	Prep data for AI in preview; Fabric data agents GA
Snowflake semantic views	Schema-level database object (replaces staged YAML semantic models)	Cortex Agents; Cortex Analyst	Cortex Analyst adheres to RBAC	Cortex Agents GA since Nov 2025; Snowflake recommends moving from Cortex Analyst to Cortex Agents
Databricks Unity Catalog metric views	YAML metric views in Unity Catalog, with agent metadata (synonyms, display names)	Genie Agents (formerly Genie Spaces), with built-in benchmarks	Unity Catalog evaluates access as the end user	Genie One (chat across assets) and Genie Code (developer assistant) are separate products
Cube	Data models as code in Cube Core	Analytics Chat, MCP server, Chat API; agents query via Semantic SQL	Access policies applied in the semantic layer before the warehouse	Cube Core backend is Apache 2.0

One more item to watch: Apache Ossie (incubating), previously the Open Semantic Interchange, is a vendor-neutral YAML specification for exchanging metric, dimension and join definitions between tools. It does not run queries, but it may reduce the cost of changing semantic engines later.

How to choose

We use four questions, in this order:

1. Where do your trusted metrics live today? Moving definitions is the expensive part. If finance already signs off Power BI measures, start there. If dbt is your modeling standard, keep metrics in dbt.
2. How many consumers need the same numbers? One BI tool plus one assistant can live inside a platform-native option. Several BI tools, a customer-facing app and internal agents argue for a layer that sits in front of all of them (dbt Semantic Layer, Cube, LookML).
3. Where does identity get enforced? Prefer an option that runs queries as the end user. Shared service accounts push security into prompt logic, which is the wrong place.
4. Which LLM is allowed? Platform-native agents generally run on models the vendor selects. If your security policy requires your own Azure OpenAI, Bedrock or Vertex tenant, check that before you check features.

What context do agents need beyond the semantic layer?

Metrics answer “how do we calculate X”. Agents also need to know which assets exist, who owns them, whether they are fresh, whether a column holds personal data and what breaks if something changes. That is catalog and lineage metadata.

Both dbt and DataHub now publish MCP servers for this. The dbt MCP server exposes models, metrics, lineage and freshness. The DataHub MCP server lets agents search assets, read ownership, glossary terms and tags such as PII, and trace lineage at table and column level. In practice this gives you two useful behaviors. The agent can refuse or warn when the underlying data is stale or uncertified, and it can explain where a number came from in terms a business user recognizes.

Two cautions. Metadata tools are read-heavy, but some MCP servers also have write tools, so start with read-only tool sets. And metadata is only as good as its curation: an agent reading empty descriptions has nothing to work with, so documentation coverage on the assets in scope has to come first.

How do you govern an AI analyst?

Treat the agent as a new BI client with an unusual query pattern, and apply the same controls.

• Identity passthrough. Queries should run as, or on behalf of, the asking user, so row-level security, column masking and audit trails work unchanged. Test it with real personas: a regional manager, a contractor, an auditor.
• Scope by domain. One agent per business domain with a curated set of metrics beats one agent over the whole warehouse. Genie Agents, Looker Explores and Fabric data agents are all built around curated scopes for this reason.
• PII. Exclude or mask personal data in the semantic layer itself, use catalog tags to block sensitive columns from agent scope, and decide explicitly whether result rows (not only prompts) may be sent to the LLM provider.
• Read-only by default. No DDL, no writes, no exports above a row limit without review.
• Logging. Store question, chosen metric, generated SQL, result size, model version and user. You will need this for both incident review and evaluation.
• Know the gaps. Microsoft notes that Copilot outputs are nondeterministic and that verified answers do not fully support RLS and OLS. Every platform has caveats like these in its documentation; read them before the security review does.

How do you test text-to-SQL accuracy before go-live?

Accuracy depends on your questions and your data more than on the model. Public benchmarks tell you little about whether the agent can answer “net revenue by region last quarter, excluding intercompany”.

Build an evaluation set:

• Source questions from real usage: report requests, analyst tickets, the questions executives ask in meetings. As an illustrative starting size, 100 to 300 questions per domain is enough to see patterns.
• Store approved expected results as well as reference SQL. Two different queries can both be correct; compare result sets with tolerance for ordering and rounding.
• Tag each question by type: single metric, metric by dimension, time comparison, ratio, ranking, multi-domain, deliberately ambiguous, and out of scope.
• Score three outcomes separately: correct, wrong, and abstained or asked for clarification. A wrong answer delivered with confidence costs more than a refusal, so a rising abstention rate can be acceptable while the wrong-answer rate must stay low.
• Run the set on every change to the semantic model, instructions, examples or model version, in CI if your platform has an API for it. Databricks has benchmarks built into Genie Agents and notes they are used for evaluation only, not as context.

Set thresholds per question type. You may accept an agent for single-metric lookups long before you trust it on multi-domain ratios.

Where should humans stay in the loop?

Keep people in the loop at the points where an error would travel furthest:

• Every answer should display the metric used, filters, time window and the SQL. Users learn quickly to spot “gross” where they expected “net”.
• Certified answers need a visible marker. Verified answers (Power BI), trusted assets (Genie) and verified queries (Snowflake) let authors pin approved logic to common questions. Flag answers that used them differently from free-form ones.
• A thumbs-down should create a ticket for the metric owner, not disappear into a log. Most fixes belong in the semantic layer, not in prompts.
• Numbers going into board packs, regulatory filings or customer communication still go through an analyst.

What does a staged rollout look like?

Phase	Scope	Exit criteria
0. Foundations	Pick one domain; define its top metrics in the semantic layer; document assets in scope; confirm identity passthrough	Metrics reconcile with the current reports; security tests pass for each persona
1. Evaluation	Build the evaluation set; run the agent in a test workspace with data team users only	Agreed accuracy threshold met per question type; wrong-answer rate tracked
2. Pilot	10 to 30 named business users (illustrative) in one domain; SQL and metric shown on every answer; feedback loop live	Feedback tickets triaged weekly; no security findings; evaluation set extended with pilot questions
3. Domain rollout	All users of that domain; certified answers for the most frequent questions; usage and cost monitoring	Stable accuracy across two release cycles; owners responding to feedback
4. Expand	Next domains, multi-domain questions, agent-to-agent or API use	Each new domain repeats phases 0 to 2

Skipping phase 0 is the most common failure mode. The second is treating prompt instructions as the place to encode business rules: they are unversioned, untested and invisible to the dashboards, so the agent and the reports drift apart. The third is launching to everyone at once, which turns the first wrong number in an executive meeting into the end of the program.

Where Duces fits

Our Data & AI practice covers the work underneath this: data platform implementation, reporting and visualization on governed data, and AI delivery across operational and analytical applications. For teams that want BI on dbt in their own tenancy, Lightdash+ is open-source Lightdash (the MIT-licensed core) run and supported by us, with metrics defined once in your dbt project, single sign-on, and row and column level security configured and tested. Its Ask Your Metrics add-on applies the approach described in this article: it answers natural-language questions on the LLM endpoint you already use (Azure OpenAI, AWS Bedrock, Google Vertex or a model you host), never writes raw SQL, and composes a Lightdash query instead, so answers resolve the same governed metric definitions as the dashboards and link back to an explore an analyst can check. For catalog and lineage context, DataHub+ is open-source DataHub as a certified, supported distribution in your tenancy, with bring-your-own-LLM AI documentation and monitoring plugins.

If you are planning an AI analyst and want the semantic layer and evaluation set built before the chat box goes live, talk to the people who build it.