priority order: semantic endpoints > published data sources > raw warehouse fallback.

import { MCPClient, ToolDefinition, ResourceDescriptor } from '@modelcontextprotocol/sdk';

interface BIPlatformConfig {
  platform: 'looker' | 'tableau' | 'powerbi';
  authEndpoint: string;
  maxResultSize: number;
}

export class SemanticToolRouter {
  private client: MCPClient;
  private platformConfig: BIPlatformConfig;

  constructor(config: BIPlatformConfig) {
    this.platformConfig = config;
    this.client = new MCPClient({ endpoint: config.authEndpoint });
  }

  async resolveQuery(nlQuery: string): Promise<ToolDefinition | null> {
    const tools = await this.client.listTools();
    
    // Prioritize semantic/governed tools over raw query tools
    const semanticTools = tools.filter(t => 
      t.name.includes('measure') || 
      t.name.includes('explore') || 
      t.name.includes('dax')
    );

    if (semanticTools.length > 0) {
      return semanticTools[0]; // Route to highest-priority semantic tool
    }

    // Fallback to published data source tools
    const sourceTools = tools.filter(t => t.name.includes('datasource'));
    return sourceTools.length > 0 ? sourceTools[0] : null;
  }
}

Rationale: MCP servers expose tools and resources dynamically. Hardcoding tool names breaks when vendors update their API surface. Dynamic discovery with semantic prioritization ensures agents query governed metrics first, reducing hallucination without sacrificing flexibility.

Step 2: Context-Aware Result Paging

BI platforms return result sets that frequently exceed LLM context windows. Power BI's community MCP server handles this by streaming large outputs to local CSV files. The pattern below generalizes this approach across platforms.

import { createWriteStream, WriteStream } from 'fs';
import { join } from 'path';

export class ResultPagingMiddleware {
  private tempDir: string;
  private contextLimit: number;

  constructor(tempDir: string, contextLimit: number = 128000) {
    this.tempDir = tempDir;
    this.contextLimit = contextLimit;
  }

  async processResult(rawPayload: unknown): Promise<string | Buffer> {
    const payloadStr = JSON.stringify(rawPayload);
    
    if (payloadStr.length <= this.contextLimit) {
      return payloadStr;
    }

    // Stream oversized results to CSV to preserve context window
    const outputPath = join(this.tempDir, `bi_result_${Date.now()}.csv`);
    const stream: WriteStream = createWriteStream(outputPath);
    
    // Flatten nested BI response structures into tabular format
    const rows = this.flattenToRows(rawPayload);
    rows.forEach(row => stream.write(row.join(',') + '\n'));
    stream.end();

    return Buffer.from(`Result paged to ${outputPath}. Use file reader tool to access.`);
  }

  private flattenToRows(data: unknown): string[][] {
    // Platform-specific flattening logic abstracted here
    const items = Array.isArray(data) ? data : [data];
    return items.map(item => Object.values(item as Record<string, unknown>).map(String));
  }
}

Rationale: LLMs degrade in reasoning quality when context windows approach capacity limits. Streaming to disk preserves agent reasoning capability while maintaining access to full datasets. The file path reference acts as a lightweight pointer that the agent can resolve via a separate file-reading tool.

Step 3: Stratified Cache Layer

BI engines aren't optimized for high-frequency identical queries. Agent exploratory loops frequently re-query the same dashboard filters. A time-to-live (TTL) cache reduces platform load and latency.

export class StratifiedCache {
  private store: Map<string, { data: unknown; expires: number }> = new Map();
  private defaultTTL: number;
  private freshnessSLA: Map<string, number>;

  constructor(defaultTTLSeconds: number = 600) {
    this.defaultTTL = defaultTTLSeconds * 1000;
    this.freshnessSLA = new Map([
      ['revenue', 300],      // 5 min
      ['inventory', 900],    // 15 min
      ['realtime_metrics', 60] // 1 min
    ]);
  }

