A semantic model makes the business meaning of analytical data explicit. It describes entities, relationships, measures and the dimensions through which people ask questions. Its purpose is to keep a familiar term from acquiring a different calculation in every dashboard or report.
The dbt Semantic Layer documentation provides one public example of centrally defined metrics. The principles below are useful when discussing Covorg’s governed analysis and reporting capabilities. They do not imply a particular implementation or named product integration.
Start with entities and analytical grain
The grain states what one row represents: an order, an order line, a daily product balance or a single event. Measures are only meaningful when their grain is understood. An order amount repeated across several line records cannot be summed as though every repetition were a different order.
Define entity keys and relationship cardinality before allowing flexible joins. A customer can have many orders, and an order can have several shipments. Combining both without controlling the relationship can multiply records and inflate amounts while producing a technically valid query.
Give each metric a complete contract
A definition should include the eligible population, aggregation rule, time basis, currency convention and exclusions. Terms such as revenue or active customer often have several legitimate interpretations. Choose a specific name and definition for each instead of forcing different questions into one ambiguous measure.
| Contract element | What it clarifies | Example |
|---|---|---|
| Population | Which records qualify | Accepted orders excluding test activity |
| Aggregation | How values combine | Sum of eligible order value |
| Time basis | Which event assigns the period | Order acceptance time |
| Dimensions | Which breakdowns are valid | Channel and product category |
| Exceptional cases | How missing or undefined results appear | No eligible orders produces an undefined average |
Aggregate ratios from their components
An average of subgroup rates can give each subgroup equal weight even when their populations differ greatly. Where the intended overall measure is a ratio, aggregate the numerator and denominator first, then calculate the result. Keep a zero denominator distinct from a genuine zero outcome.
The example contract below defines average order value. It explains the metric structure rather than prescribing a vendor configuration. The eligible amount and count still need their own approved definitions, including currency treatment and how cancellations or corrections affect the population.
{
"metric": "average_order_value",
"grain": "one eligible order",
"numerator": "sum of eligible order value",
"denominator": "count of eligible orders",
"timeBasis": "order acceptance time",
"zeroDenominator": "undefined"
}Manage definitions as shared dependencies
A changed eligibility rule can affect reports, alerts, forecasts and historical comparisons. Give each material definition change a version, effective date and owner. Compare representative outputs before publication and identify dependent analytical assets through lineage.
The semantic model should also expose limits. Some dimensions are inappropriate for a measure, some historical periods lack required fields and some views contain provisional information. Making these conditions explicit helps both human analysts and generated summaries avoid apparently precise but misleading answers.
Metric definition and approval
- 01
Define
Agree the business question, grain and eligible population.
- 02
Model
Specify entities, relationships, calculation and exceptional cases.
- 03
Validate
Compare representative results with source evidence and business expectations.
- 04
Govern
Publish an owned definition and review the impact of later changes.
Design considerations
- Test joins that combine several relationships with many records on either side.
- Keep ratios, counts and additive amounts distinguishable in aggregation rules.
- Document the interpretation of missing information and undefined calculations.
Sources
The following sources explain the underlying technical patterns.
