Before buying a BI tool, measure where the two days actually go: six classes of work, a cut-off contract, an exceptions table, and when to build the dashboard.
Key takeaways
- In most closes I see, extraction and presentation account for under a third of the elapsed time. The bulk is burned on matching and on explaining numbers that do not agree.
- BI replaces extraction, repeated calculation and distribution. It does not replace deciding which of two similar records is the real customer.
- Before any automation, define a cut-off contract: per metric, a source, a date field, a close time and an owner. Without it the dashboard shows a different number from the manual report and nobody can say which is right.
- A recurring match moves into an exceptions table with a reason and an owner, rather than living as a note in a spreadsheet. An exception with no owner returns every month.
- Power BI limits a semantic model on shared capacity to eight scheduled refreshes per day, so refresh cadence is a design constraint rather than a switch.
Two days of monthly reporting is not two days of one activity. It is extraction, cleaning, matching, calculation, explanation and presentation, and a BI tool reliably replaces only two of those. The first move is measuring time and error rate in each class separately, and only then deciding what is a candidate for automation.
Where the two days actually go
The two days are consumed by the steps nobody records, not by the step everybody talks about. Here is a measured close at a services business - seven hours on day one, six and a half on day two:
| Step | Who does it | Minutes | Error rate | What could replace it |
|---|---|---|---|---|
| Export from bookkeeping and CRM | Office manager | 45 | Low | A schedule |
| Match customer names across the two systems | Office manager | 190 | High | A shared key, not a tool |
| Match the bank file to receipts | Bookkeeper | 150 | High | Match rules plus exceptions |
| Calculate profitability per customer | Finance manager | 120 | Medium | A query |
| Investigate three outlying numbers | Finance manager | 160 | - | Not replaceable |
| Build the deck and the charts | Finance manager | 145 | Low | A dashboard |
In this table a BI tool touches 310 minutes out of 810. The remaining 500 minutes are data work and judgement work. A business that buys a BI licence without touching the two matching rows gets a faster report showing the same suspect numbers.
The six classes of work hiding inside one report
Before deciding what to automate, classify every minute into one of six:
- Extraction - pulling data out of a source system. Almost always automatable.
- Cleaning - fixing format, dates, leading zeros, currency. Automatable once the rules are written down.
- Matching - linking a record in one system to a record in another. Automatable only after a shared key exists.
- Calculation - formulas and aggregates. Automatable, provided the formula lives in exactly one place.
- Explanation - why a number moved. Not automatable, but shortened dramatically by movement-level history.
- Presentation - tables, charts, distribution. Fully automatable.
The classification is the instrument, not the list. Once it exists, "do we need BI" is replaced by "how many minutes of each class does BI actually take".
What should you measure before choosing a tool?
Measure three real preparation cycles, with a clock, rather than reconstructing from memory. For each step, record five things:
- Who performed it and in which system.
- How many minutes it took, including waiting for an answer from another person.
- How many times somebody had to go back because a figure turned out to be wrong.
- What the exact input was - file name, query, screen.
- Whether a second person could reproduce the result without a verbal explanation.
Question five is the filter. A step where the answer is "no" is knowledge held in one person's head, and that - not the elapsed time - is the real risk in a monthly close.
The cut-off contract: the definition that prevents two answers to one question
A number that differs between dashboard and manual report almost always comes from a different date cut-off, not from a calculation error. So every metric gets a written definition with four fields: source, date field, moment of close, and owner.
Take "September revenue". It can be computed on invoice date, on receipt date, or on service delivery date - three completely different numbers, all correct answers to different questions. The definition reads: source = invoices table; date field = invoice_date; cut-off = 1 October at 08:00 Israel time; owner = finance manager. Once that exists, an argument about a number becomes a question about which definition was breached, and that is answerable in a minute.
The cut-off contract has a second clause that is almost always forgotten: what happens to a document that arrives late. A supplier invoice received on 4 October carrying a 28 September date can land in September, land in October, or land in September and mark the report as restated. All three are defensible, but one has to be chosen and written down, or the same report shows two numbers on two run dates and nobody can say what moved. The simplest working pattern is to store a run timestamp and a data version with every report, so comparing two versions is a mechanical operation rather than an argument.
The same principle of one identifier carrying a figure through a process is set out in profitability by project with one job ID.
Why is an exceptions table better than cleaning the data again?
An exceptions table is better because it converts a one-off correction into a rule that survives. Instead of somebody fixing the same row every month, the exception is recorded with four fields:
- Exception ID - for example
EXC-2026-09-0031. - Type - unmatched receipt, currency mismatch, duplicate customer, movement with no document.
- Reason - a short free-text note written exactly once.
- Owner and target date - who fixes it in the source system, and by when.
The payoff arrives in month two. A close that logged 40 exceptions the first time typically logs 12 the second, because 28 of them were the same defect at source. Without the log those 40 recur forever and each looks new. The same logic appears in automating report production, except here it runs before the tool rather than after it.
When are you allowed to start building the dashboard
You are allowed to start when the numbers already agree. The readiness signal is three consecutive months in which the automated calculation and the manual report land on the same result inside a pre-agreed tolerance. Then, and only then, you add refresh and distribution.
A technical constraint belongs in that plan from the start. Microsoft's documentation states that a semantic model on shared capacity is limited to eight scheduled refreshes per day, that the quota also covers refreshes triggered through the REST API, and that a manual refresh from the interface is not counted against it. On Premium or Fabric capacity there is no fixed numeric limit, but refreshes are governed by capacity resources and can queue. So "refresh every 15 minutes" is an architecture decision, not a default. The source-connection side is covered in connecting CRM and ERP to BI.
Sources
Frequently asked questions
Can you just point the BI tool straight at the systems?
Technically yes, but then every schema change in a source system breaks the report, and every heavy query competes with live operational work. The common pattern is an intermediate layer holding a scheduled copy, partly because it also lets you keep history that the operational system overwrites in place.
How long does this decomposition take to build?
The measurement itself is three closing cycles, so three months if the report is monthly. You can shorten it by instrumenting one close live and reconstructing two backwards from files and emails, but reconstruction is least accurate exactly where accuracy matters most - the matching and investigation steps.
What if there is no shared key between the systems?
Build a mapping table by hand once, give it an owner, and treat it as an asset. It starts as a few hundred spreadsheet rows and becomes a database table. The common mistake is matching on name: two customers with similar names or different spellings merge silently, and that error is very hard to detect after the fact.
Can AI shorten the explanation step?
It can write the explanation, but only if the underlying data exists. A model given a movement table with date, document and amount can point at the three transactions that caused a jump. A model given only a final figure will guess. The difference is the availability of history, not the capability of the model.
Keep reading
Related service
Dashboards
One screen with the few numbers that actually change a decision.
About the author
Yehonatan Saadia
Freelance automation, web & MVP developer
I'm Yehonatan Saadia, a senior developer who builds business automation, custom websites, and MVPs for small and mid-sized companies across the US, Europe, and Israel. These guides come from real client work, not theory.
Work with meHave a project like this?
Tell me what you're trying to automate or build. I reply within 24 business hours with a few targeted questions, then we walk through it on a free 30-minute call, with no commitment. You come away with a scope, a timeline and a fixed price - or a straight answer that it isn't worth building.