BI and Reporting Dashboards for Companies

Business intelligence (BI) is the work of pulling data that sits scattered across a company’s systems into one place and turning it into a summary someone can decide on; a reporting dashboard is what that summary looks like on screen. In practice it answers one question — which product sold, in which branch, at what margin this month — and it answers it without asking three people for a spreadsheet. There are three ways to build it: connect a ready-made BI tool, switch on your ERP’s own reporting module, or develop a dashboard around your own data. The right choice depends on how many data sources you have and how often the questions change. Below: the three routes compared, the four layers of a dashboard, a real summary-table example, the metrics that belong on the first screen and the five mistakes that sink these projects.

What is business intelligence, and how is it different from a dashboard?

Business intelligence is not a product, it is a process: collect the data, clean it, agree on what each number means, then present it. The dashboard is only the last step — the shop window. Projects that skip the distinction all end in the same place: a screen that looks good and nobody trusts. If the box labelled “revenue” has no written definition (does it include tax, are returns deducted, do cancelled orders count?), then in the first management meeting two directors will defend two different numbers and the dashboard is shelved by month three.

Where spreadsheet reporting breaks down

A spreadsheet is not the wrong tool; up to a certain scale it is the fastest tool. The problem is that as the company grows, producing the report becomes a job of its own. The breakdown shows up in five places:

• The data lives in several places. Sales sit in the e-commerce database, collections in the accounting software, stock in the warehouse system. Merging them is manual every month, and slightly different every month.
• The report depends on one person. When they take leave, the month-end close slips, and over time nobody can explain the formulas.
• The data is dated. A report prepared on Monday drives a decision made on Thursday; the returns and cancellations in between are invisible.
• Versions multiply. Three copies of the same file circulate and which one is authoritative is a matter of debate.
• It cannot be audited. When a number is wrong, there is no way to trace back which step introduced the error.

The threshold is this: if preparing the report takes more than two days a month, or if two departments give two different answers to the same question, the problem is not what a spreadsheet can do — it is the missing layer that should be joining the data. If you already run a system that consolidates it, you are halfway there; we covered what that layer includes in our article on what an ERP is.

The four layers of a reporting dashboard

• Data sources — ERP, accounting software, the online store, CRM, marketplace seller panels, the carrier. Each is a separate connection and each refreshes at a different rhythm.
• The load layer — the scheduled job that pulls data from the sources into a single warehouse. Its one critical property is being safely re-runnable: if the job dies halfway and you start it again, it must not double the data.
• The model layer — where raw tables are translated into business language. Net revenue, active customer, late order are defined here once, and every chart on the dashboard uses the same definition.
• The presentation layer — charts, filters and permissions. A branch manager sees their branch, the CEO sees all of them. Permissions are not a decoration bolted on later; they are a decision made at setup.

Three of those four layers are invisible to the user, and roughly three quarters of the effort goes into them. The real body of work behind a “let’s get a dashboard built” request is almost always the first two layers: connecting to each system’s interface and moving data on a schedule you can rely on. We covered how those connections are built in our article on what API integration is.

A dashboard should not read the live database

The most common early choice is to point the dashboard straight at the live orders table. It works fine for a month. As order volume grows the dashboard slows down first, then every user who opens a report puts load on the sales database, and the store itself gets slower. The right shape is to produce separate summary tables for the dashboard to read: a nightly job computes totals by day, branch and product, and the dashboard only ever touches that small table.

-- Daily sales summary. The dashboard never reads the live orders table.
-- The nightly job only recomputes rows that actually changed.
INSERT INTO summary_daily_sales (day, branch_id, order_count, net_revenue, refunds)
SELECT
    CAST(o.created_at AS date)                AS day,
    o.branch_id,
    COUNT(*)                                  AS order_count,
    SUM(o.amount - COALESCE(o.refund, 0))     AS net_revenue,
    SUM(COALESCE(o.refund, 0))                AS refunds
FROM orders o
WHERE o.status IN ('completed', 'refunded')
  AND o.updated_at >= :last_run_at             -- incremental, never a full reload
GROUP BY CAST(o.created_at AS date), o.branch_id
ON CONFLICT (day, branch_id) DO UPDATE SET
    order_count = EXCLUDED.order_count,
    net_revenue = EXCLUDED.net_revenue,
    refunds     = EXCLUDED.refunds;

