Business glossary, KPI definitions & semantic layer for AI

Ask three departments for last month's "revenue" and you get three numbers: gross, net, with or without credit notes, by invoice date or by delivery date. A human colleague knows which one to use; a text-to-SQL assistant or an agent does not, and it will answer confidently with the wrong one. This preparation use case produces the missing deliverable: a short, owned glossary of terms and KPIs, and from it a semantic layer that tells the AI what each column, code and metric means. It is mostly clarification work, not AI work.

Problem & value

• Who: every company that wants natural-language questions on its numbers (DATA-001) or lets an assistant read ERP, CRM or DATEV exports.
• Today: definitions live in people's heads or hidden Excel formulas; columns like STAT or ZZ_KZ2 and status codes are known to two employees.
• AI value: a documented definition separates a plausible answer from a correct one, and gives new staff and auditors one version of the truth.
• Honest limit: no sourced benchmark is quoted; measure the effect with your own test questions (PREP-018).

Variants

• Glossary only: a table in Excel, SharePoint or Nextcloud, given to an assistant as context.
• KPI catalogue: formula, filters, time reference, unit, owner and exceptions per metric.
• Semantic layer: metrics and dimensions defined once in a BI data model, so queries use governed definitions.
• Public sector: statistical indicators and administrative codes. Machine tags: see DATA-016.

How it works

collect terms (reports, schemas, interviews)
  -> AI drafts definitions and column descriptions
  -> owner per term approves   [human-in-the-loop]
  -> publish glossary + KPI catalogue + semantic model (versioned)
  -> used by text-to-SQL, BI assistants, agents, RAG
  -> wrong answers feed back into definitions

An LLM reads schema, sample values and report formulas and proposes descriptions, synonyms and KPI formulas. The business meaning is always decided by the owner, never by the AI.

Data preparation

Which data: schemas and samples of source systems, formulas in reports and Excel files, existing documentation, and the knowledge of KPI users. Where: ERP and DATEV exports, CRM, Power BI or Power Pivot models, controlling workbooks, wikis.

Typical gaps: several definitions of one term, undocumented codes, cryptic column names, formulas hidden in cells, no named owner, no "valid since" date.

Preparation steps: 1. Pick 10-20 KPIs management really asks about; do not start with all fields. 2. Collect the current calculation per KPI (PREP-003, PREP-011). 3. Let an LLM draft column and code descriptions from a schema export (PREP-005); mark drafts "unconfirmed". 4. Hold one 60-90 minute workshop per department, resolve conflicts, name one owner. 5. Add everyday synonyms (PREP-015), then load the approved definitions into your BI model. 6. Test with 20-30 real questions with known answers (PREP-018).

Tools: Excel or SharePoint list, Microsoft 365 Copilot or Gemini for Workspace for drafting, Nextcloud or wiki for publishing, n8n or Power Automate for review reminders.

Effort: almost entirely data preparation. A first SMB version takes about 3-6 working days over 3-4 weeks, then 1-2 hours per month. See GUIDE-003.

Implementation paths

Which path for whom

• SMB - Path B: no product replaces the thinking; the work is documenting your own definitions and wiring them to your own systems. Use the tools you have (Excel, Power BI, a small cloud LLM API with an AVV) rather than a catalogue suite. Where you only have a glossary document, even plain Path A features inside M365 or Workspace are enough.
• Enterprise - Path B: the semantic layer is part of a shared data platform; cloud APIs for drafting plus a governed metrics model in the existing BI stack.
• Developer - Path C: open-source metric layers and local models draft and serve the definitions at no licence cost.
• Public - Path C: administrative codes and statistics are sensitive and procurement favours self-operated or sovereign German cloud solutions.

Path A - Buy (off-the-shelf)

• Power BI semantic models with Copilot data preparation, Looker (LookML), Tableau, Qlik; catalogues with glossaries (Atlan, Collibra, Alation) target larger firms (DATA-007). Power BI and Looker offer EU regions; Metabase is a Europe-hostable option.
• Choose A when: you already own one BI tool whose model can carry the definitions.

Path B - Build on cloud APIs (recommended)

• Drafting: a cloud LLM with EU region or EU provider (Mistral, Azure OpenAI in an EU region) reads schema and non-personal samples and drafts descriptions.
• Semantic layer: keep the approved model in the BI data model or a version-controlled file; the text-to-SQL assistant gets the relevant definitions as context.
• Orchestration: Power Automate or n8n for review reminders and publishing to SharePoint or Nextcloud.

