Reporting has been one of the longest-running headaches on the platform I work on. Our clients, councils and charities running health programmes, need to see how their services are performing. Getting that data out safely and quickly has taken us three attempts.
Attempt one: syncing to another database
The first approach was to sync data from the production database into a separate reporting database. On paper, that keeps reporting load off production. In practice, syncs fail, lag and drift, so reports were regularly stale or simply wrong. A report people can’t trust is worse than no report.
Attempt two: MySQL views
Next, we stopped copying data and instead created MySQL views that expose only non-PII data. That gave us a single, controlled surface for reporting: no patient names or contact details, and no sync to fall out of date.
We connected those views to GoGrow, our BI tool at the time. That’s where the next set of problems started.
The problem with GoGrow
- It copied everything. GoGrow pulled all of the data from the views onto its own system before it could report on it. So we were back to syncing, just inside someone else’s product.
- It was slow. A dashboard update took around two hours.
- It couldn’t handle our biggest table. We store questionnaire answers, things like a patient’s weight at each check-in, in a table with around 40 million rows. GoGrow couldn’t cope with it.
- Only one person could use it. Building reports needed training that only one member of staff had. That’s a key-person risk for something the whole business relies on.
Attempt three: Grafana
I proposed Grafana, and we’re now moving our reporting to it. The key difference is that Grafana queries our read-replica database directly rather than copying the data.
- Dashboards update in near real time, instead of taking two hours for a simple change.
- The 40-million-row table is just a table. With the right indexes and queries, MySQL handles it fine.
- Anyone can build a report. Grafana is widely used and well documented, so we’re not dependent on one trained person.
- PII stays out. Grafana only sees the non-PII views, so the data boundary we built in attempt two carries straight over.
What I’d tell anyone in the same position
Every hop between your data and your dashboard is a place for it to go stale. If you can put a safe, narrow interface (like non-PII views) in front of the source of truth and query it directly, you usually get fresher data, fewer moving parts and lower risk all at once.