Evaluation methodology
The evaluation story
Section titled “The evaluation story”The lab measures whether Copilot can answer five business questions from the semantic model before and after model improvements. The expected answers come from a DuckDB oracle over CSV data, not from the Power BI model.
The presenter-facing before and after results are linked from Expected results.
Questions file
Section titled “Questions file”evaluation/questions.yaml defines IDs, prompts, expected response shape, tolerances, and grading hints.
# Representative business questions for the Contoso Pharmacy semantic model.## Synthetic data only. The expected answers in evaluation/expected/ are produced by the# DuckDB oracle (pwsh ./scripts/Update-ExpectedResults.ps1) from reference_sql, which runs# directly over the CSVs and never touches the semantic model.## Agents answering these questions (the question-tester agent) must use the semantic model# only. They see what `python -m pharmacy_demo questions` prints: id, question, paraphrases,# and answer_columns. default_interpretation is hidden on purpose, so the model's AI# instructions (Lab 09) have to supply definitions like "latest month" and "previous period".# Agents must not read this file, reference_sql, reference_dax, or evaluation/expected/.## answer_columns is the output contract: a run file reports each answer as rows keyed by# these names so the grader can compare them. It says nothing about how to get the answer.
version: 1data_window: { start: "2024-01-01", end: "2025-12-31", latest_month: "2025-12", latest_quarter: "2025-Q4" }
questions: - id: Q01 question: How many prescriptions were filled by region and month? paraphrases: - Show monthly prescription fill counts for each region. - What is the fill volume per region per month? - Break down prescriptions filled by region and calendar month. - How many scripts did each region fill every month? default_interpretation: >- Count every fill, new prescriptions and refills together, by store region and calendar month for all 24 months in the data (January 2024 to December 2025). answer_columns: [region, year_month, prescriptions_filled] grading: type: table key_columns: [region, year_month] partial: { type: row_fraction, min: 0.5 } tolerance: { prescriptions_filled: 0 } reference_sql: | SELECT s.rgn AS region, strftime(f.fill_dt, '%Y-%m') AS year_month, count(*) AS prescriptions_filled FROM fact_rx_fill f JOIN dim_store s ON s.store_key = f.store_key GROUP BY 1, 2 ORDER BY 1, 2 reference_dax: | EVALUATE SUMMARIZECOLUMNS( 'Store'[Region], 'Date'[Year Month], "Prescriptions Filled", [Prescriptions Filled] ) ORDER BY 'Store'[Region], 'Date'[Year Month]
- id: Q02 question: Which medication categories experienced the largest volume changes? paraphrases: - Which therapeutic categories moved the most in the latest month? - Where did prescription volume change the most by drug category? - Rank medication categories by how much their fill volume changed. - Which categories grew or shrank the most last month? default_interpretation: >- Compare fills in the latest complete month in the data (December 2025) with the previous month (November 2025) for each therapeutic category. Rank categories by the absolute change in fills, largest first. Report both months, the change, and the percent change as a fraction (0.25 means 25 percent). answer_columns: [category, prior_month_fills, latest_month_fills, change, change_pct] grading: type: ranking key_columns: [category] rank_by: { column: change, absolute: true, descending: true } partial: { type: top_key } tolerance: { prior_month_fills: 0, latest_month_fills: 0, change: 0, change_pct: 0.001 } reference_sql: | WITH monthly AS ( SELECT m.ther_cat AS category, count(*) FILTER (WHERE strftime(f.fill_dt, '%Y-%m') = '2025-11') AS prior_month_fills, count(*) FILTER (WHERE strftime(f.fill_dt, '%Y-%m') = '2025-12') AS latest_month_fills FROM fact_rx_fill f JOIN dim_med m ON m.med_key = f.med_key GROUP BY 1 ) SELECT category, prior_month_fills, latest_month_fills, latest_month_fills - prior_month_fills AS change, (latest_month_fills - prior_month_fills) / prior_month_fills AS change_pct FROM monthly ORDER BY abs(latest_month_fills - prior_month_fills) DESC, category reference_dax: | EVALUATE SUMMARIZECOLUMNS( 'Medication'[Therapeutic Category], TREATAS({ "2025-12" }, 'Date'[Year Month]), "Prior Month Fills", [Prescriptions Filled PM], "Latest Month Fills", [Prescriptions Filled], "Change", [MoM Change], "Change %", [MoM %] )
- id: Q03 question: What was the average prescription processing time by store? paraphrases: - How long does each store take to process a prescription on average? - Show mean processing minutes per store. - Which stores are slowest at getting prescriptions ready? - Average time from intake to ready for pickup, by store. default_interpretation: >- Average processing minutes per fill for each store across the full data window (January 2024 to December 2025). Sort slowest first. answer_columns: [store, avg_processing_minutes] grading: type: ranking key_columns: [store] rank_by: { column: avg_processing_minutes, absolute: false, descending: true } partial: { type: top_key } tolerance: { avg_processing_minutes: 0.05 } reference_sql: | SELECT s.store_nm AS store, avg(f.proc_mins) AS avg_processing_minutes FROM fact_rx_fill f JOIN dim_store s ON s.store_key = f.store_keyThe five IDs are Q01 to Q05. They cover trend, store comparison, refill rate, class increase, and exact-value style checks.
Expected results
Section titled “Expected results”The oracle reads the generated CSV files with DuckDB and writes evaluation/expected/Q01.json to Q05.json.
"""DuckDB oracle: computes expected answers for evaluation/questions.yaml directly from the CSVs.
The oracle never reads the semantic model, so it is an independent check on both themodel's measures and the agent's answers."""
from __future__ import annotations
import jsonfrom pathlib import Path
import duckdbimport yaml
from . import datagen
COLUMN_TYPES = { "dim_date": { "cal_dt": "DATE", "yr": "INTEGER", "qtr": "VARCHAR", "yr_qtr": "VARCHAR", "mth_num": "INTEGER", "mth_nm": "VARCHAR", "yr_mth": "VARCHAR", "yr_mth_num": "INTEGER", "dow_num": "INTEGER", "dow_nm": "VARCHAR", "is_wkend": "VARCHAR", }, "dim_store": { "store_key": "INTEGER", "store_cd": "VARCHAR", "store_nm": "VARCHAR", "city": "VARCHAR", "rgn": "VARCHAR", "store_typ": "VARCHAR", "open_dt": "DATE", }, "dim_med": { "med_key": "INTEGER", "med_cd": "VARCHAR", "med_nm": "VARCHAR", "ther_cat": "VARCHAR", "dose_form": "VARCHAR", "gnrc_flg": "VARCHAR", }, "fact_rx_fill": { "fill_id": "INTEGER", "fill_dt": "DATE", "store_key": "INTEGER", "med_key": "INTEGER", "fill_typ": "VARCHAR", "refill_no": "INTEGER", "qty": "INTEGER", "days_sply": "INTEGER", "payer_typ": "VARCHAR", "proc_mins": "DOUBLE", },}
DECIMALS = 6
def load_questions(evaluation_dir: Path) -> dict: with (evaluation_dir / "questions.yaml").open(encoding="utf-8") as handle: return yaml.safe_load(handle)
def connect(generated_dir: Path) -> duckdb.DuckDBPyConnection: connection = duckdb.connect(database=":memory:") for table, columns in COLUMN_TYPES.items(): csv_path = (generated_dir / datagen.TABLE_FILES[table]).as_posix().replace("'", "''") column_spec = ", ".join(f"'{name}': '{kind}'" for name, kind in columns.items()) connection.execute( f"CREATE VIEW {table} AS SELECT * FROM read_csv('{csv_path}', header = true, " f"columns = {{{column_spec}}})" ) return connectionThis separation matters. If the model is wrong, the expected answers do not move with it.
Run schema
Section titled “Run schema”Every agent run must match evaluation/run.schema.json. A run has a label, model state, timestamp, and an answers object keyed by question ID.
{ "$schema": "https://json-schema.org/draft/2020-12/schema", "$id": "https://github.com/sabajamalian/fabric-powerbi-ai-devops-demo/evaluation/run.schema.json", "title": "Question run", "description": "One run of the question-tester agent (or a person) answering evaluation/questions.yaml against one model state.", "type": "object", "required": ["label", "model_state", "created", "answers"], "additionalProperties": false, "properties": { "label": { "type": "string", "pattern": "^[a-z0-9][a-z0-9-]*$" }, "model_state": { "enum": ["baseline", "lab07", "lab08", "ai-ready", "other"] }, "illustrative": { "type": "boolean", "description": "True for committed sample runs that illustrate typical results." }, "created": { "type": "string" }, "agent": { "type": "string" }, "surface": { "enum": ["vscode", "copilot-cli", "cloud-agent", "manual", "generated"] }, "model": { "type": "string", "description": "LLM used, if known." }, "notes": { "type": "string" }, "answers": { "type": "array", "items": { "type": "object", "required": ["question_id", "rows"], "additionalProperties": false, "properties": { "question_id": { "type": "string", "pattern": "^Q\\d{2}$" }, "answer_text": { "type": "string" }, "dax": { "type": "string" }, "assumptions": { "type": "array", "items": { "type": "string" } }, "rows": { "type": "array", "items": { "type": "object" } }, "grade_override": { "type": "object", "required": ["score", "reason"], "properties": { "score": { "enum": [0, 1, 2] }, "reason": { "type": "string", "minLength": 5 } } } } } }, "grade": { "type": "object", "description": "Written by Grade-QuestionRun.ps1. Don't edit by hand.", "required": ["total", "max", "by_question"], "properties": { "total": { "type": "integer" }, "max": { "type": "integer" }, "graded": { "type": "string" }, "by_question": { "type": "object" } } } }}Rubric
Section titled “Rubric”The rubric scores each question from 0 to 2:
| Score | Meaning |
|---|---|
| 0 | Wrong, missing, or contradicted by the data. |
| 1 | Partially right, for example right direction with wrong magnitude. |
| 2 | Correct answer with the required entities, numbers, and explanation. |
# Answer quality rubric
Each question in `questions.yaml` is scored 0, 1, or 2 by `scripts/Grade-QuestionRun.ps1`. A run's total is the sum across questions (10 points for the five questions).
| Score | Meaning | How the grader decides ||---|---|---|| 2 | Correct | Every expected row is present, keys match exactly (case and spacing ignored), every value is within the question's tolerance, and there are no extra rows. For `ranking` questions the order also matches. || 1 | Partially correct | The full check fails, but the question's `partial` rule passes (see below). || 0 | Wrong or missing | Neither check passes, or the answer is missing. |
## Partial credit rules
| Rule | Passes when ||---|---|| `row_fraction` | At least `min` of the expected rows are present with correct values. || `top_key` | The first row's key matches the expected first row (for example, the right slowest store). || `any_key` | At least one expected key is present and no more than one unexpected key is returned. || `column_match` | The named column matches the expected value (for example, the right December total with the wrong comparison). |
## Matching rules
- Column names are matched after lowercasing and replacing spaces and punctuation with `_`. The agent should still use the `answer_columns` names.- Percent and rate columns (`*_pct`, `*_rate`, `*_index`) accept either a fraction (0.25) or a percentage (25). The grader normalizes.- Numbers within tolerance count as equal. Tolerances live in `questions.yaml`.
## Human override
A reviewer can add `grade_override: { score, reason }` to an answer in a run file when the grader is too strict or too lenient, for example when the agent stated a reasonable alternative interpretation. The grader reports overrides separately so they're visible in the comparison table.
## What this rubric doesn't measure
It measures whether the answer matches the default interpretation in the question set. It doesn't score explanation quality, DAX style, or speed. Agent output varies between runs, so compare at least two runs per model state before drawing conclusions.The maximum run score is 10. The grader normalizes column names, keys, rates, percentages, and tolerances before scoring.
"""Grade question runs against the oracle's expected answers and compare runs.
Scoring follows evaluation/rubric.md: 2 correct, 1 partially correct, 0 wrong or missing."""
from __future__ import annotations
import jsonimport refrom dataclasses import dataclass, fieldfrom datetime import UTC, datetimefrom pathlib import Path
import jsonschema
from . import oracle
RATE_SUFFIXES = ("_pct", "_rate", "_index")
def normalize_name(name: str) -> str: return re.sub(r"[^a-z0-9]+", "_", str(name).strip().lower()).strip("_")
def normalize_key(value) -> str: return re.sub(r"\s+", " ", str(value).strip().lower())
def _is_rate(column: str) -> bool: return column.endswith(RATE_SUFFIXES) or column in ("refill_rate", "refill_rate_index")
def _to_number(value): if isinstance(value, bool): return None if isinstance(value, int | float): return float(value) if isinstance(value, str): text = value.strip().replace(",", "") percent = text.endswith("%") text = text.rstrip("%").strip() try: number = float(text) except ValueError: return None return number / 100.0 if percent else number return None
def values_match(column: str, expected, actual, tolerance: float) -> bool: expected_number = _to_number(expected) if expected_number is None or isinstance(expected, str): return normalize_key(expected) == normalize_key(actual) actual_number = _to_number(actual) if actual_number is None: return False slack = tolerance + 1e-9 if abs(actual_number - expected_number) <= slack: return True if _is_rate(column) and abs(actual_number / 100.0 - expected_number) <= slack: return True return False
def _normalize_rows(rows: list[dict]) -> list[dict]: return [{normalize_name(k): v for k, v in row.items()} for row in rows if isinstance(row, dict)]
def _key(row: dict, key_columns: list[str]) -> tuple: return tuple(normalize_key(row.get(column, "")) for column in key_columns)
@dataclassclass QuestionGrade: question_id: str score: int reason: str matched_rows: int = 0 expected_rows: int = 0 override: dict | None = None details: list[str] = field(default_factory=list)
def to_dict(self) -> dict: result = { "score": self.score, "reason": self.reason, "matched_rows": self.matched_rows, "expected_rows": self.expected_rows, } if self.override: result["override"] = self.override if self.details: result["details"] = self.details[:10] return result
def grade_answer(question: dict, expected: dict, answer: dict | None) -> QuestionGrade: qid = question["id"] expected_rows = expected["rows"] if answer is None: return QuestionGrade(qid, 0, "no answer", 0, len(expected_rows)) override = answer.get("grade_override") grading = question["grading"] key_columns = grading["key_columns"] tolerance = question.get("tolerance", {}) value_columns = [c for c in question["answer_columns"] if c not in key_columns] actual_rows = _normalize_rows(answer.get("rows", [])) details: list[str] = []
actual_by_key: dict[tuple, dict] = {} for row in actual_rows: actual_by_key.setdefault(_key(row, key_columns), row)
matched_keys: list[tuple] = [] for row in expected_rows: key = _key(row, key_columns) actual = actual_by_key.get(key) if actual is None: details.append(f"missing row {key}") continueBefore and after comparison
Section titled “Before and after comparison”Learners usually create at least two runs:
- Baseline model run before improvements.
- AI-ready model run after names, descriptions, relationships, measures, report bindings, DAX tests, and AI prep drafts.
The comparison is meaningful because the data and expected answers stay fixed.
Variance
Section titled “Variance”Natural-language agent answers can vary. The lab controls variance by:
- Keeping the dataset deterministic.
- Keeping questions fixed.
- Using JSON run files.
- Scoring against a deterministic oracle.
- Asking for exact supporting values where needed.
- Running deterministic DAX tests after model measures exist.
Deterministic DAX tests
Section titled “Deterministic DAX tests”Invoke-DaxQuestionTests.ps1 checks important DAX answers through Power BI Desktop after Lab 08. These tests do not replace the natural-language evaluation. They prove the semantic model can compute the expected values.
#Requires -Version 7.2<#.SYNOPSIS Runs the reference DAX for each business question against the model open in Power BI Desktop and checks the results against the expected answers (Windows only)..DESCRIPTION 1. Finds the local Analysis Services instance that Power BI Desktop starts for an open model. 2. Loads ADOMD.NET from .tools/adomd/. With -AllowDownload it downloads the pinned Microsoft.AnalysisServices.AdomdClient package from nuget.org first and checks its SHA-512. 3. Runs each question's reference DAX and grades the rows with the same grader the question runs use. 4. Writes out/dax-tests.json with PASS or FAIL per question. The DAX itself isn't printed.
The reference DAX uses the business names from Lab 08, so run this after Lab 08 with fabric/ContosoPharmacy.pbip open and refreshed..PARAMETER Port The Desktop Analysis Services port. Default: found from msmdsrv.port.txt (newest instance)..PARAMETER AllowDownload Download ADOMD.NET from nuget.org into .tools/adomd/ if it isn't there yet..PARAMETER OutFile Where to write results. Default: out/dax-tests.json..EXAMPLE pwsh ./scripts/Invoke-DaxQuestionTests.ps1 -AllowDownload#>[CmdletBinding(PositionalBinding = $false)]param( [int] $Port, [switch] $AllowDownload, [string] $OutFile)
$ErrorActionPreference = 'Stop'. (Join-Path $PSScriptRoot '_Common.ps1')
$AdomdVersion = '19.117.0'$AdomdSha512 = '85D568E72F5B09A36A0F88497801BAFB1D1E9DF5DA1190ABCF7636C264F78D6BA3978B28A78D2640B338AF0BDD6941A31447E7D33D7E0D9004BA64C96C7F3562'
if (-not $IsWindows) { Write-Host 'Skipped: the DAX tests need Power BI Desktop, which runs on Windows only.' -ForegroundColor Yellow exit 0}
$root = Get-RepoRoot$python = Get-VenvPython -Root $rootif (-not $python) { Write-Host 'Run scripts/Initialize-DevEnvironment.ps1 first.' -ForegroundColor Red exit 2}if (-not $OutFile) { $OutFile = Join-Path $root 'out' 'dax-tests.json' }$OutFile = [IO.Path]::GetFullPath($OutFile, (Get-Location).Path)
# --- ADOMD.NET ---------------------------------------------------------------------------------$adomdDir = Join-Path $root '.tools' 'adomd' $AdomdVersion$libDir = Join-Path $adomdDir 'lib' 'net8.0'$clientDll = Join-Path $libDir 'Microsoft.AnalysisServices.AdomdClient.dll'if (-not (Test-Path -LiteralPath $clientDll)) { if (-not $AllowDownload) { Write-Host "ADOMD.NET $AdomdVersion isn't in .tools/adomd/. Run again with -AllowDownload to fetch it from nuget.org." -ForegroundColor Yellow exit 2 } $package = Join-Path $root '.tools' "adomd-$AdomdVersion.nupkg" New-Item -ItemType Directory -Force -Path (Split-Path -Parent $package) | Out-Null $url = "https://www.nuget.org/api/v2/package/Microsoft.AnalysisServices.AdomdClient/$AdomdVersion" Write-Host "Downloading $url" Invoke-WebRequest -Uri $url -OutFile $package -UseBasicParsing $hash = (Get-FileHash -LiteralPath $package -Algorithm SHA512).Hash if ($hash -ne $AdomdSha512) { Remove-Item -LiteralPath $package throw "The downloaded package hash doesn't match the pinned SHA-512. Nothing was installed." } Expand-Archive -LiteralPath $package -DestinationPath $adomdDir -Force Remove-Item -LiteralPath $package}foreach ($name in 'Microsoft.AnalysisServices.Runtime.Core.dll', 'Microsoft.AnalysisServices.Runtime.Windows.dll', 'Microsoft.AnalysisServices.AdomdClient.dll') { Add-Type -LiteralPath (Join-Path $libDir $name)}
# --- Find the Desktop instance ------------------------------------------------------------------if (-not $Port) { $workspaceRoots = @(How answer leakage is prevented
Section titled “How answer leakage is prevented”The question-tester should not read the expected answers. The hook blocks reads of evaluation/expected/ and evaluation/questions.yaml for non-maintainer sessions, and it blocks maintainer-only generator files.
("/.pbi/localSettings.json", "Power BI Desktop local settings."), ( ".SemanticModel/Copilot/", "Power BI Desktop writes the Copilot folder. Draft in fabric/ai-prep/ and apply through Prep data for AI.", ),]MAINTAINER_ONLY = [ ("evaluation/expected/", "Expected answers are hidden from agents so question runs stay honest."), ( "evaluation/questions.yaml", "questions.yaml holds reference answers. Use: python -m pharmacy_demo questions.", ), ("tools/python/pharmacy_demo/modelgen/", "The checkpoint generator contains the lab answers."), ("tools/python/pharmacy_demo/samples.py", "Sample runs are generated from the answers."),