In the first part of this series, we introduced the idea of moving beyond dashboards to build diagnostic AI agents capable of uncovering the why behind business performance shifts. That article focused on architectural principles and the role of AWS Strands in enabling controlled agentic behavior.
In this follow-up, we take a more detailed look at how we applied the same diagnostic approach inside a Microsoft Fabric environment . The objective remained unchanged: create an agent that can produce consistent, evidence-backed, and domain-aware explanations for revenue fluctuations—without relying on manual analysis.
What follows is a technical walkthrough of the system we built, the constraints that shaped it, and the design decisions required to ensure reliability, correctness, and explainability in production-like workloads.
Why Fabric Required a Different Approach
Our previous example used an Azure-based data warehouse. However, many enterprises today rely heavily on Microsoft Fabric and Power BI as their analytical backbone. This was the case for the client in this scenario: a multi-location services business with detailed operational data stored in a Fabric Lakehouse.
They had dashboards that clearly showed where revenue declined, but lacked the analytical bandwidth to investigate why. Analysts were constrained by time and volume, and business teams needed explanations, not charts.
To address this gap, we connected our diagnostic agent directly to the SQL Analytics endpoint of the Fabric Lakehouse, instead of querying the Power BI semantic model. This let the agent work with raw, granular data while still respecting business logic defined in Power BI measures.
The agent’s job was straightforward: ingest data from Fabric, analyze patterns, correlate signals, and identify plausible drivers behind revenue movements—autonomously and with auditability.
The Data Landscape: Four Core Tables
The client’s data model included four domain-critical tables in the Fabric Lakehouse:
1. Branches
A dimensional table listing each business location, associated region, and geographic attributes.
2. Calendar
A date table with fiscal periods, holidays, and standard temporal attributes.
3. Weather Trends Forecast
Daily temperature, Cooling Degree Days (CDD), and Heating Degree Days (HDD) by postal code.
4. Booking Summary
Operational booking metrics by branch, service line, and date—including TotalCalls and booking labels.
These datasets form the minimum context required for the agent to move beyond pattern recognition and into causal inference heuristics—weather, time, location, and operational behavior.
Tooling the Agent for Reliable, Controlled Execution
In line with the design philosophy from Part 1, the agent was not allowed to infer internal schema or execute arbitrary metadata queries. Instead, it was given a controlled, well-defined set of tools that enforce correctness and constrain uncertainty.
The tooling fell into three groups: SQL tools, data-science tools, and PII handling tools.
SQL Tools: Eliminating Schema Guesswork
1. get_table_schema(table_name)
This tool retrieves column names and data types for any table in the Fabric Lakehouse. Purpose: prevent hallucinated columns and guarantee that SQL queries are grounded in actual metadata.
2. run_sql(query)
Executes agent-generated SQL queries with secret-protected database credentials. Purpose: isolate credentials and ensure that only validated SQL passes through the execution layer.
These SQL tools create a guardrail system in which every query is observable, reproducible, and traceable.
Data Science Tools: Turning Raw Data Into Diagnostics
Once a query returns data, analysis shifts into Python-based tools tailored for statistical inference.
1. analyze_correlation
Performs Pearson correlation to identify features most strongly associated with a target variable. Example: Assessing whether revenue decreases align with temperature anomalies or holiday periods.
2. summarize_column
Computes mean, min, max, variance, and skewness—useful for spotting branches with episodic spikes.
3. measure_variation
Calculates mean and standard deviation, highlighting operational volatility by branch or service line.
4. binned_statistic_tool
Generates temperature-based distribution bins (e.g., 40–50°F) to quantify demand under different weather conditions.
Each of these tools gives the agent the ability to contextualize anomalies rather than simply report them.
PII Tool: Enforcing Data Governance
mask_pii
Uses Azure Language PII detection to sanitize sensitive data.
This step ensures the agent can work freely with real datasets without violating compliance boundaries.
A Modular System Prompt for Operational Consistency
Large system prompts often become brittle over time. To avoid this, we broke the prompt into modular components:
- Core System Prompt
- Business Rules (markdown)
- Report Template (markdown)
The core prompt defines the agent’s identity (a data analyst), operational constraints, required behaviors, and plan-before-action logic.
Key Operational Rules Included:
1. Execution Plan First The agent must produce a 3–8 step plan before any action. This makes reasoning transparent and tool calls predictable.
2. No schema inference The agent is explicitly forbidden from exploring INFORMATION_SCHEMA or similar constructs. This eliminates unpredictable SQL behaviors.
3. Evidence and confidence required Every conclusion must include evidence and an explicit confidence score—critical for business trust.
4. Mandatory markdown reporting format Final outputs follow a fixed report structure, improving readability and auditability across different queries.
Business Rules: Translating Power BI Logic Into Natural Language
Because the agent connects directly to SQL tables rather than Power BI measures, we explicitly defined business logic within business_rules.md.
Example: Power BI DAX measure for Bookable Calls:
Bookable_calls = SUM(Bookings[TotalCalls]),
WHERE Bookings[Label] IN (“Positive”, “Negative”)
Translated to natural language:
- “Bookable calls is the sum of TotalCalls where Label is either ‘Positive’ or ‘Negative’.”
This explicitly encodes business semantics, ensuring the agent interprets the data the same way analysts do.
Report Template: Enforcing a Standardized Output Format
The report_template.md file defines a consistent way to structure the agent's conclusion:
- Query
- Analysis Summary
- Key Findings
- Supporting Evidence
- Interpretation
- Conclusion
- Confidence Level
- Recommendations
Regardless of whether the agent is analyzing weather anomalies or geographic volatility, the output remains structured and inspection-friendly.
This template ensures downstream stakeholders receive uniform, high-quality reports—an essential trait when scaling diagnostic agents across multiple teams.
What We Observed From the Agent
1. Strong Contextual Understanding
The agent demonstrated an ability to incorporate domain semantics—for instance, interpreting HDD (Heating Degree Days) and CDD (Cooling Degree Days) without additional training.
2. Consistent, Reproducible Output
Because the tools, rules, and templates are tightly controlled, reports were uniform across a wide range of user queries.
3. Accurate Logical Confinement
The combination of schema tools + rule-based guidance prevented common LLM failure patterns, such as:
- guessing column names
- inventing joins
- applying incorrect business definitions
The result is a system that behaves deterministically within the allowed space, while still benefiting from the agent's analytical reasoning capabilities.
Conclusion
By connecting directly to Microsoft Fabric’s SQL Analytics endpoint and combining it with a disciplined toolset, modular prompts, and explicit business logic, we created a diagnostic AI agent capable of delivering consistent, evidence-backed insights at scale.
The design choices—restricted metadata access, schema-controlled SQL execution, structured analytical tooling, and standardized reporting—allowed the agent to operate reliably in a real enterprise data environment.
This implementation demonstrates that diagnostic AI agents can move beyond conceptual promise. With the right guardrails, tooling, and domain grounding, they can become dependable partners in uncovering the why behind business performance shifts, even in data-dense environments like Microsoft Fabric.
Work With CloudIQ
If your organization is exploring diagnostic AI, agentic workflows, or enterprise-grade analytics modernization, CloudIQ can help you design, build, and operationalize solutions that deliver real, measurable impact.
Bring us your most complex data challenges—and challenge us to solve them.

















































