What is a semantic layer, and do AI analysts need one?

An open-orders query might count order lines, include cancellations or use the date of the last update. Each version can run successfully. Only the business definition establishes which result the reader needs.

That definition belongs in a model the answering tools can use. Otherwise, every new report or question requires someone to work out the same rules again.

Connect business terms to calculations

Start with the words a reader uses for the measure. For “open orders,” record which statuses count, the reporting time and what the count will be used for. The description should help someone choose it before reading its SQL.

Translate that meaning into the model. Measures provide calculations, dimensions provide groupings, relationships connect records and filters select the records that qualify. Check that these parts express the description together.

The underlying records may already share a warehouse or lakehouse. Their location does not settle these choices.

Cube describes metrics, dimensions, join paths and access rules within the semantic layer. Interfaces that consume the maintained model can request its definitions when calculating an answer.

Keep the unit of counting intact

The model must preserve what each row and measure represents when tables are joined. Joining an order to its lines repeats the header for every line. Adding a header value afterwards can therefore multiply it.

Choose the calculation around the question. A distinct order count and a line count have different uses. The shared table does not make them interchangeable.

Ratios require attention at aggregation too. A regional ratio may need to be recalculated from its numerator and denominator. An average of already calculated departmental ratios can answer a different question because the underlying groups have different sizes.

Test the groupings people use in the report. Inspect the company total and its departmental breakdown, particularly where a record can relate to several groups. A correct total alone does not establish that every allocation is correct.

Supply AI with the intended meaning

An AI analyst needs the business definition as well as the schema. A familiar column name is insufficient to establish the population, dates and exclusions behind a measure. Make those rules available to the answering tool.

A question about “staff” illustrates the ambiguity. Employees, occupied positions and available people can each be sensible measures. Establish which one the question requires before calculating its result.

Keep the executed query with the answer. The reviewer can inspect whether the selected definition became the intended calculation, then follow any surprising figure into its records.

Evaluate the behavior with known questions and exceptions. Include difficult joins, period boundaries and accounts with restricted access. The presence of a correct definition in the model is useful only if the tool selects and applies it. These tests establish whether that happens in practice.

Review definitions before wider use

A proposed definition should be tested against records the business owner understands. A readable description is a useful start, but the review also needs the query and its results. Include cases where the intended treatment is easy to get wrong.

For open orders, examine a cancellation, a partially completed order and a record updated after the reporting cutoff. These cases reveal whether the filters and dates express the intended business rule.

Keep the accepted definition and the reason for later changes. A revised status rule may affect many reports at once. Compare the affected results and approve the change before asking recipients to rely on it.

Responsibility should be clear without making every change a new project. The business owner approves meaning; the implementation maintains the model and tests its consumers. That arrangement lets technical repairs and business decisions follow appropriate review paths.

Check which tools share the model

Trace a measure into the outputs people actually open. A dashboard may request the maintained model while a spreadsheet still uses a copied formula. A later correction to the model will reach those two consumers differently.

A universal semantic layer aims to share definitions across tools. Check its connections to the reports and AI interfaces you need, including permissions and the path a definition update takes.

Cube, the dbt Semantic Layer and AtScale are products in this category. The dbt documentation describes metrics over existing models and queries through MetricFlow. The consumers you need to serve should guide that choice.

Try changing a definition in a test model. Follow it through an important report, its cache and an account with restricted access. Inspect the returned result to establish what the consumer used.

Implement the measure through to use

The model needs prepared source records and an output people can use in their work. Start with a repeated question and follow its answer from source to screen. This exposes missing history, unclear relationships and interpretation that currently happens by hand.

Databasin combines source connections and semantic models with reports, AI questions, rollout and support. The work on definitions continues into the outputs people use and the support those outputs need.

An existing report may already contain a sound definition. Preserve that knowledge and make it reusable where appropriate. The first implementation should demonstrate that users can obtain the intended answer and understand a difference when one appears.

Example: counting orders across a join

At 08:00, this illustrative dataset contains two orders and three lines. The report needs the count of open orders.

The answer depends on both grain and inclusion. A report and an AI question should use those same rules when they claim to show open orders.

Before sharing a business measure

1. Choose the measureTake a question people ask repeatedly. Write its meaning, reporting date and intended use beside a result the business owner can check.
2. Check the counting unitFollow a few records through the joins. Inspect totals and groupings to see whether duplication or allocation changes the answer.
3. Test the consumersAsk for the measure through each required report and AI tool. Try a restricted account and a change to the definition.
4. Assign definition reviewName who approves the meaning and who maintains the model. Keep previous definitions and test cases so changes can be explained.

Sources