PostgreSQL aggregations beyond OData

OData is a feed, not a full analytics language

OData supports standardized query options for selection, filtering, sorting, pagination, and more. The options a service actually offers differ per implementation. For Power BI that is a useful, stable source layer.

Business analysis sometimes requires calculations that are better done in the database. DigiData AI may use an approved internal query path for this purpose. DigiData MCP provides a smaller, bounded subset for remote clients.

Groupings and conditional totals

A simple aggregation counts invoices or sums amounts per month. A conditional aggregation can distinguish within the same grouping, for example open and paid, billable and non-billable, or projects within and outside an agreed status.

The database processes the conditions before the result is sent to the model. This reduces the amount of outgoing data and prevents a language model from having to add individual rows.

Distinct counts and date windows

A regular row count is not the same as the number of unique customers, projects or employees. PostgreSQL supports distinct counts and date functions that allow grouping results by day, week, month, quarter, or year.

The chosen time zone, date column and definition remain important. An approved measure determines whether, for example, invoice date, booking date or payment date is leading.

Rankings and running totals

Window functions calculate values over related rows without losing any detail. This makes rankings, cumulative turnover and comparisons with a previous period possible. PostgreSQL documents, among other things, rank functions and the use of regular aggregations as a window function.

This kind of calculations requires stable sorting and clear partitions. A running total per administration is different from one total for the entire tenant. Therefore, the semantic layer should determine which dimensions are allowed and meaningful.

Percentiles and outliers

An average can be affected by outliers. For example, percentiles help to describe lead times or payment behavior without sending all records to the model. Here too, the database function is technical, the business definition determines which population and filters are correct.

Why not free SQL?

Powerful database features are no reason to give a model arbitrary SQL. DigiData validates sources, tables, columns, operations and limits server-side. Only approved query forms are constructed and executed. This way, tenant isolation and the connection allowlist remain leading.

OData, MCP and DigiData AI side by side

Use OData to model tables in Power BI or Excel. Use MCP for a limited external AI contract with read-only tools. Use DigiData AI when richer approved relationships and metrics are needed. The OData and MCP page lists the routes side by side; the article about PostgreSQL instead of live API calls describes the data path.