2. Data sources for crash safety

A crash safety dashboard is only as trustworthy as the data feeding it. Before a single DAX measure is written, you must understand what each source system actually records, how those records are keyed, and where they will disagree. This chapter examines the four primary source families — police crash reports, hospital and trauma records, vehicle telematics, and road inventory data — and then shows how to design a star schema and a data quality layer that survives contact with real-world data.

What each source actually contributes

Police crash reports are the anchor of most crash databases. A typical collision report (for example, the US MMUCC-compliant format or a national equivalent) provides a crash-level record with a unique report number, date, time, location (often a route and milepoint or a geocoded coordinate), crash configuration (head-on, angle, rear-end, rollover), posted speed limit, weather and light conditions, and vehicle- and person-level sub-records with injury severity coded on a scale such as the KABCO scale (Killed, A-injury, B-injury, C-injury, O — property damage only). Police data is broad and near-universal, but severity coding is performed by officers at the scene, not clinicians, and is known to under-report serious injuries relative to hospital data.

Hospital and trauma records supply the clinical truth. Emergency department and trauma registry extracts give you the Injury Severity Score (ISS), Maximum Abbreviated Injury Scale (MAIS), body region injured, length of stay, and discharge disposition. The critical join problem is that hospital records rarely contain the police report number; linkage usually requires probabilistic matching on date, time, location, age, and sex, or a deterministic match through an EMS run number where one exists. Expect a match rate in the 60–85% range for a well-tuned probabilistic linkage — and treat unmatched hospital records as a known coverage gap, not as noise to be deleted.

Vehicle telematics (connected-vehicle or aftermarket OBD/TCU feeds) contribute event-level kinematics: pre-impact speed, delta-v, principal direction of force, seatbelt status, airbag deployment, and post-event acceleration traces. Telematics is high-frequency and objective, but it is biased toward newer, connected vehicles and toward events severe enough to trigger an automatic collision notification. Its value in a dashboard is as a severity corroborator and as a source of exposure data (distance travelled, harsh-braking rates) for normalization.

Road inventory data describes the network itself: segment ID, functional class, lane count, curvature, grade, median type, shoulder width, lighting, and posted speed. This is your primary dimension source. It is also the source most likely to be stale — inventory updates lag construction by months — so segment geometry must be versioned with effective dates rather than overwritten.

Designing the schema

The natural shape for this domain is a star schema with a shared DimDate, a DimRoadSegment (slowly changing, versioned), a DimVehicle, a DimPerson, and a DimSeverity conformed across sources. Facts sit at different grains and must not be merged into one table: FactCrash at one row per crash, FactCasualty at one row per injured or killed person, and FactTelematicsEvent at one row per event. Keeping grains separate is what allows a single DimSeverity to reconcile KABCO and MAIS without forcing a lossy mapping at load time.

Data quality: the checks that matter

Four quality dimensions carry most of the risk in this domain. Completeness is the first: police reports frequently have null coordinates (rural crashes, geocoding failures), and telematics has near-zero coverage for older vehicles. Profile null rates per source per field per year before building anything, because a null rate that drifts from 4% to 30% across years will silently distort every trend line. Validity covers domain constraints: KABCO must be one of K/A/B/C/O, ISS must be 0–75, delta-v must be non-negative, and posted speed must fall in a plausible range. Consistency is where cross-source reconciliation lives — a crash coded "fatal" by police but with no hospital record, or a telematics event with delta-v above 60 km/h but a KABCO of "O", is a flag for review, not an automatic correction. Timeliness matters because police reports are amended for weeks after the event and hospital records arrive on a 30–90 day lag; your dashboard must state its data-as-of date and treat recent months as provisional.

A practical implementation pattern is a staging layer that applies these checks in a fixed order and writes a reason code for every rejected row. In Power Query, this looks like a validation step that adds a DQ_Status column rather than filtering rows out:

// Power Query M — validation flag, not a filter
let
    Source = Csv.Document(File.Contents("crash_reports_2023.csv"), [Delimiter=",", Encoding=65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(Promoted, {
        {"report_no", type text}, {"crash_date", type date},
        {"kabco", type text}, {"posted_speed_kmh", Int64.Type},
        {"latitude", type number}, {"longitude", type number}
    }),
    Flagged = Table.AddColumn(Typed, "DQ_Status", each
        if [report_no] = null then "REJECT_MISSING_KEY"
        else if not List.Contains({"K","A","B","C","O"}, [kabco]) then "REJECT_INVALID_SEVERITY"
        else if [posted_speed_kmh] <> null and ([posted_speed_kmh] < 10 or [posted_speed_kmh] > 160)
            then "REJECT_SPEED_OUT_OF_RANGE"
        else if [latitude] = null or [longitude] = null then "WARN_NO_GEOLOCATION"
        else "PASS", type text)
in
    Flagged

Rows flagged REJECT_* are routed to a quarantine table; WARN_* rows stay in the model but are excluded from map visuals and counted in a coverage KPI. This distinction is important: a crash without coordinates is still valid evidence of a crash, and deleting it would bias your severity statistics toward urban areas where geocoding succeeds.

Linkage and the severity bridge

The hardest design decision is reconciling KABCO with MAIS. Do not map them one-to-one — KABCO "A" (incapacitating injury) spans MAIS 2 through 5. Instead, build DimSeverity with both code sets as separate attributes and a documented crosswalk that records the mapping confidence. When a dashboard reports "serious injuries," define the measure explicitly, for example MAIS ≥ 3 where hospital data exists and KABCO ∈ {K, A} as the fallback, and expose the fallback share as a visible data quality indicator. Analysts who see that 38% of serious-injury counts rely on police coding alone will interpret the trend correctly.

With sources understood, grains separated, and a validation layer that preserves rejected rows, the model is ready for the DAX layer. The schema decisions made here — conformed dimensions, versioned road segments, and an explicit severity bridge — determine which measures are even expressible later, so treat this chapter's output as a contract rather than a preliminary step.