DEV Community: Max Mealing

What Is a Semantic Layer? A Practical Guide for Data Engineers

Max Mealing — Thu, 16 Jul 2026 15:26:01 +0000

Your data warehouse has a table called orders. It has columns like amount, status, created_at, and customer_id. Now three people ask "What was Q1 revenue?"

The analyst writes SELECT SUM(amount) FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31'. The data engineer adds WHERE status = 'completed'. Finance excludes refunds and trial conversions. Three queries, three numbers, one question. Nobody is wrong. They just defined "revenue" differently.

Multiply this by every metric in your organization, every team that queries the warehouse, and every tool that displays a number. That's the problem.

A semantic layer solves it by defining each metric once, in one place, and serving that definition to every consumer.

What is a semantic layer?

A semantic layer is a metadata layer between your data warehouse and every tool that queries it. It defines business metrics, maps them to SQL, and exposes them through APIs. Instead of every consumer writing its own query, they all reference the same definition.

When someone asks for "revenue," the semantic layer knows that means:

SUM(CASE WHEN status != 'refunded' AND type != 'trial' THEN amount ELSE 0 END)

That definition lives in one place. Dashboards, APIs, AI agents, and ad-hoc queries all use it. Change the definition once and every consumer gets the updated calculation. No Slack thread asking "which number is right." No detective work tracing a wrong number back to a stale query in a notebook somewhere.

The core components of a semantic layer:

Metrics (measures). The numbers you aggregate: revenue, order count, average deal size. Each metric has a fixed SQL definition.
Dimensions. The columns you filter and group by: date, status, category, region. Dimensions define the axes of analysis.
Relationships (joins). How tables connect: orders belong to customers, products belong to categories. Defined once, reused by every query.
Access rules. Who can see what. Row-level security, tenant isolation, role-based access. Enforced on every query, not bolted on per tool.
Caching rules. Which rollups to pre-compute, how often to refresh, when to invalidate. Performance is part of the definition.

A brief history

The idea of abstracting business logic from raw data isn't new.

In the 1990s, Business Objects introduced "universes," metadata layers that mapped business terms to database columns. Analysts queried "Revenue by Region" without knowing the underlying table structure. It worked, but only within Business Objects.

SSAS (SQL Server Analysis Services) cubes brought multidimensional modeling to the Microsoft stack. You defined measures and dimensions in a cube, and every Excel pivot table and SSRS report consumed the same definitions. Powerful, but tightly coupled to the Microsoft ecosystem.

Looker's LookML (2012) moved semantic modeling into code. Metric definitions lived in version-controlled files, not a GUI. This was a significant shift: data teams could review metric changes in pull requests, not point-and-click editors. But LookML was (and is) proprietary to Looker, which is now proprietary to Google Cloud.

dbt's MetricFlow (2023) brought metric definitions into the dbt ecosystem. Define metrics alongside your transformations. Good idea, but MetricFlow defines metrics without serving them: no caching, no multi-tenancy, no API layer.

The current generation of semantic layers (Cube, AtScale) are standalone infrastructure. They connect to any warehouse, serve any consumer, and run independently of your BI tool. The semantic layer is no longer a feature inside a product. It's a layer in the stack.

What's driving this shift: the number of consumers has exploded. In 2015, a semantic layer served dashboards. In 2026, it also serves AI agents via MCP. One definition needs to serve all of them, and the newest consumer is an agent that queries metrics and then wants to chart the result in the conversation. A BI-embedded semantic layer can't reach that consumer at all.

Why does it matter?

Without a semantic layer, business logic lives in three places: your analysts' heads, scattered SQL files, and buried dashboard definitions. Each copy drifts over time. Someone updates the revenue definition in Looker but forgets the dbt model. A new analyst writes a fresh query from scratch and gets a number that doesn't match either.

The cost isn't abstract:

Inconsistent metrics. Marketing reports 12% growth. Finance reports 8%. The board meeting stalls while someone debugs which number is right. This happens every quarter at companies without a shared metric layer.

Duplicated logic. Every new dashboard, API endpoint, or report re-implements the same calculations. You end up with revenue defined in 14 places, each slightly different, each maintained by a different person. One team discovers a bug in their version and fixes it. The other 13 don't.

Slow onboarding. New data engineers spend weeks learning which queries are canonical and which are stale. "Ask Sarah, she knows which revenue query is the right one." Tribal knowledge doesn't scale.

The data team becomes a service desk. Instead of building, they spend their time answering ad-hoc questions: "Can you pull revenue by region for Q4?" "Which dashboard has the right churn number?" "Why does this report show different numbers than that one?" A semantic layer makes the data self-serve. The team defines the metrics. Everyone else queries them.

AI agents make it worse. An AI agent with warehouse access generates plausible SQL from column names. It doesn't know your business rules. Ask two different agents the same question and you'll get two different numbers. Neither matches finance. Text-to-SQL gives you speed without trust. A semantic layer gives you both.

Types of semantic layers

Not all semantic layers work the same way. The architecture matters because it determines what consumers you can serve, how fast queries run, and how much you control.

Embedded semantic layers live inside a BI tool. Looker's LookML, Tableau's semantic model, Power BI's DAX measures. They define metrics well, but only for that tool's consumers. Your Looker semantic layer doesn't help your React app or your AI agent. If you're in one BI tool and staying there, this can be enough. Most teams outgrow it.

Virtual semantic layers sit between the warehouse and consumers without moving data. AtScale is the primary example. Queries pass through the semantic layer, which translates them to the right SQL dialect and sends them to the warehouse. No data duplication. The tradeoff: every query hits the warehouse, so latency depends on warehouse performance. Virtual layers add caching to mitigate this, but it's an optimization, not the default.

Materialized semantic layers pre-compute rollups and store them separately. Cube's pre-aggregation engine is the best-known example. You define which combinations of measures and dimensions to pre-compute, and the semantic layer builds and maintains those materialized tables. Hot queries hit the cache (single-digit milliseconds). Cold queries fall through to the warehouse. This is how you get sub-second performance at scale.

Hybrid semantic layers combine virtual and materialized approaches. Most modern semantic layers work this way. Define pre-aggregations for your most-queried metrics. Let everything else pass through to the warehouse. You configure the tradeoff per metric based on query frequency and latency requirements.

For most teams, hybrid is the right choice. You get the flexibility of virtual queries with the performance of materialized caching where it matters.

How it works in practice

Modern semantic layers are defined in code (typically YAML), version-controlled in Git, and deployed as an API. Here's what that looks like end to end.

Define your metrics

cubes:
  - name: orders
    sql_table: public.orders
    measures:
      - name: total_revenue
        sql: "CASE WHEN status != 'refunded' AND type != 'trial' THEN amount ELSE 0 END"
        type: sum
      - name: order_count
        type: count
      - name: average_order_value
        sql: amount
        type: avg
    dimensions:
      - name: status
        sql: status
        type: string
      - name: category
        sql: category
        type: string
      - name: created_at
        sql: created_at
        type: time
      - name: customer_id
        sql: customer_id
        type: string

Measures are the numbers you aggregate: revenue, count, average. Dimensions are the columns you filter and group by: status, date, category. total_revenue is now a governed definition, not a column someone interprets.

You define joins between cubes to model relationships:

cubes:
  - name: customers
    sql_table: public.customers
    joins:
      - name: orders
        relationship: one_to_many
        sql: "{customers.id} = {orders.customer_id}"
    measures:
      - name: count
        type: count
    dimensions:
      - name: plan
        sql: plan
        type: string
      - name: signed_up_at
        sql: created_at
        type: time

Now any consumer can query "revenue by customer plan" without knowing how the tables join. The semantic layer handles the SQL generation.

Add caching with pre-aggregations

Queries against raw warehouse tables are expensive, especially when AI agents are querying them frequently. A pre-aggregation caches rollups so consumers get sub-second responses instead of waiting for full table scans.

    pre_aggregations:
      - name: daily_revenue
        measures:
          - total_revenue
          - order_count
        dimensions:
          - status
        time_dimension: created_at
        granularity: day
        refresh_key:
          every: 1 hour

The semantic layer rebuilds this rollup every hour. Hot queries hit the cache. Cold queries fall through to the warehouse. You configure the tradeoff per metric: high-traffic metrics get aggressive caching, low-traffic metrics query the warehouse directly.

Define access control

For B2B products serving multiple customers, every query needs to be scoped to the right tenant. A security context enforces this structurally, not through prompt engineering or middleware hacks.

    security_context:
      - name: tenant_filter
        sql: "{SECURITY_CONTEXT.tenant_id} = customer_id"

Every query for a given tenant automatically includes this filter. It's not optional. The consumer can't skip it or override it. This is how RBAC works in a semantic layer: defined in the schema, enforced on every query. It doesn't matter if the consumer is a dashboard, an API call, or an AI agent. The access rules are the same.

Expose to consumers

One set of definitions, multiple consumers. A BI dashboard queries via REST API. A backend service calls an SDK. An AI agent discovers and queries metrics over MCP. They all hit the same definitions and get the same numbers. That's the point.

The agent is the consumer that changed the most. It queries a metric, then wants to show the result, not as a wall of numbers but as a chart in the conversation. That's where @bonnard/mcp-charts comes in: a visualize tool you add to your MCP server that renders interactive charts from your query results, right in Claude or ChatGPT.

import { addCharts } from "@bonnard/mcp-charts";

// rows come back from your warehouse, the chart renders in the client
addCharts(server, { runSql });

The agent calls visualize with a query, your runSql returns the rows, Bonnard infers the chart from the typed result, and an interactive widget renders in the host. You write no frontend code, and Bonnard never touches your database. More in the MCP Charts guide.

Real-world use cases

Customer-facing analytics in a B2B SaaS product

You're building a SaaS product and your customers want to see their data. Usage metrics, billing summaries, performance dashboards. Without a semantic layer, you build custom API endpoints for each view, maintain the SQL yourself, and hope the numbers match what your internal dashboards show.

With a semantic layer: define the metrics once with row-level security, and let customers query their data through your product's UI or through their own AI agents (via MCP). Multi-tenancy is structural. The same metric definition serves every customer with automatic data isolation.

Replacing internal dashboards nobody opens

Your team built 47 Metabase dashboards. Three of them get used. The rest are stale, broken, or duplicated. The data team spends time maintaining dashboard infrastructure instead of defining the metrics that matter.

A semantic layer shifts the model: the data team defines and governs metrics. Consumers choose their own surface. Some use dashboards. Some query the API. Some ask an AI agent. The data team's job is the definitions, not the delivery mechanism.

Connecting AI agents to governed data

Your engineering team wants to ship an AI-powered analytics feature. The first prototype uses text-to-SQL. It works in demos and breaks in production: inconsistent numbers, no tenant isolation, no audit trail. The fix isn't better prompts. It's a semantic layer. The agent queries metric definitions instead of raw tables. Every answer traces back to a versioned, governed definition. Full walkthrough: How to Connect an AI Agent to Your Data Warehouse.

Semantic layer vs. dbt metrics

This is the most common question in the space, and the answer is: they're complementary, not competing. But the differences matter.

dbt defines transformations: how raw data becomes clean tables. It runs SQL models that create dim_customers, fct_orders, and other modeled tables in your warehouse. This is the "T" in ELT. dbt is excellent at this.

dbt's MetricFlow adds metric definitions on top of those tables. You can define revenue as a metric in your dbt project. This is genuinely useful for documentation and consistency within dbt's ecosystem.

Where dbt stops: MetricFlow defines metrics but doesn't serve them to production consumers. There's no built-in:

Caching or pre-aggregation. Every query hits the warehouse.
Multi-tenancy. No per-tenant isolation for B2B use cases.
API serving. No REST API or SDK for applications to query.
AI agent integration. No MCP server or tool-use interface.
Row-level security. Access control is warehouse-level, not metric-level.

A standalone semantic layer fills these gaps. It connects to the same warehouse where dbt outputs its modeled tables, defines metrics on top, and serves them with caching, multi-tenancy, and multiple APIs.

The practical workflow: dbt transforms your raw data into clean tables on a schedule. The semantic layer defines business metrics on those tables and serves them to dashboards, applications, and AI agents in real time. Many semantic layers can import dbt models directly. You don't choose between them. You use both.

Semantic layer tools compared

Several tools occupy this space, each with different design priorities.

Tool	Approach	Strengths	Limitations
Cube	Open-source semantic layer engine	Battle-tested query engine, pre-aggregation, REST API, large community	No built-in MCP, dashboards, or multi-tenant keys. You build the product layer yourself.
dbt Semantic Layer / MetricFlow	Metric definitions in dbt	Tight dbt integration, SQL-native, good for documentation	Defines metrics but doesn't serve them. No caching, no multi-tenancy, no agent support.
Looker (LookML)	BI-embedded semantic layer	Mature modeling language, strong governance, Google Cloud integration	Expensive, enterprise-only, proprietary. Definitions locked to Looker's ecosystem. No MCP.
AtScale	Virtual semantic layer	Enterprise BI compatibility, no data movement, MDX/DAX support	Enterprise pricing. Not agent-native. Complex deployment.
Power BI (DAX)	BI-embedded semantic layer	Deep Microsoft integration, familiar to analysts	Locked to Power BI ecosystem. No API serving. No multi-tenancy for embedded use cases.
Tableau (Semantic Model)	BI-embedded semantic layer	Visual modeling, strong visualization	Definitions don't leave Tableau. No API access. Salesforce pricing.
Bonnard	Interactive charts via the MCP `visualize` tool	`@bonnard/mcp-charts` adds a `visualize` tool to your MCP server, rendering charts in Claude/ChatGPT from your query results. No frontend code.	Charts the result, not a full semantic layer engine. Pair it with one.

