Building an Executive KPI Dashboard for Your Custom ERP

Businesses leveraging real-time KPIs in their ERP environment have a competitive advantage through the ability to see, understand, and act on data in real time. For executive teams at Indonesian companies, the ERP dashboard is often the most visible deliverable of the entire ERP project — the one thing the CEO looks at every morning. Getting it right means choosing the right metrics (not every metric), designing for speed, and making it actionable rather than just informational. In my freelance ERP consulting work, I've built executive dashboards for manufacturing, trading, and services companies. This post covers what works.

Choosing the Right KPIs

The most common mistake in executive dashboards is showing too many metrics. An executive dashboard should have 8-12 KPIs maximum — the ones that reflect business health and enable decisions. For a trading company in Indonesia, the core KPIs are: Revenue (current month vs. target, vs. same month last year), Gross Margin %, AR Days Outstanding (DSO), AP Days Outstanding (DPO), Inventory Turnover, Cash Position (current bank balance by account), Open Orders (unshipped orders by value), and Overdue Invoices (by count and value). Each of these tells a story — together they give the CEO a complete business health picture in 30 seconds.

The Dashboard Data Model

For performance, executive dashboards should not query raw transaction tables directly. I pre-compute a daily_kpi_snapshot table that stores the key metric values as of each business day. A NestJS cron job runs at 1 AM WIB nightly and populates this table with yesterday's closing values. The dashboard then queries the snapshot table — a simple SELECT with no joins — returning in milliseconds. For real-time metrics (current cash position, today's orders), I allow direct queries but optimize them with materialized views refreshed every 15 minutes.

Executive KPI Dashboard Architecture

  Nightly cron (01:00 WIB)
  → Queries raw tables: journal_lines, ar_invoices, stock_movements, ...
  → Computes 12 KPIs for yesterday
  → Writes to daily_kpi_snapshot table
  → Invalidates Redis cache for dashboard

  Dashboard API endpoint: GET /dashboard/kpis
  → Reads daily_kpi_snapshot (fast: single row lookup)
  → Merges with real-time queries for cash/orders (15min cache)
  → Returns single JSON payload (not 12 separate calls)

  KPI Card structure (React + Recharts):
  ┌─────────────────────────────────────┐
  │  Revenue (Month-to-Date)            │
  │                                     │
  │  Rp 2.4B         ▲ +8% vs target   │
  │  Target: Rp 3B   ▼ -12% vs LY      │
  │                                     │
  │  [sparkline: last 12 months]        │
  │  ▁▂▃▃▄▄▅▆▆▇▇▇                       │
  └─────────────────────────────────────┘

  12 KPI Cards:
  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
  │ Revenue  │ │ GP Margin│ │ AR DSO   │ │ AP DPO   │
  │ 🟢 80%  │ │ 🟡 36%  │ │ 🔴 68d  │ │ 🟢 42d  │
  └──────────┘ └──────────┘ └──────────┘ └──────────┘
  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
  │ Inv Turn │ │ Cash Pos │ │Open Ord  │ │ Overdue  │
  │ 🟢 4.2x │ │ 🟢 2.1B │ │ 🟡 45   │ │ 🔴 23  │
  └──────────┘ └──────────┘ └──────────┘ └──────────┘

Dashboard Architecture and Performance

The dashboard API returns all 12 KPIs in a single endpoint call — not 12 separate API calls. I batch the KPI computation and return a single JSON payload. This reduces the number of round-trips and prevents the 'waterfall loading' effect where each card pops in at a different time. For the front end, I use React Query with a 15-minute staleTime so the dashboard doesn't refetch on every navigation — the executive isn't going to get new information in 15 minutes, and unnecessary queries put load on the database.

Drill-Down from KPI to Transactions

Every KPI card should be clickable and drill down to the underlying transactions. Click on 'Overdue Invoices: 23' and you get a list of 23 invoices with customer name, amount, days overdue, and the AR owner. Click on 'Inventory Turnover: 4.2x' and you see turnover by product category for the last 12 months. This drill-down capability is what separates a useful dashboard from a vanity screen. The drill-down views use standard paginated list endpoints — no special optimization needed since they're used on-demand, not on every page load.

// NestJS: KPI service with Redis caching
@Injectable()
export class KpiService {
  constructor(
    private financeService: FinanceService,
    private arService: ArService,
    private inventoryService: InventoryService,
    @InjectRedis() private redis: Redis,
  ) {}

  async getDashboardKpis(): Promise<DashboardKpis> {
    const cacheKey = 'dashboard:kpis:' + new Date().toISOString().slice(0, 13);
    const cached = await this.redis.get(cacheKey);
    if (cached) return JSON.parse(cached);

    // Fetch all KPIs in parallel
    const [revenue, margin, dso, dpo, cashPos, overdueInvoices] =
      await Promise.all([
        this.financeService.getRevenueKpi(),
        this.financeService.getGrossMarginKpi(),
        this.arService.getDsoKpi(),
        this.financeService.getDpoKpi(),
        this.financeService.getCashPositionKpi(),
        this.arService.getOverdueKpi(),
      ]);

    const kpis = { revenue, margin, dso, dpo, cashPos, overdueInvoices };

    // Cache for 15 minutes
    await this.redis.setex(cacheKey, 900, JSON.stringify(kpis));
    return kpis;
  }

  // Threshold alert job — runs Mon morning 07:00 WIB
  @Cron('0 7 * * 1', { timeZone: 'Asia/Jakarta' })
  async checkKpiAlerts() {
    const kpis = await this.getDashboardKpis();
    const alerts = this.alertConfig;

    if (kpis.dso.value > alerts.dso.threshold) {
      await this.notifier.sendEmail(alerts.dso.recipients, {
        subject: `⚠️ AR DSO Alert: ${kpis.dso.value} days`,
        body: `DSO has exceeded ${alerts.dso.threshold} day threshold.`,
      });
    }
    // ... additional alert checks
  }
}

How I Approached This

The dashboard backend I built is a NestJS DashboardModule with a single KpiService. The service orchestrates queries across FinanceService, InventoryService, ARService, and APService, assembles the KPI payload, and caches it in Redis with a 15-minute TTL. The front end is a React dashboard using Recharts for sparklines and KPI cards built with a shared component pattern. I use CSS variables for theming — the same dashboard component renders in dark and light mode depending on the user preference.

Mobile Dashboard for Executives

Indonesian executives are mobile-first. The CEO reviews the dashboard on their phone while traveling between meetings. I built a mobile-optimized dashboard view that collapses the 12 KPIs to the 5 most critical, shows large numbers with minimal labels, and uses green/amber/red color coding for instant status. The mobile view is a separate React route with a breakpoint at 768px. I also built a WhatsApp bot that sends a daily KPI summary at 7 AM — the CEO's favorite feature, because they get the numbers before they arrive at the office.

Alerting and Exception Management

A dashboard is reactive — the executive sees it when they look. Alerts are proactive. I added threshold-based alerts: if AR DSO exceeds 60 days, email the CFO. If cash position drops below a configured minimum, WhatsApp the director. If overdue invoices exceed Rp 500M, SMS the collections manager. These alerts run as NestJS cron jobs that check the KPI values daily and send notifications when thresholds are breached. The thresholds are configurable by the admin — no code deployment needed to adjust alert sensitivity.