# How to Define Metrics with Aggregate Expressions in the Ossie Specification

> Learn how to define metrics using aggregate expressions like SUM and COUNT in the Apache Ossie specification. Master quantitative business calculations across datasets.

- Repository: [The Apache Software Foundation/ossie](https://github.com/apache/ossie)
- Tags: tutorial
- Published: 2026-07-26

---

**In Apache Ossie, metrics are top-level objects within a semantic model that use SQL aggregate expressions—such as `SUM`, `COUNT`, or `AVG`—to define quantitative business calculations across one or more datasets.**

The Apache Ossie specification provides a framework for building semantic models that standardize business metrics across data platforms. When you define metrics with aggregate expressions in the Ossie specification, you create portable, dialect-aware calculations that reference fields from any dataset in your model. These definitions reside in YAML configuration files governed by the Core Metadata Specification and the Expression Language Specification.

## Metric Schema Structure

According to the [`core-spec/spec.md`](https://github.com/apache/ossie/blob/main/core-spec/spec.md) file, each metric follows the schema defined in the **Metrics** table. A metric requires a unique `name` and an `expression` object containing one or more SQL dialects, with `ANSI_SQL` serving as the default. You can optionally include a human-readable `description`, logical `datatype`, `ai_context` metadata for natural language interfaces, and vendor-specific `custom_extensions`.

## Supported Aggregate Functions

The [`core-spec/expression_language.md`](https://github.com/apache/ossie/blob/main/core-spec/expression_language.md) file specifies the portable SQL subset that Ossie implementations must support. Required aggregation functions include `SUM`, `COUNT`, `AVG`, `MEDIAN`, and `APPROX_COUNT_DISTINCT`. Expressions can be single aggregates or compositions of aggregates, such as ratios that divide one aggregation by another (for example, `SUM(sales) / COUNT(orders)`).

## Defining Aggregate Metrics in YAML

To create a metric with an aggregate expression, follow these steps:

1. Create a metric entry under the `metrics` array of the semantic model.
2. Assign a unique `name` to the metric.
3. Add an `expression` object containing a `dialects` list.
4. Write the aggregate expression in your chosen dialect (typically `ANSI_SQL`).
5. Optionally configure `description`, `datatype`, `ai_context`, and `custom_extensions`.

### Simple Aggregation on a Single Dataset

This example defines a total revenue metric using the `SUM` aggregate on a single dataset:

```yaml
metrics:
  - name: total_revenue
    description: Total revenue across all orders
    datatype: Decimal
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(orders.amount)
    ai_context:
      synonyms:
        - "total sales"
        - "revenue"

```

### Cross-Dataset Calculations

Metrics can reference fields from multiple datasets to create composite calculations. This example calculates average order value per customer by aggregating across `orders` and `customers` datasets:

```yaml
metrics:
  - name: avg_order_value
    description: Average revenue per distinct customer
    datatype: Decimal
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(orders.amount) / COUNT(DISTINCT customers.id)

```

### Multi-Dialect Expressions

When working with platform-specific syntax, supply multiple dialects. Ossie implementations select dialects in deterministic order: ANSI SQL first, followed by Snowflake, Databricks, and others:

```yaml
metrics:
  - name: monthly_sales
    description: Sales summed per calendar month
    datatype: Decimal
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(orders.amount) GROUP BY DATE_TRUNC('month', orders.order_date)
        - dialect: SNOWFLAKE
          expression: SUM(orders.amount) GROUP BY DATE_TRUNC('month', orders.order_date)

```

### Approximate Aggregations

For large-scale datasets, use approximate aggregation functions to improve query performance:

```yaml
metrics:
  - name: unique_customers_approx
    description: Approximate count of distinct customers
    datatype: Integer
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: APPROX_COUNT_DISTINCT(customers.id)

```

## Expression Language and Dialect Handling

The Expression Language specification in [`core-spec/expression_language.md`](https://github.com/apache/ossie/blob/main/core-spec/expression_language.md) mandates support for standard SQL aggregates while allowing dialect-specific optimizations. When multiple dialects are provided, implementations process them in a deterministic order, defaulting to `ANSI_SQL` when no specific platform dialect matches. This ensures that metrics remain portable across different query engines while preserving calculation semantics.

## Summary

- Metrics in Apache Ossie are defined in the [`core-spec/spec.md`](https://github.com/apache/ossie/blob/main/core-spec/spec.md) schema as top-level objects with unique names and SQL expressions.
- Aggregate expressions must use supported functions from the [`core-spec/expression_language.md`](https://github.com/apache/ossie/blob/main/core-spec/expression_language.md) specification, including `SUM`, `COUNT`, `AVG`, and `APPROX_COUNT_DISTINCT`.
- YAML configurations support multiple SQL dialects, with `ANSI_SQL` as the default and deterministic fallback ordering.
- Cross-dataset calculations allow metrics to reference fields from any dataset within the semantic model.

## Frequently Asked Questions

### What aggregate functions are supported in the Ossie Expression Language?

The Ossie Expression Language requires implementations to support standard SQL aggregates including `SUM`, `COUNT`, `AVG`, `MEDIAN`, and `APPROX_COUNT_DISTINCT`. You can also compose these into ratios or more complex expressions as defined in [`core-spec/expression_language.md`](https://github.com/apache/ossie/blob/main/core-spec/expression_language.md).

### Can a single metric reference fields from multiple datasets?

Yes. Metrics can reference fields from any dataset defined in the semantic model, enabling cross-dataset calculations such as dividing aggregated values from one dataset by distinct counts from another.

### How does Ossie handle multiple SQL dialects in a single metric expression?

When you provide multiple dialects, Ossie implementations select them in a deterministic order: ANSI SQL first, followed by platform-specific dialects like Snowflake or Databricks. If no specific dialect matches, the system defaults to the `ANSI_SQL` expression.

### What is the purpose of the `ai_context` field in metric definitions?

The `ai_context` field provides metadata such as synonyms and descriptions specifically designed for AI systems, helping natural language interfaces understand and correctly map user queries to the defined metric calculations.