The right choice depends on your primary consumer. If internal dashboards are the only output and you're already in Looker or Power BI, the embedded semantic layer may be sufficient. If you're serving multiple consumers (customer-facing products, AI agents, APIs, internal dashboards), you need a standalone semantic layer that isn't locked to one tool.

Warehouse-specific considerations

Your semantic layer needs to work with your warehouse. The integration isn't just "can it connect." It's how well the semantic layer exploits each warehouse's strengths.

Snowflake. Pre-aggregations work well with Snowflake's auto-suspend compute. The semantic layer can target specific virtual warehouses for different workloads: fast cache rebuilds on a small warehouse, heavy ad-hoc queries on a larger one. Snowflake's separation of storage and compute means pre-aggregation tables don't compete with production workloads.

BigQuery. BigQuery's slot-based pricing means pre-aggregations save real money. Every query scans data and costs money. Caching a daily revenue rollup avoids scanning terabytes of raw data on every request. The semantic layer's pre-aggregation layer can cut BigQuery costs by 10-100x for frequently queried metrics.

Databricks. Unity Catalog provides table-level governance. The semantic layer adds metric-level governance on top: defining what "revenue" means, not just who can access the orders table. Databricks' Photon engine handles the heavy queries. The semantic layer caches the results.

PostgreSQL (including Supabase, Neon, and RDS). Works well for smaller datasets and development. Most teams start with Postgres for prototyping their semantic layer and migrate to a cloud warehouse as data volume grows. Pre-aggregation is especially valuable here because Postgres doesn't have the raw compute power of cloud warehouses for large analytical queries.

DuckDB (including MotherDuck). In-process analytical database. Useful for local development and testing without a remote warehouse connection. Define your metrics, test locally against DuckDB, deploy to production against Snowflake or BigQuery. Same schema, different warehouses.

How to evaluate a semantic layer

If you're choosing a semantic layer for your stack, here's what to look at:

Who are your consumers? This is the most important question. If it's only internal dashboards, an embedded semantic layer in your BI tool might be enough. If it's dashboards plus a customer-facing product plus AI agents, you need a standalone semantic layer with API serving.

Does it support your warehouse? Check for native support, not just "we can connect via JDBC." Native support means the semantic layer generates optimized SQL for your specific warehouse dialect and exploits warehouse-specific features like Snowflake's clustering or BigQuery's partitioning.

How does it handle caching? Pre-aggregation support varies widely. Some tools don't cache at all (every query hits the warehouse). Some cache at the query level. The best cache at the metric level with configurable refresh schedules and automatic invalidation. If AI agents are querying your data, caching is not optional.

Multi-tenancy. If you're building a B2B product, every query needs tenant isolation. Look for structural multi-tenancy (security context in the schema), not middleware-level filtering. One missed filter is a data leak.

Schema-as-code. Metric definitions should live in version control. Changes should go through pull requests. Rollbacks should be git revert, not clicking through a UI. If you can't diff your metric definitions, you can't govern them.

AI agent support. If AI agents are current or future consumers, look for MCP support or a tool-use interface. An API is the minimum. MCP is the emerging standard for agent-to-tool communication and gives agents native discovery of available metrics.

When do you need a semantic layer?

Not every team needs one. Here's when it starts to matter.

You need one when:

Multiple consumers query the same metrics (dashboards + API + agents)
Different teams report different numbers for the same question
You're shipping analytics to customers (B2B embedded analytics)
AI agents need access to your data with governance
New analysts spend days learning which queries are canonical
Your data team is stuck answering ad-hoc metric questions instead of building

You probably don't need one when:

One analyst queries the warehouse with SQL and shares results in spreadsheets
You have a single dashboard tool and no API consumers
Your data model is small enough that everyone knows the canonical queries

The threshold is usually "more than one consumer for the same metrics." Once dashboards, APIs, and applications all need revenue numbers, a shared definition pays for itself immediately.

Getting started

If you're evaluating options, start with what your primary consumer needs. Internal dashboards only? Your BI tool's built-in semantic layer may be enough. dbt-native workflow with documentation as the goal? Start with MetricFlow. Multiple consumers including AI agents? A standalone semantic layer like Cube or AtScale, with metrics defined once and served over an API.

When AI agents are a consumer, the last mile is the chart. An agent queries a metric, then needs to show the result in the conversation. Add that with one tool:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, { runSql });

addCharts registers a visualize tool on your MCP server. The agent calls it with a query, your runSql returns the rows, and Bonnard renders an interactive chart in Claude or ChatGPT. Bonnard never touches your database. Repo: github.com/bonnard-data/mcp-charts. Full walkthrough: MCP Charts.

Frequently asked questions

What is a semantic layer in simple terms?

A semantic layer is a single place where you define what business terms like "revenue" or "active user" mean in terms of database columns and calculations. Every tool that needs that number references the same definition instead of writing its own query. One definition, every consumer, same answer.

What is the difference between a semantic layer and a data warehouse?

A data warehouse stores your data. A semantic layer defines what the data means. The warehouse has a column called amount. The semantic layer defines that total_revenue = SUM(amount) WHERE status != 'refunded'. They work together: the warehouse holds the data, the semantic layer holds the business logic.

Is dbt a semantic layer?

dbt is primarily a transformation tool that models raw data into clean tables. dbt's MetricFlow adds metric definition capabilities, which is one part of a semantic layer. But a full semantic layer also includes caching, multi-tenancy, access control, and API serving, which MetricFlow doesn't provide on its own. Most teams use dbt for transformations and a separate semantic layer for metric serving.

What is the difference between a semantic layer and an OBT (One Big Table)?

An OBT flattens everything into a single denormalized table. A semantic layer keeps your data normalized and defines metrics as calculations across tables. OBTs are simpler at first but break down at scale: they're expensive to rebuild, hard to govern, and create massive tables with redundant data. A semantic layer gives you the same query simplicity without the storage and maintenance cost.

What is the difference between a semantic layer and a metrics layer?

The terms are often used interchangeably, but a metrics layer is a subset of a semantic layer. A metrics layer defines measures and dimensions. A semantic layer adds relationships (joins), access control, caching, and API serving on top. dbt's MetricFlow is a metrics layer. Cube and AtScale are full semantic layers.

Do I need a semantic layer for AI agents?

If AI agents query your data, a semantic layer is the difference between "plausible numbers" and "correct numbers." Without one, agents interpret column names and generate ad-hoc SQL. With one, agents query governed metric definitions and get the same answer every time. See What Is an Agentic Semantic Layer? for the full breakdown.

How does a semantic layer handle performance?

Through pre-aggregation. The semantic layer pre-computes common rollups (daily revenue by status, weekly order count by category) and caches the results. Queries hit the cache instead of scanning raw tables. Hot queries resolve in single-digit milliseconds. The cache rebuilds on a configurable schedule with automatic invalidation when source data changes.

Can I use a semantic layer with Snowflake, BigQuery, and other warehouses?

Yes. Modern semantic layers are warehouse-agnostic. You configure the connection to your warehouse (Snowflake, BigQuery, Databricks, PostgreSQL (including Supabase, Neon, and RDS), Redshift, DuckDB (including MotherDuck)) and the semantic layer generates the appropriate SQL dialect. Swap warehouses without changing your metric definitions.

What is a universal semantic layer?

A universal semantic layer serves every consumer from one set of definitions, regardless of what tool they use. Instead of defining metrics separately in Looker, Power BI, and your application code, you define them once and serve them everywhere. "Universal" means tool-agnostic: the semantic layer isn't embedded in any single BI product.

Turn Your dbt Project Into a Semantic Layer for Agents

Max Mealing — Thu, 16 Jul 2026 15:25:58 +0000

Draft placeholder. Full article coming soon.

You already have dbt models, tests, and a manifest. This post will show how to turn that into a governed semantic layer agents can query, without rebuilding your stack.

What this will cover

Reading your dbt manifest to bootstrap metrics and relationships
Defining metrics once so every consumer gets the same number
Tagging the tenant key that scopes each customer's rows
Exposing governed metrics to agents over MCP
Charting the agent's query results in Claude or ChatGPT with @bonnard/mcp-charts

Frequently asked questions

Do I need to migrate off dbt?

No. A semantic layer reads your existing dbt models. You keep dbt as your transformation layer.

What if I only have models, not a semantic layer?

That's the common case. You bootstrap from the manifest and define metrics on top.

How do agents chart the results?

Add a visualize tool to your MCP server with @bonnard/mcp-charts. The agent queries a metric, your runSql returns the rows, and an interactive chart renders in Claude or ChatGPT. Bonnard never touches your database. See the MCP Charts guide.

Self-Service BI Is a Lie (Unless You Govern the Metrics)

Max Mealing — Thu, 16 Jul 2026 15:25:55 +0000

Self-service BI was supposed to free the data team. Give everyone a BI tool, teach them to drag and drop, and they'll answer their own questions. The data team can stop building dashboards and focus on infrastructure.

That's not what happened.

What happened: everyone builds their own dashboards with their own metric definitions. Marketing's "active users" counts monthly logins. Product's "active users" counts weekly feature usage. Finance's "active users" counts paying customers. Three dashboards, three numbers, one term. The data team now spends their time reconciling conflicting metrics instead of building.

Self-service BI without governed metrics is just self-service chaos.

What is self-service BI?

Self-service BI means non-technical users can query data, build visualizations, and generate reports without help from the data team. The tools (Metabase, Looker, Power BI, Tableau, Superset) provide drag-and-drop interfaces, visual query builders, and template libraries.

The promise: democratize data access. Anyone can answer their own questions.

The reality: it works for simple questions ("how many orders this week?") and breaks for anything requiring business logic ("what's our net revenue retention?"). Users either define metrics incorrectly or ask the data team anyway. The data team becomes a help desk for the self-service tool instead of a help desk for SQL.

Where self-service BI goes wrong

Metric sprawl

Without a central definition, every self-service user creates their own version of key metrics. A company with 50 Metabase users might have 30 different "revenue" calculations saved across collections. Which one is right? The one that matches the board report. Which one matches the board report? Nobody knows without checking each one.

The "someone who knows" bottleneck

True self-service requires understanding the data model. Which table has revenue? What does status = 3 mean? Is the amount column in cents or dollars? Pre-tax or post-tax? Including shipping or excluding?

Without this context, self-service users either guess (and get wrong answers) or ask the data team (and it's not self-service). The BI tool gives access to data. It doesn't give understanding of what the data means.

Dashboard proliferation

Every self-service user creates dashboards for their use case. The dashboard count grows from 10 to 500. Most are stale. Many are duplicates. Finding the right dashboard becomes its own research project. The data team periodically "cleans up," deleting abandoned dashboards and breaking someone's workflow each time.

Open source BI tools and their limitations

Open-source BI tools make self-service accessible without enterprise licensing. The main options:

Metabase (AGPL): Most popular. Visual query builder, drag-and-drop dashboards. No semantic layer. Multi-tenancy requires Enterprise license.
Superset (Apache 2.0): SQL-native, powerful for technical teams. No semantic layer. Limited multi-tenancy.
Redash (BSD): SQL editor focused. Simple, lightweight. No governance features. Maintenance mode since Databricks acquired it.
Lightdash (MIT): dbt-native. Good for teams already using dbt. Growing community. No MCP or embedded SDK.
Evidence (Apache 2.0): Code-first reporting. Markdown + SQL. Good for scheduled reports. Not designed for embedded or multi-tenant use cases.

They share the same fundamental limitation: no semantic layer. Users query raw tables and define metrics ad-hoc. The open-source tools democratize access. They don't democratize understanding.

What real self-service looks like

Real self-service means anyone can query data and get the right answer. Not "anyone can query data and get an answer." The difference is governance.

The data team defines metrics

cubes:
  - name: revenue
    sql_table: analytics.revenue
    measures:
      - name: total_revenue
        sql: "CASE WHEN status != 'refunded' AND type != 'trial' THEN amount ELSE 0 END"
        type: sum
        description: "Finance-approved revenue definition (excludes refunds and trials)"
      - name: net_revenue_retention
        sql: "current_period_mrr / prior_period_mrr"
        type: avg
        description: "NRR for existing customer cohorts"
    dimensions:
      - name: plan
        sql: plan_name
        type: string
      - name: region
        sql: region
        type: string
      - name: period
        sql: period_date
        type: time

These definitions live in Git. Finance approves them in a pull request. When the definition changes, every consumer gets the update. There's one "revenue." Not 30.

Everyone queries the same definitions

The product manager asks an AI agent: "What's revenue by plan this quarter?" The agent calls explore_schema, finds revenue.total_revenue, and queries it. Same definition finance approved.

The customer success team views an embedded analytics dashboard in the product. Same definition.

The CEO checks a markdown dashboard. Same definition.

Nobody re-implements the metric. Nobody guesses which table to query. The data team defined it once. Every surface serves it.

The data team builds, not debugs

With governed metrics, the data team's job shifts from "answer ad-hoc questions" and "reconcile conflicting dashboards" to "define and maintain metric definitions." They add new metrics, refine existing ones, and optimize pre-aggregation strategies. The mechanical work (querying, formatting, presenting) is handled by the semantic layer and its consumers.

Self-service BI tools compared

Tool	Self-service model	Governed metrics	AI agent support	Open source
Metabase	Visual query builder	No	No	Yes (AGPL)
Superset	SQL + visual builder	No	No	Yes (Apache 2.0)
Redash	SQL editor	No	No	Yes
Power BI	Drag-and-drop	DAX (PBI only)	Copilot	No
Tableau	Drag-and-drop	Tableau model	Limited	No
Looker	LookML + Explore	LookML	No	No
ThoughtSpot	Natural language search	Proprietary	Proprietary	No
Charts in AI agent (Bonnard)	Charts inside Claude or ChatGPT via MCP	N/A (your `runSql`)	Native (`visualize` tool)	Yes (npm)

The tools in the top half give users direct data access with no metric governance. The tools in the bottom half add governance but lock it to their ecosystem. The bottom row is a different kind of option: @bonnard/mcp-charts does not replace a BI tool. It adds a visualize tool to your MCP server so customers get interactive charts inside Claude or ChatGPT, rendered from your own query results.

When traditional self-service BI is enough

Self-service BI works when:

A small team (< 10 analysts) agrees on metric definitions informally
One BI tool serves all consumers
Internal use only (no customers seeing the numbers)
The data model is simple enough that users understand it
You have someone who maintains a "source of truth" dashboard that others copy

It breaks when:

Multiple teams define the same metric differently
You need metrics in dashboards AND APIs AND AI agents AND embedded analytics
Customers see the numbers (trust and consistency matter)
The data team spends more time reconciling than building
New hires take weeks to find the right dashboard

Self-service for customers, inside the agent

Governed metrics fix the consistency problem for internal self-service. There's a separate question for customer-facing self-service: where does the customer ask their question? Increasingly the answer is an AI agent. They want to ask about their data in Claude or ChatGPT and get a chart back.

@bonnard/mcp-charts covers that surface. It is not a BI tool or a dashboard product. It is a visualize tool you add to your MCP server that renders interactive charts from your query results, inside the agent.

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, {
  runSql: async (sql) => db.query(sql),
});

