The five-layer architecture that separates concerns, enforces governance, and scales from prototype to production.
The Agentic BI system is organized into five distinct layers. Each layer has a single responsibility and communicates only with its immediate neighbors. This separation ensures that security policies, business logic, and UI concerns never bleed into each other.
The Golden Rule
Treat the VS Code chat participant as just the front end/orchestrator, not the place where all business logic lives. VS Code chat participants own user interaction. Tool-like capabilities and MCP servers handle reusable, agent-invokable actions.
The VS Code chat participant is purely a front-end orchestrator. It owns the user interaction — collecting natural-language prompts, displaying responses, and managing conversation context.
A backend API service that sits between the chat UI and all data systems. This is the single enforcement point for security, governance, and business rules.
Parse the user's question to understand what they're asking: KPI lookup, trend analysis, data exploration, report generation, etc.
Decide whether to query Power BI semantic model or Snowflake based on the nature of the question and available metadata.
Construct source-specific prompts with relevant metadata subset, few-shot examples, and business glossary context.
Validate generated queries before execution. Enforce row limits, deny dangerous operations, apply role-based restrictions.
Discrete, reusable tools exposed via the Model Context Protocol. Each tool does one thing well.
| Tool | Action | Returns |
|---|---|---|
list-semantic-models | Enumerate available Power BI datasets | Model IDs, names, workspace |
get-measures | Retrieve measures from a semantic model | Measure definitions, descriptions |
generate-dax | Produce validated DAX skeleton | DAX query string |
generate-sql | Produce validated SQL query | SQL query string |
execute-preview | Run query with row limit | Result rows (capped) |
save-export | Export to file or create report link | Export URI / report URL |
Access via the Execute Queries REST API against semantic models. Requires Azure AD / Entra app registration with proper scopes.
Access via the SQL API — submit, poll, cancel, fetch statements. Always through a policy layer with warehouse/role mapping.
Every response includes multiple output types, giving the user actionable results:
Two example scenarios showing how the pipeline works in practice:
// User asks: "Show top 20 clients by PMPM trend" // Step 1 — Intent Classification → type: "kpi_trend", entities: ["clients", "PMPM"] // Step 2 — Source Routing → source: "power_bi" // PMPM is a defined measure // Step 3 — Prompt + Validate → DAX query generated and validated against policy // Step 4 — Execute + Present → Table of 20 clients with PMPM values, trend chart suggestion
// User asks: "How many claims were denied in Q3 by denial reason?" // Step 1 — Intent Classification → type: "exploration", entities: ["claims", "denied", "Q3"] // Step 2 — Source Routing → source: "snowflake" // detail-level, no PBI measure // Step 3 — Prompt + Validate → SQL query generated, validated, schema whitelisted // Step 4 — Execute + Present → Table of denial reasons with counts, bar chart suggestion