Executive Dashboard Design: Right KPI, Single Source of Truth and Performance with PostgreSQL

Most dashboard projects end the same way: screens opened with excitement in the first week turn into charts nobody looks at a month later. The reason is usually not technical; the screen was designed according to what the data allows, not the executive's question. In this article, we explain how to do the opposite: first the question, then the metric, then the data model, and finally the visualization.

1. Start with the question

Every indicator must be tied to a decision question. A "revenue chart" is not an indicator, it is data. "In which branch are we behind target this month and why?" is a question, and its answer requires a breakdown of revenue, target, branch, and variance reason. As an example, for a logistics company executive, the questions might be: which region is the source of delivery delays, how is cost per vehicle changing, and which customers are exceeding their payment terms.

2. KPI selection: few but connected

Five to eight indicators are sufficient on an executive screen. For each indicator, four things are defined: definition (its formula), source (which table/system), refresh frequency, and owner. An indicator whose definition cannot be written down should not be brought to the screen; concepts like "efficiency" that everyone calculates differently produce nothing other than debate.

3. Single source of truth

The reliability of a dashboard depends on the same question giving the same answer everywhere. This means calculations are made in the data layer, not in the reporting tool. Views and materialized views in PostgreSQL are ideal for this job: the KPI formula is defined once, and all screens read the same view.

-- Example: monthly branch performance (simplified)
create materialized view mv_branch_monthly as
select
  b.id            as branch_id,
  date_trunc('month', s.sold_at) as month,
  sum(s.net_amount)              as revenue,
  max(t.target_amount)           as target,
  sum(s.net_amount) - max(t.target_amount) as variance
from sales s
join branches b on b.id = s.branch_id
left join targets t on t.branch_id = b.id and t.month = date_trunc('month', s.sold_at)
group by b.id, date_trunc('month', s.sold_at);

create unique index on mv_branch_monthly (branch_id, month);
-- Refresh (via scheduled task): refresh materialized view concurrently mv_branch_monthly;

4. Performance: do not scan raw data on every open

An executive screen should open within seconds. Materialized views, correct indexes, and period-based pre-calculated summaries ensure this. A small number of indicators requiring instant data (such as today's cash register) are fed by separate and lightweight queries. The desire for "everything to be live" often results in "nothing opening at all."

5. Permission-aware views

The same dashboard should be seen by the group manager for all companies, and by the branch manager only for their own branch. This filtering must be applied in the data layer (Row Level Security), not in the interface; otherwise, a user with direct access to the API could pull data from other branches.

A good dashboard is a screen where the executive can answer the "why" question by drilling down to the source with a single click. A screen that only displays charts is just a nice presentation.

6. Visualization principles

• Have a comparison point in every indicator: target, previous period, or average.
• Use colors for meaning (direction of variance), not for decoration.
• Use tables where charts cannot replace them, not where charts adorn tables.
• Clearly display the time zone and currency at the top of the screen.

7. Multi-source data: from Excel to API

Some of the sources may still be Excel. This is normal; the important thing is that the Excel import passes through validation rules and every upload is recorded. Over time, as API-fed sources increase, the Excel step decreases; but the approach of "let's make all sources automatic first, then build the dashboard" delays the project indefinitely.

8. Post-release: look at usage data

Measure which screens are opened and which filters are used. An indicator not looked at for three months is either the wrong question or in the wrong place; removing it is as valuable as adding one.

Data model: dimensions and measures

The data beneath the dashboard should be thought of as dimensions (branch, period, product group, customer) and measures (revenue, cost, quantity, days). Each measure must know in advance which dimensions it can be broken down by; the situation of "we can see revenue by customer but we cannot see cost" stems from cost not being associated with the customer in source systems and is solved at the source, not in the dashboard. Therefore, a dashboard project often starts with minor data model corrections in source systems.

Refresh frequency and "last updated" tag

Every indicator should visibly carry when the data was refreshed. An executive who knows it is "yesterday's data" makes the right decision; an executive who does not know says "the system is wrong." Refresh frequency varies according to need: cash balance hourly, monthly profitability at period close. Trying to make everything instant is both costly and unnecessary.

Alerts: don't wait to look at the dashboard

Executives do not open dashboards every day; significant deviations should go to them. Threshold-based alerts (receivable due date exceeded, budget variance exceeded a certain ratio, inventory dropped below minimum level) are delivered via email or message, and the alert carries a link to the relevant screen. Thresholds are defined and modified by the finance or operations team; embedding thresholds in code creates development costs upon the first change request.

-- Example: alert rules table (conceptual)
create table alert_rules (
  id          serial primary key,
  metric_key  text not null,        -- e.g. 'receivables_overdue'
  comparator  text not null,        -- '>', '<'
  threshold   numeric not null,
  scope       jsonb not null default '{}',   -- branch, company filter
  recipients  text[] not null,
  is_active   boolean not null default true
);

Mobile view

Executives mostly open dashboards from their phones. A screen designed for desktop and shrunk down is unusable on a phone. Priority order for mobile view: summary cards first (five to eight indicators), then a single chart, and tables last. Filters should be few and large; the trio of "all companies / select branch / select period" meets most needs.

Data quality indicators

The dashboard itself must be monitored: missing loads (this month's data from a branch hasn't arrived), rows rejected from validation, new codes awaiting mapping. This "data health" panel appeals to the data owner, not the executive, and explains why numbers in the main dashboard might be incomplete.

Example: logistics manager view (fictional)

• Number of shipments planned and completed today; delayed ones on a separate card.
• List of delayed shipments by region; clicking opens the shipment card.
• Weekly cost trend per vehicle; distribution key written on screen.
• Overdue receivables; by customer, sorted by amount.
• Data health: vehicles from which tracker data is not arriving.

This example is for illustrative purposes only; actual indicators are derived from the executive's questions during the discovery phase.

Cy Logic's data analytics and reporting service follows these principles: consolidating scattered sources into a single source of truth and building fast, queryable screens with permission-aware viewing.