addCharts registers a visualize tool (and visualize_read_me). The agent calls visualize, your runSql returns rows, Bonnard infers the chart from the typed result, and it renders an interactive ui:// widget in Claude or ChatGPT. Chart types: line, bar, area, pie, scatter, funnel, waterfall, and table. Native adapters cover Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql. Bonnard never touches your database.

For background: What Is a Semantic Layer?. For the full walkthrough: MCP charts.

Repo: github.com/bonnard-data/mcp-charts.

Frequently asked questions

What is self-service BI?

Self-service BI is a BI model where non-technical users can query data, build visualizations, and generate reports without help from the data team. Tools like Metabase, Power BI, and Tableau provide drag-and-drop interfaces for this. The limitation: self-service without governed metrics leads to inconsistent numbers across the organization.

What is the best open-source BI tool?

For internal dashboards: Metabase (most popular), Superset (more technical), or Redash (SQL-focused). For governed metrics served to multiple surfaces including AI agents: a semantic layer approach. Open-source BI tools give you self-service querying. A semantic layer gives you self-service with correct, governed answers.

What is the difference between self-service BI and a semantic layer?

Self-service BI gives users tools to query data. A semantic layer defines what the data means. They're complementary: a self-service BI tool with a semantic layer underneath gives users access to governed metrics. Without the semantic layer, self-service users define metrics ad-hoc and the numbers diverge.

How do I prevent metric chaos in self-service BI?

Define each metric once in a semantic layer. Version the definitions in Git. Review changes in pull requests. Serve the same definitions to every consumer. When everyone queries the same governed definitions, there's nothing to reconcile.

How do I offer self-service BI for customers in 2026?

Decide where customers ask their questions. A traditional embedded BI tool puts a dashboard in your product. The newer surface is the AI agent: customers ask in Claude or ChatGPT and get a chart back. For that surface, @bonnard/mcp-charts adds a visualize tool to your MCP server that renders interactive charts from your query results. The two are complementary, not substitutes. Install with npm install @bonnard/mcp-charts.

Real-Time Analytics: When You Need It and When You Don't

Max Mealing — Thu, 16 Jul 2026 15:25:52 +0000

"We need real-time analytics" is one of the most common requests in data engineering. It's also one of the most misunderstood. When the VP of Sales says "real-time," they usually mean "faster than the dashboard that refreshes overnight." When the CTO says it, they might mean sub-second event streaming. The gap between those two definitions is a 6-month infrastructure project.

Most teams don't need true real-time. They need fast enough. And "fast enough" is achievable with pre-aggregation caching at a fraction of the complexity and cost of a streaming architecture.

What is real-time analytics?

Real-time analytics means querying data with minimal latency between when an event happens and when it's visible in your analytics. The spectrum:

Freshness	Latency	Architecture	Use case
True real-time	< 1 second	Event streaming (Kafka, Flink)	Fraud detection, stock trading, live monitoring
Near real-time	1-60 seconds	Micro-batch or streaming	Operational dashboards, alerting
Frequent refresh	1-60 minutes	Scheduled refresh + caching	KPI dashboards, AI agent queries
Batch	Hours to daily	Scheduled ETL	Board reports, monthly summaries

Most analytics use cases fall in the "frequent refresh" category. Revenue by region doesn't need sub-second freshness. Active users in the last hour doesn't need event streaming. A pre-aggregation cache that refreshes every 15 minutes covers 90% of what teams call "real-time."

When you actually need real-time

True real-time analytics (sub-second latency from event to query result) is worth the infrastructure investment when:

Fraud detection. Every second of delay is potential fraud that slips through.
Live monitoring. Server health, API error rates, active user counts for live products.
Trading and pricing. Financial instruments where stale data means wrong prices.
Live events. Streaming metrics during a product launch, marketing campaign, or live broadcast.

If you're in one of these categories, you need an event streaming architecture: Kafka, Flink, Materialize, or similar. A semantic layer complements this by governing the metric definitions, but the freshness comes from the streaming pipeline.

When you don't (and what to use instead)

Most business analytics doesn't need sub-second freshness. It needs:

Fast query response (milliseconds, not seconds)
Reasonably fresh data (minutes or hours, not days)
Consistent numbers (same answer everywhere)

Pre-aggregation gives you all three without a streaming architecture.

How pre-aggregation works

The semantic layer pre-computes your most common queries and caches the results. When a dashboard or AI agent asks for "revenue by region this month," the query hits the cache instead of scanning raw warehouse tables.

cubes:
  - name: orders
    sql_table: public.orders
    measures:
      - name: total_revenue
        sql: "CASE WHEN status != 'refunded' THEN amount ELSE 0 END"
        type: sum
      - name: order_count
        type: count
    dimensions:
      - name: region
        sql: region
        type: string
      - name: created_at
        sql: created_at
        type: time

    pre_aggregations:
      - name: revenue_by_region
        measures:
          - total_revenue
          - order_count
        dimensions:
          - region
        time_dimension: created_at
        granularity: day
        refresh_key:
          every: 15 minutes

The cache rebuilds every 15 minutes. Queries that match the pre-aggregation return in single-digit milliseconds. Queries that don't match fall through to the warehouse (seconds).

The result: your dashboard loads in under a second. Your AI agent gets responses in milliseconds. Your data is at most 15 minutes old. For 90% of analytics use cases, this is indistinguishable from "real-time."

Different refresh rates for different metrics

Not all metrics need the same freshness. Configure per rollup:

    pre_aggregations:
      # Operational: refresh every 5 minutes
      - name: active_users_live
        measures: [active_users]
        time_dimension: last_seen_at
        granularity: minute
        refresh_key:
          every: 5 minutes

      # Strategic: refresh every hour
      - name: daily_revenue
        measures: [total_revenue, mrr]
        dimensions: [plan, region]
        time_dimension: created_at
        granularity: day
        refresh_key:
          every: 1 hour

      # Historical: refresh daily
      - name: monthly_cohorts
        measures: [retention_rate, ltv]
        dimensions: [cohort_month, plan]
        time_dimension: created_at
        granularity: month
        refresh_key:
          every: 24 hours

Operational metrics refresh every 5 minutes. Revenue refreshes hourly. Historical cohorts refresh daily. Each metric gets the freshness it needs without over-engineering.

Real-time analytics for B2B products

If you're shipping analytics to B2B customers, "real-time" has a different meaning. Your customers expect their dashboards to load fast and show recent data. They don't expect sub-second event streaming.

Pre-aggregation handles this well:

Fast load times. Cached queries return in milliseconds. Customer dashboards feel instant.
Multi-tenant caching. The cache respects tenant isolation. Customer A's cached rollup doesn't include Customer B's data. Security context filters apply at cache build time.
Cost control. On usage-based warehouses (BigQuery, Snowflake), fewer raw queries means lower costs. Serving from cache is essentially free.
AI agent compatibility. Agents make multiple queries per interaction. Pre-aggregation keeps response times low even with agent-scale query volumes.

Real-time analytics architecture compared

Approach	Freshness	Query speed	Complexity	Cost	Best for
Event streaming (Kafka + Flink)	Sub-second	Fast (materialized views)	Very high	High	Fraud, trading, live monitoring
Streaming DB (Materialize, RisingWave)	Seconds	Fast	High	Medium-high	Continuous queries, CDC
Warehouse + pre-aggregation	Minutes	Milliseconds (cached)	Low	Low-medium	Dashboards, AI agents, embedded analytics
Warehouse direct	Minutes-hours (ETL dependent)	Seconds	Low	Medium	Ad-hoc analysis, batch reports
Spreadsheet	Manual	N/A	None	Free	One-off analysis

For most teams, the warehouse + pre-aggregation approach covers the use case. Add streaming only for the metrics that genuinely need sub-second freshness.

Getting started

Once your queries return fast enough, agents become a consumer, and an agent that fetches rows still has to chart them. Add a visualize tool to your MCP server so it can:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, { runSql });

That registers the visualize tool on your server. The agent calls it with a query, your runSql returns the rows (from a cached rollup or the warehouse), and Bonnard renders an interactive chart inside Claude or ChatGPT. Adapters ship for Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql.

Full walkthrough: Add Interactive Charts to Your MCP Server. For the design choices behind the tool: How Bonnard Builds Agent-Friendly MCPs.

Source is on GitHub.

Frequently asked questions

What is real-time analytics?

Real-time analytics means querying data with minimal delay between when events happen and when they appear in your analytics. True real-time (sub-second) requires event streaming. Near-real-time (minutes) is achievable with pre-aggregation caching on top of a data warehouse.

Do I need real-time analytics?

Most business analytics doesn't need sub-second freshness. If your use case is dashboards, AI agent queries, or embedded analytics, a pre-aggregation cache refreshing every 5-60 minutes provides "real-time" performance at a fraction of the streaming complexity. True real-time is needed for fraud detection, live monitoring, and financial trading.

What is the difference between real-time and batch analytics?

Batch analytics processes data on a schedule (hourly, daily). Real-time analytics processes data continuously with minimal delay. Pre-aggregation caching sits in between: the cache rebuilds on a schedule (every N minutes), but queries against the cache return in milliseconds. It's batch refresh with real-time query performance.

How does pre-aggregation make analytics feel real-time?

Pre-aggregation pre-computes your most common queries and caches the results. When a user or AI agent queries "revenue by region," the response comes from the cache in single-digit milliseconds instead of scanning raw warehouse tables (which takes seconds). The data is minutes old, but the query feels instant.

What is streaming analytics?

Streaming analytics processes events as they arrive, maintaining continuously updated query results. Technologies include Kafka, Flink, Materialize, and RisingWave. It's the right choice for sub-second freshness requirements. For most business analytics, pre-aggregation caching provides comparable user experience at lower cost and complexity.

KPI Dashboards Are Broken. Here's What Replaces Them.

Max Mealing — Thu, 16 Jul 2026 15:25:47 +0000

Your company has a KPI dashboard. It was built six months ago by someone who has since moved teams. It shows revenue, churn, and a few product metrics. It loads slowly. The numbers don't match what finance reports. Nobody trusts it, but everyone screenshots it for the Monday standup.

This is the state of KPI dashboards at most companies. Not because the tools are bad, but because the approach is wrong. A dashboard is a static view of a dynamic system. The moment someone builds it, it starts drifting from reality.

What is a KPI dashboard?

A KPI (Key Performance Indicator) dashboard is a visual display of an organization's most important metrics. Revenue, customer count, churn rate, conversion rate, average order value, NPS. The metrics that tell you whether the business is healthy.

Traditional KPI dashboards live in a BI tool: Power BI, Tableau, Looker, Metabase, Grafana. An analyst builds the dashboard, connects it to a data source, and shares a link. People visit the dashboard (or receive a scheduled screenshot) to check the numbers.

The concept is sound. The execution breaks for predictable reasons.

KPI dashboard examples

Before diving into what's broken, here's what teams typically build:

SaaS executive dashboard. MRR, ARR, net revenue retention, churn rate, new customers this month, average contract value. Updated daily. Viewed by the CEO and board. The numbers must match the finance team's report exactly.