  get(cacheKey: string, category?: string): unknown | null {
    const entry = this.store.get(cacheKey);
    if (!entry) return null;
    
    const ttl = category ? this.freshnessSLA.get(category) ?? this.defaultTTL : this.defaultTTL;
    if (Date.now() > entry.expires) {
      this.store.delete(cacheKey);
      return null;
    }
    return entry.data;
  }

  set(cacheKey: string, data: unknown, category?: string): void {
    const ttl = category ? this.freshnessSLA.get(category) ?? this.defaultTTL : this.defaultTTL;
    this.store.set(cacheKey, { data, expires: Date.now() + ttl });
  }
}

Rationale: Uniform caching breaks when different metrics have different freshness requirements. Stratification by category aligns cache TTLs with business SLAs, preventing stale executive reports while allowing rapid iteration on exploratory queries.

Step 4: Observability Gateway

BI audit trails lack the granularity required for agent accountability. Every MCP call must be intercepted and shipped to centralized observability infrastructure.

export class ObservabilityGateway {
  private exporter: (event: MCPEvent) => Promise<void>;

  constructor(exporter: (event: MCPEvent) => Promise<void>) {
    this.exporter = exporter;
  }

  async wrapCall<T>(toolName: string, params: unknown, fn: () => Promise<T>): Promise<T> {
    const start = performance.now();
    const eventId = crypto.randomUUID();
    
    try {
      const result = await fn();
      await this.exporter({
        id: eventId,
        tool: toolName,
        status: 'success',
        latency: performance.now() - start,
        timestamp: new Date().toISOString()
      });
      return result;
    } catch (error) {
      await this.exporter({
        id: eventId,
        tool: toolName,
        status: 'error',
        error: (error as Error).message,
        latency: performance.now() - start,
        timestamp: new Date().toISOString()
      });
      throw error;
    }
  }
}

interface MCPEvent {
  id: string;
  tool: string;
  status: 'success' | 'error';
  latency: number;
  timestamp: string;
  error?: string;
}

Rationale: Vendor logs capture dashboard views, not agent tool invocations. Interception at the MCP client layer ensures every query, parameter, and latency metric is traceable. This closes the audit gap and enables post-incident analysis of agent behavior.

Pitfall Guide

1. Admin Impersonation

Explanation: Authenticating the agent with a BI admin account bypasses row-level security, exposing sensitive data to unauthorized contexts. Compliance frameworks (SOC 2, GDPR) flag this as a critical control failure. Fix: Use scoped service principals or forward user identity tokens. Configure the MCP server to resolve RLS rules based on the authenticated session, not a static admin role.

2. Unbounded Context Consumption

Explanation: Agents that request full dataset exports without pagination will exhaust LLM context windows, causing silent truncation or degraded reasoning. Fix: Enforce result size limits at the middleware layer. Stream oversized payloads to disk or object storage, returning only metadata and file references to the agent.

3. Silent Query Loops

Explanation: Exploratory agent loops can trigger thousands of dashboard renders or metric queries in minutes, incurring unexpected API costs and platform throttling. Fix: Implement token budgeting per agent identity. Set daily query quotas and enforce exponential backoff on repeated identical requests. Log quota breaches to alerting channels.

4. Assuming Write Parity

Explanation: Community MCP servers are predominantly read-optimized. Attempting to publish dashboards, modify datasets, or alter permissions via MCP tools often fails or returns partial success states. Fix: Restrict agent scopes to read-only credentials. Route mutation operations through approved CI/CD pipelines or platform-native admin interfaces. Document write limitations explicitly in agent tool definitions.

5. Audit Blind Spots

Explanation: BI platforms log user interactions, not programmatic tool calls. Without interception, agent queries become invisible in compliance reports and incident investigations. Fix: Deploy an observability gateway that wraps every MCP invocation. Ship structured events to Datadog, Honeycomb, or OpenTelemetry collectors. Include tool name, parameters, latency, and status in every log entry.

6. Cache Staleness Misconfiguration