Path C - Self-host (open source)

• dbt MetricFlow, Cube, Metabase; Wren AI or Vanna for text-to-SQL around an explicit semantic model. Ollama with a 7-14B model drafts descriptions on HW1; schemas are small.
• The Open Semantic Interchange (OSI) initiative targets a vendor-neutral format (specification v1 published in 2026); check maturity and tool support first.

Path D - Custom training

Not needed. Fine-tuning does not teach a company's definitions reliably; context from a maintained semantic layer is cheaper, auditable and updatable the day a definition changes.

State of the art (2026)

• Text-to-SQL on real company schemas is far less reliable than on academic benchmarks; the usual remedy is this layer of curated descriptions, metrics and example queries as context [1][2].
• Vendors converge on semantic models as the contract between data and AI [1][2][3][4].
• LLMs draft descriptions well from names and samples but cannot know internal rules (credit notes, fiscal calendar).
• Failure modes: unreviewed drafts, stale definitions after reorganisations, two "official" versions of one KPI, ambiguous synonyms.

Shared resources & combinations

• Direct consumer: DATA-001 (natural-language BI), DATA-006 (spreadsheet assistant), DATA-002 (report narratives).
• Neighbours: DATA-007 (catalogue tooling), PREP-015 (terminology and synonym lists), PREP-016 (gold examples), PREP-004 (stewardship roles), PREP-001 (readiness assessment).
• Same stack: one small LLM, one workflow engine (n8n or Power Automate) and one publishing space serve glossary, terminology and knowledge-base use cases alike (Baukasten effect).

Effort, cost & KPIs

• Path A: 1-2 weeks if the BI tool holds the model. Path B: 3-6 working days for 10-20 KPIs; LLM cost negligible. Path C: plus 1-2 days setup.
• Cost driver: workshop time of department leads.
• KPIs: share of test questions answered correctly, KPIs with named owner, columns with description, "numbers differ" tickets.

People & change

Involve early: management (decides the official definition), department leads and controlling as owners, data owners and IT. The DPO is needed only if sample rows contain personal data; check the AVV (Art. 28 GDPR) with the LLM provider (GUIDE-005, GUIDE-010).

Works council: no in the normal case, a glossary does not monitor staff. Inform it anyway (§90(1) no. 3 BetrVG); if KPIs measure individuals, such as sales per person, §87(1) no. 6 applies (GUIDE-004).

AI literacy: Art. 4 AI Act requires measures that support staff's AI literacy; show users how to report a wrong answer (GUIDE-006).

Pilot: frame it as ending the "which number is right" argument; start with one department, 10 KPIs and an AI champion from controlling (GUIDE-008, GUIDE-009). Humans in control: owners approve every definition; the assistant names the definition it used.

Risks, regulation & compliance

• EU AI Act: minimal risk, a glossary is documentation; re-assess the downstream assistant if it feeds HR or credit decisions (GUIDE-014).
• GDPR: schemas and definitions are normally non-personal; use masked or synthetic samples (PREP-022).
• Risks: confident wrong answers, outdated definitions, shadow definitions in new Excel files. Mitigation: owners, review dates, versioning, retest after each change (GUIDE-011).

Getting started

1. List the 10 figures management asks about most and note who calculates each one today.
2. Compare the calculations; write down every conflict (gross versus net, date logic).
3. Run one 90-minute workshop per department, decide, name an owner, record "valid since".
4. Let an LLM draft column and code descriptions from a schema export; confirm them in a short review.
5. Load the approved definitions into your BI model or assistant context and test with 20 real questions.

Sources

1. Microsoft - Prepare your data for AI in Power BI (Copilot) - https://learn.microsoft.com/power-bi/create-reports/copilot-prepare-data-ai
2. dbt Labs - About the dbt Semantic Layer - https://docs.getdbt.com/docs/use-dbt-semantic-layer/dbt-sl
3. Google Cloud - Looker semantic layer (LookML) - https://cloud.google.com/looker/docs/what-is-lookml
4. Open Semantic Interchange (OSI) - https://github.com/open-semantic-interchange/OSI
5. Wren AI - open-source text-to-SQL with semantic layer - https://github.com/Canner/WrenAI
6. Metabase - open-source BI - https://github.com/metabase/metabase
7. Regulation (EU) 2024/1689 (AI Act) - https://eur-lex.europa.eu/eli/reg/2024/1689/oj