Product analytics dashboard. Daily active users, feature adoption rates, conversion funnel stages, time to value. Updated hourly. Viewed by product managers. Often the first dashboard built and the first to drift from reality.

Customer health dashboard (B2B). Per-customer usage metrics, support ticket volume, NPS, renewal risk score. Updated daily. Viewed by customer success. In embedded analytics use cases, the customer sees this too.

Engineering operations dashboard. API error rates, p95 latency, deployment frequency, uptime. Updated real-time. Viewed by engineering leads. Often built in Grafana rather than a BI tool.

Sales pipeline dashboard. Open deals by stage, weighted pipeline value, win rate, average deal cycle. Updated daily. Viewed by sales leadership. Usually lives in Salesforce or a connected BI tool.

Each of these follows the same pattern: connect to a data source, write queries, build charts, share a link. The problems start when multiple dashboards need the same metric (revenue, active users, churn) and define it differently.

Why KPI dashboards fail

The metrics drift

Revenue is defined in the dashboard as SUM(amount) WHERE status = 'completed'. Finance defines it as SUM(amount) WHERE status != 'refunded' AND type != 'trial'. The CFO looks at the dashboard, compares it to the finance report, and stops trusting the dashboard. This happens every quarter at companies without a shared metric layer.

The root cause: business logic is defined in the dashboard, not in a governed layer. Every dashboard that shows revenue re-implements the calculation. Each implementation drifts independently.

Nobody opens them

Dashboards that load in 8 seconds don't get used. Dashboards behind a BI tool login don't get used. Dashboards that require navigating to a separate tool don't get used. The data team builds 50 dashboards. Three get regular traffic. The rest are abandoned.

The data team keeps building new ones because stakeholders keep asking. Each request means a new dashboard, a new query, a new maintenance burden. The team becomes a dashboard factory instead of building the metrics layer that would make dashboards unnecessary.

They only serve one surface

A KPI dashboard in Looker serves people who open Looker. What about:

The product team that wants metrics in their React app?
The customer success team that wants account health in Slack?
The AI agent that needs to answer "how's revenue trending?"
The B2B customer that wants their own usage dashboard inside your product?
The executive who wants a weekly email summary?

Each of these requires a different integration. The dashboard metric definitions don't transfer. You end up rebuilding the same KPIs in every tool, with each copy drifting independently.

Stale data, stale insight

Most KPI dashboards refresh on a schedule: hourly, daily, sometimes manually. Between refreshes, the numbers are stale. For operational KPIs (active users right now, orders this hour, API error rate), stale data is useless data.

Even with real-time refresh, the dashboard is a passive display. It shows numbers. It doesn't answer questions. "Revenue is down 12% this week" is visible on the dashboard. "Why?" requires a human to open the BI tool, write a query, drill into dimensions, and figure it out. An AI agent could answer that question in seconds, but the dashboard can't feed an agent.

The alternative: a KPI chart the agent renders on demand

The static dashboard is a snapshot someone built once. The agent path is different: instead of opening a tool and reading a chart someone else laid out, you ask "how's revenue trending?" and the agent runs the query and draws the chart in the conversation.

That last step (drawing the chart) is what @bonnard/mcp-charts adds: a visualize tool you add to your MCP server that renders interactive charts from your query results. The agent already has access to your data. The visualize tool turns the rows it gets back into a chart the person can actually read.

Add the visualize tool

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

// runSql runs against your warehouse and returns typed rows
addCharts(server, { runSql });

addCharts registers a visualize tool (plus a visualize_read_me companion) on your MCP server. Bonnard never touches your database. Your runSql does, against Postgres, BigQuery, Snowflake, Databricks, DuckDB, or any source you wire up.

How a KPI request flows

Someone asks the agent "how's MRR trending this quarter?"
The agent calls visualize with a query, and your runSql returns the rows.
Bonnard infers the chart from the typed result: a line chart for MRR over time, a bar chart for revenue by plan, a funnel for a conversion sequence.
Claude or ChatGPT renders an interactive ui:// widget right in the conversation.

The chart types cover the KPI cases you'd reach for on a dashboard: line, bar, and area for trends, bar variants (stacked, grouped, horizontal, 100%) for breakdowns, pie for shares, scatter for distributions, funnel and waterfall for staged metrics, and a table when the answer is a table. Axes, number formatting, and gap-fill come from the typed schema, so the same KPI query renders the same chart every time.

No dashboard to patch. The KPI gets drawn the moment someone asks, from whatever query answers the question.

KPI dashboard tools compared

Approach	Metric governance	Surfaces	AI agent support	Multi-tenant	Performance
Power BI dashboard	DAX (locked to PBI)	Power BI only	Copilot (internal)	Complex	Import mode or DirectQuery
Tableau dashboard	Semantic model (locked)	Tableau only	Limited	Enterprise	Extract or live
Looker dashboard	LookML	Looker + embed	No	Enterprise	PDTs
Metabase dashboard	None	Metabase + embed	No	Enterprise only	No caching
Grafana dashboard	None	Grafana only	No	Basic	Query-level
Superset dashboard	None	Superset + embed	No	Limited	No caching
Bonnard	Inherits your `runSql`	Claude and ChatGPT (MCP apps)	MCP native (`visualize` tool)	Inherits your `runSql`	Inherits your warehouse

The traditional tools render KPIs inside the dashboard tool. Bonnard renders them as interactive charts via the MCP visualize tool, from query results, inside Claude or ChatGPT.

When to keep your KPI dashboard

KPI dashboards aren't always wrong. They work when:

One team, one tool, one audience
The metrics are simple and stable
Nobody else needs the same numbers in a different format
You don't need AI agent access or embedded analytics
The dashboard builder is still on the team and maintains it

They break when:

Multiple teams need the same KPIs in different tools
Customers need to see their metrics inside your product
AI agents need to query KPIs
The dashboard shows different numbers than finance reports
The data team spends more time maintaining dashboards than defining metrics

Getting started

Add a visualize tool to your MCP server so an agent can render a KPI chart on demand:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, { runSql });

That registers the visualize tool on your server. Someone asks the agent for a KPI, your runSql returns the rows, and Bonnard renders an interactive chart inside Claude or ChatGPT. Adapters ship for Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql.

Full walkthrough: Add Interactive Charts to Your MCP Server. For the design choices behind the tool: How Bonnard Builds Agent-Friendly MCPs.

Source is on GitHub.

Frequently asked questions

What is a KPI dashboard?

A KPI dashboard is a visual display of key performance indicators: revenue, churn, active users, conversion rate, and other metrics that measure business health. Traditional KPI dashboards live in BI tools like Power BI, Tableau, Looker, or Grafana. Modern approaches define KPIs in a semantic layer and serve them across dashboards, AI agents, and embedded analytics simultaneously.

What KPIs should be on a dashboard?

The KPIs depend on your business. Common B2B SaaS KPIs: MRR, ARR, churn rate, net revenue retention, active users, customer count, NPS, average contract value. The specific metrics matter less than the governance: every KPI should have one canonical definition that every consumer references.

What is the best KPI dashboard tool?

For internal-only dashboards: Power BI (Microsoft ecosystem), Tableau (Salesforce ecosystem), Looker (Google Cloud), or Metabase (open source). For governed KPIs served to multiple surfaces including AI agents and embedded analytics: a semantic layer approach. The right choice depends on how many consumers need the same metrics.

How do I make my KPI dashboard load faster?

Most dashboard slowness comes from querying the warehouse on every render. Pre-aggregation caching pre-computes common KPI rollups. The dashboard queries the cache instead of the warehouse. Response times drop from seconds to single-digit milliseconds.

Can AI agents query KPIs?

With a semantic layer and MCP support, yes. AI agents discover available KPIs via explore_schema and query them via query. The agent gets governed, multi-tenant data scoped to their access level. Without a semantic layer, agents generate ad-hoc SQL and return inconsistent numbers. See What Is an Agentic Semantic Layer?.

What is the difference between a KPI dashboard and analytics dashboard?

A KPI dashboard focuses on a small set of headline metrics that measure business health. An analytics dashboard provides broader exploration: filtering, drilling, and ad-hoc querying. In practice, both benefit from governed metric definitions. The KPI dashboard shows the numbers. The analytics dashboard lets you investigate why they changed.

How often should a KPI dashboard refresh?

Depends on the KPI. Revenue and churn: daily or hourly. Active users and API metrics: real-time or near-real-time. Strategic KPIs (NPS, retention cohorts): weekly or monthly. With pre-aggregation, refresh frequency is configurable per metric. Set aggressive refresh for operational KPIs and relaxed refresh for strategic ones.

How Bonnard Builds Agent-Friendly MCPs

Max Mealing — Thu, 16 Jul 2026 15:25:44 +0000

Exposing your data over MCP is the easy part. Designing a tool an agent uses well is the hard part. An agent can only use a tool it can read, so the work is shaping the tool for how the model calls it, not just for the human looking at the result. These are the techniques behind @bonnard/mcp-charts and the visualize tool.

Discovery-first, so the agent stops guessing

An agent that guesses your schema writes wrong queries. So the first tool the agent meets is a discovery tool. It calls visualize_read_me to load the chart options, the tool schema, and worked examples before it ever calls visualize, and an explore_schema tool to learn your tables and columns before it writes SQL. The agent reads, then acts.

A small set of purpose-built tools

The temptation is one tool per metric, or a single tool that takes arbitrary SQL and hopes. Both fail: too many tools blow the agent's attention budget; one firehose tool gives it no guardrails. Bonnard ships a small set, discover, query, visualize, each with a narrow, obvious job. The agent picks the right one because there are few of them and each does one thing.

// a small, purpose-built set, not one tool per metric
server.registerTool("explore_schema", { /* list tables + columns */ }, listSchema);
addCharts(server, { runSql }); // registers visualize_read_me + visualize

Compact, honest responses

A tool that returns 10,000 raw rows poisons the context window and the agent's next decision. Bonnard's responses are sized for a model to read:

Row caps with a completeness flag. Results are capped and tagged partial or complete, so the agent knows whether it is looking at everything.
Partial-result warnings. When results are capped, the response says so and tells the agent not to sum or average the visible rows, use a measure instead.
Summaries over dumps. The chart comes back with a compact text summary the model can reason over, not just an image it cannot read.

Errors that guide the next action

A bare "error: invalid column" stalls an agent. Bonnard's errors carry a fix. A bad field name returns a hint to check names with explore_schema; a SQL error returns targeted guidance; successful calls include a next_step pointing at the right follow-up tool. The agent self-corrects instead of looping.

Determinism over improvisation

The most important move is pushing deterministic work out of the model and into the code. The agent picks what to show (a chart type, a query). Bonnard decides how: it infers axes from the typed result, auto-detects currency and percentage formatting, fills missing time buckets, and labels null dimensions. The smaller the agent's decision surface, the less there is to hallucinate.

Tool descriptions that double as instructions

To an agent, the tool description is the documentation. Bonnard's tools use a typed schema with a description on every field, plus a read-me tool that returns usage examples on demand. The agent knows how and when to call visualize because the schema tells it, not because you wrote a prompt.

Frequently asked questions

What makes an MCP tool "agent-friendly"?

It is shaped for how a model calls tools: discovery-first so the agent learns before it acts, a small set of narrow tools, compact responses with completeness flags, errors that suggest the fix, and a typed, self-describing schema. The agent uses it correctly without hand-holding.

Why not just let the agent write SQL and draw the chart?

Text-to-SQL against raw tables is wrong often enough that you cannot put it in front of a customer, and an agent drawing its own chart HTML is non-deterministic. A governed query path plus a dedicated chart tool returns consistent, trustworthy visuals.

Does Bonnard support raw SQL at all?

Yes, as a narrow, audited path, not a bypass around governance. You control what runs via your runSql callback.

See the product this is built on: MCP Charts.

How to Build Customer-Facing Analytics for B2B SaaS

Max Mealing — Thu, 16 Jul 2026 15:25:42 +0000

Your B2B customers want to see their data. Usage metrics, billing summaries, conversion funnels, performance dashboards. Every customer expects analytics inside your product. They shouldn't have to ask your support team for a CSV export.

The question isn't whether to ship customer-facing analytics. It's how.

Most teams start with one of two approaches. They embed a BI tool (Metabase, Looker, Power BI) and fight with multi-tenancy, iframe styling, and paid embedding licenses. Or they build custom charts from scratch and spend months maintaining SQL queries, API endpoints, and frontend components that nobody asked for.

Both approaches burn engineering time on the wrong problem. You end up building analytics infrastructure instead of your product.

And there's a surface most of these tools miss entirely: the AI agent. Customers increasingly want to ask questions about their data inside Claude or ChatGPT and get a chart back. That's a different problem from embedding a dashboard, and it's where @bonnard/mcp-charts fits, covered later in this post.

Why embedded BI tools fall short

Embedding a BI tool sounds fast. Drop in an iframe, connect to your database, ship it. In practice, the friction shows up quickly.

Multi-tenancy is an afterthought

Most BI tools were built for internal teams, not B2B products serving hundreds of tenants. Multi-tenancy is either missing, manual, or gated behind an enterprise license.

Metabase requires the Enterprise license ($500+/month) for row-level permissions and sandboxed embedding. The open-source version has basic embedding but no tenant isolation. You end up writing middleware to filter queries by tenant, which is exactly the custom infrastructure you were trying to avoid.

