Agent experience with data in Onelake using Fabric IQ

Business Scenario

A retail organization runs multiple promotional sales events across its store network in different cities, featuring various product categories.

Data is captured from:

• Third-party systems (products, stores, sales events)
• ERP system i.e. Finance and Operations (customer data)

Customers are linked to stores based on city in the ERP system, and each store hosts specific sales events.

However, while significant investments are made in marketing campaigns, inventory allocation, and event planning, the organization needs to identify

• Which stores generate the highest revenue during events
• Which locations are having majority of customer footprint

Solution:

Rather than creating custom reports on raw data, an agent can leverage the data in Onelake and use Fabric semantic models and Fabric IQ to provide conversational analytics. The agent can intelligently normalize user inputs and return accurate results even when values such as city names are entered with spelling errors or variations.

 

Advantage:

Because the data comes from two disconnected worlds (third-party systems for products/stores/events, ERP for customers), the "join" between them — customer → city → store → event → product — is business logic, not a database key. Fabric Ontology captures exactly that: entity types, relationships, rules and source mappings, so agents don't have to rediscover it from raw tables each time.

 

A. Prerequisite:

Data Sources & Relationships Overview

• Product, store, and sales event data are sourced from third-party systems.
• Customer data is sourced from the ERP (Dynamics 365 Finance & Operations) system through Fabric lakehouse.

How the data is connected:

• Customers are linked to stores based on city alignment (customer city = store location).
• Sales events are associated with:
• The products being sold, and
• The stores where the events are conducted.

 

Licenses and access requirement:

             

 

B. Step by step configuration: 

 

1. Push the data to OneLake : Connect D365 F&O customer data to Fabric Lakehouse through PowerPlatform

 

 

Ref: Link your Dataverse environment to Microsoft Fabric and unlock deep insights - Power Apps | Microsoft Learn

2. A Lake house will be created in Fabric with the data

 

3. Ingest the Data for Store , Sales event and Product from third party system to Fabric using any of the methods as outlined in the documentation below (as relevant)

Ref: https://learn.microsoft.com/en-us/fabric/data-engineering/load-data-lakehouse

 

4. Create a semantic model and relationship between them

 

 

Ref: https://learn.microsoft.com/en-us/fabric/data-engineering/tutorial-lakehouse-build-report

 

5. Create Ontology using the semantic model

              

 

Ref: https://learn.microsoft.com/en-us/fabric/iq/ontology/concepts-generate

 Ref: Create an Ontology with Fabric IQ - Training | Microsoft Learn

 

Click on View entity type details > click on manage relationship > click on the relation

          

 

Configure the source and target entity names and connected fields. Repeat similar setup for others as relevant

         

 

6. Login to https://copilotstudio.preview.microsoft.com/ and Create Copilot studio agent with following instruction:

 

When processing a user query:

1. Determine whether the user input contains a city name, location name, region, state, or geographical reference.
2. If a location reference is detected:

o   Identify potential spelling mistakes, abbreviations, alternate spellings, phonetic variations, or non-standard user input using the  <<custom prompt>>.

o   Normalize the value to the most likely official city name used in the enterprise data model.

o   Examples:

• "Bombay" → "Mumbai"
• "NYC" → "New York"

3. Use only the normalized location value when querying Fabric IQ.
4. If confidence in the normalization is high, proceed automatically without asking the user for confirmation.
5. If multiple cities are equally likely matches, ask a clarifying question before querying Fabric IQ.
6. When invoking Fabric IQ MCP:

o   Replace the original user-entered city value with the normalized city value.

o   Use the normalized value consistently across all ontology searches and filters.

7. Never expose the internal normalization process unless the user explicitly asks how the result was determined.
8. Return the business result based on data retrieved from Fabric IQ, not based on assumptions.

Example: User: "Show sales for Bangaluru last quarter" Normalized City: "Bengaluru" Fabric IQ Query: "Show sales for Bengaluru last quarter"

 

7. Add Fabric IQ MCP tool

         

 

Click on Fabric IQ MCP and provide workspace ID and Ontology ID as retrieved from the Ontology URL in Fabric

To find the URL, follow these steps:

• Open your ontology item in Fabric.
• View the URL in the browser, in the format https://app.fabric.microsoft.com/groups/<workspace-ID>/ontologies/<ontology-item-ID>.
• Copy the values of <workspace-ID> and <ontology-item-ID> from the URL.

 

 

• Form the MCP server URL by entering the copied values into this string: https://api.fabric.microsoft.com/v1/mcp/dataPlane/workspaces/<workspace-ID>/items/<ontology-item-ID>/ontologyEndpoint.You use this MCP server URL in the next section
• Following figures explains the details once the process

         

       

 

     Ref: https://learn.microsoft.com/en-us/fabric/iq/ontology/how-to-create-agent-copilot-studio

 

8. Add a tool Prompt to ensure normalised search for any user input (e.g. City)

   

 

 

 

Put the following instruction in a custom prompt :

You are a location normalization expert.

 Your task is to identify the most likely city name from the user's input, even when:

- The city name contains spelling mistakes.

- The city name is partially entered.

- The city is entered using an old or alternate name.

- The city contains abbreviations or phonetic spellings.

 

Rules:

1. Determine the most likely official city name.
2. Correct spelling mistakes using geographic knowledge.
3. Expand abbreviations where appropriate.
4. Return only the normalized city name.
5. If confidence is below 80%, return "AMBIGUOUS".
6. Never invent a city when multiple equally likely matches exist.

 Examples:

 Input: Banglore

Output: Bengaluru 

Input: Mumbi

Output: Mumbai

Input: BNG

Output: Bengaluru 

Input: Londn

Output: London 

City input: {{CityName}}

Output format:

{

"normalizedCity": "Bengaluru",

"confidence": 0.95

}

 

9. Test the agent

• Open the Test pane using the Test button in the top right corner of the screen. Enter NL query
• Allow the MCP tool when prompted

 

Test case 1: What is the top product revenue across all stores?

 

Test case 2: Intelligent Location Normalization with Fabric IQ

 

When a user submits a query such as "Compare the customer footfall between city Blr and Hyd", the agent first applies an AI-powered normalization layer before querying enterprise data.

The normalization prompt analyses abbreviations, alternate spellings, phonetic variations, and non-standard location references, mapping them to their canonical business values. In this example:

Blr → Bengaluru (Bangalore)

Hyd → Hyderabad

The normalized city names are then passed to Fabric IQ for semantic retrieval against the ontology and underlying data sources. This approach improves query accuracy, reduces dependency on exact user input, and enables a more natural conversational experience while ensuring consistent reporting and analytics results.

Process Flow :

User Query

 → AI Prompt Normalization

 → Canonical City Resolution (Bengaluru, Hyderabad)

 → Fabric IQ Semantic Search

 → Data Retrieval & Comparison Results