Skip to content

Lesson 3 of 8 · 9 min

Building the Trusted Semantic Layer

Natural language is flexible. Business data is precise.

The semantic layer is what connects the two. Think of it as a bilingual contract between business language and query language. It explains that “students who came back” means a specific retention measure, not whichever enrollment column happens to look close.

In this lesson, semantic layer means the curated context that tells the agent which business concepts exist, how they relate, how important metrics are calculated, and which query patterns are approved.

That context may live in a Power BI semantic model, in SQL or KQL source metadata and instructions, or across both.

Start with business questions, not tables

Take the question-to-source map from the previous lesson and group questions by concept.

For a Student Success Advisor, the concepts might be:

  • Student
  • Program
  • Enrollment
  • Course performance
  • Attendance
  • Risk
  • Advisor

Now ask whether each concept has:

  • A clear name
  • A stable identifier
  • A documented grain
  • Defined relationships
  • Trusted calculations
  • An owner

This creates a business-first schema rather than a database-first schema.

Prepare a Power BI semantic model

A Power BI semantic model is the strongest choice when users ask metric-oriented questions such as:

  • What is current retention by program?
  • How many high-risk students are assigned to each advisor?
  • How did course completion change from last term?

Prepare it carefully.

Measures

Use explicit measures for important business calculations. Avoid expecting generated DAX to reconstruct a complex definition from raw columns every time.

Examples:

  • [Active Students]
  • [Retention Rate]
  • [High Risk Students]
  • [Average Attendance Rate]

Descriptions

Describe what the measure means, its time window, and exclusions.

For example:

Percentage of students enrolled in the prior fall term who reenrolled in the current fall term. Excludes nondegree and visiting students.

AI data schema

Expose the tables, columns, and measures that are useful for AI questions. A smaller high-quality schema is easier to reason over than the full implementation model.

The AI data schema guides query generation. It is not a security boundary. Continue to enforce item permissions, RLS, CLS, and OLS separately.

AI instructions

Add model-specific guidance such as:

  • Use academic term rather than calendar year for enrollment comparisons.
  • Treat risk scores of 0.70 or higher as high risk.
  • Never infer protected demographic attributes.

Verified Answers

Use Verified Answers for important, repeated questions where trusted analytical context should influence future DAX generation.

A Verified Answer is not a frozen response that the agent repeats. Its trigger questions and the underlying visual’s measures, fields, and filters help guide a newly generated DAX query. You still need to evaluate the result.

Prepare SQL sources

For a Warehouse or Lakehouse SQL analytics endpoint:

  1. Select only the relevant schemas and tables.
  2. Add source instructions that define grain, joins, filters, and terminology.
  3. Add example question-and-SQL pairs for difficult patterns.

An effective example is not simply a basic SELECT. It teaches a pattern the generator might otherwise miss.

Example:

SELECT
    a.AdvisorName,
    COUNT(DISTINCT r.StudentId) AS HighRiskStudents
FROM RiskAssessment r
JOIN Student s
    ON r.StudentId = s.StudentId
JOIN Advisor a
    ON s.AdvisorId = a.AdvisorId
WHERE r.IsCurrent = 1
  AND r.RiskScore >= 0.70
GROUP BY a.AdvisorName;

The value of this example is the current-record filter, risk threshold, and join path.

Prepare KQL sources

For an Eventhouse KQL database, focus on event meaning and time.

Example questions might include:

  • Which students had three or more learning-platform inactivity events this week?
  • When did advising-system errors begin increasing?
  • Which campuses have the highest failed check-in rate in the last 24 hours?

Define:

  • The timestamp to use
  • The event identifier
  • The user or entity key
  • Time-zone assumptions
  • Event categories
  • Standard time windows

Example:

StudentActivity
| where EventTime >= ago(7d)
| where EventType == "LearningPlatformInactivity"
| summarize InactivityEvents = count() by StudentId
| where InactivityEvents >= 3
| order by InactivityEvents desc

Use examples carefully

Examples guide query generation, but examples can also conflict. Treat them like worked examples given to a new analyst: each one should teach a reusable method, not create a second definition of the metric.

Keep them:

  • Correct
  • Distinct
  • Representative
  • Focused on reusable query patterns
  • Aligned with current schema and definitions

Do not create many near-duplicate examples that teach competing filters.

Example-query validation is currently strongest for SQL. Treat unsupported validation paths as a reason for additional manual testing, not as evidence that the examples are safe.

Exercise: build your semantic packet

Create a short document with:

  1. Ten business concepts and definitions
  2. Five approved measures or calculations
  3. The table grain for every exposed table
  4. The approved join paths
  5. Five synonyms users commonly use
  6. Five source instructions
  7. Three example queries for difficult questions

Test the packet by giving it to someone who did not build the model. Ask them to identify the correct source, measure, join, and filter for two of your required questions. If they cannot, the agent is unlikely to infer it reliably either.

This semantic packet is more valuable than a long general instruction telling the agent to “be accurate.”

Carry this forward

Reliable query generation is not produced by one clever prompt. It is produced by a source that is explicit about meaning.

In the next lesson, you will create the Fabric Data Agent, attach this prepared context, and publish a controlled first version.

Microsoft references

Next: Creating the Fabric Data Agent →
← Preparing Agent-Ready Data All lessons