Looker's embedded analytics requires an enterprise contract. Power BI Embedded uses capacity-based pricing that gets expensive at scale. Tableau's embedding story is Salesforce-priced.

Even when multi-tenancy is available, it's usually dashboard-level or role-based, not per-query structural enforcement. You're trusting the BI tool to filter correctly on every query. One misconfiguration and Customer A sees Customer B's data.

Styling and UX limitations

An iframe is a foreign element in your product. It looks like a foreign element. Matching your brand's fonts, colors, spacing, and interaction patterns inside an embedded BI tool ranges from difficult to impossible. Your customers notice.

White-label analytics means your customers shouldn't know they're using a third-party tool. Most embedded BI solutions make this hard. The ones that make it easy charge for it.

You're locked to dashboards

Embedded BI gives you dashboards. That's one surface. But your customers might also want:

An API to pull metrics into their own tools
AI agents that answer questions about their data
Scheduled email reports
Webhook alerts when metrics cross thresholds
CSV/Excel exports for their finance team

Each of these requires a different integration, often with different tools and different metric definitions. The dashboard shows one number. The API returns a different one. The export uses yet another query. The metrics drift.

Why building from scratch is worse

The alternative: skip the BI tool and build it yourself. Custom SQL queries, custom API endpoints, custom React charts.

This works for the first dashboard. Then the second. By the tenth, you're maintaining a bespoke analytics platform. Every new metric means a new SQL query, a new API endpoint, a new frontend component, and a new set of tests. Your data engineers are writing API handlers instead of defining metrics. Your frontend engineers are debugging chart edge cases instead of building product features.

The real cost isn't the initial build. It's the ongoing maintenance:

Metric drift. Revenue is calculated differently in the dashboard, the API, and the export. Nobody notices until a customer complains.
No caching layer. Every API call hits the warehouse. Response times grow with data volume. Your customers experience slow dashboards at month-end when they need them most.
Security is DIY. You build tenant filtering yourself. You test it yourself. You hope you didn't miss an edge case.
No schema evolution. Adding a new dimension means updating every query, endpoint, and component that touches the affected metric. A one-line change in business logic cascades into a multi-day project.

The teams on Reddit asking "Is embedded analytics for SaaS actually worth it vs building your own charts?" are wrestling with exactly this tradeoff. There's no single right answer. The decision depends on how many surfaces you serve and whether your customers want charts in a dashboard, an API, or an AI agent.

The AI agent surface: charts inside Claude and ChatGPT

The sections above cover the two classic customer-facing analytics options: embed a BI tool or build dashboards from scratch. There's a third surface that neither covers well: the AI agent. When your customer asks a question in Claude or ChatGPT and wants a chart back, an embedded dashboard doesn't help.

@bonnard/mcp-charts is the MCP-native option for that surface. It is not an embedded BI tool, a dashboard product, or a multi-tenant analytics platform. It is a visualize tool you add to your MCP server that renders interactive charts from your query results, inside the agent.

How it works

Install the package and call addCharts with your existing MCP server and a runSql function:

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, {
  runSql: async (sql) => {
    // Run the query against your database and return typed rows.
    return await db.query(sql);
  },
});

addCharts registers a visualize tool (and a visualize_read_me companion) on your server. The flow:

The agent calls visualize with a query.
Your runSql returns rows.
Bonnard infers the chart from the typed result.
It renders an interactive ui:// widget in Claude or ChatGPT.

Chart types: line, bar, area, pie, scatter, funnel, waterfall, and table. Bar charts have stacked, grouped, horizontal, and 100% variants, and a donut is a pie variant. Axes, formatting, and gap-fill are determined automatically, so the same query produces the same chart.

Native adapters cover Postgres, BigQuery, Snowflake, Databricks, and DuckDB. You can also pass your own runSql. Bonnard never touches your database. Your code runs the query and returns the rows.

Built for the agent, not only the human

The visualize tool returns compact summaries, row caps with completeness flags, and a typed schema so the agent can reason about the result. Errors are instructive and include a next_step, so when a query fails the agent knows what to try next. For how this is designed, see how Bonnard builds agent-friendly MCPs.

Build vs. embed vs. charts in the agent

	Build from scratch	Embed BI tool	Charts in AI agent (MCP)
Surface	In your product UI	In your product UI	Inside Claude or ChatGPT
Time to first chart	2-4 months	1-2 weeks	A few lines (`addCharts`)
What renders the chart	Your frontend code	The BI tool	The agent host (`ui://` widget)
Where the query runs	Your code	The BI tool	Your `runSql`, your database
Chart types	Whatever you build	Tool's library	line, bar, area, pie, scatter, funnel, waterfall, table
Maintenance burden	High (you own everything)	Medium (tool updates, iframes)	Low (one tool registration)
AI agent support	Build from scratch	None	Native (`visualize` tool)

These are not competing for the same slot. Embedded BI and a from-scratch build put charts in your product UI. Charts in the agent put them inside Claude or ChatGPT. Many teams will do both.

What makes the AI agent surface different

Embedded analytics tools (Metabase Embedded, Holistics, Explo, Luzmo, Reveal) give you dashboards inside your product. That's their scope. They're good at it. None of them render a chart inside an AI agent.

@bonnard/mcp-charts is the adjacent option, not a member of the embedded BI category:

1. The chart lives in the agent. When a customer asks a question in Claude or ChatGPT, the visualize tool renders an interactive ui:// widget right in the conversation. An embedded dashboard can't do that.

2. Your database stays yours. Bonnard never touches your database. Your runSql runs the query and returns the rows. Bonnard infers the chart from the typed result.

3. Designed for the agent. Compact summaries, row caps with completeness flags, typed schema, and instructive errors with a next_step. The tool is built for an agent to call, not only for a human to read.

The tradeoff: embedded BI tools render charts in your product UI, which is where many customers expect them. @bonnard/mcp-charts only covers the agent surface. If your customers want both a dashboard and an in-agent chart, use an embedded BI tool for the dashboard and @bonnard/mcp-charts for the agent.

Getting started

Install the package and register the tool on your MCP server:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, {
  runSql: async (sql) => db.query(sql),
});

That registers the visualize tool (and visualize_read_me). Connect your server in Claude or ChatGPT, and the agent can render charts from your query results. Native adapters cover Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql.

Repo: github.com/bonnard-data/mcp-charts. For the full walkthrough: MCP charts. For the design principles: how Bonnard builds agent-friendly MCPs.

Frequently asked questions

What is customer-facing analytics?

Customer-facing analytics is analytics embedded in your product for your customers to use. Instead of internal dashboards for your team, the analytics are exposed to end users: your B2B customers, their teams, and their tools. The key challenges are multi-tenancy (each customer sees only their data), performance (customers expect fast load times), and consistency (the numbers should match across every surface).

What is embedded analytics?

Embedded analytics means integrating analytics capabilities directly into another application. This can be as simple as an iframe embedding a dashboard or as sophisticated as native React components querying a governed API. The term covers a range of approaches from basic chart embedding to full white-label analytics platforms.

How is this different from Metabase embedding?

Metabase offers embedded dashboards via iframe or full-app embedding. The open-source version has basic embedding but no tenant isolation. The Enterprise version ($500+/month) adds row-level permissions and sandboxed embedding. Metabase renders charts in your product UI. @bonnard/mcp-charts is a different surface entirely: it renders interactive charts inside an AI agent (Claude or ChatGPT) via a visualize tool on your MCP server. They are not substitutes. Use Metabase for an in-product dashboard, and @bonnard/mcp-charts for charts in the agent.

What is white-label analytics?

White-label analytics means your customers see your brand, not a third-party tool's brand. No "Powered by Metabase" footer. No foreign-looking iframe. The analytics feel native to your product. Most embedded BI tools charge for white-labeling, and the harder it is to match your design, the more obvious the third-party tool becomes. This is a property of in-product embedding, not of charts rendered inside an AI agent.

What is customer-facing analytics for AI agents?

It is letting your customers ask questions about their data inside an AI agent (Claude or ChatGPT) and get a chart back. @bonnard/mcp-charts covers this with a visualize tool you add to your MCP server. The agent calls visualize, your runSql returns rows, and Bonnard renders an interactive chart in the conversation. This is the 2026 surface that embedded BI tools do not address.

Can my customers get charts inside an AI agent?

Yes, if you expose an MCP server. Add @bonnard/mcp-charts with addCharts(server, { runSql }), and the agent can call visualize to render interactive charts from your query results in Claude or ChatGPT. Your runSql runs the query against your database, so Bonnard never touches your data. See MCP for how the protocol works.

What databases does @bonnard/mcp-charts work with?

Native adapters cover Postgres, BigQuery, Snowflake, Databricks, and DuckDB. You can also pass your own runSql function to query anything that returns rows. Bonnard never connects to your database directly.

How much does @bonnard/mcp-charts cost?

It is open source. Install it with npm install @bonnard/mcp-charts. Repo: github.com/bonnard-data/mcp-charts.

How to Connect an AI Agent to Your Data Warehouse

Max Mealing — Thu, 16 Jul 2026 15:25:39 +0000

Most teams connecting AI agents to their data warehouse start with text-to-SQL. The agent generates SQL from natural language, runs it against the warehouse, and returns results. It works until it doesn't: hallucinated JOINs, inconsistent aggregations, no access control, no audit trail.

There's a better approach. Define your business metrics in a semantic layer, expose them via MCP (Model Context Protocol), and let any AI agent query governed definitions instead of raw tables. Then add one tool so the agent can chart the result in Claude or ChatGPT. This tutorial shows how to set it up in under 30 minutes.

Why does text-to-SQL break in production?

The agent sees column names but not business logic. It doesn't know that your company excludes refunds from revenue. It doesn't know that status = 'completed' means something different in orders than in subscriptions. It doesn't know that marketing and finance defined "active user" differently three years ago and never reconciled.

So the agent writes plausible SQL and returns plausible numbers. Ask the same question twice with different phrasing and you get different answers. Ask two different agents and you get two different numbers. Neither matches the number your finance team reports.

Beyond consistency, there's no row-level security. No multi-tenancy. No audit trail showing which agent queried what, when, and for whom. In production, with real customers, that's a non-starter.

Text-to-SQL gives you speed. It doesn't give you trust.

What is the semantic layer approach?

Instead of letting agents write arbitrary SQL, define your metrics once in YAML: cubes, measures, dimensions, access rules. Then expose those definitions via MCP so agents query governed metrics, not raw tables.

The difference: every agent gets the same answer because the metric definition is fixed. total_revenue isn't a column the agent interprets. It's a pre-defined calculation with agreed-upon filters and aggregations. When your finance team updates the revenue definition to exclude trial conversions, that change propagates to every consumer instantly. No agent retrained. No dashboard patched. One diff in your schema repo.

This architecture also decouples the query interface from the warehouse dialect. Swap BigQuery for Snowflake and your agents don't notice. The semantic layer abstracts the SQL generation, so consumers stay stable while infrastructure evolves underneath.

Approach	Governance	Consistency	Multi-tenant	Access Control	Setup Time
Direct SQL	None	Varies by query	Manual	Manual	Minutes
Text-to-SQL	None	Varies by prompt	Manual	Manual	Hours
Semantic Layer via MCP	Full	Guaranteed	Built-in	Row-level	Under 30 min

The semantic layer is the control plane between your warehouse and every consumer, whether that's a human analyst, a React component, or an AI agent.

Step 1: Set up your semantic layer

Stand up a semantic layer that connects to your warehouse and exposes metrics over MCP. Open-source engines like Cube connect to BigQuery, Snowflake, Redshift, Databricks, PostgreSQL (including Supabase, Neon, and RDS), and DuckDB (including MotherDuck). You configure your warehouse connection in a config file or pass credentials via environment variables.

If you want to explore without connecting your own warehouse, point the semantic layer at a sample PostgreSQL database with orders, customers, and products data so you can follow along with the rest of this tutorial. A few thousand rows across three tables is enough to test aggregations, filters, and multi-dimensional queries.

Step 2: Define your metrics

Create a cube that maps to a table in your warehouse and defines the metrics your agents will query.

cubes:
  - name: orders
    sql_table: public.orders
    measures:
      - name: total_revenue
        sql: amount
        type: sum
      - name: count
        type: count
    dimensions:
      - name: status
        sql: status
        type: string
      - name: created_at
        sql: created_at
        type: time

Measures are the numbers you aggregate: sums, counts, averages. Dimensions are the columns you filter and group by: status, date, category. One definition. Every tool, dashboard, and AI agent that queries total_revenue gets the same number.

You can also define pre_aggregations in the same file to cache expensive computations. For example, a daily rollup of total_revenue by status can cut query times from seconds to single-digit milliseconds. The semantic layer rebuilds these rollups on a configurable schedule and invalidates stale caches automatically.

Schemas are version-controlled alongside your application code. Review metric changes in pull requests. Roll back a bad definition with git revert. Your data contracts get the same CI/CD workflow as your product.

To control what's exposed to specific consumers, define a view:

views:
  - name: order_metrics
    cubes:
      - join_path: orders
        includes:
          - total_revenue
          - count
          - status
          - created_at

Views act as a curated interface. Your agents see order_metrics with four fields instead of navigating the full schema.

Step 3: Deploy and connect your agent

