How to Build an Operations Dashboard: From ERP Data to Reports on the Boss's Phone

Eleven Hours Every Fifth of the Month

An industrial parts distributor in Taiwan bills around NT$30M a month. On the 5th, accounting exports four ERP reports, an assistant pulls an order dump from the storefront, and VLOOKUP stitches it into a 12-tab Excel file — 11 working hours, every month. By the time the owner sees "gross margin fell 2.4 points last month," it is mid-March; the problem started in early February, leaving two weeks to react. This is not a discipline problem. The data flow was never engineered.

When It Fits and When It Does Not

Good time to build:

• Data sits in two or more systems and manual merging takes over 6 hours a month
• Three roles need different views: owner on margin, sales on quota, warehouse on turnover days
• Decisions happen weekly or daily, and master data is consistently named

Hold off if:

• You run one ERP whose native reporting already covers you — the vendor's license is usually cheaper
• Under 300 transactions a month; an Excel pivot finishes in 30 minutes
• Master data is messy (one customer under three codes) — clean it first, or the dashboard only amplifies the errors

Alternatives Matrix

The Full Process

• 1 — Metric alignment (3–5 days): 60-minute interviews with owner, finance and sales leads; converge in Notion on no more than 12 metrics. Deliverable: a metric dictionary with formula, denominator, source table and refresh cadence.
• 2 — Data inventory (5–8 days): map source systems and access paths (ERP read-only account, API, scheduled CSV), build the master-data mapping table. Deliverable: a data lineage diagram in draw.io.
• 3 — Pipeline (8–12 days): stand up a staging database; use the Laravel scheduler with queues for 02:00 nightly incremental loads into fact and dimension tables. Deliverable: ETL scripts, retry logic, alert channel.
• 4 — Dashboard build (10–15 days): three Figma layouts first (executive, sales, inventory); code after sign-off. Deliverable: live site plus role-permission table.
• 5 — Reconciliation (5 days): reconcile against last month's closed books, variance under 0.5%. Deliverable: reconciliation report and screen recordings.

Real Cost Breakdown

• Entry, NT$60,000–120,000: self-hosted Metabase, one source, three dashboards, 2–3 weeks.
• Standard, NT$180,000–350,000: 3–4 sources, full ETL, 8–12 metrics, role permissions, nightly refresh, 6–8 weeks. Where most SMEs land.
• Advanced, NT$450,000–900,000: three years of backfill, layered warehouse, budget-vs-actual variance, scheduled PDF delivery, 10–14 weeks.

Hidden costs people forget:

• Hosting about NT$800/mo for 4GB; add roughly NT$1,200/mo past 50GB
• Master-data cleanup: mismatched customers and SKUs commonly eat 15–40 hours, NT$18,000–48,000
• ERP vendor fee to open a read-only account or API: NT$8,000–30,000 one-off
• Maintenance NT$4,000–12,000/mo covering monitoring, metric tweaks, one new report per quarter

Reality vs Expectation

Common Traps and How to Avoid Them

• Undefined semantics: is revenue tax-inclusive, do returns net out? → Put it in the metric dictionary and have finance sign off.
• Querying the production ERP: one slow query freezes order entry → use a read replica or sync to staging first.
• Time zones and day boundaries: ERP stores ROC years, the storefront returns UTC, cross-midnight orders land on the wrong day → normalise to Gregorian and Asia/Taipei in the ETL layer.
• Over-broad permissions: reps see company margin and colleagues' commissions → build a role matrix before launch, default to least privilege.
• No anomaly alerting: a renamed source column makes the ETL fail silently → alert by email or LINE on job failure and on any 30% drop in row counts.
• Slow first paint: three years of line items pushes load to 8 seconds → pre-aggregate daily tables and keep LCP under 2.5 seconds.

Success Metrics and the 90-Day Roadmap

• Day 30: reconciliation variance under 0.5%; weekly active users at 80% of target; ETL success rate at or above 99%.
• Day 60: manual reporting down from 11 hours to under 2; two new metrics requested by users.
• Day 90: at least three decisions traceable to the dashboard (e.g. discontinuing SKUs below 8% margin); report requests move to a request form; decide whether phase two is worth funding.

Compliance Notes for Taiwan

• Data minimisation: dashboards do not need full national ID or mobile numbers — mask them during ETL, consistent with the necessity principle of Taiwan's Personal Data Protection Act; special-category data such as health records must stay out.
• Accounting retention: under Article 38 of the Business Accounting Act, vouchers must be kept at least 5 years and books and statements 10 years. A BI staging database is not the system of record.
• Audit and outsourcing: log report exports and revoke departing accounts on the last day; sign personal-data processing clauses with the developer specifying post-project deletion.

Decision Checklist

• ☐ Manual reporting takes more than 6 hours a month
• ☐ Two or more source systems are involved
• ☐ Master data naming is consistent
• ☐ A read-only ERP account or API is obtainable
• ☐ Finance will sign off on metric definitions
• ☐ There is a named internal owner
• ☐ Decisions are made weekly or daily
• ☐ Budget of at least NT$150,000 is available
• ☐ A 6–8 week build window is acceptable
• ☐ You know what decision day 90 should support

Seven or more ticks means the return justifies starting now. Fewer than four means master-data cleanup comes first.

FAQ

We only have 8 employees — is a dashboard worth it?

Hours matter, not headcount. If manual reporting exceeds 6 hours a month and one error affects purchasing or quoting, an entry-tier NT$60,000–120,000 build typically pays back in 8–12 months. Small teams have less slack for repetitive work, not more.

Why not just use free Looker Studio?

If your data sits in the Google ecosystem it works and should be your first try. Three things break it: connecting your own MySQL, row-level permissions, and joining across systems. Hit two of the three and it is time for Metabase or a custom build.

Our ERP vendor refuses to open a database account. Now what?

In order of preference: the vendor API, a scheduled CSV export to a designated folder, and only as a last resort screen scraping. The first two run reliably; the third carries high maintenance cost and should be a bridge only. Ask for the data dictionary in the same contract amendment.

How often should the data refresh?

Finance and margin metrics: nightly at 02:00, because accounting postings lag. Shipping, inventory and open tickets can run hourly. Anything genuinely needing second-level freshness is an alert, not a report.

Next Step

ScriptWalker's Operations Dashboard Build starts at NT$180,000 and includes the metric dictionary, the ETL pipeline and three dashboards. Still evaluating? A two-week NT$18,000 Metric Inventory Workshop produces the metric dictionary and data lineage diagram first, so you can decide with evidence. Bring your current Excel reports and we will walk through them.

• Email: [email protected]
• Phone: 0916-224-047
• LINE: @ufv9089p