Design a metrics layer for company KPIs

Different revenue numbers come from different definitions, not from bad arithmetic. One dashboard counts gross orders, another subtracts refunds, a third uses booking date rather than payment date. The fix is to define each metric once, in one place, and make every dashboard read from it.

Diagnose before building

Pick revenue and take the three dashboards that disagree. Trace each back to its SQL. Write down the differences: which table, which filters (cancelled orders, test accounts), which date (order, payment, shipment), which currency conversion, whether tax is in. Show them to leadership. Usually the agreement meeting is the hard part, and the technology is easier.

Build one definition

metric: net_revenue
definition: sum(order_amount - refund_amount - tax)
filters: status in ('paid','shipped'), is_test = false
grain: order line; date: payment_date
owner: finance-analytics

Implement it in a semantic or metrics layer: dbt's Semantic Layer (MetricFlow), LookML in Looker, Cube, or a similar tool. Dashboards ask for net_revenue by month, and the layer generates the SQL. Nobody rewrites the logic in each report.

Certified gold tables

Under the metrics layer, build clean, tested gold tables (fct_orders, dim_customer) that everyone uses. Mark them certified in the catalog, and discourage reports built on raw tables.

Make it stick

• Every metric has a named owner, a written definition, and a change log. Changes go through a review, and the old and new values are shown side by side.
• Tests: reconcile the metric against a source of truth, such as the finance ledger, each month. Alert if totals drift apart by more than a small tolerance.
• Retire duplicate dashboards. Move them to the shared definitions or remove them, otherwise confusion returns.
• Document the metric in the BI tool where people see it, with a link to the definition.

What to say about trade-offs

A semantic layer adds a dependency and a learning curve, and not every BI tool integrates well. A cheaper start is a small set of certified gold tables and a metrics glossary, with the semantic layer coming next. The principle stays: one definition, one owner, tested.