Deploy your schema so the semantic layer serves it over an MCP endpoint, then add the MCP server URL to your client's config:

{
  "mcpServers": {
    "semantic-layer": {
      "type": "http",
      "url": "https://your-semantic-layer.example.com/mcp"
    }
  }
}

Paste this into your MCP client's config file and restart. For Claude Desktop, that's ~/Library/Application Support/Claude/claude_desktop_config.json. For Cursor, it's .cursor/mcp.json in your project root. Claude Code reads from .mcp.json in your project directory.

The MCP server handles tool discovery, schema introspection, and query execution over HTTP. Your agent sees available metrics the same way it sees any other MCP tool. No custom integration code required.

For customer-facing use cases, scope each connection to a specific tenant's data with row-level security defined in the schema, so a customer's agent sees only that customer's rows.

Step 4: Query from your agent

Once connected, your AI agent can discover and query your metrics using natural language. Behind the scenes, the agent calls MCP tools.

Ask: "What's our total revenue this quarter?"

The agent calls explore_schema to discover available metrics, then calls query with the right measures and time filters. The response comes back as structured data, not raw SQL results.

Ask: "Break down order count by status for the last 30 days."

Same flow. The agent uses query with count as the measure, status as the dimension, and a date filter on created_at.

A semantic layer typically exposes a handful of MCP tools:

explore_schema: Discover available cubes, measures, and dimensions
query: Fetch aggregated data using governed metric definitions
sql_query: Run queries for cases that need custom SQL (still governed by access controls)
describe_field: Get metadata about a specific measure or dimension

Every query runs through the semantic layer. The agent never touches raw tables. Read more about the architecture in our agentic analytics guide.

Charting the agent's results

A governed query returns rows. The agent usually needs to show the user a chart, not a wall of numbers. Leaving the model to draw HTML puts the most important part of the answer in its hands to improvise.

@bonnard/mcp-charts fixes that. It adds a visualize tool to your MCP server in a few lines:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

// your data, your connection. Bonnard never touches the database
addCharts(server, { runSql });

The agent calls visualize with a query, your runSql returns the rows, and Bonnard infers the chart from the typed result, then renders an interactive widget in Claude or ChatGPT. It renders line, bar, area, pie, scatter, funnel, waterfall, and table, with bar variants for stacked, grouped, horizontal, and 100% stacked. The chart comes from your query result, not from tokens the model invents. Same data, same chart, every time. Full walkthrough: MCP Charts.

From raw SQL to governed metrics

You started with a warehouse and an AI agent that writes its own SQL. Now you have governed metrics defined in YAML, exposed via MCP, queryable from any AI tool your team or customers use. Setup took under 30 minutes.

The shift from raw SQL to governed metrics pays off immediately: consistent numbers across every consumer, row-level access control per tenant, and a full audit trail for every query. As your team adds more agents and surfaces, the semantic layer scales with you. No duplicate logic. No drift between dashboards and AI answers.

Give the agent a way to chart what it queries: npm install @bonnard/mcp-charts, call addCharts(server, { runSql }), and interactive charts render in Claude and ChatGPT. The repo is on GitHub. Read the MCP Charts guide to go deeper.

Frequently asked questions

Do I need to retrain my AI model?

No. A semantic layer with MCP support works with any MCP-compatible AI agent out of the box. Your agent discovers available metrics through the MCP protocol at runtime. There is no fine-tuning, prompt engineering, or model modification required.

What data warehouses does this work with?

A semantic layer connects to BigQuery, Snowflake, Redshift, Databricks, PostgreSQL (including Supabase, Neon, and RDS), and DuckDB (including MotherDuck). For charting the agent's results, @bonnard/mcp-charts ships native adapters for Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or you pass your own runSql. Bonnard never touches your database.

How is this different from text-to-SQL?

Text-to-SQL lets an AI agent generate arbitrary SQL from natural language. It has no governance, no consistency guarantees, and no access control. A semantic layer defines metrics once in YAML and exposes them as governed APIs. Every consumer gets the same answer because the calculation is fixed, not interpreted per query. And when the agent charts the result, @bonnard/mcp-charts renders from the query result, not from tokens the model invents.

How do I let the agent show charts?

Install @bonnard/mcp-charts and call addCharts(server, { runSql }) on your MCP server. That registers a visualize tool. The agent calls it with a query, your callback returns the rows, and an interactive chart renders in Claude or ChatGPT. No frontend code.

How long does setup take?

Under 30 minutes for most teams. Stand up a semantic layer, connect your warehouse, define a few metrics in YAML, and deploy it over MCP. Add charts with npm install @bonnard/mcp-charts and one addCharts call. The tutorial above walks through each step with working code examples.

Best Embedded Analytics Tools for SaaS in 2026

Max Mealing — Thu, 16 Jul 2026 15:25:36 +0000

If you're building a B2B SaaS product and need to ship analytics to your customers, you're evaluating embedded analytics tools. The market has more options than it needs. This guide cuts through the noise.

We compare 10 tools across the dimensions that matter for B2B: multi-tenancy, customization, AI agent support, pricing model, and how many surfaces each tool serves beyond dashboards.

What to look for in embedded analytics

Before comparing tools, know what matters for your use case:

Multi-tenancy. Every customer sees only their data. This needs to be structural (enforced at the query layer), not bolted on (middleware filters that might miss an edge case). One data leak and your customers' trust is gone.

Customization. The analytics should look like part of your product. Iframe embedding limits this. Native SDK components give you full control over styling.

Metric governance. Every chart should show the same number as your API. If the dashboard says $45K and the API returns $43K, your customer notices.

Performance. Dashboards that take 8 seconds to load don't get used. Caching matters.

Pricing model. Per-user pricing kills B2B use cases. If you have 1,000 customers each with 10 users, per-seat licensing makes embedded analytics unaffordable.

AI agent support. Increasingly, customers want to connect AI tools to their data. None of the traditional embedded analytics tools support this. It's a differentiator.

The 10 tools compared

Tool	Type	Multi-tenancy	AI/Agent	Pricing	Open source
Metabase	BI tool with embedding	Enterprise only ($500+/mo)	No	Free OSS / $500+ Pro	Yes (AGPL)
Holistics	Embedded BI platform	Built-in	No	Per-user	No
Explo	Embedded analytics SDK	Built-in	No	Usage-based	No
Luzmo	Embedded analytics SDK	Built-in	Limited	Usage-based	No
Reveal	Embedded analytics SDK	Built-in	No	Per-app	No
GoodData	Enterprise embedded BI	Built-in	Limited	Usage-based	Partial
Looker	Enterprise BI with embedding	Enterprise	No	Enterprise pricing	No
Power BI Embedded	Enterprise BI with embedding	Complex setup	Copilot (internal)	Capacity-based	No
Tableau Embedded	Enterprise BI with embedding	Enterprise	Limited	Per-user + Server	No
Bonnard	Charts for AI agents via MCP (not embedded BI)	N/A (your `runSql`, your DB)	Native (`visualize` tool)	Open source (npm)	Yes

Tool-by-tool breakdown

Metabase

The most popular open-source BI tool. Great for internal dashboards. Embedding requires Pro ($500+/mo) for static embeds or Enterprise for interactive. Multi-tenancy (sandboxing) is Enterprise only. No semantic layer, no AI agent support.

Best for: Internal dashboards, MVPs, teams that want free self-hosted BI.
Not great for: Customer-facing B2B analytics at scale. Multi-tenancy and embedding costs add up.

Holistics

Self-service embedded BI aimed at product teams. Built-in multi-tenancy with row-level security. Strong data modeling layer. Drag-and-drop dashboard builder with embedding.

Best for: Teams that want a managed embedded BI platform with a modeling layer.
Not great for: AI agent use cases, custom React components, or teams that want open-source infrastructure.

Explo

SDK-first embedded analytics. React and JavaScript components for building dashboards in your product. Built-in multi-tenancy. Good API for programmatic control.

Best for: Product teams that want prebuilt dashboard components with customization.
Not great for: Teams that need governed metrics across multiple surfaces beyond dashboards. No semantic layer, no AI agent support.

Luzmo

Embedded analytics with a focus on developer experience. SDK components, API access, and a dashboard builder. Some AI capabilities for insight generation.

Best for: Small to mid-size SaaS products that want quick embedded dashboards.
Not great for: Enterprise multi-tenancy, AI agent integration, or teams that need metrics governance.

Reveal

Embedded analytics SDK targeting .NET and Java applications. Rich visualization library. Built-in multi-tenancy.

Best for: .NET and Java shops building enterprise applications.
Not great for: React/TypeScript teams, AI agent use cases, or modern data stack.

GoodData

Enterprise embedded analytics platform. Strong multi-tenancy, API access, and metric definitions ("metrics as code" via MAQL). Partial open-source.

Best for: Large SaaS companies with enterprise analytics requirements.
Not great for: Smaller teams, open-source-first teams, or AI agent integration.

Looker

Google Cloud's BI platform. LookML provides a strong semantic layer, but it's locked to Looker's ecosystem. Enterprise pricing. Embedding requires Looker Embed API.

Best for: Google Cloud teams with enterprise budgets that need a mature semantic layer.
Not great for: Teams outside the Google ecosystem, budget-conscious products, or AI agent use cases.

Power BI Embedded

Microsoft's embedded BI. Capacity-based pricing (SKUs). Deep Azure integration. Complex multi-tenancy setup requiring Azure AD.

Best for: Microsoft shops with Azure infrastructure.
Not great for: Non-Microsoft teams, simple multi-tenancy, or AI agent access.

Tableau Embedded

Salesforce's visualization platform. Rich charting. Embedding requires Tableau Server/Cloud plus custom auth. Per-seat pricing.

Best for: Organizations deeply invested in Tableau with budget for per-seat licensing.
Not great for: B2B products with many end users (per-seat cost is prohibitive). No AI agent support.

Bonnard

Not an embedded BI tool. @bonnard/mcp-charts is a visualize tool you add to your MCP server. You call addCharts(server, { runSql }), and it registers a visualize tool that renders interactive charts from your query results, inside the agent. When the agent asks for a chart, your runSql returns rows, Bonnard infers the chart from the typed result, and renders an interactive ui:// widget in Claude or ChatGPT. Chart types: line, bar, area, pie, scatter, funnel, waterfall, and table (with stacked, grouped, horizontal, and 100% bar variants). Native adapters for Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or your own runSql. Bonnard never touches your database.

Best for: Teams already building an MCP server who want interactive charts inside Claude or ChatGPT, rendered from their own query results.
Not great for: Teams that want in-product dashboards for non-technical users. This is charts for AI agents, not an embedded dashboard tool.

Decision framework

Choose dashboard embedding (Metabase, Holistics, Explo, Luzmo, Reveal) if:

Dashboards are the only analytics surface you need
You want a prebuilt UI your customers interact with
AI agent access is not a requirement
You don't need the same metrics in APIs and SDKs

Choose enterprise BI embedding (Looker, Power BI, Tableau) if:

You're already in that vendor's ecosystem
Enterprise pricing fits your model
Internal + embedded analytics in one tool matters
You have dedicated BI administrators

Choose charts for AI agents via MCP (Bonnard) if:

You're building an MCP server and want interactive charts inside Claude or ChatGPT
You want charts rendered from your own query results, not a separate dashboard product
Your runSql returns rows and you want Bonnard to infer the chart from the typed result
You want open source with a one-line install, not a dashboard license
This is adjacent to embedded BI, not a replacement for it

Frequently asked questions

What is the best free embedded analytics tool?

Metabase (AGPL) is the most popular free option for dashboards. Superset (Apache 2.0) is free for SQL-native dashboards. If you want charts inside an AI agent rather than an embedded dashboard, @bonnard/mcp-charts is a free, open-source visualize tool for your MCP server. "Free" depends on what you need: basic charting, full embedded BI, or interactive charts inside Claude or ChatGPT.

What is embedded analytics?

Embedded analytics is analytics integrated directly into your product for end users. Instead of linking customers to a separate BI tool, charts and metrics appear inside your application. Ranges from basic iframe embedding to native SDK components with governed metrics.

How much does embedded analytics cost for SaaS?

Free (Metabase OSS) to enterprise pricing (Looker, Tableau). Mid-range tools (Explo, Holistics, Luzmo) charge per-user or usage-based, typically $500-5,000/mo. If your need is charts inside an AI agent rather than an embedded dashboard, @bonnard/mcp-charts is open source and installs via npm.

Which embedded analytics tool has the best multi-tenancy?

Tools with structural multi-tenancy (enforced at the query layer): Explo, Holistics. Tools with configuration-based multi-tenancy: Metabase Enterprise (sandboxing), Power BI (Azure AD + RLS). Tools with no built-in multi-tenancy: Metabase OSS, Superset, Redash.

Do any embedded analytics tools support AI agents?

The traditional embedded analytics tools render charts in your product, not inside an AI agent. Some enterprise tools (Power BI Copilot, Luzmo, GoodData) have limited AI features but none render interactive charts inside an MCP host. If you want charts inside the agent, that's a different category: @bonnard/mcp-charts adds a visualize tool to your MCP server that renders interactive charts from your query results in Claude or ChatGPT. Install it with npm install @bonnard/mcp-charts. Repo: github.com/bonnard-data/mcp-charts. More in the MCP charts guide.