Explanation: Applying a uniform TTL across all metrics causes executive dashboards to serve outdated numbers while exploratory queries suffer unnecessary latency. Fix: Implement stratified caching aligned with business SLAs. Tag queries by category (e.g., revenue, inventory, realtime) and assign TTLs accordingly. Invalidate cache on known data refresh events.

7. Semantic Drift

Explanation: Agents default to raw SQL when semantic tools fail or return ambiguous results, reconstructing calculations that diverge from governed metrics. Fix: Enforce tool routing rules that prioritize LookML measures, DAX queries, or VizQL endpoints. Block direct warehouse access for agents operating in governed analytics mode. Validate outputs against dashboard baselines during testing.

Production Bundle

Action Checklist

• Provision read-only service principals scoped to specific workspaces or projects
• Configure RLS passthrough or user token forwarding for all agent identities
• Implement result paging middleware with disk streaming for payloads exceeding context limits
• Deploy stratified cache layer with category-based TTL alignment to business SLAs
• Wrap all MCP invocations in an observability gateway shipping structured events to centralized logging
• Set daily query quotas and rate limits per agent identity to prevent silent loops
• Validate agent outputs against dashboard baselines before production rollout
• Document write limitations and route mutations through approved pipelines

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Startup / Proof of Concept	Community MCP + Direct Auth	Fastest path to semantic alignment; minimal infra overhead	Low initial cost; scales poorly beyond 50 agents
Mid-Market / Compliance-Heavy	Scoped Service Principals + RLS Passthrough + Observability Gateway	Meets audit requirements; prevents data leakage; traceable agent behavior	Medium infra cost; reduces compliance risk and reconciliation overhead
Enterprise / High-Volume	Stratified Cache + Query Quotas + Platform-Native Auth Federation	Prevents platform throttling; aligns freshness with SLAs; enforces governance	Higher initial engineering cost; eliminates unexpected API spend and stakeholder distrust

Configuration Template

import { StratifiedCache } from './cache';
import { ResultPagingMiddleware } from './pager';
import { ObservabilityGateway } from './observability';
import { SemanticToolRouter } from './router';

export const BIIntegrationConfig = {
  platform: 'powerbi' as const,
  authEndpoint: process.env.BI_AUTH_ENDPOINT!,
  maxResultSize: 100000,
  contextLimit: 128000,
  tempDir: '/tmp/bi_agent_cache',
  cacheDefaults: {
    revenue: 300,
    inventory: 900,
    realtime_metrics: 60
  },
  observability: {
    exporter: async (event: any) => {
      // Ship to Datadog / Honeycomb / OpenTelemetry
      console.log(JSON.stringify(event));
    }
  }
};

export const initializeAgentStack = () => {
  const cache = new StratifiedCache(600);
  Object.entries(BIIntegrationConfig.cacheDefaults).forEach(([cat, ttl]) => {
    cache['freshnessSLA'].set(cat, ttl * 1000);
  });

  const pager = new ResultPagingMiddleware(BIIntegrationConfig.tempDir, BIIntegrationConfig.contextLimit);
  const gateway = new ObservabilityGateway(BIIntegrationConfig.observability.exporter);
  const router = new SemanticToolRouter({
    platform: BIIntegrationConfig.platform,
    authEndpoint: BIIntegrationConfig.authEndpoint,
    maxResultSize: BIIntegrationConfig.maxResultSize
  });

  return { cache, pager, gateway, router };
};

Quick Start Guide

1. Provision Credentials: Create a read-only service principal or personal access token scoped to target workspaces. Avoid admin roles.
2. Deploy MCP Client: Initialize the SemanticToolRouter with your platform endpoint. Verify tool discovery returns semantic endpoints (measures, explores, DAX queries).
3. Attach Middleware: Wire the ResultPagingMiddleware and StratifiedCache to intercept responses. Configure TTLs based on metric freshness requirements.
4. Enable Observability: Register the ObservabilityGateway exporter to ship structured events to your logging stack. Validate that every tool invocation logs latency, status, and parameters.
5. Test & Quota: Run a controlled query loop against known dashboard baselines. Set daily query quotas and verify cache hit rates before enabling production agent identities.