The short answer
Work out what the system has to be able to tell you before you design how it stores things. A data model built purely for transactions will make some questions expensive or impossible to answer later.
The classic example is not keeping history. A system that overwrites a status cannot tell you how long things spent in each state, and that is usually the question someone asks first.
Decide what must be reportable
- Which numbers will be reported externally or to a board
- Which need history rather than current state
- Which need to be reconcilable with another system
- Which are used operationally, several times a day
- Which questions nobody can answer today and should be able to
The second point is the one with architectural consequences. Reporting on change over time requires keeping the changes, and that is a decision made early or not at all.
Fixed reports or self-serve
| Fixed reports | Self-serve | |
|---|---|---|
| Build cost | Lower per report | Higher up front |
| New questions | Needs a developer | User can answer |
| Consistency | High, one definition | Risk of different answers |
| Training | Minimal | Real |
| Suits | Regular known questions | Exploratory analysis |
Most businesses need both: a small set of fixed reports everyone trusts, plus a way for a few people to explore. Trying to make everything self-serve produces a tool nobody uses and three versions of every number.
Agree the definitions
The most common reporting dispute is not about the software. It is that finance and operations define the same number differently, and both are right within their own frame.
Get the definitions written down and owned before the reports are built. A report producing a number nobody agrees with is worse than no report, because people now argue about the tool instead of the definition.
Do not report off the live database
Heavy reporting queries against the transactional database slow down the application, usually at the worst moment because reports get run at month end.
- Use a replica for anything heavy.
- Precompute the expensive aggregates on a schedule.
- Keep the operational dashboards light and targeted.
- Move genuinely analytical work to a separate store if it grows.
- Monitor query time so a slow report is noticed before users are.
Export is not a failure
People will want the data in a spreadsheet. Fighting that produces workarounds; supporting it properly means the export is accurate and traceable rather than a screen scrape.
A good export with clear column definitions is often more valuable than another dashboard, and considerably cheaper to build.