Analytics API: How to Serve Governed Metrics to Any Consumer

Max Mealing — Thu, 16 Jul 2026 15:25:33 +0000

You have metrics. Revenue, active users, churn, usage per customer. They live in your data warehouse. Now you need to serve them to multiple consumers: your product's frontend, your customers' integrations, an AI agent, a scheduled report generator.

The first instinct is custom API endpoints. One endpoint for revenue by region. Another for churn by plan. Another for the customer dashboard. Each endpoint has its own SQL query, its own response format, its own maintenance burden. By the twentieth endpoint, you're running a bespoke analytics service.

An analytics API backed by a semantic layer gives you one query interface that serves every consumer. Define metrics once. Query them from anywhere.

What is an analytics API?

An analytics API is a programmatic interface for querying metrics. Instead of connecting to a database and writing SQL, consumers call an API endpoint with the metrics they want and get structured results back.

curl https://analytics.example.com/v1/query \
  -H "Authorization: Bearer bon_pk_..." \
  -d '{
    "measures": ["orders.total_revenue", "orders.order_count"],
    "dimensions": ["orders.region"],
    "timeDimensions": [{
      "dimension": "orders.created_at",
      "granularity": "month",
      "dateRange": ["2026-01-01", "2026-03-31"]
    }],
    "orderBy": { "orders.total_revenue": "desc" }
  }'

The response is structured JSON:

{
  "data": [
    { "orders.region": "EMEA", "orders.total_revenue": 142000, "orders.order_count": 340, "orders.created_at": "2026-01-01" },
    { "orders.region": "APAC", "orders.total_revenue": 98000, "orders.order_count": 220, "orders.created_at": "2026-01-01" }
  ]
}

Every consumer uses the same interface. The dashboard frontend, the customer's API integration, the scheduled report, and the AI agent all query the same endpoint with the same metric definitions. The analytics API handles query generation, execution, caching, and access control.

Why not just build API endpoints?

Custom endpoints work at small scale. They break at medium scale:

Metric drift. /api/revenue and /api/dashboard/revenue both return "revenue" but use different SQL queries. One gets updated. The other doesn't.

N+1 endpoint problem. Every new metric or dimension combination is a new endpoint. Revenue by region. Revenue by plan. Revenue by region AND plan. Revenue by region AND plan AND month. The combinatorial explosion is real.

No caching strategy. Each endpoint hits the warehouse on every request. Response times grow with data volume. You end up building a caching layer per endpoint.

No access control. Each endpoint implements its own auth. Customer A's key works on all endpoints or none. Per-tenant, per-metric access control is custom code.

A shared query interface handles all of this. But there's a gap that shows up once AI agents become consumers: an agent that fetches rows still has to present them. A wall of JSON tells the user nothing. Agents need to chart query results, not just fetch them.

Charting the results an agent fetches

Fetching rows is half the job. The other half is rendering them so a person can read the answer. That's where @bonnard/mcp-charts fits: a visualize tool you add to your MCP server that renders interactive charts from your query results.

Add it to your MCP server

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

// runSql runs against your warehouse and returns typed rows
addCharts(server, { runSql });

addCharts registers a visualize tool (and a visualize_read_me companion) on your server. Bonnard never touches your database. Your runSql does, against Postgres, BigQuery, Snowflake, Databricks, DuckDB, or any source you wire up.

How the flow works

The agent calls visualize with a query.
Your runSql returns rows.
Bonnard infers the chart from the typed result: line, bar, area, pie, scatter, funnel, waterfall, or table.
The host renders an interactive ui:// widget inside Claude or ChatGPT.

The agent gets line, bar, and area charts for trends, scatter for distributions, funnel and waterfall for staged data, pie for shares, and a table when a table is the right answer. Axes, formatting, and gap-fill are decided from the typed schema, so the same query produces the same chart every time.

One query interface for the numbers. One visualize tool for the picture.

Analytics API vs alternatives

Approach	Metric governance	Multi-tenant	Caching	Maintenance
Custom REST endpoints	None (per-endpoint SQL)	DIY	DIY	High (per endpoint)
GraphQL API	Schema-level	DIY	Per-resolver	Medium-high
Direct warehouse access	None	Manual	None	Low (but dangerous)
BI tool API (Looker, Metabase)	Tool-specific	Tool-specific	Tool-specific	Medium
Bonnard	Inherits your `runSql` definitions	Inherits your `runSql`	Inherits your warehouse	Low (a few lines on your server). Adds interactive charts via the MCP `visualize` tool, rendered in Claude/ChatGPT from query results

When you need an analytics API

You need one when:

Multiple consumers query the same metrics (frontend + backend + AI agents)
Customers integrate with your data via API
You're building embedded analytics with a frontend SDK
AI agents need programmatic access to governed data
Your data team is tired of building custom endpoints

You don't need one when:

A single dashboard covers all use cases
Nobody queries your metrics programmatically
The data model is so simple that direct SQL is fine

Getting started

Once your analytics API can fetch rows, add a visualize tool so agents can chart them:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, { runSql });

That registers the visualize tool on your MCP server. Agents call it with a query, your runSql returns rows, and Bonnard renders an interactive chart inside Claude or ChatGPT. Adapters ship for Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql.

For the full walkthrough: Add Interactive Charts to Your MCP Server. For the design choices behind the tool: How Bonnard Builds Agent-Friendly MCPs.

Source is on GitHub.

Frequently asked questions

What is an analytics API?

An analytics API is a programmatic interface for querying business metrics. Instead of writing SQL against a database, consumers call an API with the measures, dimensions, and filters they want. The API handles query generation, caching, and access control.

How is an analytics API different from a REST API?

A generic REST API exposes resources (users, orders, invoices). An analytics API exposes metrics (revenue, churn rate, active users) with aggregation, filtering, and time dimensions built in. The query interface is designed for analytical questions, not CRUD operations.

Do I need an analytics API for AI agents?

If AI agents query your data, an analytics API is the governed path. The alternative is giving agents direct database access (dangerous) or text-to-SQL (inconsistent). An analytics API backed by a semantic layer gives agents governed access to metric definitions. See What Is an Agentic Semantic Layer?.

What about GraphQL for analytics?

GraphQL works for analytics APIs but you end up building the aggregation, caching, and access control layer yourself. A semantic layer provides these out of the box with a purpose-built query interface for analytical workloads.

AI Reporting: How to Automate Reports Without Losing Trust

Max Mealing — Thu, 16 Jul 2026 15:25:31 +0000

Your data team spends 40% of their time building reports. Weekly revenue summaries. Monthly board decks. Quarterly business reviews. Customer-facing usage reports. Each one requires pulling data, checking the numbers match last month's methodology, formatting it, and sending it out.

AI reporting tools promise to automate this. Ask for a report in natural language and get charts, tables, and summaries in seconds. The problem: the AI generates the queries from scratch each time. This month's revenue calculation might differ from last month's. The board deck numbers might not match the customer report. Automation without governance creates reports nobody trusts.

What is AI reporting?

AI reporting uses large language models to automate parts of the reporting workflow: querying data, generating visualizations, writing narrative summaries, and scheduling delivery. Instead of an analyst manually pulling numbers and building slides, the AI handles the mechanical work.

The tools range from simple (ChatGPT generating a summary from a CSV) to sophisticated (enterprise BI platforms with AI-powered report builders). What they share: the AI interprets your data on every run. There's no guarantee that "revenue" means the same thing in this week's report as it did last week.

Why AI-generated reports break trust

Inconsistent methodology

The AI generates SQL from your schema context. This week it calculates revenue as SUM(amount). Next week, with slightly different prompt context, it adds a WHERE status = 'completed' filter. The CEO compares the two reports and asks why revenue dropped 8%. It didn't drop. The calculation changed. Nobody noticed because the SQL is generated on the fly.

No audit trail

When the CFO asks "where did this number come from?", the answer should be a versioned metric definition, not "an LLM wrote some SQL." Automated reports need the same auditability as manual ones. If you can't reproduce a number from a specific report, the report is useless for compliance, board materials, or customer-facing delivery.

Metric drift across reports

The weekly ops report, the monthly board deck, and the quarterly customer report all show "revenue." Without a shared definition, each report calculates it independently. The numbers diverge. Stakeholders compare reports and lose confidence. The data team gets pulled into reconciliation instead of building.

The governed approach to AI reporting

The fix isn't avoiding AI. It's separating what AI is good at (generating natural language summaries, choosing visualizations, scheduling delivery) from what it's bad at (defining business metrics).

Define metrics once

cubes:
  - name: revenue_metrics
    sql_table: analytics.monthly_revenue
    measures:
      - name: total_revenue
        sql: "CASE WHEN status != 'refunded' AND type != 'trial' THEN amount ELSE 0 END"
        type: sum
        description: "Finance-approved revenue (excludes refunds and trials)"
      - name: mrr
        sql: monthly_amount
        type: sum
        description: "Monthly recurring revenue from active subscriptions"
      - name: net_revenue_retention
        sql: "current_mrr / previous_mrr"
        type: avg
        description: "NRR: current period MRR / prior period MRR for the same cohort"
    dimensions:
      - name: plan
        sql: plan_name
        type: string
      - name: region
        sql: region
        type: string
      - name: period
        sql: period_date
        type: time

Every report, whether generated by AI or built manually, references these definitions. The methodology is fixed. This month's "revenue" uses the same calculation as last month's.

Let AI handle presentation, not calculation

The AI's job shifts from "calculate revenue" to "present the governed revenue number." It queries the semantic layer, gets a trustworthy number, and wraps it in a narrative: "Revenue grew 12% QoQ, driven primarily by Enterprise plan expansion in EMEA."

This is a better division of labor. The data team defines what's true. The AI makes it readable.

Charts in the agent, from your query results

When the report lives inside an AI agent, the customer often wants a chart in the conversation, not only a narrative. That's a separate surface from the governance layer, and it's where @bonnard/mcp-charts fits.

@bonnard/mcp-charts is not a reporting tool or a BI platform. It is a visualize tool you add to your MCP server that renders interactive charts from your query results, inside the agent.

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, {
  runSql: async (sql) => db.query(sql),
});

Axes, formatting, and gap-fill are determined automatically, so the same typed result produces the same chart every run. The tool returns compact summaries, row caps with completeness flags, and instructive errors with a next_step, so the agent can reason about the result. For the design principles, see how Bonnard builds agent-friendly MCPs.

AI report generators: what's available

AI report generators automate the mechanical parts of building reports. Here's what exists and where each approach fits.

General-purpose LLMs as report generators. Upload a CSV to ChatGPT or Claude and ask for an analysis. Good for one-off reports. No governance, no scheduling, no multi-tenancy. The report is as good as the prompt. Ask the same question next month and you might get a different methodology.

Dedicated AI reporting tools. Narrative BI, Rath, and similar tools generate automated dashboards and written insights from your data. They connect to your warehouse and produce reports on a schedule. The limitation: they define metrics at report generation time, not in a governed layer. Different reports can calculate the same metric differently.

BI tools with AI features. Power BI Copilot, Tableau AI, Domo AI. The AI helps you build reports inside the BI tool. Better than general-purpose LLMs because the data stays in your warehouse. Limited to whatever the BI tool supports.

Semantic layer + AI. Define metrics once in a governed layer. Use AI to generate narrative summaries, choose visualizations, and schedule delivery. The AI handles presentation. The semantic layer handles correctness. This is the approach that produces trustworthy automated reports.

AI reporting tools compared

Tool	Approach	Governed metrics	Multi-tenant reports	Audit trail
ChatGPT / Claude	Upload data, ask for analysis	No	No	No
Power BI Copilot	AI in Power BI	DAX measures (PBI only)	Complex	Within PBI
Tableau AI	AI in Tableau	Tableau model only	Enterprise	Within Tableau
Domo AI	AI in Domo platform	Within Domo	Within Domo	Within Domo
Automated BI tools (Narrative BI, Rath)	AI-generated dashboards	No	No	No
Charts in agent (Bonnard)	`visualize` tool on your MCP server	N/A (your `runSql`)	N/A (your DB)	N/A

When to use AI reporting

Use AI reporting with a semantic layer when:

Reports go to the board, customers, or regulators (trust matters)
Multiple reports use the same metrics (consistency matters)
You're automating B2B customer reports with per-tenant data
The data team is spending too much time on report generation

Use raw AI reporting tools when:

One-off ad-hoc analysis for internal consumption
Exploring a new dataset where methodology isn't established
Speed matters more than consistency

Getting started

If you want charts rendered inside an AI agent from your own query results, install the package and register the tool on your MCP server:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, {
  runSql: async (sql) => db.query(sql),
});

Repo: github.com/bonnard-data/mcp-charts. Full walkthrough: MCP charts. For background: What Is a Semantic Layer?.

Frequently asked questions

What is AI reporting?

AI reporting uses large language models to automate report generation: querying data, creating visualizations, writing summaries, and scheduling delivery. The AI handles the mechanical work that analysts currently do manually. The risk: without governed metric definitions, the AI generates different calculations on each run.

Can AI replace my reporting team?

No. AI can automate the mechanical parts (pulling data, formatting charts, writing summaries). It can't define what metrics should mean, decide which numbers matter for a specific audience, or judge whether an insight is actionable. The best approach: data teams define governed metrics, AI automates the delivery.

What is automated reporting?

