Reconstructed call load and on-duty skills from raw switch logs — fine grain to multi-year zoom, for France's largest emergency call platform

Every emergency call made in Paris and its three inner departments — 17 for police, 18 for fire and rescue, 112 for anything — lands on one platform. It sits in the fire brigade's Champerret barracks and it is the largest emergency call centre in France. Two services staff it side by side, firefighters and police, under the authority of the Préfecture de Police.

Its switchboard was an Alcatel system. It produced call tickets by the million. Nobody could see how many calls were arriving, nor how many operators were actually on duty for each number.

Others had tried before me and had not landed a usable series. That is the honest reason this counted as a result rather than a report.

What a call ticket actually is

A ticket from a telephony switch is a row about a technical event: a line, an agent code, a set of timestamps, a disposition code. It is written for billing and for the switch's own bookkeeping, not for the question you want to ask. The codes point at reference data — what an agent is, what a level is, what "transferred" means when the transfer is internal — that lives in nobody's model. It exists in the heads of the people who run the platform, and in the switch's configuration, and those two are not always the same thing.

So you cannot answer, from the raw tickets, questions as basic as: how does call load vary through the day? How many operators are covering 18 right now? How long does a caller wait before someone picks up, and how many hang up first? On a platform where a queued call can be a life, those were being decided by experience rather than by measurement.

Three layers, because a ticket is not a call

The switch describes equipment. The questions are about service. Closing that gap took three preprocessing layers, each materialised rather than computed on read:

1. Raw events — one row per switch message, loaded as-is, so the source is never re-interrogated.
2. Calls — stitched back together from the sequences of messages that describe them. A call is not one ticket; it is a sequence, and the interesting cases live at the edges: transfers, abandons, re-presentations, a caller who dials 17 and needs 18.
3. Load and presence — call load and operator presence resolved per emergency number. Presence is the hard one: "present for 18" is a state, inferred from logins and skill assignments over time, not a field that exists anywhere in the source.

Materialising rather than computing on read was deliberate. A query that has to stitch a call back together from equipment messages every time it runs is a query nobody runs twice.

Making years of telephony zoomable

A dispatch centre does not want a monthly report. It wants to look at last Saturday night at the minute, then pull back to the year, then dive into New Year's Eve — without waiting.

Raw events cannot drive that. The point count defeats the wire and the browser long before it defeats the database. So every measure was pre-aggregated at several time grains, and the chart asks for the window it is showing while the server returns the grain that window deserves. It is the pattern behind a lazy-loading stock chart, applied to emergency telephony: the cost moves from every render to one load, once.

flowchart LR
    SW["Alcatel switchboard<br/>lines 17 · 18 · 112"] --> EX["Ticket Extractor<br/>raw call tickets"]
    EX --> L1["Layer 1 — raw events<br/>one row per switch message"]
    L1 --> L2["Layer 2 — calls<br/>stitched from message sequences<br/>transfers · abandons · re-presentations"]
    L2 --> L3["Layer 3 — load and presence<br/>per emergency number"]
    MDS["Master Data Services<br/>agents · lines · levels · outcomes"] --> L2
    MDS --> L3
    L3 --> R[("Multi-grain rollups<br/>one pre-aggregate per time grain")]
    R --> V{{"Chart asks for the visible window<br/>server returns the matching grain"}}
    V --> C["Interactive views for the dispatch centre<br/>minute to multi-year, no wait"]
    L3 --> BI["Power BI<br/>analyst reporting"]
    L3 --> EXT["Consolidated extracts<br/>for external research"]

Power BI served the analysts. The zoomable views went in front of the dispatch centre itself — the audience that had never had them.

Reference data before reporting

One decision underpins all of it, and it looks like a delay if you are waiting for a dashboard.

I put Master Data Services in front of the reporting layer and governed the telephony reference data first: agents, lines, levels, call outcomes, the mapping between what the switch calls a thing and what the platform calls it. Only then did the SSIS flows load against those definitions.

The argument is not tidiness. Reports built before the definitions are fixed get rebuilt every time two people disagree about what a number means — and on a joint police/fire platform, two people disagreeing about what a number means is the default state, not the exception. Governing the semantics once is what stops the reporting layer from being permanently re-litigated.

It also had a consequence I did not design for.

An outside audit I did not plan

A research team from INRIA and the CMAP at École polytechnique was running a quantitative performance evaluation of the platform, under a programme of the Préfecture de Police and the Ministry of the Interior's IHEMI. They needed the telephony data — not a report, the data — consolidated enough to build four years of doctoral work on.

I handled that extraction: a full year of emergency calls, 26 December 2018 to 5 January 2020, the bounds deliberately set to include New Year's Eve, the single most informative night of the year for a Paris emergency platform and the one a calendar-year extract cuts in half.

The resulting thesis — Marin Boyet, Piecewise affine dynamical systems applied to the performance evaluation of emergency call centers, École polytechnique, May 2022 — names me in its acknowledgements for the extraction. Its verdict on the data, written by people with no reason to be generous about someone else's plumbing:

This analysis beneficiated from a huge amount of very precise data, which brought a whole development in itself. Having a complete year of emergency calls and consolidated datasets gives to this study a very high degree of confidence.

The second-level fire-brigade analysis alone runs on 811,429 cases. Agent tags being unique and stable in the model is what let the researchers reconstruct real-time staffing levels from the call records — a use nobody specified in advance, and exactly what a governed reference layer makes possible and an ungoverned one makes impossible.

What the data turned out to hold

The findings are the researchers', not mine. They belong here because they are the measure of what the extract could carry.

What I take from it

The usual test of a warehouse is whether it feeds your own dashboards. That test is too easy: you are both builder and judge, and you know what every column means.

The harder test is whether someone outside your organisation, with no access to your context and no ability to phone you every afternoon, can build on the data and defend conclusions in front of a doctoral jury. That is an audit you cannot talk your way through. The extract held up precisely because it did not need me attached to it.

Honest limits: this was a warehouse over one source system, not a distributed estate; the volumes are large by emergency-services standards and modest by internet standards; and the platform's second level on the police side ran on a different system, so the model covers the fire-brigade side of level 2 and both sides of level 1. What transfers is the part that is not about telephony at all — that a source built for billing can be turned into a source fit for research, and that most of the work which makes that true happens before anyone sees a chart.