Business intelligence

Why it matters

The questions that matter most to a growing business almost always span two systems. Profit per customer needs sales and costs. Marketing return needs campaign spend and revenue. Staff productivity needs hours and jobs. As long as those systems are separate, every such question is an afternoon of exporting and matching, so it gets asked once a year or never. BI makes cross-system questions routine, which is where most of the hidden profit in an SMB is found.

The parts of a BI setup

A BI setup has four jobs, shown in the lead figure. Collection gets data out of the source systems, either by scheduled export, a built-in integration or an API connection. Storage keeps a copy of everything in one place, with history, so you can compare this quarter with the same quarter two years ago even if the source system only shows the current state. Modelling cleans and connects the data and, crucially, defines terms: what counts as a "customer", when revenue is "recognised", how a "job" is identified across systems. Visualisation presents the result in dashboards and reports.

The storage and modelling layers are what distinguish real BI from a dashboard bolted onto one system. A dashboard inside your accounting software shows you accounting data. A BI layer shows you accounting data next to job data and marketing data, with one agreed definition of each customer.

• Collection: integrations, APIs or scheduled exports from each source.
• Storage: a central database or data warehouse that keeps history.
• Modelling: matching identifiers and defining business terms once.
• Visualisation: dashboards, scheduled reports and alerts.

When spreadsheets stop being enough

Spreadsheets are a perfectly good BI tool for a small business with one or two systems and one person doing the analysis. They stop working at a predictable point: when the same export is being repeated weekly, when more than one person maintains the file, when the row count makes it slow, or when a decision was made on a version that turned out to be stale.

The next step does not have to be a full data warehouse. Many businesses move first to a BI tool connected directly to their live systems, which removes the manual export without adding infrastructure. A warehouse becomes worthwhile when you need history the source systems do not keep, when the volume is large, or when several systems have to be joined on messy identifiers.

Choosing a first project

The most common BI failure is starting with "a dashboard of everything". It takes months, nobody owns it and it answers no specific question. A better first project joins exactly two systems to answer one question the owner already cares about, delivers in a few weeks and is then extended.

Use a simple grid to pick: how valuable is the answer, and how hard is the data to get? High value and easy data is where you start. High value but messy data is the second project, once the first has proven the approach. Low value questions are left alone no matter how easy they are.

Keeping it alive

A BI setup is a product, not a project. Sources change their export formats, staff add new product codes, someone renames a category. If nobody owns the pipeline, the dashboard quietly drifts from reality and people stop trusting it. Give one person responsibility for checking the numbers against the source each month and for adding new questions as the business asks them.

Where the systems involved are the ones that run the business day to day, it is worth getting the integration built properly rather than relying on manual exports. SMBH Consulting's database and integration services do this kind of work for growing Australian businesses, but the principle holds whoever builds it: automate the collection, keep the history and document the definitions.

How to start with business intelligence

1. Pick one cross-system question Something the owner asks regularly and cannot answer today, such as profit per customer after delivery costs.
2. Identify the two systems involved Confirm each can export or connect, and which field will link records between them.
3. Write the definitions Agree exactly how revenue, cost, customer and period are defined. Get sign-off from whoever uses the numbers.
4. Choose the lightest tool that works A BI tool on live data for most; a warehouse if you need history or many joins.
5. Build one page One dashboard page answering the question, with the figures reconciled to the source systems.
6. Review it in a real meeting Use it to make a decision. If nobody changes anything because of it, the question was wrong.
7. Assign an owner and extend One person checks accuracy monthly and adds the next question.

Real-world example

A Sunshine Coast wholesale bakery joins its systems

Hinterland Bakehouse supplies 40 cafés and grocers. Orders come through an ordering app, deliveries are planned in a route app, and invoices and credits sit in the accounting software. Weekly revenue is about $62,000 and the owner, Mei, believes the business is comfortably profitable on every route. Her first BI project joins orders, deliveries and credits for one question: what is the gross margin of each delivery route after the cost of each drop?

The answer changes her week. Her main routes average $310 per drop and cost about $18 per drop to deliver. One coastal route has nine stops averaging $140 per drop, and the same route generates most of the credits for returned stock, because the small shops over-order to meet a minimum. After drop costs and credits, that route's gross margin is 11 per cent against 38 per cent elsewhere. Mei introduces a $200 minimum order, moves the coastal route to three days a week and offers a standing-order discount to the shops that stay. The dashboard now refreshes nightly and takes her ten minutes each Monday to review.

Common mistakes

• Starting with a dashboard of everything Big scopes take months and answer nothing specific. Start with one question across two systems.
• Skipping the definitions If sales and finance count revenue differently, the dashboard will be argued about rather than used. Agree the terms in writing first.
• Manual exports forever A weekly copy-and-paste pipeline breaks the week the person is on leave. Automate collection as soon as the question is proven useful.
• No owner after launch Sources change and numbers drift. Someone must reconcile the dashboard to the source each month.
• Buying the tool before the question The tool matters far less than the data model and the question. Choose the lightest option that answers it.

KPIs to track

• Time to answer a cross-system question From hours of exporting to minutes. Track it for your top three questions.
• Reconciliation variance The gap between dashboard totals and the source system. Should be zero or explained.
• Dashboard usage How many of the intended users open it weekly. Below half means it is answering the wrong question.
• Decisions made from BI Count them each quarter. A BI setup that changes nothing is a cost, not an asset.

Frequently asked questions

What is the difference between BI and reporting?

Reporting tells you what a single system recorded. BI joins several systems and keeps history so you can ask questions across them, such as margin per customer or return on a campaign.

Do I need a data warehouse?

Not at first. Many growing businesses start with a BI tool connected to live systems. A warehouse becomes worthwhile when you need history the sources do not keep, high volumes or messy joins across many systems.

How long does a first BI project take?

A focused project joining two systems to answer one question is typically a matter of weeks, most of it spent agreeing definitions and cleaning identifiers rather than building.

Is my data safe in a BI tool?

Reputable tools encrypt data and support access controls, but you are still responsible for who sees what. Limit access to the people who need each dashboard, and check where the data is stored if it includes personal information.

Key takeaways

• BI joins your separate systems so cross-system questions become routine.
• Storage with history and agreed definitions are what make it more than a dashboard.
• Start with one valuable question across two clean systems; extend from there.
• Automate collection once the question proves useful; manual exports do not last.
• Give the setup an owner who reconciles it monthly and adds the next question.