Synthetic data model

Dataset purpose

The dataset is fictional retail pharmacy data. It is small enough for a laptop and rich enough for natural-language questions to fail before the model is improved.

The generator is deterministic. The seed is 20260101, and the date range is 2024-01-01 to 2025-12-31.

data/README.md

# Synthetic data

Everything in this folder is synthetic. It was produced by a seeded random generator and describes fictional stores, generic medication names, and anonymous fill events. There are no patients, prescribers, members, addresses, or health records, and the repository is not meant to hold PHI. Don't add real data here, even for a quick test.

## What's here

| Path | What it is |
|---|---|
| `generated/dim_date.csv` | One row per day, 2024-01-01 to 2025-12-31 (731 rows). |
| `generated/dim_store.csv` | 12 fictional stores in four regions. |
| `generated/dim_med.csv` | 60 generic medications in eight therapeutic categories. Codes like `M001` aren't real drug codes. |
| `generated/fact_rx_fill.csv` | About 41,500 fill events, new and refill. |
| `schema/tables.json` | Column names, types, and descriptions for every file. |

The column names are short, database-style codes on purpose (`rgn`, `ther_cat`, `proc_mins`, `fill_typ`). The baseline semantic model copies them as-is, and the labs turn them into business names with Copilot's help.

## The signals the questions depend on

The generator plants a few patterns so the five business questions have clear answers:

- **Seasonality and trend.** Respiratory fills rise in the winter months, and total volume grows slowly across the two years.
- **Category change.** From November to December 2025, Respiratory has the largest increase and Dermatology has one of the largest drops.
- **Slow store.** Sunset Mesa (`S008`) has the longest average processing time.
- **Refill outliers.** Riverbend (`S003`) and Granite Park (`S010`) have unusually high refill rates in 2025-Q4.
- **Previous period.** December 2025 has 2,052 fills against 1,761 in November 2025.

The reference answers live in `evaluation/expected/`, computed from these files with DuckDB.

## Regenerate or verify

The files are deterministic for a given seed. CI fails if they drift from the generator.

```powershell
# Check that the committed files match the generator
pwsh ./scripts/New-SyntheticData.ps1 -Check

# Rewrite them (only after changing tools/python/pharmacy_demo/datagen.py)
pwsh ./scripts/New-SyntheticData.ps1
pwsh ./scripts/Update-ExpectedResults.ps1
```

On macOS or Linux you can call the Python tool directly:

```bash
.venv/bin/python -m pharmacy_demo data --check
```

Don't edit the CSV files by hand. The Copilot hooks in this repo deny agent edits under `data/generated/` for the same reason.

## How Power BI finds the files

