Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

jenbeiser

Optimizing Semantic Models for Copilot: Best Practices and Why They Matter

jenbeiser_1-1760481381011.png


🔹1. Name Fact Tables Based on Actions or Events

Use names like SalesTransactions, CustomerInteractions, or InventoryMovements.

Why: Copilot interprets table names semantically. Action-oriented names help it understand the purpose of the data, improving the relevance of generated insights.


🔹2. Ensure All Fact Tables Have Relationships

Connect facts to dimensions using proper relationships.

Why: Relationships allow Copilot to traverse the model intelligently. Without them, it may misinterpret data or fail to generate meaningful summaries. I had a model with one, flat table, and Copilot could only return a card visual as a new report page.


🔹3. Set Default Categories for Location Data

Use the data category property to assign categories like City, State, or Country to geographic columns.

Why: Helps Copilot recognize location fields for mapping and geo-based analysis.


🔹4. Run Diagnostics on Import Models

Use tools to check for query folding.

Why: Query folding pushes transformations to the source system, improving performance and reducing memory usage. This does not directly affect Copilot, but you can obtain efficiencies on on refresh duration and CPU(s).

Technical insight: The developer of the Power BI connector builds logic into their connector to translate M functions into the language of the source system. Some functions "fold" and others do not. The source system compute will always perform better than the mashup engine of Power BI, which was originally built for Excel.


🔹5. Monitor Performance in Direct Query Models

Use Performance Monitor and DAX Studio to check for folding and bottlenecks.

Why: Ensures efficient query execution and helps identify areas for optimization, for example, identifying steps that do not fold or inefficient DAX measures. If Copilot cannot answer a question from the visuals, it will search the semantic model, and you need it to be timely.


🔹6. Use Measure Killer to Clean Up

Identify and remove unused columns and measures with Measure Killer.

Why: Reduces model complexity and size, making it easier and more efficient for Copilot to navigate and generate accurate results.


🔹7. Review Default Aggregations

Ensure fields are aggregated correctly by default (e.g., Average for price, not Sum). Some fields, like Year or CustomerNumber should be set to "Don't Summarize" by default.

jenbeiser_0-1760480159926.png

Why: Prevents misleading insights and ensures Copilot applies the correct logic.


🔹8. Validate Data Types

Check that columns are correctly typed (e.g., numeric, date, Boolean).

Why: Proper data types help Copilot interpret fields correctly and apply appropriate operations. It also has performance bennefits when going from text to numeric if numeric data is stored as text.


🔹9. Hide Key Columns

Hide primary, foreign, and irrelevant business keys from report view.

Why: These are useful for relationships but not for analysis. Hiding them de-clutters the model and guides Copilot toward meaningful fields. This is easily done through Prep for AI.


🔹10. Rename Abbreviated Columns

The naming convention, e.g. Customer_ID or Customer ID does not matter, but be sure replace CustID with CustomerID, Rev with Revenue, spell out acronyms, etc. 

Why: Full names improve clarity and help Copilot understand the semantic meaning of each field.


🔹11. Rename Columns to Be Descriptive

Use names like TotalSalesAmount instead of Amount.

Why: Descriptive names help Copilot generate more accurate narratives and visuals.


🔹12. Prep Data for AI Consumption

  • Cast numeric strings to numeric types
  • Convert 0/1 flags to Boolean
  • Format date fields properly (e.g., Year as a date formatted as YYYY)
  • De-normalize tables to include dimensions

Why: These steps ensure Copilot can apply time intelligence, Boolean logic, and numeric operations correctly. Flattened models also improve performance and simplify analysis.


Final Thoughts

Optimizing your semantic model both about performance and about enabling Copilot to deliver the best possible insights. These practices help bridge the gap between raw data and intelligent, AI-powered storytelling.

Comments

Thank you, Jen.

 

I appreciate the digestable instructions. 

This is awesome work @jenbeiser