Case studies

From Excel exports to Power BI — and reports 16× faster

Problem

Solar panels, weather sensors and energy devices write a reading every second — millions of rows a quarter. Analysis meant exporting to Excel and drawing charts by hand. With the energy worked out inside the report, three months took 21 minutes to load, and the year the staff needed was estimated at over an hour.

Built

Power BI on the hub’s own database: five energy reports, one per monitoring system, a report of sunlight against panel output, and an overview. The energy calculation moved into the database, so the report only adds up by the interval picked — one second to one day.

Result

1 month, by minute

3 min 50 s

~50 s

1 month, by hour

4 min 50 s

~70 s

3 months, by hour

21 min 40 s

~80 s

beforeafter

16× faster on three months of data. Built to requirements agreed with the hub, so its staff can analyse the data without Excel. A reference from the project’s supervisor is available on request.

Order and warehouse reconciliation, measured on public data

Real data from an online retailer, a footwear manufacturer’s warehouse and a multinational’s purchasing in SAP, published for research. Measured with the same script we run on client files; the datasets are linked on the Evidence page, so anyone can check the numbers.

Problem

Everyone in this market says orders and stock go missing between systems. Almost nobody shows the number, because the two files that prove it are rarely public.

Found

• Orders against shipments, 50,231 orders: 0.64% paid for and never shipped. In the worst hour 42 of 43 orders were lost, across 42 different products — a connection failure, not a stockout.
• Picking against orders, 122,370 order lines: compared as exported, the files said 27.3% “ordered, not picked” and 23.9% “picked, not ordered” at once — because one system wrote a shoe size as 105 and the other as 10.5. After normalising the key: 97% matched, 4.58% ordered and never picked, 0.01% picked and never ordered.
• Purchasing with three-way matching in SAP: 4.51% of received items had no invoice, and 4.06% were invoiced more than once.

Result

Each dataset changed the script behind our checks: SKU normalisation, a refusal to print a number when the key match is too low, and cancelled, returned and recent orders excluded before anything counts as missing. The same script runs the orders and inventory checks.

Operations reporting that no longer breaks when an export changes

A reinsurer is the company that insures insurance companies. This one works worldwide; its name is kept confidential.

Problem

Events, requests and tasks sat in separate exports and regional lists, each with its own columns and spellings. Reports were rebuilt by hand, and broke whenever an export changed.

Built

• One Power BI model for events, requests and tasks. Files are picked up automatically and headers detected, so a changed export no longer means a rebuild.
• One master list from the regional lists: columns mapped, names aligned, duplicates across regions checked before anything is counted.
• Recurring events matched across years, even after they were renamed. Doubtful pairs go to a person, marked green, yellow or red.
• A completeness score for every critical field, ranked by owner, with placeholders like “N/A” counted as missing.
• Where requests wait: time from submission to acceptance to completion, the oldest overdue stages, and the workload per person.

Result

Reports always on the latest data. Gaps and delays visible while they can still be fixed, each with a name next to it. Year-over-year comparisons became possible, and duplicate planning got caught.

A new data platform for an energy lab: faster reports, and problems caught as they happen

Problem

The lab’s instruments stream readings non-stop, and the previous MariaDB setup had reached its limits: millions of rows, slow reports, and no warning when a device stopped sending or started sending zeros.

Building

• TimescaleDB on new hardware, with readings sent straight from the instruments.
• New devices and measurements added by configuration, not by rewriting code.
• Faster reporting in Power BI and on the lab’s website, from summaries the database keeps up to date in the background.
• A watchdog in Python on every stream — missing data is the failure nobody notices, because nothing looks broken. Gaps are logged; zero and impossible readings are flagged.
• Anomaly detection with machine learning on the installations’ power output.
• EV-charging price forecasting from charging-station data.
• A technical manual, so the lab can run and extend it on its own.

Aim

Reports that read ready-made summaries instead of millions of raw rows, and a platform the lab can grow without code. Load times will be measured before and after, as on the hub.