Automated reporting is any system that generates reports without manual intervention. This includes scheduled dashboard refreshes, programmatic report generation via API, and AI-generated narrative summaries. The key question isn't whether to automate, but whether the automated reports use governed metric definitions.

How do I make AI reports trustworthy?

Separate metric definition from report generation. Define each metric once in a semantic layer with a fixed calculation. Let the AI query those definitions instead of generating SQL. Every report references the same versioned definitions. When someone questions a number, you point to a Git commit, not "the AI decided."

What is the best approach to AI reporting for SaaS?

Govern the numbers first so methodology stays fixed across runs. Then pick the surface where the report lands. If your customers read reports inside Claude or ChatGPT and want a chart in the conversation, @bonnard/mcp-charts adds a visualize tool to your MCP server that renders interactive charts from your query results. Your runSql runs the query, so Bonnard never touches your database. Install with npm install @bonnard/mcp-charts. Repo: github.com/bonnard-data/mcp-charts.

AI Data Analysis: Why Governed Metrics Beat Raw SQL Generation

Max Mealing — Thu, 16 Jul 2026 15:25:28 +0000

AI data analysis tools are everywhere. Upload a spreadsheet to Julius AI and get a chart. Ask ChatGPT's Data Analyst to find trends. Connect Databricks AI/BI to your warehouse and let it generate queries. The pitch: anyone can analyze data without writing SQL.

The pitch works for one-off exploration. It breaks when you need trustworthy numbers at scale: consistent results across tools, governed access per customer, and an audit trail for every answer. That's where most AI analytics tools stop and where a semantic layer starts.

What is AI data analysis?

AI data analysis uses large language models and machine learning to automate parts of the data analysis workflow: generating queries, identifying patterns, creating visualizations, and summarizing findings in natural language. Instead of writing SQL by hand, you describe what you want and the AI produces it.

The tools fall into three categories:

Spreadsheet AI. Upload a CSV and ask questions. Julius AI, Formula Bot, ChatGPT Data Analyst. Good for ad-hoc exploration of small datasets. No governance, no multi-tenancy, no audit trail. Your data leaves your infrastructure.

BI tool AI. Built into an existing BI platform. Tableau's AI analytics, Power BI Copilot, Domo AI, ThoughtSpot's natural language search. Better than spreadsheet tools because they query your warehouse. Still limited by the BI tool's ecosystem and licensing model.

Text-to-SQL agents. An LLM generates SQL against your warehouse schema. DataGPT, custom GPT agents, Databricks AI/BI. Most flexible, most dangerous. The agent writes arbitrary SQL. No guardrails on what it queries or how it calculates metrics.

All three share the same fundamental problem: the AI interprets your data on every query. There's no shared definition of what "revenue" means. Different tools, different phrasings, different numbers.

The accuracy problem nobody talks about

AI data analysis demos are impressive. The tool generates a chart from a natural language question in seconds. But run the same question through two different tools and compare the numbers. They don't match.

Why? Because each tool independently interprets your schema. "Revenue" might be SUM(amount) in one tool and SUM(total_price) WHERE status = 'completed' in another. Both are plausible. Neither knows that your finance team excludes refunds and trial conversions.

This isn't a model quality issue. GPT-4, Claude, and Gemini all exhibit this behavior. The model does what it's designed to do: generate plausible SQL from schema context. But "plausible" and "correct" are different things.

The accuracy problem compounds at scale:

One analyst, one question, one tool. Accuracy is "good enough." You eyeball the result and know if it's in the right ballpark.
Multiple analysts, same question, different tools. The numbers diverge. Debates about "which dashboard is right" start.
Customers querying their own data. You can't tell customers "the number is approximately right." They expect precision. One wrong number and trust is gone.
AI agents querying autonomously. No human in the loop to sanity-check. Wrong numbers propagate through workflows without anyone noticing.

Text-to-SQL gives you speed. It doesn't give you trust. For production analytics, you need both.

What makes AI analytics production-grade?

The gap between a demo and a production system is five things. Most AI data analysis tools have one or two. None of the spreadsheet or text-to-SQL tools have all five.

1. Governed metric definitions

Every metric has one definition. "Revenue" is SUM(amount) WHERE status != 'refunded' AND type != 'trial'. This definition is fixed, versioned in Git, and referenced by every consumer. The AI doesn't interpret what revenue means. It queries the governed definition.

cubes:
  - name: orders
    sql_table: public.orders
    measures:
      - name: total_revenue
        sql: "CASE WHEN status != 'refunded' AND type != 'trial' THEN amount ELSE 0 END"
        type: sum
        description: "Total revenue excluding refunds and trials"
      - name: order_count
        type: count
      - name: average_order_value
        sql: amount
        type: avg
    dimensions:
      - name: status
        sql: status
        type: string
      - name: category
        sql: category
        type: string
      - name: created_at
        sql: created_at
        type: time

This is a semantic layer: a metadata layer between your warehouse and every consumer. The AI agent calls explore_schema to discover available metrics, then query to fetch governed data. It never generates SQL from scratch.

2. Consistency across every surface

The same metric definition serves dashboards, React components in your product, REST APIs, AI agents, and markdown reports. Ask for "revenue" from any surface and you get the same number. Not because the AI happened to generate the same SQL, but because the definition is fixed.

This matters for AI specifically because AI tools generate different SQL on every call. Even the same tool, same question, different session can produce different queries. Governed definitions eliminate this variance.

3. Multi-tenant access control

For B2B products, Customer A's AI agent should never see Customer B's data. This can't be a prompt instruction ("only query data for customer A"). It needs to be structural: enforced on every query regardless of what the AI does.

    security_context:
      - name: tenant_filter
        sql: "{SECURITY_CONTEXT.tenant_id} = customer_id"

Every query through a tenant's key automatically includes this filter. The AI agent can't bypass it. See RBAC for more on how this works.

4. Performance at AI scale

AI agents make more queries than humans. A human asks one question. An agent might make 10 queries to answer it: exploring the schema, trying different dimensions, following up on anomalies. Multiply by hundreds of customers' agents querying simultaneously.

Pre-aggregation caching pre-computes common rollups. Hot queries return in single-digit milliseconds. Without it, AI-scale query volumes overwhelm most warehouses.

5. Audit trail

When the number is wrong, you need to trace it. With a semantic layer, every result traces back to a versioned metric definition in Git. You can point to the exact commit that defined the calculation, see when it changed, and who approved the change. With raw text-to-SQL, the trail is "an LLM generated some SQL" with no reproducibility.

AI data analysis tools compared

Tool	Approach	Governed metrics	Multi-tenant	Audit trail	Best for
Julius AI	Spreadsheet upload + LLM	No	No	No	Ad-hoc exploration of CSVs
ChatGPT Data Analyst	File upload + code interpreter	No	No	No	One-off analysis, prototyping
Databricks AI/BI	Text-to-SQL + Unity Catalog	Partial (table-level)	Partial	Partial	Teams already on Databricks
Power BI Copilot	Natural language in Power BI	DAX measures (locked to PBI)	Complex setup	Within PBI	Microsoft ecosystem
Tableau AI	Natural language in Tableau	Tableau semantic model	Enterprise	Within Tableau	Salesforce ecosystem
ThoughtSpot	Natural language BI	Proprietary semantic layer	Enterprise	Yes	Enterprise AI-powered BI
DataGPT	Conversational analytics agent	No	No	No	Autonomous data analysis
Domo AI	BI platform with AI features	Within Domo	Within Domo	Within Domo	End-to-end data platform
Charts in agent (Bonnard)	`visualize` tool on your MCP server	N/A (your `runSql`)	N/A (your DB)	N/A	Interactive charts inside Claude or ChatGPT

The tools in the top half are AI-first but governance-second. The ones in the middle (enterprise BI) have governance but lock it to their ecosystem. The bottom row is a different kind of option: @bonnard/mcp-charts does not generate SQL or define metrics. It renders interactive charts from the rows your runSql returns, inside the agent.

When each approach makes sense

Use spreadsheet AI (Julius, ChatGPT) when:

You're exploring a dataset for the first time
Nobody else needs to reproduce the result
The data isn't sensitive
You want speed over accuracy

Use BI tool AI (Power BI Copilot, Tableau AI) when:

You're already in that BI ecosystem
Internal teams are the primary consumer
You don't need to serve AI agents or embed in products
Enterprise licensing fits your budget

Use a semantic layer when:

Multiple consumers need the same metrics (dashboards + API + agents)
Customers see the results (B2B embedded analytics)
AI agents query data autonomously
You need auditability (which definition produced this number?)
Metrics must be consistent across every tool and surface

Getting the chart back: charts inside the agent

Governing the numbers is one half of AI data analysis. The other half is what the customer actually sees. When someone asks a question in Claude or ChatGPT, they often want a chart, not a table of rows. That's a separate surface from the governance layer, and it's where @bonnard/mcp-charts fits.

@bonnard/mcp-charts is not a semantic layer, a BI tool, or a text-to-SQL agent. It is a visualize tool you add to your MCP server that renders interactive charts from your query results, inside the agent.

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, {
  runSql: async (sql) => db.query(sql),
});

addCharts registers a visualize tool (and a visualize_read_me companion). The flow:

The agent calls visualize with a query.
Your runSql returns rows.
Bonnard infers the chart from the typed result.
It renders an interactive ui:// widget in Claude or ChatGPT.

Chart types: line, bar, area, pie, scatter, funnel, waterfall, and table. Axes, formatting, and gap-fill are determined automatically, so the same typed result produces the same chart. Native adapters cover Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql. Bonnard never touches your database.

The tool is built for the agent: compact summaries, row caps with completeness flags, a typed schema, and instructive errors with a next_step. For the design principles, see how Bonnard builds agent-friendly MCPs.

The shift from AI-generated SQL to AI-queried metrics

The first generation of AI analytics gave agents raw warehouse access and hoped for the best. It worked in demos. It failed in production because the agent interprets business logic on every query.

The next generation separates the concerns. Data teams define governed metrics in a semantic layer. AI agents query those definitions instead of raw tables. The data team's expertise is encoded in the schema. The AI's role shifts from "guess what revenue means" to "pick the right governed metric and present the result."

This is a better division of labor. The data team defines what's true. The AI handles the interface. Trustworthy numbers with a natural language frontend.

Getting started

If you want charts rendered inside an AI agent from your own query results, install the package and register the tool on your MCP server:

npm install @bonnard/mcp-charts

import { addCharts } from "@bonnard/mcp-charts";

addCharts(server, {
  runSql: async (sql) => db.query(sql),
});

Repo: github.com/bonnard-data/mcp-charts. Full walkthrough: MCP charts. For background: What Is a Semantic Layer?.

Frequently asked questions

What is AI data analysis?

AI data analysis uses large language models and machine learning to automate data querying, pattern detection, and insight generation. Instead of writing SQL manually, you ask questions in natural language and the AI produces queries, charts, and summaries. Tools range from spreadsheet analyzers (Julius AI) to enterprise BI platforms with AI features (Power BI Copilot, Tableau AI).

Is AI data analysis accurate?

For ad-hoc exploration, accuracy is "good enough." For production analytics where customers see the numbers, accuracy varies per query because the AI generates SQL from scratch each time. Two tools generating SQL for "revenue" may produce different numbers because they interpret the calculation differently. A semantic layer solves this by defining each metric once and letting AI query the definition instead of generating its own.

What is the best AI tool for data analysis?

It depends on the use case. For one-off CSV exploration: Julius AI or ChatGPT Data Analyst. For enterprise BI: Power BI Copilot or Tableau AI. For governed metrics served to AI agents and B2B customers: a semantic layer approach. See the comparison table above.

What is the best approach to AI data analysis for SaaS?

For a SaaS product, two things matter: trustworthy numbers and where the customer sees the result. Govern the numbers with a semantic layer so the AI queries fixed definitions instead of guessing. Then decide the surface. If your customers work in Claude or ChatGPT, @bonnard/mcp-charts adds a visualize tool to your MCP server that renders interactive charts from your query results, inside the agent. Your runSql runs the query, so Bonnard never touches your database. Install with npm install @bonnard/mcp-charts.

How is AI analytics different from traditional BI?

Traditional BI requires analysts to write queries and build dashboards. AI analytics automates the query generation. The risk: AI-generated queries are inconsistent. Traditional BI with a semantic layer gives you consistent definitions. AI analytics with a semantic layer gives you both: consistent definitions with a natural language interface.

Can AI replace data analysts?

No. AI can generate queries and create visualizations faster than humans. It can't define what metrics should mean, decide which questions to ask, or judge whether an insight is actionable. Data analysts bring domain knowledge and judgment. AI handles the mechanical parts. A semantic layer encodes the analyst's expertise so AI can query it reliably.

What is the difference between AI analytics and text-to-SQL?

Text-to-SQL is a technique where an LLM generates SQL from natural language. AI analytics is broader: it includes text-to-SQL plus pattern detection, anomaly identification, forecasting, and visualization generation. Both share the same governance gap: the AI interprets your data on every query. A semantic layer addresses this for both.

What warehouses work with AI data analysis?

Most AI analytics tools work with common warehouses: Snowflake, BigQuery, Databricks, PostgreSQL (including Supabase, Neon, and RDS), Redshift, and DuckDB (including MotherDuck). The semantic layer generates the appropriate SQL dialect for each warehouse.