RaceReport web application

Presentation

The RaceReport web application started as a quick and dirty project. It was meant to be a proof of concept for an idea about deliver online race result during a kart race. By providing the result online during the day it was possible for friends and relatives to follow the race on distance. Like aunt Lisa who has sponsored part of the grandchild’s racing career.

The reception the first year was at best mild, but then it starts to gain in popularity and last season it seems to be indispensible. When the site took over the presentation and summing of race results from the SKCC series in 2007 it become even more important.

Data collection

All the result data where already present from the RaceTracker application used for administration of the kart race. RaceTracker handles generation of start and grid lists, collecting data from the TimeTracker timing system and printing of race results. I didn’t want to make any large changes to RaceTracker, because it is a critical application, so I just make RaceTracker save the race result as an xml data set at the same time as the race result are printed.

This result file is detected by the RaceUpload application and sent to the RaceReport web service. The upload is usually made by a mobile phone on GPRS so the data set was designed to be fairy sparse, less than 5k each.

Upload data handling

There are two types of upload data: race administrative data like driver names, race classes etc and result data. The administrative data is uploaded once and when the data change and contain local id for drivers and race classes. Those ids are used for mapping the race result data that don’t contain drivers name and race class names, just id.

Uploaded data are stored as raw data in upload tables in the database. When the web service receives the administrative data it generates the mapping tables used to map the driver id in the race result to the drivers result in the OLAP data base. Those mapping tables are used to prevent lookup during handling of race results.

Web server application diagram

Click to enlarge image

Transfer to OLAP

The actual result presentation data is stored in a star shaped OLAP database with dimension tables for race event, result set names, drivers etc and fact tables for driver position, lap times etc. Dimension tables are populated by the administrative data from RaceTracker and the fact tables are populated from the race result data. When the web service receives a result data set it will store the data in the upload database and then transfer the data into the OLAP database via the transfer service objects. Transfer service objects use the mapping tables to convert driver ids from RaceTracker into dimension ids in the OLAP database.

OLAP database

The reason for using OLAP architecture is that it enables reporting in all kinds of dimensions like what was the result for drivers from a club per class and year.

Driver id problem

In the first version I made a decision to not store the driver as a unique entity and just used a name dimension for the drivers. This decision was partly made because there was no way to identify the unique driver from data the race administration data. In a single race the driver is identified by start number and race class but over time they can change both class and start number. The drivers name is of course not usable as id, and some driver don’t know how to spell their name anyway.

This decision caused some problem when it comes to handle the SKCC result where the result was collected and summarized over several races. For that data I have to use start number and class and hope the drivers don’t change start number. That work fine until after SM 2008 when the drivers achieved their new start numbers and wanted to use them for the last race in SKCC! Well you can always make changes in the database by hand so it was fixed.

A new solution was needed and for the 2009 race season I will give each driver a unique GUID. This is possible thanks to Henrik Nilsson who handles the web appliance system that is used when driver apply to a racing event. Now the problem is how to convert existing precious data. At the moment I don’t have any good solution for that problem, but it would be really nice to be able to report statistics for drivers over several years so I will find a way.