BI software, spreadsheets and analysis platforms

Why this matters

Most of the numbers an organisation acts on reach people through a tool, not a script: a spreadsheet passed around a team, a dashboard in a business intelligence platform, a statistical package used by a specialist. Each tool makes some work easy and some mistakes easy too, and choosing the wrong one for a job is a decision the exam expects you to recognise.

This lesson covers spreadsheets, business intelligence tools and analysis platforms: what each is for, where each fails, and how to choose between them for a task, a data size and an audience.

The lesson

Spreadsheets: what they do well and where they fail silently

The spreadsheet, such as Microsoft Excel or Google Sheets, is the most widely used analytical tool there is. It is excellent for small data sets, quick calculations, ad hoc what-if modelling and sharing a table with someone who has no other tools. Pivot tables summarise data by category without writing a formula, and charts are a click away.

Spreadsheets fail in ways that do not announce themselves:

• Size limits: a worksheet has a maximum number of rows, a little over a million in Excel, and files slow down well before that. Data beyond the limit can be silently cut off on import.
• Hidden logic: a formula in one cell may reference the wrong range, and nothing shows it unless someone clicks the cell. A value typed over a formula looks identical to a calculated one.
• Manual steps: sorting, filtering and pasting leave no record, so the same work cannot be repeated or checked.
• Type conversion: values that resemble dates or large numbers may be converted automatically on entry or import, damaging identifiers.
• Versions: copies emailed and edited separately drift apart, and it becomes unclear which is current.

Spreadsheets remain the right tool for small, one-off work and for final presentation of small tables. They are the wrong tool for large data, repeated processes and anything that must be audited.

Business intelligence tools and the semantic layer

A business intelligence (BI) tool connects to data sources and lets people build reports and interactive dashboards. Common examples include Microsoft Power BI, Tableau, Qlik and Looker. Their core strengths are visual reports, interactivity such as filters and drill-down, scheduled refresh from live sources, and sharing results with controlled access.

Many BI tools include a semantic layer: a model that sits between the raw tables and the reports, defining relationships between tables, business-friendly names, and measures such as "net revenue" or "active customers" with their calculation fixed once. The semantic layer is what makes a BI platform more than a charting tool. When "net revenue" is defined in one place, every report that uses it agrees. When each report author writes their own version, two dashboards can show two different net revenues from the same data.

BI tools usually load or query data through connectors, sometimes importing a copy on a schedule and sometimes querying the source live. The difference decides how fresh the numbers are and how much load the dashboard puts on the source.

Self-service analytics against governed reporting

BI tools make self-service analytics possible: business users build their own reports without waiting for a data team. This spreads access to data and speeds up answers, which is its purpose.

It also creates risks. Different users define the same metric differently. Reports multiply until nobody knows which is authoritative. Sensitive data is shared further than intended. Calculations nobody reviewed inform decisions.

Governed reporting is the counterweight: certified data sets and measures maintained by a data team, official reports that have been reviewed, and controls over who can publish and who can see what. Most organisations aim for a balance, often in tiers: a small set of certified, governed data sets and reports as the trusted core, with self-service exploration built on top of those certified sources rather than on raw copies. When a self-service report and a governed report disagree, the governed one is the reference, and the disagreement is a problem to investigate.

Statistical packages and analysis platforms

Beyond spreadsheets and BI, analysts meet several other kinds of tool.

Statistical packages such as SPSS, SAS and Stata provide statistical procedures through menus and their own scripting languages, and are common in research, healthcare, government and some large enterprises. They are strong in established statistical methods and in producing documented, repeatable analyses.

Data science and analytics platforms, often cloud-based, combine notebooks, managed computing, data access and model deployment in one environment, so teams can work on large data without managing servers. Cloud providers and specialist vendors offer them.

Data preparation tools provide visual interfaces for cleaning and combining data, recording each step so the preparation can be repeated, which addresses one of the spreadsheet's main weaknesses without requiring code.

The categories overlap, and products change quickly. For the exam, the useful skill is recognising the category a tool belongs to and what that category is good for, not memorising product features.

Choosing a tool for the task, the data size and the audience

Three questions narrow the choice.

What is the task? A quick calculation or a small one-off table suits a spreadsheet. A recurring report for many people suits a BI tool. Complex cleaning, statistics or automation suits code in Python, R or SQL. Specialist statistical work may call for a statistical package.

How large is the data? Thousands of rows are fine anywhere. Beyond a spreadsheet's limits, the work belongs in a database, a warehouse, or code that processes data in the database rather than on a laptop.

Who is the audience? Executives usually need a concise dashboard or summary, not a notebook. Technical peers may want the code. A regulator or auditor needs a documented, reproducible process.

Two further considerations often decide in practice: what the organisation already licenses and supports, and whether the result must be repeated. Anything that will run again should be built in a tool that records its steps.