Routing & Metadata — Agentic BI Portal

Semantic Routing

The routing layer determines which data source can best answer a user's question. It uses a combination of keyword matching, embedding similarity, and metadata lookup.

Routing Decision Tree

"Show PMPM by region"
    │
    ├─ Does a PBI measure named "PMPM" exist?
    │  → YES → Route to Power BI
    │
    ├─ Is the question about detail-level data?
    │  → Route to Snowflake
    │
    └─ Ambiguous?
       → Check embedding similarity against known query patterns
       → Route to highest-confidence match

Routing rules (in priority order):

#	Rule	Routes To
1	Question references a known PBI measure	Power BI
2	Question asks for a report or dashboard	Power BI
3	Question asks for detail rows or raw data	Snowflake
4	Question matches a few-shot example	Per example
5	Fallback: embedding similarity	Highest match

Metadata Service

A central service that provides schema, measure, and relationship information from both data sources.

📊

Power BI Metadata

Tables, columns, measures, relationships, descriptions — extracted via the PBI REST API or TMDL.

❄️

Snowflake Metadata

Databases, schemas, tables, columns, data types — extracted from INFORMATION_SCHEMA.

🔗

Cross-Source Mapping

Map PBI measures to Snowflake tables so the system knows which Snowflake tables back which PBI models.

⚡

Metadata Cache

Metadata refreshed on schedule (e.g., every 6 hours). Cached in-memory for sub-millisecond lookups.

The metadata service ensures prompts only include relevant context — not the entire schema. For a question about "PMPM by region," the prompt includes only the PMPM measure, region dimension, and their relationship.

Business Glossary

Domain-specific terms resolved to exact column/measure references before they reach the LLM:

Business Term	Resolves To	Source
`PMPM`	`[Fact Claims].[PMPM]` measure	Power BI
`member months`	`[Fact Eligibility].[Member Months]` measure	Power BI
`LOB`	`[Dim LOB].[Line of Business]` column	Both
`region`	`[Dim Region].[Region Name]` column	Both
`denied claims`	`claims WHERE status = 'DENIED'`	Snowflake
`high-cost claimant`	`claims WHERE amount > 50000`	Snowflake

The glossary is maintained as a JSON file and can be updated by business users without code changes.

Few-Shot Examples

Curated question→query pairs injected into prompts. These dramatically improve LLM accuracy for domain-specific patterns:

Few-Shot Example SetDAX

// Example 1
Q: "What is the current PMPM by line of business?"
A: EVALUATE
   SUMMARIZECOLUMNS(
     'Dim LOB'[Line of Business],
     "PMPM", [PMPM]
   )

// Example 2
Q: "Show top 10 clients by total claims amount"
A: EVALUATE
   TOPN(
     10,
     SUMMARIZECOLUMNS(
       'Dim Client'[Client Name],
       "Total Claims", [Total Claims Amount]
     ),
     [Total Claims Amount], DESC
   )

Best practices for few-shot examples:

✓Include 3–5 examples per query pattern (KPI, trend, comparison, etc.)
✓Only inject examples that match the detected intent type
✓Keep examples verified — wrong examples poison all future queries
✓Version-control examples alongside the metadata configuration

Caching Strategy

Metadata Cache

Schema, measures, glossary. Refreshed every 6 hours or on demand. Stored in-memory. TTL-based eviction.

Query Cache

Recent query results keyed by normalized query string + user role. TTL: 15 minutes. Invalidated on data refresh.

Cache hits return in under 50ms. Cache misses fall through to the full pipeline.