senn-techsenn-tech
Strategy
Strategy2026-10-01· By Franz Senn

From a transport management system to a number the dispatch desk accepts

The obvious route is a BI tool hung directly on the TMS database, querying the tables the system writes to when it saves. In our Translogica-TMS that is a SQL Server database, and Metabase supports SQL Server officially from the oldest supported version up to whichever version is current. The direct route works immediately. It still only lasts until somebody challenges a number.

Why the direct query stops after a week

A capture system is built for speed when writing, and analytics have a different load shape. The tables are normalized, a report on carrier cost per tour is a join across order, shipment, tour, vehicle and addresses, and that join runs again on every click against the disk where dispatch is currently working. Metabase's documentation recommends exactly the other way for this case: a replica, or a database optimized for analytics, with the querying connection separated from the writing one. Microsoft has documented the same pattern for years: move read load to a secondary copy so the primary stays free for the daily business. Against that stands the practical experience that small report queries barely register on a healthy system, and the warning that the secondary copy is itself an operational risk. What is debatable is the load level at which you move. What is not debatable is that ad-hoc scans and booking runs are different shapes of load.

Then there is something no load measurement solves: the schema belongs to the TMS vendor. Translogica is based in Innsbruck and registered as a GmbH in the commercial register. A public developer documentation with stable table commitments I did not find, after going through the vendor's site, the module overview and the job advertisements. Which TMS we run on-premises is covered in an earlier post. With the next update a column can mean something else, and your reports keep running without reporting that they now add up something different.

The data path we run

From TMS to reportTMSSQL ServerImportread access, scheduledData lakeParquet, per subject areaReportingMetabase
The import is the only step that produces news. Everything to its right depends on when the last run finished. (Quelle: senn-tech, own operation)

On the TMS side a dedicated read access is enough, never a shared administrator account. The import runs on a schedule and writes one Parquet table per subject area: order master, shipments, tours, carrier services, billing-relevant views. Those files sit on a machine that exists for analytics and are read there with DuckDB. Metabase hangs on that analytics file plus a few direct connections for questions that need the current state.

Measured on the live inventory of our BI machine as of 01 October 2026: 30 dashboards, 1,048 saved questions, 84 tabs, nine database connections, six active users. The lake covers 1.2 gigabytes, spread over subject areas whose directories were last written the same day. Those 1,048 questions are not 1,048 reports; much of it is working stock and stale rows you carry around until somebody cleans up.

What runs on our BI machine, read out on 01 October 2026Saved questions1048Tabs84Dashboards30Database connections901100
Figures from the Metabase metadata database, read out with SQL. The question count is working stock; reports are what survives the cleanup. (Quelle: senn-tech, own measurement 01 October 2026)

Four failure sources where logistics numbers die

No data date on the report. A number without the line "data as of: import 30 September, 14:05" is an assertion. Judging staleness needs a distinction we had to learn first: a table from a scheduled import may legitimately be called stale when the run did not complete. A table fed by events is quiet when nobody posted, and that is not an outage. The rule exists; the implementation is a gap on our side. The import writes data dates per subject area into one file, and on 01 October 2026 that file held exactly one entry, against 27 tables in the analytics database. A data date on every report is therefore something we do not have yet.

Empty columns that do not fail. We wanted to show the nearest own truck to a shipment. The vehicle table in the lake held exactly 366 rows on 01 October 2026, and the column for the last position timestamp was filled in not one of them, as were two further address columns in the same table. No query reports that as an error; a report happily carries on with "no match". Without that measurement we would have built a feature on live positions and looked for the cause two weeks later. What gets built now is a calculation over dispatched tours and distances, and the position question is logged as an open item with the vendor.

Definitions without an owner. Margin on a tour is the order revenue minus carrier cost from the shipment, that is how we calculate it, and that line now sits there with a name attached. The second case gets overlooked often: invoiced revenue and ordered revenue are two numbers, and a five percent gap between controlling and dispatch is almost always caused by two SQL variants.

Lake version. One script writes our analytics file and a driver inside the Metabase application reads it, and both have to speak the same file format line. DuckDB guarantees backward compatibility of its own format since version 0.10; that does not transfer to Parquet, where compatibility is an implementation-by-implementation question. Our own rule, written down on 01 September 2026: raise the writer version first and check one import run, then rebuild the Metabase application against the matching driver. The other order means the reports are down while you search for the cause on a Sunday.

The same metric, two numbers

