The data was there, the answers were not

The problem

The company had data. Plenty of it, and well kept: years of operations in the ERP’s database. What it did not have were answers.

Producing a report was a procedure. First you asked the ERP — proprietary, years old, no API of any kind — and that step alone took its time. Then came the real work: export, open the spreadsheet, cross-reference by hand, fix whatever the ERP had returned in a different shape from last month. All in, close to an hour per report.

The visible cost was that hour. The cost nobody counted was the other one: when asking a question costs an hour, people stop asking. Dashboards were looked at once a month, KPIs were argued over from memory, and decisions that should have started by checking a number were made without checking it. Not out of carelessness — the number simply arrived too late.

The decision

The obvious pitch was to replace the ERP. It is old, closed and has no API: anyone can argue it has to go, and it also happens to be the largest and most expensive project available in that room.

We decided not to touch it. The ERP did what the company used it for every day, and replacing it would have put the whole operation at risk to solve a reading problem. What needed building went beside it, not in its place.

The second decision was the BI layer. We chose Apache Superset, open source, with no licence cost — none per user, none per dashboard. A proprietary product would have been easier to sell and would have left a resale margin every year. Instead that budget went into tailoring it: Superset was adapted to the client’s operation and to their visual identity, so that people were not opening “a BI tool” but a system that looked like the rest of theirs. That is not cosmetic — it is the difference between a thing being used and a thing being ignored.

The hard work, though, was underneath. The database was not mapped. No documentation, no data dictionary, nobody who could explain what each table meant. So it was rebuilt: reading the database’s own catalogue, following foreign keys where they existed and inferring the relationships where they did not, until it was clear where each figure came from and when it was written.

And this is the part that defines the case. An inferred schema cannot be trusted just because it hangs together. So not one dashboard was published until its figures matched a report the ERP already produced and the client already trusted. Every metric was validated against something the company already believed. That made the project longer and it shows up on no screen, but it is the only thing separating a dashboard from a tidy guess.

Extraction was done without putting the operation at risk, under four rules:

• A read-only user, with permissions scoped to what the BI actually needed to read. Never the ERP’s own credentials.
• Heavy queries against a replica, not against the database the business was using at that moment. A report must not slow down a sale.
• Nothing written into the ERP’s schema. Everything derived lives in a separate schema, so a vendor update does not collide with our work and our work does not void their support.
• Views as a contract layer. When the ERP changes a column you fix one view, not forty dashboards.

The result

The report that took close to an hour is now available in about ten seconds, automated and complete. The same figures, without the manual spreadsheet step and without waiting on the ERP.

That changed what was possible: dashboards looked at when they are needed rather than when they are due, KPIs followed as they move instead of reconstructed at month end, and forecasts built on data that was already there but that nobody could assemble in reasonable time. The BI licence cost was zero.

The number is not the point, though. The point is that asking stopped being expensive. When the answer takes ten seconds, people ask before deciding — and that is the difference between having data and having answers.