FSAE Telemetry Tracker

A SQL Server pipeline that turns a raw race-day logger file into per-driver, per-stint data the team can compare in Power BI.

DATABASE

T-SQLSQL ServerSSMS

REPORTING

Power BI

PROBLEM APPROACH OUTCOME

The Problem

Race-day telemetry comes off the car’s logger as one long CSV sampled at 20 Hz. The file has no label for who was driving, and it includes long stretches where the car is sitting still.

Drivers swap during a session, so one file mixes two drivers’ data with nothing marking where one stops and the next starts.

Idle time at the start, end and between runs sits inside the same file and skews every average.

Comparing drivers meant finding the swap points by hand and cutting the file apart before any analysis could begin.

Doing that by hand for each session made the results slow to produce and hard to repeat.

We needed a repeatable way to turn a raw logger file into clean, per-driver, per-stint data the whole team could compare on the track.

Approach

The pipeline loads a logger CSV into SQL Server, finds where the drivers swapped, labels every row with its driver and stint, and passes the labelled table to Power BI. Each step is a SQL script run in order, with written instructions for the team.

Pipeline: logger CSV to RawData, Detail, Staging, Runs, Change runs, Segments, Labelled and Power BI

Load the data

Import the CSV into the RawData table with the SQL Server Import Wizard in SSMS. Add a row to the Header table that describes the data, then run move_raw_to_detail.sql with that Header ID to move the rows into the Detail table.

Find the driver changes

Staging. Filter Detail to one Header ID and number the rows in time order. The script checks that the row counts match and rolls back if they don’t.

Runs. Trim the idle start and end of the session (GPS speed of 5 or less), then group consecutive rows into low-speed and moving runs.

Change runs. Keep the low-speed runs of at least 3000 samples. Those long stops are treated as driver swaps.

Label and report

Segments. Split the data into segments between the long stops. With no long stops, the whole session is one segment.

Labelling. Give each row a driver_id, a stint_id and an adjusted_time, in seconds from the start of its segment (0.05 s per sample for a 20 Hz logger). The result goes into the Labelled table that Power BI reads.

Labelling rule across three example segments

Each long stop closes a segment, and the next segment belongs to the other driver.

Outcome

The Power BI report reads the Labelled table and compares drivers side by side from a single log file. A summary table lists top speed, average speed, average throttle position, average motor speed, negative torque command time, and maximum lateral and longitudinal G. The charts are coloured by driver, with red for driver 1 and blue for driver 2.

Histograms of throttle input and motor speed, shown as the share of each driver’s samples so drivers with different amounts of driving time can be compared.

A brake pressure histogram and a traction circle, which plots lateral against longitudinal acceleration for each driver.

Line charts of module and motor temperature against time, one line per driver.

NEXT PROJECT

Ontario University Metrics

→

TORONTO, ONGITHUB · LINKEDIN · EMAIL