Two details make this query survive production. First, the filter on the update timestamp: instead of recomputing all history every night, only changed records are processed, so the job takes seconds rather than minutes. Second, the conflict clause: if the job runs twice in a day, totals are overwritten rather than doubled. Without that second property, one night of network trouble ends with revenue appearing twice as large the next morning — and the dashboard loses its credibility in a single stroke.

Technical failure is rare in reporting projects; loss of trust is common. If one number disagrees with accounting in the dashboard’s first week, the team never opens it again. That is why the first item on a rollout plan is not the number of charts but reconciling three key metrics against the source system, figure by figure, and signing off on the result in writing.

Ready-made BI tool or custom dashboard?

• A ready-made BI tool (Power BI, Metabase, Looker Studio, Tableau and similar) — Fast to set up, rich in chart types, and after a while users build their own reports. Pricing is usually per user per month, so cost grows linearly with the number of people reading the reports. For standard sales and finance reporting this is the most efficient route.
• The reporting module of software you already run — No extra integration is needed because the data is already inside, which makes it the cheapest option. Its limit is flexibility: producing a breakdown the module does not offer (campaign-level profitability, customer lifetime value) is usually simply not possible.
• A custom-built dashboard — Necessary when you want your own screens, your own metric definitions and an interface you can open to people outside the company. There is no per-user licence; you pay for the build up front. If the dashboard is part of your product and will be opened to dealers, franchisees or customers, this is in practice the only option.

The decision rule is simple: if 5-10 people inside the company read the report and the questions are standard, use a ready-made tool; if the report is part of the product, will be opened to hundreds of people, or your metric definitions are specific to your industry, build a custom dashboard. We looked at reporting opened up to a dealer network separately in our article on the B2B dealer ordering system.

Which metrics belong on the dashboard?

A dashboard is judged not by how many charts it has but by how many decisions it speeds up. A good first version fits on one screen and answers five questions:

• How much did we sell? Net revenue, order count and average basket, compared against the same period last year.
• How much did we earn? Gross profit and margin broken down by product or category. Margin falling while revenue grows is the problem noticed latest.
• What do we have? Days of stock on hand and products at risk of running out. This metric is only as accurate as your stock system; we compared the options in stock control software or custom development.
• Where is the money? Overdue receivables and an ageing table. This data comes from accounting software; we described the connection in accounting software integration.
• Is operations slipping? Late orders, return rate and average delivery time.

Five mistakes that sink the project

• Starting without written metric definitions. If “revenue” has no single written meaning, the dashboard does not settle the argument in the meeting — it amplifies it.
• Connecting straight to the live database. Report traffic slows down operations; the summary-table layer belongs in the design from day one.
• Leaving permissions until later. On a dashboard carrying salary, cost and customer data, role-based access is part of the first release; adding it afterwards means rewriting the model.
• Letting the load job fail silently. If nobody is told when the nightly job errors, the dashboard shows yesterday’s data as if it were today’s. Failure alerting is a requirement, not a nice-to-have.
• Launching with fifty charts. Every chart nobody uses is maintenance debt. Starting with five metrics and growing on request produces a cheaper dashboard that gets used more.

Cost: what are you actually paying for?

The budget for a BI project is not one line but five. Seeing them separately in a proposal is what makes two suppliers genuinely comparable:

• Source connections — each system is its own piece of work. Connecting to a cloud product with documented endpoints takes a few days; reading from the database of an old on-premise program takes considerably longer.
• The data model — extracting metric definitions and building the summary tables. Usually the largest item, and the one most often left out of estimates.
• Dashboard development — screens, filters, permissions and exports.
• Licences or subscriptions — a per-user monthly fee if you chose a ready-made tool; only server and warehouse cost on a custom dashboard.
• Maintenance — the time spent when one of the source systems changes version or a new metric is requested. Projects with no annual maintenance line go stale in their second year.

Asking for these five items in person-days tells you far more than a single total: you also see which item grows when you scale. We explained how person-day estimating works, line by line, in our article on the cost of custom software.

Conclusion

A reporting dashboard turns data the company already owns into decisions; it does not create new data. Success therefore has little to do with how the charts look and everything to do with three things: written metric definitions, a load job that moves data reliably on a schedule, and the discipline to keep the first release down to five metrics. If you are thinking about a smaller starting point, our article on ERP for small businesses is a good entry. If you want your scattered systems consolidated into one dashboard, take a look at our corporate solutions service or request a quote with a short description of the systems you run today.