1. Crash safety data landscape

A crash safety dashboard is only as trustworthy as the data feeding it. Before a single visual is placed on the canvas, the designer must understand where crash data originates, how it is structured, what its limitations are, and which metrics genuinely reflect occupant protection performance. This chapter maps that landscape so that every later design decision — from table relationships to DAX measures to visual selection — rests on a clear understanding of the underlying data.

Where Crash Safety Data Comes From

Crash safety data typically arrives from four distinct source families, each with its own granularity, latency, and bias profile.

• Regulatory and consumer test programs. Organizations such as NHTSA (US NCAP), Euro NCAP, IIHS, and equivalent bodies publish structured test results: frontal offset, side barrier, pole impact, pedestrian protection, and active safety scores. These datasets are comparatively clean, standardized, and small — often a few hundred to a few thousand records per year — but they represent controlled laboratory conditions rather than real-world outcomes.
• Police crash reports and national crash databases. Systems like FARS (Fatality Analysis Reporting System) and state-level crash repositories capture real-world collisions with fields for vehicle type, impact direction, restraint use, injury severity (KABCO scale), and environmental conditions. Volume is high, but reporting quality varies, and injury severity is often coded by non-medical personnel.
• Vehicle telematics and event data recorders (EDRs). Modern connected vehicles stream pre-crash speed, delta-V, seatbelt status, airbag deployment timing, and post-impact acceleration. These are high-frequency, high-fidelity signals but require careful handling of timestamps, sensor calibration, and privacy constraints.
• Insurance and claims data. Claims records link vehicle damage, repair cost, and bodily injury claims to specific VINs and crash configurations. They are commercially sensitive and often aggregated, but they provide the strongest bridge between physical damage and financial/medical outcomes.

In practice, a dashboard rarely uses a single source. The design challenge is to harmonize these sources around a common key — typically VIN, crash ID, or a composite of date, location, and vehicle model — while preserving the provenance of each metric so that users can distinguish a regulatory star rating from a field-observed injury rate.

Formats and Structural Characteristics

Source formats determine how much transformation work sits between raw ingestion and a usable semantic model. The table below summarizes the typical shape of each source family.

Two structural issues deserve early attention. First, grain mismatch: a crash-level table cannot be directly joined to a person-level injury table without fan-out, which inflates counts. Second, ordinal encoding: severity scales and star ratings are ordered categories, not interval measures. A dashboard that computes an average star rating across a fleet produces a number that looks precise but is statistically meaningless. The correct treatment is to count records per category and display distributions, or to map ordinal scales to a documented numeric score only when the mapping is explicitly justified.

Key Metrics That Belong on a Crash Safety Dashboard

Metric selection should follow the questions the dashboard must answer: How safe is the fleet? Where are the residual risks? Are interventions working? The following metrics form a practical core set.

• Fatality rate per 100,000 registered vehicles or per 100 million vehicle miles traveled (VMT). Normalizing by exposure is essential; raw fatality counts reward small fleets and penalize large ones.
• Injury severity distribution across KABCO or MAIS categories, expressed as counts and proportions rather than a single average.
• Delta-V distribution by impact direction (frontal, side, rear, rollover). Delta-V is the change in velocity during impact and is the single strongest physical predictor of occupant injury; displaying its distribution by direction reveals which crash modes dominate.
• Restraint and airbag effectiveness indicators — belt-use rate at time of crash, airbag deployment rate, and injury outcome cross-tabulated against restraint status.
• Crash configuration frequency — offset frontal, full frontal, side pole, side barrier — mapped against injury outcomes to identify under-protected configurations.
• Time-to-treatment or post-crash response metrics where telematics or EMS data permit, since outcome depends on response latency as well as crash severity.

From Source to Semantic Model: A Worked Example

Consider a fleet safety team combining three sources for a single dashboard: a regulatory test results file, a police crash extract, and a telematics event log. A reproducible preparation sequence looks like this:

-- Example: harmonize crash-level records to a common key
-- Source A: police_crashes (crash_id, vin, crash_date, impact_dir, kabco)
-- Source B: telematics_events (event_id, vin, event_ts, delta_v_kmh, belt_status)
-- Source C: ncap_results (test_id, model_year, make, model, frontal_stars, side_stars)

WITH crash_base AS (
    SELECT c.crash_id, c.vin, c.crash_date, c.impact_dir, c.kabco,
           t.delta_v_kmh, t.belt_status
    FROM police_crashes c
    LEFT JOIN telematics_events t
      ON c.vin = t.vin
     AND ABS(DATEDIFF('second', c.crash_date, t.event_ts)) <= 120
)
SELECT impact_dir,
       COUNT(*)                                   AS crash_count,
       SUM(CASE WHEN kabco IN ('K','A') THEN 1 ELSE 0 END) AS serious_or_fatal,
       AVG(delta_v_kmh)                           AS avg_delta_v,
       SUM(CASE WHEN belt_status = 'unbelted' THEN 1 ELSE 0 END) AS unbelted_count
FROM crash_base
GROUP BY impact_dir;

The join window of ±120 seconds is a deliberate design choice: telematics events are timestamped at the moment of impact detection, while police reports record an approximate crash time. Widening the window increases match rate but risks pairing an unrelated event. The expected output is one row per impact direction with crash counts, serious-or-fatal counts, average delta-V, and unbelted counts. A useful verification step is to check that the sum of crash_count across directions equals the count of distinct crash_id values in the base table; a mismatch indicates fan-out from multiple telematics events matching one crash, which must be resolved by selecting the highest-delta-V event per crash.

How the Data Landscape Shapes Dashboard Design

The figure below summarizes how the four source families converge into a semantic model and which metric families each source can credibly support. It is a design reference, not a pipeline diagram: it shows which metrics are defensible from which sources, so that visual placement and filter scope can be decided before development begins.

Read the figure as a coverage map. Exposure-normalized rates draw primarily on police and registration data; severity and delta-V metrics require the police–telematics join; protection-effectiveness metrics combine regulatory test results with field restraint data. A dashboard that attempts to show all three metric families from a single source will either omit fields or silently misrepresent them. Deciding coverage before building the model prevents the most common rework in crash safety reporting: discovering, after visuals are built, that a metric has no defensible denominator.

With sources, formats, and metrics mapped, the next design decisions — star schema shape, relationship cardinality, and measure definitions — become traceable engineering choices rather than guesses. The metric dictionary established here will be referenced repeatedly as the dashboard grows.