Fabric - Copilot optimisation

[michaelgambling]

Hello,

I have recently been tasked with testing Microsoft Fabric and alongside it Copilot.

I have a mirrored MS Sql database connection that is being used to land my data into Fabric.

Regarding the database, it has quite a complext structure, it is a mixture of structured and unstructured data with alot of interlinking tables. Its been built on top of over the course of 20+ years, should have been corrected but never was. In terms of documentation, we do not have much of that either. Its a bit of an uphill battle. 

 My question is this, we are trying to make things as cheap as possible, when we tested copilot in an F4 capacity, it was somewhat effective when reading from test semantic models that we built, however it used alof of resources to find the answers and quite frankly it burned out the capacity after asking simply 20-30 questions.

What would people deem a good method to build a solid AI model for copilot to reference? Below are some ideas ive seen but im unsure what is best practice hence my post:

1. Alongside semantic models that are being used for Power BI reports, we have another set of Semantic models built specically for copilot to reference, these copilot datasets would be based off of SQL view that have been created on a per table basis. i.e. One table would have a view created for it that would act as a kind of 'golg' layer for copilot to reference.

2. Create custom copilot agent using Foundry, leverage Azure AI search to issue things like table and column descriptions etc.

3. I have observed the Fabric 'data agents' but they seem to be irrelevant for what i am trying to achieve.

How would you reccomend i tackle this? Im very unsure on how to proceed, i really do have very little working experience in the data engineering field as well as AI, as im sure you know by reading this post!

Any help would be appreciated, and more then happy to provide more info / context if needed?

Thanks!

[v-abhinavmu]

Hi @michaelgambling,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you.

[v-abhinavmu]

Hi @michaelgambling,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Murtaza_Ghafoor for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[Murtaza_Ghafoor]

@michaelgambling 

I would recommend not considering Copilot as a replacement for a well-designed semantic model. Copilot performs best when the underlying data model is clean, well-documented, and business-friendly annotation.
With reference to your environment (20+ years of legacy SQL schema, limited documentation, and cost-sensitive F4 capacity that is only recommended for light weight workloads), I would recommend :

Create a Gold layer rather than exposing mirrored SQL tables directly to Copilot.

Build business-oriented views and Fabric Warehouse/Lakehouse tables with meaningful names, simplified relationships, and only the columns users need.
This reduces both query complexity and capacity consumption.

Build dedicated semantic models for Copilot. Organize models by business domain (e.g., Sales, Finance, Operations). Add descriptions for tables, columns, measures, and relationships, as Copilot relies heavily on metadata to understand user questions.

Don't rely on mirrored operational databases directly. Mirroring is excellent for data ingestion only, but analytical models should sit on top of curated data, not the operational schema.

Regarding Fabric Data Agents, they're useful for conversational exploration of curated Fabric data, but they won't pay off for a poorly modeled or undocumented database. Focus on improving the data model first.

F4 Capacity Issue:

Regarding your F4 capacity issue, 20–30 Copilot questions draining the capacity isn't surprising. F4 is intended for evaluation and light workloads. Copilot queries often generate multiple DAX queries and consume significant CPU, especially against complex semantic models. Optimizing the model (star schema, fewer relationships, optimized measures, and good metadata) will have a much greater impact than simply increasing capacity.

If this helps, ✓ Mark as Kudos | Help Others

Proud to be a Super User

[michaelgambling]

Hi Murtaza,

Firstly - thank you so much for the detailed response. This is what i was kind of thinking in my head. I just have a question, as per your final paragraph, F4 capacity being non-production standard, would this potentially be an acceptable capacity if i were to treat it as a side, 'copilot only' capacity that is linked to the primary production capacity?

Essentially im fighting a battle in terms of coming up with a workable soloution but keeping it in a cost range that our clients would be willing to pay...

Thanks again for your response!

[Murtaza_Ghafoor]

@michaelgambling 
You need higher capacity if your semantic model/gold layer is not optimize that you will link with Co Pilot, first of all you need to optimize your gold layer and then test copilot questions,if your gold layer optimize and it will not drain your capcity resources then you are good with F4 capcaity.  

Now the question is how to monitor ur capacity useage this can be done via fabric capacity metrics app.
and to go for the higher capcaity will be based on propoer evaluation.

If this helps, ✓ Mark as Kudos | Help Others

Proud to be a Super User