The semantic model reads the CSVs through a Power Query parameter, `DataFolderPath`, which defaults to `C:\ContosoPharmacyDemo\data\`. That fixed path avoids spaces and user names in the model. `scripts/Set-LocalDataLink.ps1` creates it as a directory junction that points at `data/generated/` in your clone (Windows only; no admin rights needed). Remove it with `-Remove`.

View data/README.md on GitHub

Tables and grain

Table	Business name	Grain	Rows
dim_date	Date	One row per calendar date.	731
dim_store	Store	One row per fictional pharmacy store.	12
dim_med	Medication	One row per generic medication.	60
fact_rx_fill	Prescription Fill	One row per prescription fill event.	About 41,500

data/schema/tables.json (lines 1-120)

{
  "$comment": "Generated by pharmacy_demo.datagen. Synthetic data only; no person-level data.",
  "seed": 20260101,
  "startDate": "2024-01-01",
  "endDate": "2025-12-31",
  "tables": {
    "dim_date": {
      "file": "generated/dim_date.csv",
      "columns": [
        {
          "name": "cal_dt",
          "type": "date",
          "description": "Calendar date, one row per day from 2024-01-01 to 2025-12-31."
        },
        {
          "name": "yr",
          "type": "int",
          "description": "Calendar year."
        },
        {
          "name": "qtr",
          "type": "string",
          "description": "Calendar quarter label, Q1 to Q4."
        },
        {
          "name": "yr_qtr",
          "type": "string",
          "description": "Year and quarter, for example 2025-Q4."
        },
        {
          "name": "mth_num",
          "type": "int",
          "description": "Month number, 1 to 12."
        },
        {
          "name": "mth_nm",
          "type": "string",
          "description": "Month name, January to December."
        },
        {
          "name": "yr_mth",
          "type": "string",
          "description": "Year and month, for example 2025-12."
        },
        {
          "name": "yr_mth_num",
          "type": "int",
          "description": "Year and month as a sortable number, for example 202512."
        },
        {
          "name": "dow_num",
          "type": "int",
          "description": "Day of week number, 1 is Monday and 7 is Sunday."
        },
        {
          "name": "dow_nm",
          "type": "string",
          "description": "Day of week name."
        },
        {
          "name": "is_wkend",
          "type": "string",
          "description": "Y when the date is a Saturday or Sunday, otherwise N."
        }
      ]
    },
    "dim_store": {
      "file": "generated/dim_store.csv",
      "columns": [
        {
          "name": "store_key",
          "type": "int",
          "description": "Surrogate key for the store."
        },
        {
          "name": "store_cd",
          "type": "string",
          "description": "Store code, S001 to S012."
        },
        {
          "name": "store_nm",
          "type": "string",
          "description": "Store name."
        },
        {
          "name": "city",
          "type": "string",
          "description": "Fictional city where the store is located."
        },
        {
          "name": "rgn",
          "type": "string",
          "description": "Sales region: North, South, East, or West."
        },
        {
          "name": "store_typ",
          "type": "string",
          "description": "Store format: Standard, Drive-thru, or 24-hour."
        },
        {
          "name": "open_dt",
          "type": "date",
          "description": "Date the store opened."
        }
      ]
    },
    "dim_med": {
      "file": "generated/dim_med.csv",
      "columns": [
        {
          "name": "med_key",
          "type": "int",
          "description": "Surrogate key for the medication."
        },
        {
          "name": "med_cd",
          "type": "string",
          "description": "Medication code, M001 to M060. Not a real drug code."
        },
        {

View data/schema/tables.json on GitHub

Star schema

erDiagram
  DIM_DATE ||--o{ FACT_RX_FILL : date_key
  DIM_STORE ||--o{ FACT_RX_FILL : store_id
  DIM_MED ||--o{ FACT_RX_FILL : med_id

  DIM_DATE {
    string date_key PK
    date date
    int year
    int month
    string quarter
  }
  DIM_STORE {
    string store_id PK
    string store_name
    string region
    string state
    string store_tier
  }
  DIM_MED {
    string med_id PK
    string generic_name
    string therapeutic_class
    boolean maintenance_flag
  }
  FACT_RX_FILL {
    string fill_id PK
    string date_key FK
    string store_id FK
    string med_id FK
    int fill_count
    decimal gross_revenue
    decimal net_revenue
    decimal ingredient_cost
    decimal margin
    boolean refill_flag
  }

Retail pharmacy star schema

Column names with business meaning

The conventions skill includes the data dictionary learners use while improving names and descriptions.

.github/skills/pharmacy-model-conventions/references/data-dictionary.md

# Data dictionary

Standard business names for every source column. Rename the model object to the business name and keep `sourceColumn` set to the source name.

## Tables

| Source table | Business name | Grain |
|---|---|---|
| `dim_date` | Date | One row per calendar day, 2024-01-01 to 2025-12-31 |
| `dim_store` | Store | One row per store (12 stores, 4 regions) |
| `dim_med` | Medication | One row per medication product |
| `fact_rx_fill` | Prescription Fill | One row per prescription fill, new or refill |

## Date

| Source | Business name | Notes |
|---|---|---|
| `cal_dt` | Date | Key of the marked date table |
| `yr` | Year | Don't summarize |
| `qtr` | Quarter | Q1 to Q4 |
| `yr_qtr` | Year Quarter | For example 2025-Q4 |
| `mth_num` | Month Number | Hidden; sort helper for Month |
| `mth_nm` | Month | Sort by Month Number |
| `yr_mth` | Year Month | For example 2025-12; sort by Year Month Number |
| `yr_mth_num` | Year Month Number | Hidden; sort helper |
| `dow_num` | Day of Week Number | Hidden; sort helper for Day of Week |
| `dow_nm` | Day of Week | Sort by Day of Week Number |
| `is_wkend` | Is Weekend | True on Saturday and Sunday |

## Store

| Source | Business name | Notes |
|---|---|---|
| `store_key` | Store Key | Hidden surrogate key |
| `store_cd` | Store Code | Synthetic code such as S003 |
| `store_nm` | Store Name | Default label |
| `city` | City | Fictional city |
| `rgn` | Region | North, South, East, or West |
| `store_typ` | Store Type | |
| `open_dt` | Open Date | Not related to the Date table |

## Medication

| Source | Business name | Notes |
|---|---|---|
| `med_key` | Medication Key | Hidden surrogate key |
| `med_cd` | Medication Code | Synthetic code |
| `med_nm` | Medication Name | Default label |
| `ther_cat` | Therapeutic Category | Also called drug category or medication category |
| `dose_form` | Dosage Form | |
| `gnrc_flg` | Generic Flag | |

## Prescription Fill

| Source | Business name | Notes |
|---|---|---|
| `fill_id` | Fill ID | Hidden; never summarize |
| `fill_dt` | Fill Date | Relates to Date[Date] |
| `store_key` | Store Key | Hidden foreign key |
| `med_key` | Medication Key | Hidden foreign key |
| `fill_typ` | Fill Type Code | N or R. Hidden once the decoded Fill Type column exists |
| (calculated) | Fill Type | New or Refill, decoded from Fill Type Code |
| `refill_no` | Refill Number | 0 for a new prescription; don't summarize |
| `qty` | Quantity | Units dispensed; sums |
| `days_sply` | Days Supply | Sums |
| `payer_typ` | Payer Type | |
| `proc_mins` | Processing Time (min) | Averages; never sum |

View .github/skills/pharmacy-model-conventions/references/data-dictionary.md on GitHub

The baseline model starts with raw names such as fact_rx_fill, rx_count, and refill_flag. The AI-ready model uses names such as Prescription Fill, Prescription Count, and Refill Prescription Flag, plus descriptions and synonyms.

How the generator works

tools/python/pharmacy_demo/datagen.py creates dimensions first, then fills. It plants store mix, region effects, class seasonality, refill behavior, margin values, and month-specific patterns.

tools/python/pharmacy_demo/datagen.py (lines 1-80)

"""Deterministic synthetic data generator for the Contoso Pharmacy demo.

Every value is synthetic. There are no patients, prescribers, or people of any kind.

Only ``random.Random.random()`` is used, because the Mersenne Twister core is stable
across platforms and Python versions while helpers such as ``randint`` or ``gauss``
are not guaranteed to be. Output files are written with LF line endings and fixed
number formatting so they are byte-identical on Windows, macOS, and Linux.
"""

from __future__ import annotations

import hashlib
import json
import math
import random
from dataclasses import dataclass
from datetime import date, timedelta
from pathlib import Path

SEED = 20260101
START_DATE = date(2024, 1, 1)
END_DATE = date(2025, 12, 31)

REGIONS = ("North", "South", "East", "West")

# store_key, store_cd, store_nm, city, rgn, store_typ, open_dt, volume_weight, processing_factor
STORES = (
    (1, "S001", "Lakeview", "Lakeview", "North", "Standard", "2015-03-02", 1.10, 1.00),
    (2, "S002", "Pine Hollow", "Pine Hollow", "North", "Drive-thru", "2017-06-12", 0.90, 0.95),
    (3, "S003", "Riverbend", "Riverbend", "North", "24-hour", "2012-09-24", 1.25, 1.05),
    (4, "S004", "Stonebridge", "Stonebridge", "South", "Standard", "2016-01-18", 1.00, 0.90),
    (5, "S005", "Harbor Point", "Harbor Point", "South", "Drive-thru", "2019-04-08", 0.85, 1.10),
    (6, "S006", "Magnolia Square", "Magnolia Square", "South", "Standard", "2014-11-03", 1.05, 0.95),
    (7, "S007", "Oak Terrace", "Oak Terrace", "East", "24-hour", "2011-05-16", 1.30, 1.00),
    (8, "S008", "Sunset Mesa", "Sunset Mesa", "West", "Standard", "2018-08-27", 0.95, 1.70),
    (9, "S009", "Willow Creek", "Willow Creek", "East", "Standard", "2020-02-10", 0.80, 0.90),
    (10, "S010", "Granite Park", "Granite Park", "East", "Drive-thru", "2013-10-07", 1.00, 1.05),
    (11, "S011", "Bayside", "Bayside", "West", "24-hour", "2016-07-25", 1.15, 1.00),
    (12, "S012", "Fox Run", "Fox Run", "West", "Standard", "2021-03-15", 0.75, 0.95),
)

SLOW_STORE_CODE = "S008"
HIGH_REFILL_STORE_CODES = ("S003", "S010")
HIGH_REFILL_WINDOW = (date(2025, 10, 1), date(2025, 12, 31))

CATEGORIES = (
    "Cardiovascular",
    "Diabetes",
    "Respiratory",
    "Anti-infective",
    "Pain and Inflammation",
    "Mental Health",
    "Dermatology",
    "Gastrointestinal",
)

CATEGORY_SHARE = {
    "Cardiovascular": 0.22,
    "Diabetes": 0.14,
    "Respiratory": 0.12,
    "Anti-infective": 0.10,
    "Pain and Inflammation": 0.10,
    "Mental Health": 0.14,
    "Dermatology": 0.06,
    "Gastrointestinal": 0.12,
}

CATEGORY_REFILL_PROBABILITY = {
    "Cardiovascular": 0.74,
    "Diabetes": 0.74,
    "Respiratory": 0.45,
    "Anti-infective": 0.05,
    "Pain and Inflammation": 0.35,
    "Mental Health": 0.72,
    "Dermatology": 0.25,
    "Gastrointestinal": 0.66,
}

ACUTE_CATEGORIES = {"Anti-infective", "Pain and Inflammation", "Dermatology"}

View tools/python/pharmacy_demo/datagen.py on GitHub

The generator also writes data/schema/tables.json, so the data and schema stay aligned.

Planted patterns

The patterns make the five business questions test different model capabilities:

Pattern	Why it matters
Respiratory medications rise in winter.	Tests therapeutic class names and time intelligence.
Respiratory has the largest class increase in December 2025.	Tests class grouping and month comparison.
Dermatology drops in December 2025.	Makes the largest increase non-trivial.
Sunset Mesa is the slowest store.	Tests store naming and sorting.
Riverbend and Granite Park have high refill outliers in 2025-Q4.	Tests refill logic and quarter filters.
December 2025 has 2,052 fills and November 2025 has 1,761 fills.	Gives an exact month-over-month expected answer.

Regenerate and check

Windows

pwsh ./scripts/New-SyntheticData.ps1
pwsh ./scripts/Update-ExpectedResults.ps1
pwsh ./scripts/Invoke-Validation.ps1 -Only data,expected,questions

macOS

python -m pharmacy_demo data generate
python -m pharmacy_demo expected generate
python -m pharmacy_demo validate --only data expected questions

Use scripts from Scripts when you are following the lab. Use the Python module directly only when you are maintaining the tool package.

Baseline versus AI-ready

Area	Baseline	AI-ready target
Table names	Technical source names.	Business nouns.
Column names	Abbreviations and raw flags.	Business names with units and flags spelled out.
Descriptions	Sparse or missing.	Plain-English meaning and calculation notes.
Relationships	Some missing or inactive.	Explicit star-schema relationships.
Measures	Not enough reusable business measures.	Core measures and deterministic DAX tests.
AI metadata	Missing drafts.	Instructions, schema, synonyms, and verified answers prepared for Desktop.