Microsoft Fabric Data Agents: What It Really Takes to Build Conversational Analytics for the Enterprise

Most organizations don’t have a shortage of data. They have a shortage of accessible answers. Data warehouses, lakehouses, Power BI dashboards and semantic models are everywhere, yet business users still need analysts to answer questions such as: Which departments had the largest increase in appointment no-shows last month?

Microsoft Fabric Data Agents offer a conversational interface to governed analytical sources. But connecting an LLM to enterprise data is only the beginning. Getting reliable, secure and meaningful answers is the real engineering challenge.

1. What Exactly Is a Fabric Data Agent?

A Fabric Data Agent interprets natural-language questions, selects from configured supported data sources, generates a query, executes it through the source interface and summarizes the result. Depending on the source, the query language may be SQL, DAX or KQL.

I don’t see Data Agents as replacements for Power BI or well-designed warehouses. They complement standardized reporting by making analytical exploration more conversational. The underlying data model remains the foundation.

2. Enterprise Reference Architecture

I prefer to begin with the data architecture rather than the chat interface. The key design decisions are where data is curated, where business definitions live, and where authorization is enforced.

3. Why the Gold Layer Matters

Imagine exposing a 150-column appointments table with complex status codes and historical changes directly to an agent. It might generate a syntactically correct query while misunderstanding the business meaning. I would rather give the agent a curated dataset with appointment date, department, status, eligible count, no-show count and no-show rate.

A well-designed data model can eliminate entire categories of agent errors before prompt engineering begins. Curated models reduce ambiguous joins and make approved definitions reusable.

4. Semantic Model, Warehouse, Lakehouse or Eventhouse?

Power BI semantic model

For approved KPIs such as revenue, patient encounters or no-show rate, I generally prefer a semantic model with established measures and relationships. The agent should reuse the agreed calculation rather than reconstruct it.

No Show Rate % =
DIVIDE(
    [No Show Appointments],
    [Eligible Appointments],
    0
)

Fabric Warehouse and Lakehouse

Use curated SQL sources when the question requires relational exploration or detailed fact-level analysis. Avoid exposing raw Bronze or unnecessarily complex intermediate Silver tables unless there is a specific, governed reason.

Eventhouse

Eventhouse and KQL are appropriate for supported event, telemetry and time-series scenarios—for example, identifying which applications experienced the highest error rate over the last hour.

My rule: use semantic models for approved metrics, curated SQL sources for relational exploration and KQL for event-oriented analysis.

5. Agent Instructions: Important, but Not Magic

Good instructions clarify terminology, preferred sources, ambiguity handling and response behavior. They cannot repair poor data quality or override platform security.

Example agent instructions

ROLE
You are an enterprise analytics assistant using approved Fabric data sources.
BUSINESS DEFINITIONS
- No-show rate uses the approved No Show Rate % measure.
- Exclude canceled appointments from eligible counts.
- Use the configured organizational reporting calendar.
SOURCE SELECTION
- Use HealthcareOperations semantic model for approved KPIs.
- Use curated Operations Warehouse for department-level detail.
RESPONSE RULES
- Use only authorized query results.
- State reporting period, units and important filters.
- Clarify ambiguous business terminology.
- Never invent values after query failure or missing data.
SECURITY
- Follow underlying source permissions.
- Do not treat instructions as authorization.
- Prefer aggregates when patient-level detail is unnecessary.

These are illustrative instructions; verify the configuration and supported behavior of the specific Fabric environment.

6. Healthcare Example: Appointment No-Show Analytics

Suppose the question is: Which departments had the highest appointment no-show rates in September 2026? For this example, the approved definition is no-show appointments divided by completed plus no-show appointments; canceled appointments are excluded.