The official German goods carriage statistics, as of 30 March 2026, report among other things 20,685 million loaded kilometres and 6,351 million empty kilometres, plus 231.5 million loaded trips and 140.7 million empty trips. Computed from that: the empty share is 23.5 percent measured in kilometres and 37.8 percent measured in trips. Two correct numbers for one metric, separated by nothing but the choice of denominator.

The reason for that precision is the margin. The branch data analysis by the Vienna Chamber of Labour for forwarding and logistics reports, for the 2022 financial year on the basis of 48 companies with 12.3 billion euro of revenue, an average ordinary EBIT margin of 3.7 percent. At that margin the denominator decides whether a one-percentage-point improvement exists on either of those two numbers at all. Every metric in a logistics dashboard therefore needs its denominator in the name: empty kilometre share is one metric, empty trip share is the other.

What else the tooling counts

Three points worth settling before you pick a tool, all from vendor and project documentation.

Support windows. Metabase version 63 was released on 07 July 2026 and leaves support on 01 November 2026; long-term support version 58 runs to 17 February 2027, and non-LTS branches get roughly two months as a rule. Our application sits, measured, on 0.63.13, and the next release on the same line is 0.63.18 from 16 September 2026. A four-week upgrade window is a planning task for a BI application that dispatch depends on, not a formality.

Two things are called cache. The query cache lives in the Metabase metadata database and only applies when the query runs through Metabase; the free tier has adaptive caching with one duration setting per database. A model, by contrast, can write back a precomputed table, which is a different mechanism with different consequences for your database. When a tender text says "caching", ask which one is meant.

If you report on people, you need the logging too. Usage analytics and meta analytics, meaning who views, downloads or subscribes to which report, are not part of the free edition and belong to the paid features. For reporting with personal data that is not a comfort item.

Reporting on people puts duties first in Austria

The regulation on data protection impact assessments (DSFA-V, BGBl. II No. 278/2018) requires in paragraph 2 subsection 2 an impact assessment for, among others, processing that "comprises an evaluation or classification of natural persons – including the creation of profiles and forecasts – for purposes concerning their work performance ... and rests entirely on automated processing". The same paragraph names the exception: in employment relationships it does not apply where a works agreement or the consent of the staff council exists. One subsection further, employees are listed as vulnerable data subjects, and a separate line covers merging datasets from different purposes. For a forwarding company that means one thing: a dashboard that scores dispatchers or drivers by count and quality needs the paperwork before the first chart.

What the work is, and what it is not

The software is the cheap part: Metabase is a container, the lake is a directory of Parquet files, and a read access is one line of SQL. The time goes into what surrounds those files, and in our case that never ran as a project. Between 26 August 2026 and 01 October 2026 the dashboard count went from 29 to 30, and the actual work of those weeks went into definitions cut down to one line and one query, an import that shows its data date, and the two measurements above that overturned a planned feature.

Four prerequisites before the first dashboard

  • A dedicated read access to the TMS with its own login, so it stays traceable which query came from where.
  • One owner per metric, denominator included. One line of text and one query, not a discussion in a meeting.
  • An agreed import cadence and a visible data date on every report.
  • A test that makes a failed import loud. A report that shows yesterday's state after a failed import is worse than no report.

If you want such a path built on your TMS or ERP, I do it, from the read-access question through to the third number that holds up: contact. The start is small: a read-only account and the three numbers your dispatchers need every day.

Further sources

Questions?
Can Metabase not simply sit on the TMS database?+

It can, and for one day that is even the right answer. Metabase supports SQL Server officially from the oldest supported version up to the current one. The project's own documentation nevertheless recommends a replica or an analytics database for reporting, and it separates the querying connection from the writable one. That separation is exactly what our own import path takes over.

How often should the import run?+

As often as dispatch needs, and the answer has to appear on the report. A five-minute cycle brings a refresh delay that nobody wants at late dispatch, and an overnight run makes daytime decisions with yesterday's numbers. What decides it is a visible data date on each report and a test that makes a failed import loud.

Do we have legal duties when reporting on dispatchers and drivers?+

In Austria yes, and before the first dashboard. The Austrian DSFA-V regulation in force since BGBl. II No. 278/2018 requires a data protection impact assessment for processing that evaluates or classifies natural persons for purposes concerning their work performance and rests entirely on automated processing. The exception is a works agreement or the consent of the staff council. Anyone reporting on driver or dispatcher performance without that paperwork starts at the wrong end.