Reporting and analytics

The report exists. Changing it is what costs you

Somebody wants scrap by work centre instead of by part. Same data, one more column, and it takes three weeks and an invoice. So people stop asking, and within a year the real reporting system in your business is a folder of spreadsheets that disagree with each other. This is the project that ends that.

Talk about your reporting What it costs

The problem

The report exists. Changing it is what costs you

Nobody complains that their ERP has no reports. They complain that adding one column means raising a ticket.

Somebody in production wants last month's scrap broken out by work centre instead of by part. It is the same data, one more column, five minutes of thought. It takes three weeks and an invoice, and by the time it lands the question has moved on.

So people stop asking. They export to Excel instead, and within a year the real reporting system in your business is a folder of spreadsheets, each maintained by one person, none of them agreeing, all of them out of date the moment they are sent.

There are usually three reasons it is like this, and they compound:

The report writer is a separate productCrystal, SSRS or the vendor's own designer, licensed separately, installed on one machine, and understood by one person who left.
The schema was not written to be readTables called UD101, fields called CHR01 holding whatever the implementer put there in 2014, and the actual meaning living in somebody's head or in a consultant's notes.
The invoice for each request is smallSmall enough that nobody escalates it, so it never becomes a project and never gets fixed. The annual total is the number that surprises people, and nobody adds it up.

What we build

A copy of your data that your own people can ask questions of

A read only replica, refreshed on a schedule you chooseNightly for most, every few minutes where it matters. Nobody reports off the live database, ever: one careless query against production locks tables and stops the floor booking work. This single decision is why the rest of it is safe.
A layer that names things in your languageViews called job, operation, hours booked and scrap, not UD101. Written once, with the definitions agreed with the people who use the words. This is the actual work and it is where the value is.
One definition per numberIf two reports disagree about last month's revenue, that is a bug in the layer rather than a difference of opinion between departments. Agreeing the definitions is often the most useful week of the project.
Reports your team can change without usOn whatever you already have. Power BI if you are on Microsoft, Metabase or similar if you are not, and Excel connected directly for the people who will use Excel whatever anyone says.
Scheduled deliveryThe Monday morning numbers arriving as a PDF or a link before the meeting, rather than somebody rebuilding them at seven in the morning.
Written down, so it is yoursEvery view documented, in your repository, on your infrastructure. If you want another firm to extend it next year, they can read it.

Three to six weeks for most, and your ERP is untouched throughout. No modules, no upgrade, no change to how anyone books a job.

The trap

How this goes wrong, so you can watch for it

The failure mode is that the replica becomes a second source of truth. Someone builds a report straight off the raw tables because the view did not have quite what they needed, their number is slightly different, and now you have two answers again with a nicer chart on one of them.

The defence is a rule rather than a technology: reports read the named layer, never the raw tables. When the layer is missing something, the layer gets extended. That is a ten minute change once somebody in your team can make it, which is the whole reason for doing it this way.

Being straight with you

Two things this does not fix

It does not fix wrong data. If downtime is written on a clipboard and typed in on Friday, or operators book time to whichever job is open because the estimate was wrong to begin with, better reporting shows you wrong numbers faster and with more confidence. That is worse than no report. If that is your situation, the shop floor capture is the project and this is the one after it.

Sometimes your vendor's own module is the right answer. Several of the mid-market systems now ship analytics that are genuinely fine for standard questions, and if your reporting need is standard, buying theirs is cheaper than anything we would build. We will say so. Where this work earns its money is when the questions are specific to how you make things, which is exactly what a packaged dashboard cannot anticipate.

FAQ

Common questions

Will this slow down or put at risk our live ERP?

No, and that is the main reason for the design. Reporting runs against a read only copy, so even a badly written query cannot lock a table that production needs. The only contact with the live system is the scheduled read that refreshes the copy, which is the same kind of load as a nightly backup.

Do we need to buy Power BI?

Only if you want it. The layer we build is standard SQL views, so anything can sit on top: Power BI, Metabase, Looker Studio, Excel's own connection, or a page we build for you. If you already pay for something, we use that. The reporting tool is the cheap and replaceable part. The named layer underneath is the part worth owning.

Our ERP is hosted by the vendor and we cannot touch the database. Now what?

Common, and it changes the first step rather than the plan. We pull through whatever the vendor does expose, usually an API or a scheduled export, into the same read only copy. It is slower to build and the refresh is less frequent, and everything downstream is identical. Worth checking your contract first: some hosting agreements include database access on request.

How is this different from the BI project we abandoned two years ago?

Usually those fail for one of two reasons: the dashboards were built before anyone agreed what the numbers meant, so nobody trusted them, or they were built by people who then left and could not be changed. We do the definitions first and in writing, and the deliverable includes your team being able to change a report without calling anyone.

Who owns it, and what happens if we stop working with you?

You own all of it from the first commit, in your repository, running in your cloud account or on your server. The views are documented plain SQL, deliberately unclever, so another firm or a capable person internally can pick it up. There is no per seat licence on our work and nothing that has to be renewed.

Keep exploring

Related

Next step

Count the reports somebody rebuilds by hand every month.

That number, times the hours, is what this project is worth to you. Bring it to a free call and we will tell you whether your ERP already does it.

Free tools, no sign-up