A hands-on build log: Microsoft Fabric, a semantic model, a 3-page Power BI report, a Fabric Data Agent, and a Copilot Studio agent wired together through the new Fabric IQ MCP tool.
Introduction
Organizations often invest heavily in dashboards, reports, and analytics platforms. However, most business users still struggle to access insights because they must navigate multiple reports, understand data models, and know where information is stored.
The emergence of AI-powered experiences changes this model entirely.
Rather than asking users to navigate data, we can enable data to participate in conversations.
In this article, we build an end-to-end solution using:
- Microsoft Fabric
- OneLake
- Warehouse
- Semantic Models
- Power BI
- Fabric Data Agents
- Copilot Studio
The objective is to create an architecture where business users can ask questions in natural language and receive responses grounded in enterprise data.
Why semantic models matter
An agent can only be as precise as its understanding of the business terms behind a question. Ask “what’s our risk indicator by department” and the agent needs a trustworthy concept of Department and Risk Indicator, not just raw tables and column names it has to infer meaning from. That’s the job a semantic model does: it sits between the agent and the warehouse, and turns schema into business language.
Without a semantic model:
Agent → Raw Tables
With one:
Agent → Business Concepts (Department, Revenue, Customer Satisfaction, Performance Score)
A semantic model gives the agent business-friendly terminology, relationships, pre-defined measures, row-level security, and business context, all in one place, reused by every downstream consumer: reports, data agents, and Copilot. Build it once, and Power BI, the Data Agent, and Copilot Studio agent all reason over the same trusted definitions.
Prerequisites
- A Microsoft Fabric workspace with an F2-or-above capacity
- Power BI semantic model support enabled on that workspace
- A Copilot Studio environment, using the new agent experience, with MCP tools allowed
- The same Microsoft Entra ID identity with access to both the Fabric workspace and the Copilot Studio environment
- Microsoft 365 Copilot license, only if you’re doing Step 12
Step 1: Ingest data into Microsoft Fabric
Source data can come from Azure SQL, Dataverse, Salesforce, ServiceNow, SAP, CSVs, Excel, REST APIs, SharePoint, or OneDrive — whatever your organization already runs on.
In your workspace: click New Item → Data Pipeline. Use a Copy Activity to move source data into the Lakehouse. This is the one part of the build worth treating as infrastructure, not a one-off: a repeatable pipeline is what lets Bronze refresh on a schedule instead of by hand.
External Source → Fabric Lakehouse
Step 2: Build the Lakehouse on Medallion architecture
Create a Lakehouse (e.g. `EmployeeExperienceLakehouse`) and lay it out in three tiers:
- Bronze — raw, untouched: `bronze_employee`, `bronze_sales`, `bronze_engagement`
- Silver — cleansed and standardized: `silver_employee`, `silver_sales`
- Gold — business-ready: `gold_employee_performance`, `gold_department_health`, `gold_sales_summary`
Gold is the only layer anything downstream should ever query. If a report or an agent is reaching into Bronze, that’s a modeling gap, not a shortcut.
Step 3: Create a Warehouse for business consumption
Create a Warehouse (e.g. `EmployeeExperienceWarehouse`) and use a Data Pipeline or Copy Job — mine was `PL_EmployeeExperience_Refresh` — to move curated Gold datasets into it.
Gold Layer → Warehouse
This split matters more than it looks: it separates the engineering surface (Lakehouse, pipelines, notebooks) from the consumption surface (Warehouse) that Power BI, the semantic model, and the agents actually query. Change the engineering layer without breaking the contract downstream.
Step 4: Design a star schema
Inside the Warehouse, build dimension tables — `dim_employee`, `dim_date`, `dim_department`, `dim_country` — and a fact table, `fact_performance`. Connect them with 1:N relationships:
dim_department → fact_performance
This star schema is the structure every downstream layer consumes — Power BI, the semantic model, and eventually the agents. Get the grain of `fact_performance` right here and everything above it stays simple.
Step 5: Build the semantic model
Create a new Semantic Model on top of the warehouse, storage mode as Direct Lake on SQL, and bring in your fact and dimension tables only.
The one rule that actually matters here: expose business language, not schema. `Department`, `Country`, and `Performance Score` — not `tbl_001`, `emp_key`, `score_value`. Every AI experience downstream inherits whatever naming you choose here. Name it for the business user, not for yourself.
Step 6: Create business measures — with descriptions
Average Performance Score = AVERAGE(fact_performance[PerformanceScore])
Average Engagement = AVERAGE(fact_performance[EngagementScore])
Risk Indicator = AVERAGEX(fact_performance, fact_performance[OvertimeHours] * 2)
Write a plain-language description for every measure — for example: “Risk Indicator measures potential burnout risk based on overtime patterns.” These descriptions are exactly what the Fabric Data Agent and Copilot Studio read to decide when and how to use a measure. A measure with no description is invisible to the agent, even though it’s fully visible to Power BI.
Step 7: Build the Power BI report
I built a 3-page report directly on the semantic model, each page doing a different job:
Executive Overview — the top-line KPIs: Performance Score, Engagement, burnout risk Indicator, at a glance.
Wellbeing Analysis — department trends and country-level analysis, for the people who own the numbers.
Department & Country Health — a Department × Country matrix, so an anomaly in one country’s department doesn’t get averaged away in a global number.
Power BI here is the visual consumer of the same semantic model the agent will ground on — same measures, same definitions, no drift between what a human sees in a report and what the agent says in a conversation.
Step 8: Create a Fabric Data Agent
Go to the Fabric workspace, Click New Item → Data Agent and name it. Select the semantic model as the grounding source, and scope it deliberately — pick the specific tables and measures it should have access to (`fact_performance`, `dim_department`, `dim_country`), not the whole model by default. Scope is a governance decision, not a checkbox.
Step 9: Ground the agent
Give it instructions that constrain its behaviour, not just describe its purpose:
You are a business analytics assistant.
Use available enterprise analytics data to answer questions.
Explain results in business language.
Provide actionable recommendations.
Use data whenever possible.
Step 10: Publish the Data Agent
A published Data Agent is already useful on its own — anyone with access can query it directly, without Copilot Studio in the loop at all. Publish it with a clear, detailed description. That description is exactly what downstream Copilot experiences use to decide whether your agent is the right one to call — treat it as an interface contract, not a formality.
Step 11: Connect the Data Agent to Copilot Studio — via the Fabric IQ MCP tool
In Copilot Studio, open your agent (or create a new one via + Create blank agent).
Select Tools from the tabs next to the agent name, then + Add tool.
Search for Fabric IQ and select Fabric IQ MCP (Preview).
Under Connection, select Create new connection, keep the default authentication type — Login with Microsoft Entra ID — and connect using the identity that has access to both the Copilot Studio environment and the Fabric workspace.
Open the tool’s details and confirm the expected functions are listed — for a data-agent grounding scenario this is the entry point into your published Enterprise Analytics Agent, exposed as a callable MCP tool rather than a static knowledge source.
Open the Test pane, ask a natural-language question, and allow the MCP tool call when prompted.
Copilot Studio → Fabric IQ MCP tool → Data Agent → Semantic Model
Wiring the Data Agent in as an MCP tool rather than a knowledge source means the agent treats it as a callable capability the orchestrator decides to invoke — the same pattern it would use for any other MCP-exposed system. That’s a more composable integration point, and it’s the one Microsoft is actively investing in — Fabric Data Agents can now be consumed as MCP endpoints not just by Copilot Studio, but by Microsoft Foundry and VS Code Agent Mode too. Build the connection this way once, and you’re not locked into Copilot Studio as the only consumer.
Step 12: Enable access from Microsoft 365 Copilot
Once your Copilot Studio agent is published, it can be surfaced to Microsoft 365 Copilot as well. Users can now ask the same grounded questions from Teams, Microsoft 365 Copilot, or Business Chat, without ever opening a report.
Conclusion
The most important artifact in this build is the semantic model. Everything downstream — three report pages, a Fabric Data Agent, a Copilot Studio agent, and (optionally) Microsoft 365 Copilot — reasoned over the exact same measures and business definitions, because they were all grounded on one model instead of three separate reinterpretations of the warehouse.
The one build decision worth taking away: when you connect a Fabric Data Agent into Copilot Studio’s new agent experience, do it through the Fabric IQ MCP tool, not the older knowledge-source pattern. It’s the same governed grounding, but it composes — the same Data Agent becomes reachable from Foundry, VS Code, and any other MCP-aware client without rebuilding the connection each time.