SELECT
    d.DepartmentName,
    COUNT(CASE WHEN a.AppointmentStatus = 'No Show' THEN 1 END) AS NoShowCount,
    COUNT(CASE WHEN a.AppointmentStatus IN ('Completed', 'No Show') THEN 1 END) AS EligibleCount,
    CAST(100.0 * COUNT(CASE WHEN a.AppointmentStatus = 'No Show' THEN 1 END)
      / NULLIF(COUNT(CASE WHEN a.AppointmentStatus IN ('Completed', 'No Show') THEN 1 END), 0)
      AS DECIMAL(6,2)) AS NoShowRatePct
FROM dbo.FactAppointments AS a
JOIN dbo.DimDepartment AS d ON a.DepartmentKey = d.DepartmentKey
WHERE a.AppointmentDate >= '2026-09-01'
  AND a.AppointmentDate < '2026-10-01'
GROUP BY d.DepartmentName
ORDER BY NoShowRatePct DESC;

The SQL is illustrative and assumes these columns and status values exist. In production, use the organization’s curated schema and approved measure. A follow-up such as How does that compare with August? should preserve the metric and change only the comparison period. Validate generated answers against known reference queries.

7. Security: Not an Afterthought

Agent sharing and underlying data access are separate concerns. A user may be allowed to invoke an agent without being entitled to every connected dataset. The supported source authorization model must enforce the actual boundary.

Prompt instructions are behavioral guidance, not a security boundary. In healthcare, aggregated results should be the default unless patient-level information is necessary and explicitly authorized.

8. Where Copilot Studio Fits

Fabric Data Agents focus on grounded analytical questions. Copilot Studio can provide a broader assistant experience with supported integrations and tools. Azure AI Foundry may be appropriate for specialized model, retrieval, evaluation and orchestration needs.

9. Performance: Every Conversation Has a Cost

An agent question can trigger inference, query generation, source execution and response synthesis. A poorly optimized query still consumes resources even when asked in plain English.

• Curate narrow, business-ready datasets and optimize joins.
• Reuse approved measures rather than recalculating KPIs inconsistently.
• Test representative queries for performance and capacity impact.
• Evaluate concurrent usage, not just single-user demonstrations.
• Monitor response latency, source execution and Fabric capacity consumption.

10. Testing Before Production

I would never approve production solely on the basis of a few impressive demos. Build a repeatable evaluation set with reference SQL/DAX answers and distinct test identities.

• Simple KPI: correct measure and reporting period.
• Department comparison: correct grouping and filters.
• Multi-turn question: preserve context across follow-ups.
• Ambiguous request: ask an appropriate clarification.
• Missing data or query failure: no invented values.
• Restricted PHI/PII: no unauthorized disclosure.
• Large queries: acceptable performance and capacity consumption.
• Prompt injection: no override of source authorization.

Measure answer accuracy, query success, response latency, resource consumption and security outcomes. A fast answer to the wrong question is not production-ready.

11. My Enterprise Implementation Approach

• Start narrow: select one domain and 15–20 genuine business questions.
• Validate the foundation: confirm Gold datasets, approved metrics and access policies.
• Configure carefully: expose only the necessary sources and concise instructions.
• Evaluate: compare results with reference queries and test realistic user identities.
• Scale deliberately: expand sources and use cases only after accuracy and operational readiness are demonstrated.

12. Common Mistakes to Avoid

• Exposing raw tables and expecting prompts to infer every relationship.
• Treating agent instructions as security enforcement.
• Assuming valid SQL or DAX necessarily answers the right business question.
• Allowing multiple competing definitions of the same KPI.
• Ignoring Fabric capacity and query execution costs.
• Moving from prototype to production without monitoring and ownership.

Final Thoughts

Fabric Data Agents make enterprise analytics more accessible, but conversational fluency isn’t the measure of success. The answer must be correct, the user must be authorized to see it, and the platform must deliver it reliably at scale.

The LLM makes enterprise data conversational. The architecture makes it trustworthy.

Further Reading

• Microsoft Fabric Data Agent concepts
• Create a Fabric Data Agent
• Configure Fabric Data Agents
• Fabric Data Agent sharing